SQL Server Performance Forum – Threads Archive

Backup of Secindary server

Hi, Is it possible to backup the databases on secondary server while the logshipping is active? We use Veritas backup Exec to backup. Thanks
AKM

Yes you can take backup of STANDBY database and use Veritas to backup that file. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi satya, Is it safe backing up the database using Enterprise Manager and then backup the file using Veritas backup? Log shipping is scheduled to run between 4am to 8pm. I have scheduled the database backup(Using Enterprise manager) at 10pm. Thanks AKM

Yes, just make sure Veritas ignores the active database files and backs up the backup files.

As you’ve schedule the backup it is managed by SQLAgent and need not to worry how it is going to process. As suggested just choose Veritas to take care of backup file and not any other files, I hope the backup will be stored on a seperate drive and directory. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya and Mulhall.

Hi Satya, I had scheduled the backup of database on standby system last night and it failed.
The error is as follows: Executed as user: Domain<sid>adm. Database ‘SID’ is in warm-standby state (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence is completed. [SQLSTATE 42000] (Error 3036) BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed. When I look at the error 3036, it says – "Your standby database has not been recovered and may not be backed up. Usually, you recover this database only if your primary fails and you switch operations to the standby. Until this occurs, rely on backups taken from the primary server." I am not sure if we could backup the database on standby system. Any suggestions. –AKM

AKM
Apologies for forgetting about a bit of process on secondary database to take full backup, this involves breakup of log shipping process. The reason behind this is :
The Log Shipping process leaves the database on the secondary server in either a STANDBY or a LOADING status depending upon which option you select when you configure Log Shipping. Both STANDBY and LOADING are read-only states, which prevents any writes to the transaction log. Because the backup process needs to log its operations (backup history), and changes to the transaction log are disallowed, SQL Server does not allow you to perform backups performed against a log shipping secondary database. At my end we follow the process as:
– Apply last log shipping backup to secondary database using WITH RECOVERY clause
– Then perform full database backup on secondary server
– Then before log shipping starts, a backup sync. process between primary and secondary server will take care of backups. HTH Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Hi Satya, I have configurred Log Shipping using Enterprise Manager’ Database Maintenance Planner. I am not sure if I could restore the last T-log in recovery mode, backup the secondary database and backup sysnc process between primary and secondary before log shipping starts at 4am. Any ideas? –AKM

What is the interval of log shipping for these maintenance plans?
In your case if the veritas can look up for primary server and then choose the full database backup from this server and leave secondary server as is. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Interval is 30 minutes. We backup primary server using veritas backup exec. We were trying to backup the secondary server using Enterprise Manager without making any changes in Logshpping. Thanks
Amrendra

As you’re performig Veritas backup from primary then why to backup secondary server database as well, as the databases are in sync in between. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Just to be safer side for secondary server. I think we will have to rely on the backup of Primary server. Thanks
AKM

If you have secured the primary server backup then need not worry about secondary server, otherwise you must have to break the log shipping process in order to accomplish your task. Otherwise don’t worry much about it and ensure to maintain similar versions of SQL & OS in between them. Satya SKJ
Moderator
http://www.SQL-Server-Performance.Com/forum
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Thanks Satya. –AKM

]]>