The objective is to have a backup of a relatively large (30gb) database that can be restored rapidly, with a very narrow window of potential data loss, as this database is in use 24x7x365. This should all be documented in such a manner that we can assume the entire IT team was beamed up by aliens and the janitor is responsible for bringing the database back up.
There is much helpful information on this site. I've read enough now that I've started to second guess the plan. What I've outlined below seems to work just fine, but I'm sure someone can advise a better way. Database mirroring is coming, but not until the first part of next year.
We have 'ServerA' which is running the database, and 'ServerB', which will be the backup target. ServerB is maintained at the same patch level, etc as ServerA. Both servers are 64bit Windows.
On ServerB there is a batch file that uses DBBACKUP to create a full backup. For now it runs once a week. It's a pretty straight forward commnand:
Another batch file runs every 30 minutes to grab the transaction log
Should we need to use the backup -- which implies that all three of my VMWare HA hosts are down and the redundant iSCSI sans have failed:
asked 02 Sep '14, 16:19
Aside from the discussion whether a live backup may be helpful or not, I would strongly recommend to have a copy of the previous backup available, either by copying the backup files to a second location or by using different backup folders in round-robin fashion (aka "backup generations"). The reason is that your current plan seems to use only one backup location for the full database backup and the incremental log, so in case the machine would somehow "crash" during the log backup you might have both the active log and the backup log corrupted, and the previous backup log file would already have been overwritten...
You might have a look at Breck's articles on that topic, such as Rotating Database Backups Revisited.
In case you happen to have more then one log backup for the last full backup, and those log backups have different names (say, by using DBBACKUP -n or one of the "rename log" variants), you can simply the following steps:
3. apply the transaction log from the full backup DBSRV16 mydatabase.db -a mydatabase.log 4. Exercise patience until the server shuts down 5. Copy mydatabase.log from c:\backup\incr 6. Repeat steps 3 & 4
by using the -ad or -ar database switch to apply the all required transaction logs in one step. Note: That would not work currently as (in my understanding) the log from the full backup and from the incremental backup has the same name, so they cannot exist in the same directory.
Switch -as might be helpful if you want to have the database server available after recovery without a database restart.
answered 03 Sep '14, 03:49
> a very narrow window of potential data loss, as this database is in use 24x7x365
> entire IT team was beamed up by aliens and the janitor is responsible for bringing the database back up.
Those two requirements are reasonable for a high availability setup, not backup and recovery... not even live backup. Experience has shown that backup is easy but recovery is hard because you can never predict exactly what the problem's going to be.
You always need a backup and recovery plan, and it should be tested, but don't expect it to provide high availability... for that you need... wait for it :)... high availability.
FWIW a dbbackup -l live backup command can be wrapped in a GOTO start loop that repeatedly runs the command if it fails. The usual cause for failure is loss of contact with the master database server... if it is intermittent then the GOTO loop will let dbbackup reconnect and continue on... if the loss of contact is permanent then the GOTO loop will continue until you kill it, which you will probably do because it's time to start the secondary server and apply the live log.
All that live backup stuff is possible, but for V16 it's the Amish way of doing high availability... no offense to the Amish, or to live backup, or SQL Remote, or any of The Old Ways... supported by all but embraced by few.
Remember that when the live backup is used for failover you have to (a) start the second server and (b) apply the log... and if it's been a while since a full backup has been taken, applying the live log may be time consuming.
Plus, it's harder and more time consuming to practice a live log failover than an HA failover. Janitor-ready documentation is a real challenge, far beyond my abilities (I tried, with V9, failed).