SQL Server Performance Forum – Threads Archive

Backing up a log shipping destination database

I have two database servers separated over a WAN. The primary is log shipping to the secondary. On the secondary (failover) server, there are tesing and training instances of the primary database. The database is approx 20Gb in size. Is there any way of backing up and restoring /copying the read only /stand by log shipped database destination instance to the test and training instances on the same server. This would avoid a large transfer over the WAN to populate the test and train databases.

You should be able to backup the read-only database on secondary server. 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.

No, on trying to setup a backup process, SQL tells me that it is not available for backup a message saying that "the database is in warm-standby mode (set by executing RESTORE WITH STANDBY) and cannot be backed up until the entire load sequence uis completed". This status is ideal for us to ensure the integrity of the log shipped destination database and will ensure that users can’t accidentally be accessing the production backup copy when being directed to test and training databases on that server. Apparently it is standard for this to be the case. I am hoping that there is a third party backup utility or some work around for this.

One of the MSDN article refers: A warm standby server can be brought up in read-only mode between transaction log restore operations if an undo file is used.
Expect different results as compared to earlier versions of SQL Server. Use an undo file for a warm standby server using the STANDBY clause of RESTORE, as shown in the following example. B. Restore a database using the STANDBY clause and an undo file This example brings the server up to allow write operations on the databases by using a final, necessary RESTORE statement. RESTORE DATABASE mydatabase
FROM mydb_backup
WITH NORECOVERY RESTORE LOG mydb
FROM mydb_log1
WITH RECOVERY STANDBY = ‘c:mssqldatamydbundo.dat’ RESTORE LOG mydb
FROM mydb_log2
WITH RECOVERY STANDBY = ‘c:mssqldatamydbundo.dat’ RESTORE DATABASE mydb
WITH RECOVERY 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.

I have a similar question…wonder if it is even a good idea to do this… I want to set up a database maintenence plan on the warm standby to backup the read only databases daily, with hourly incrementals. My reasoning is that I want the ability to recover the warm standby in an emergency, but it is not practical to do this from a backup file of 6 months ago and a series of 15-minute transaction log backups that are used for the log shipping. I tried setting this up and the tasks failed. Is there a better way to do this? For example, if the log shipping plan itself could simply do full backups every night, I could just save those files for 24-hours or so to have what I need. That would also help keep log shipping from breaking as it often does, by allowing it to re-initialize itself from a full backup. Any tips would be appreciated…Thanks!

It looks like you do have a similar situation. We do take a nightly backup of the primary database whilst log shipping all day every 15 minutes to the secondary database. Normally, I would be able to use this backup for my copy to populate other testing and training databases…but there is 100 miles and a 10Mbps WAN link that gets choked up during production time that prohibits me copying the daily backups to the secondary end. If your secondary (log shipped) database exists on the same LAN link, there is no reason you can’t create a backup from the primary database on a once per day basis as we do. Backup the database and transaction logs.

Hi Brian. Did you find a solution? We have exactly the same requirement. At the moment our warm standby in over the wan at a data centre and our main server local.
We backup the main server to disk at night and restore to our two test servers. We would like to put our main server at the data centre and the standby on our lan.
Same issue. Have you found a way to take a copy of a database in read only standby mode? Regards
Paul Cahill

Why not use the full database backup from the primary server and not to disturb the secondary which works as hot standby. Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

That’s what we do now.
We have our live server on site and our warm standby in a rack at an external data center (run by Telecity/Verizon).
We ship the logs over the wan to the data centre using sql litespeed compression. However we have three other remote sites that access our live server.
Therefore it makes sense to move our live server to the data centre and get the warm standby in house.
ie If we have a fire here then our remote sites can continue to trade.
Also if our main server fails we have local access to get the warm standby up and running. With this scenario we would have to restore our development and reporting servers over the wan.
Database is about 80GB. We could buy litespeed licences for our dev and reporting boxes and that would help a bit but if we could somehow get a copy of the warm standby database it would be great. We stop log shipping between midnight and 2:30 am during the disk and tape backups.
A perfect window to get at the warm standby database. Paul

True as long as the copy process is taking care during the less traffic hours, I don’t see any issues in copying the files. I believe you can take advantage of involving multiple secondary servers in log shipping. Be aware about the points referred in the following KBAs:
http://support.microsoft.com/kb/314515
http://support.microsoft.com/kb/300497/
http://support.microsoft.com/default.aspx?scid=kb;en-us;321247&sd=tech – configure multiple servers Satya SKJ
Contributing Editor & Forums Moderator
http://www.SQL-Server-Performance.Com
This posting is provided â€œAS ISâ€ with no rights for the sake of knowledge sharing.

Any updated solution on this? I have similar requirement to backup the standby databases in my office to tape, because our live hosting site is charging exhorbitant prices for tape handling. What I do now is stop the standby sql server for a couple of hours, backup the mdf and ldf to tape, and startup again to continue the logshipping schedule. In the event I need to recover a database, say from one month ago, I will restore the mdf/ldf from tape to a different location, and use sp_attach_db to restore the database to a different name. sql123

I tried to set up logshipping in stand by mode and not able to backup the database from the stand by server.We are looking for for options that reduce the load from the live server.Any input to achive this ?
Thanks in advance

Also one more point to keep in mind that we have the plan of taking backup two times from the
stand by server ,before a process is run and after the process is done
Thanks a lot

Hwo big is the database on primary server?
What kind of problems you are getting when BACKUP is performed?

The database is financial and is around 120 gb in size .

Not a big one then, how about the backup strategy: FULL, DIFFERENTIAL and TRANSACTION Log backups for this database.
Also confirm what kind of issues you are getting while the backup is perfomed.

Thanks satya for updating
As per the top mgmt we need to take the full backup before and after the process and it slows down the access over the internet.
Also when we do the backup we are updateing the statistics and doing the checkdb every day.
Thanks

Ok, I mean to say do you see any disk related issues or slow down on SQL SErver when the FULL database backups are performed.
I believe if you can set the process right you don’t need to perform full database backups before and after.

I have gotten around this in a less than optimal way. On your secondary server shut down the sql process. Then copy the MDF and optionally the LDFs of the secondary server dbs. Upon restarting SQL server the database will continue log shipping. You can then attach the copied MDF files as a new db. Then you can do whatever you want with them. Not a great solution, but in some cases it will work. Optionally you might consider setting up a SQL instance for each of these that you log ship to. That way the server is simply handling this one database and shutting it down does not effect other databases. I do see this as a major oversight when the "copy only" feature was added to "backup database". There is no real reason why SQL server couldn’t support this operation with some limitations. Even if the backup didn’t allow differentials and transaction log backups to be restored after the fact this could be useful in many QA situations to get much quicker nightly restores, especially across WAN links and in the cloud. It does seem like transaction logs should be restorable against this backup.

Twillerror, Welcome to the forums. Thanks for your collaboration.
Last post, in this thread, is one year old. Hope help other members anyway.

]]>