August 13, 2009 at 9:31 am
For the first time since we've gone live with our SQL 2005 database I have tried to simulate a crash recovery. We need to restore our live database on top of our development database to actualize the data so I thought it would be a nice occasion to try a normal procedure by restoring the most recent Full Backup and then all the transaction logs backups.
So I am trying to restore Database1 on top of Database2. Both databases are set to Full Recovery model and our backups procedures are as follows.
Full Database Backup every day at 6:00PM
Transaction Logs Backups every 2 hours from 1:00AM to 11:00PM using "WITH NO INIT", all backup sets are in the same file.
Differential Backup every day at 12:00PM (To make recovery faster should we have to restore in the afternoon.)
My understanding is that I need to restore my most current full backup leaving the database in norecovery mode, then apply all the transaction logs backup in sequence still in norecovery mode and then recover the database to complete the process.
I've been trying that for 2 hours now and so far I have failed miserably :laugh:
Here's what I did in sequence, I'm using SQL Server Management Studio
Step 1: Restored the Full Backup. I right-clicked on Database2 and selected Task->Restore->Database.
To Database: I selected Database2
From Database: I chose Database1
Select backup sets to restore: I unchecked everything except my Full Backup (Dated yesterday 6:00PM)
Then in "Options" I checked "Overwrite existing database", in "Restore database files as:" I changed the "Restore as:" names to those of Database2 and set "recovery state" to "Leave the database non-operational...................(RESTORE WITH NORECOVERY)".
That worked, my restore went through and the database stayed in "Restoring" mode.
Step 2: Tried restoring the transaction logs backups. I right-clicked on Database2 and selected Task->Restore->Transaction Log
Database: Database2
From previous backups of Database: Database1
Select transaction log backups to restore: I selected the first one in the list.
Options: "Leave the database....................(NORECOVERY)".
On my first attempt I had all the transaction log backups selected and got the same message. Then I tried de-selected them one at a time from bottom to top until I had only the first one selected and I'm still getting this.
Why am I getting an error about the backup sets beginning at LSN 33934 when that backup set isn't selected. It says I need a backup set that includes LSN 33742xxxxxxx which is clearly included in the set I have chosen.
August 13, 2009 at 1:58 pm
Hi, the screenshot looks right but seems suspect that it states what you are trying to restore just happens to be the last log backup in set.
Try restoring it manually and if it still fails then post the command and result please.
August 13, 2009 at 4:28 pm
I think I know what is going on here - but before I say anything on that, please confirm that you are in fact backing up to a device (single file) and using NOINIT, and at no time during the process are you re-initializing the file (WITH INIT).
My guess is that you have re-initialized the log backup file and wiped out your previous transaction log backups. Now, you are trying to restore from a previous backup file using the 'current' transaction log backup device which no longer contains the right log backups.
This is exactly why I do not recommend ever using backup devices. In almost every case, the backup device is initialized and the previous backups thrown away. A good example:
- Backup database (6pm) to backup device (initialize file)
- Backup tlogs every hour after that (noinit)
Now, as soon as the full backup occurs again - the previous full backup is gone, as well as all of the transaction log backups taken right up to the point where you initialized the device (file).
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2009 at 4:50 pm
Jeffrey you put your finger right on it. I was just copying/pasting my backup commands here and saw it.
BACKUP LOG [Database1] TO [Database1Log] WITH INIT , NOUNLOAD , NAME = N'Database1 Log Backup', NOSKIP , STATS = 10, NOFORMAT
I modified by backup plan recently to start running an index defrag job every night instead of once a week and didn't realize I was screwing up my transaction log backups. I must have checked 5 times today but just didn't see it until now.
I'm a bit puzzled now though. How/When do you reinitialize the transaction log backup file ? I can't run backups with NOINIT forever, the file needs to be reset at some point otherwise it's gonna grow forever ?
The only thing I can think of is running a transaction log backup with INIT juste before my full backup but somehow it doesn't sound like "best practice" method. There has to be a more elegant way of handling transaction log backups.
August 13, 2009 at 5:27 pm
In my post above I forgot to mention that I think I wasn't clear in my first post. I do not keep my full backups and transaction log backups in the same file.
I have one backup file for the full and differential backup, the full backup runs with init and the diff. backup runs with noinit.
I have another file for my transaction log backups which because of my mistake was "init'ed" every time.
I obviously need to reinitalize the transaction log backup file once a day so now I'm thinking the simplest way is probably to change my full backup job to perform a transaction log backup with init before.
backup log database1 to database1logbackup with init
backup database database1 to database1backup with init
Then my 12:00pm differential backup will be appended to database1backup and the transaction log backups will be appended every 2 hours to database1logbackup until it's reinitialized by the daily full backup.
August 13, 2009 at 5:28 pm
The solution is rather simple - don't use backup devices. Change the plans to use a file and let SQL Server generate the file name. When you do that, the file is generated with a name like:
{database name}_backup_YYYYMMDDHHSSMM.bak - full and diff backups
{database name}_backup_YYYYMMDDHHSSMM.trn - transaction log backups
You setup the plan so it uses the same folder for the full and the transaction logs - and check the box to create a directory for each database.
Then, add two Maintenance Plan Cleanup tasks to your daily full backup sub plan. The first one will remove any backup (bak) files older than xx days/hours/minutes (I have a custom procedure I use, but you can use 25 hours to keep the current backup and the previous backup available online). The second task will be setup to remove your transaction log backups (trn), and again it is by the setting older than xx days/hours/minutes. Note, the issue here is to make sure you account for how long the rest of the plan takes before the cleanup tasks are run. If not, you will delete files you don't expect - for example, if you set it to 1 day, and tomorrow the plan takes longer - the cleanup task will run later in the morning deleting your previous days backup files.
The above manages the backups as individual files in a folder instead of a single file - and is much easier to manage.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 13, 2009 at 5:35 pm
Thanks a lot Jeffrey. I'll have to read that again tomorrow morning and look into it. I like the idea of keeping more than one full backup at a time.
Until now it's been an issue because of our daily disk to tape backups which pick up the sql server backup files for storage outside.
Your solution would pretty much fix all my problems AND give me the possibility to return to a 2 days old backup with losing 3 hours restoring a 20G backup file from tape.
Thanks again.
August 13, 2009 at 5:41 pm
eric (8/13/2009)
Thanks a lot Jeffrey. I'll have to read that again tomorrow morning and look into it. I like the idea of keeping more than one full backup at a time.Until now it's been an issue because of our daily disk to tape backups which pick up the sql server backup files for storage outside.
Your solution would pretty much fix all my problems AND give me the possibility to return to a 2 days old backup with losing 3 hours restoring a 20G backup file from tape.
Thanks again.
Glad I could help - now, the other thing you might want to look into is something like Hyperbac or Litespeed to compress and speed up your backups/restores. Just something we do on our systems that helps out a lot.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
August 14, 2009 at 9:35 am
I don't really have a SQL Maintenance plan for my backups. I've created my own backup plan and implemented it with a bunch of jobs. I have 10 databases ranging in size from less than 100 Megs to 12 GB (8 GB actually used) and I've created three jobs for each.
Job 1: Full Backup
Job 2: Differential Backup
Job 3: Transaction Log backup.
This morning I fixed all my Transaction Log backup jobs to stop resetting the backup file and in each of my Full Backup jobs I added a 2nd step to perform a transaction log backup with init. That should take care of my restore problem, I'll test again in 24 hours.
I'm planning on changing all these to a backup Maintenance Plan to get things cleaner but I'll have to do some searching to see what's the best way of doing it. Is it best to create only one plan with many sub-plans each having their own schedule, or is it preferable to create separate maintenance plans for the Full, Differential and Transaction Log backups ?
I have to be careful with how many days I keep my full backups because we're already tight in space with our disc to tape backups and I'm not we can double the amount of backup data we keep.
I downloaded the trial version of ListeSpeed and tested it on my largest database, with a compression setting at 6 I got a backup file less than 500 Megs as opposed to my regular 8 GB file. Unfortunately I am afraid the cost of these backup solutions will be prohibitive for us and I doubt it will be approve.
There it is, I just thought I'd come back and post the final steps I performed in case it could be useful to other inexperienced self-trained self-teaching dba's like me.
Jeffrey and Steve, thanks again for your help !
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply