July 7, 2003 at 8:08 am
Hello
We have 2 servers. Server A is the production box, Server B is the Standy server. We have established log shipping between A and B. Is it possible to take full database backup on the standby server B?
Thanks
Taiyib
July 7, 2003 at 9:03 am
You can't back up a database that is in STANDBY or NORECOVER mode. Log shipping will leave the database at the standby server in one of these 2 modes.
July 7, 2003 at 10:31 am
Thanks for replying ianscarlett.
But I read a document in SQL Server 2000 Resourec kit, which says the following:
Since the log shipped database is only a small increment of time out of sync with the primary database, it is a good idea to use it to generate the full database backups. This would keep the primary production server free of the potential overhead and contention incurred by a daily (or scheduled) backup.
But it does not tell , how to take database backup of the Standby server.
Thanks
Taiyib
July 8, 2003 at 8:15 am
Tricky. You cannot backup the log-shipped database, because it is literally in the middle of the process of being restored. I half-way suspect they wrote that without thinking it through very far...
One tricky and awkward option would be to do the log shipping routine, then stop SQL Server, and make a backup/copy of the restored .mdf. If you ever needed to make a given backed-up instance live, you'd stop SQL Server, overwrite the current .mdf with the desired target/saved .mdf, restart SQL Server, and "activate" the standby database.
There are a number of reasons this wouldn't work too well. First off, you could only do it once; you'd have to restart the whole log shipping process to do it again. Secondly, there may well be values or statistics in the Master database (let alone the log shipping records in MSDB) pertaining to the log shipped database that would act up should you flip files in this fashion. Lastly, as this isn't what anyone ever intended you to do, there's other and even more esoteric stuff that could blow up.
Which leads to a very tricky and very awkward process that would work: do the log shipping routine, shut down SQL Server, and make copies of all the database files--and particularly Master and MSDB. Then if you need to restore a prior version of the log-shipped database, stop SQL Server and overwrite all the existing databases with the set from the target date. This would and can work (and you can even do this to a different server, though that can be three times as complex); even as I wrote this it just seems kind of stupid... but then it reminded me of the real plan I came up with a while back that we may yet use for other purposes.
The real plan:
-- do log shipping
-- stop SQL Server
-- make copies of all database files [A]
-- start SQL server
-- "activate" log shipped database
-- perform "real" backup on it
-- stop SQL server
-- blow away databases and replace with the files from [A] above
-- start SQL Server
-- repeat next day
This is automatable but complex, and if done improperly or if something fails you're dead in the water. I suspect it's far too complex to be used on a regular basis, but it's an interesting intellectual exercise and could be used in critical or emergency situations.
Philip
July 8, 2003 at 8:47 am
It looks like there is no easy way of taking the backups on the Standby server. Thanks
March 1, 2006 at 8:46 am
Hi everybody,
We have a DR site about 1000km away from our primary site, so full backups of the databases can' be shipped on WAN (size @ 13gb). Hence I want to take backup on standby server. We have configured log shipping. Is it possible to stop log shipping for some time, open standby database in normal mode, take a backup, again make it standby & start log shipping. Please advice on the situation.
March 1, 2006 at 9:13 am
Abhijeet,
Please do not 'hijack' other people's posts.
Proper practice:
1. start your own thread.
2. do not 'hijack' other threads.
3. pay attention to when a thread was started...this one is over 3 years old.
4. don't post the same question multiple times.
-SQLBill
Viewing 7 posts - 1 through 6 (of 6 total)
You must be logged in to reply to this topic. Login to reply