Error doing restore of full/differential backup
-
I am running the following script to attempt a restore of a differential backup:
RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'
WITH
NORECOVERY
GO
RESTORE DATABASE AdventureWorks
FROM DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH RECOVERY
GO
I thought this was the way to do it. It does restore the full backup, but on the attempt to restore the differential backup, I get the following error:
Msg 3136, Level 16, State 1, Line 1
This differential backup cannot be restored because the database has not been restored to the correct earlier state.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Does anyone know what this means? Do I have to use "with recovery" on the first restore? (The sample I took this from used "with norecovery")
The original backups were done with SQL Agent scheduled jobs. The script for the full backup is:
BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorks.bak'
The script for the differential backup is:
BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT
All I can say is, it's a good thing I am testing this out with non-critical data, because I obviously don't know what I am doing. I can't access the AdventureWorks database now. Can anyone help?
Thanks,
Cynthia
-
Cynthia,
NORECOVERY should be used to do a point in time restore using transaction log backup.
Hope this helps
Thanks
Sreejith
-
So ... I should do WITH RECOVERY for both full and differential?
I just tried it -- now I get this error:
Msg 3117, Level 16, State 1, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I guess my question really is -- how do I restore from a full backup and a differential backup that succeeds it?
-
I discovered the problem. In my differential backup
BACKUP DATABASE AdventureWorks
TO DISK='C:\SQL2005_Backups\AutoBackups\AdventureWorksDiff.bak'
WITH DIFFERENTIAL, INIT
I should not be using "INIT".
I thought that since you just use the last differential backup file, it would make sense to overwrite it every time, so I put in the INIT. However, it doesn't work. You have to have all the differential files saved, even though you only use the last one.
It doesn't make sense to me, but it appears to be the answer, because when I do it that way the restore works.
-
You can overwrite an existing differential backup. Restoring a database using a combination of Full, differential, and transaction log backups is a viable solution. You only need the most current full and differential backup files and all transaction log backups created since the most current differential backup to restore a database to the most current state possible based on this files.
The error message you reported indicates to me that the full backup you restored was actually more current than the differential you then tried to apply.
In my production environment I append the date/time of the backup to the filename so I can retain several days of differential backups on disk to restore test/development/production databases when required.
-
OK, you may be right that the backup order was wrong, because when I start over, doing a full backup, and two differential backups using INIT for the differential backups, and then do a restore, I don't get an error, and the restore appears to be correct.
In my production environment I append the date/time of the backup to the filename so I can retain several days of differential backups on disk to restore test/development/production databases when required.
Doesn't this mean that each differential backup gets stored to its own file -- as opposed to appending to a single file?
That might be the simplest and safest solution -- just avoid the whole INIT/NOINIT issue for differential backups.
-
Yes, each differential backup gets stored to its own file. These files are backed up to tape nightly. My transaction log backups get appended to the same file for each day, so I have a file for each day. This helps keep the number of files down in the backups directory and makes it simple to see which files are needed for a restore.
-
I realize this is an old post, but like Cynthia I'm practicing backups, and themes overlap i think. I am new to the forum and using SQL Server 2008.
If I want to restore a database in NORECOVERY mode do I *have* to begin the process with a backup of the tail of the log? (ie. even if there are no transactions in the tail)?
My steps.
1. Full backup with INIT
2. Diff backup with INIT
3. Trans backup with INIT
4. Trans backup with NOINIT
5. Damage delete database
6. Full restore with NORECOVERY (my plan was to restore the full and transaction logs separately). Here's the script the GUI uses.
RESTORE DATABASE [AdventureWorks2008]
FROM DISK = N'G:\backups\AdventureWorks2008_full.bak' WITH FILE = 1,
NORECOVERY, NOUNLOAD, STATS = 10
GO
Msg 3159, Level 16, State 1, Line 1
The tail of the log for the database "AdventureWorks2008" has not been backed up.
Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose.
Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the
contents of the log.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
7. However, if I restore Full & all transaction logs with RECOVERY there are no problems, which leads me to another question.
How is it possible that the database fully restored, even though the diff backup wasn't used? The restore that succeeded involved only the Full and 2 transaction log backups!
Thanks in advance, Helen
RESTORE DATABASE [AdventureWorks2008]
FROM DISK = N'G:\backups\AdventureWorks2008_full.bak'
WITH FILE = 1, NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
RESTORE LOG [AdventureWorks2008]
FROM DISK = N'G:\backups\AdventureWorks2008.trn'
WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 10
GO
RESTORE LOG [AdventureWorks2008]
FROM DISK = N'G:\backups\AdventureWorks2008.trn'
WITH FILE = 2, NOUNLOAD, STATS = 10
GO
So, to recap, three questions:
1. If I want to restore a database in NORECOVERY mode do I *have* to begin the process with a backup of the tail of the log?
2. How can I restore the full backup separately from the transaction log backups?
3. How is it possible that the database fully restored, even though the diff backup wasn't used?
-
1. You can't restore transaction logs or differential backups unless the database is in norecovery mode.
You don't have to backup the last transaction log before you restore because the database will restore to the last T-Log you backed up so it's up to you and how comfortable you are with the amount of data you would lose.
Personally I do T-Logs every hour, Diffs every morning and a full backup every weekend.
2. I would set up backup devices to accomplish this, one each for the full, diff and t-log. Then set up SQL Agent jobs timed to perform the backup at whatever time you want (some brilliant docs on the web that'll teach you how to do this, that's how I learned). After that, just restore from the backup device you want to use.
3. I don't really understand the question. If you have a full database backup and subsequent diff back ups then you would have to restore both to fully restore the database. The only way that it would be fully restored is if you restored all the t-logs before all the diffs. then it wouldn't matter
All the differential backups include are all the changes since the last full or differential backup. The transaction logs store the changes since the last T-log backup (i think that's right) so, in fact, you can restore a full backup and all the t-logs without ever having to touch the diffs.
I am ready to be corrected. I probably will be. 😀
-
Hi. I started a new post with this question and it was answered really well there. Actually, I think it may have morphed into a better question by the time it got there. Additionally, as other ideas came up I tried to pursue them further. Got a ton of help. Thanks. http://qa.sqlservercentral.com/Forums/Topic930089-1292-1.aspx
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply