January 14, 2009 at 4:05 pm
For a SQL Server 2000 database I want to verify that the three jobs I've set will provide what is necessary for a restore.
The set of jobs has a full backup run on Sunday night, differentials run each night Mon - Sat and the 3rd job runs a transaction log backup hourly during the work days. If I needed to restore to some point in the day, in the middle of the week, would the way these backups are created allow for that? Before the full backup and before each differential I am running an integrity check against the database. Do I ever have to run a log backup with 'init' ? Thank you much for any assistance.
Job One :
Backup Database WebDB To WebDB_D1, WebDB_D2 with init,checksum, name='WebDB-Full'
Job Two:
Backup Database WebDB To WebDB_D1, WebDB_D2 with noinit, differential, checksum, name='WebDB-Differential'
Job Three:
BACKUP LOG WebDB TO WebLogD WITH NOINIT, NAME = 'WebDB-Log'
January 15, 2009 at 1:52 pm
I don't know what you mean by
Backup Database WebDB To WebDB_D1, WebDB_D2
But the idea is the right one, it should work
We have the same backup policy here
Note though, one hour trans log backup = maximum loss of 1 hour's data, if that's acceptable
Otherwise shorten it to 15 or 30 minutes, whichever is accepted by business
January 15, 2009 at 3:58 pm
Thank you for your reply. The part you questioned is one of the databases that we backup up to multiple devices instead of one. On the more important databases the log backups run every 20 minutes.
I had wondered if it was correct [allowed] to have the 'NAME' be different on the three jobs. The documentation/terminology for keeping a 'backup set' is not clear to me. I am not sure if it is the device that is used in the full and differential backups that makes it a 'set' or if there is another way that it is tied together.
Did you understand my question about the transaction log backups? I am a little confused as to when the Transaction Log backup gets restarted to correspond to the full backup --- or perhaps it does not and is not supposed to.
In other relational databases it was common to take a checkpoint and switch to a new log before starting a full or differential backup but I see no reference to that for SQL Server.
I appreciate any insight to understanding the methodology of the SQL Server backup sets.
Thank you.
January 15, 2009 at 4:39 pm
The name doesn't matter. It's up to you and there to make it easier for you to understand what is in the backup.
Just as an aside, the restore sequence is full, last diff only (in chronological order) and then all logs since the last diff.
The log manages itself. The full and diff will record the last LSN number that is in there and when you go to restore a log, it will start at the correct point in the log. When you do log backups, you free up log space, and mark committed transactions as complete, so you need to be sure that you preserve those log backups. If you miss one, you can't restore past the last point in time of the previous log file backup.
January 16, 2009 at 12:19 pm
Thank you. On another note, is there a prescribed way to prune the tables that contain the backup data? It looks like these tables have never been purged of old data.
If I set an expiration of a certain number of days for the backup using either RETAINDAYS = or EXPIREDATE = , will that keep the backupset table from retaining old data?
January 16, 2009 at 7:37 pm
Search for sp_delete_backuphistory stored procedure under books online and you will get your answer regarding backup tables pruning.
MJ
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply