 |
 | Home | Contact Us | FAQ | Search & Site Map | Link to Us |
 | Sign In | Join | Other 45 Sites in Network |
SQL Server Forum / DB Engine / SQL Server / March 2008Restoring differential backup
I am using SQL Server 2005 I have a full backup from midnight and a differential backup from 8 am. I restored the full backup from midnight to a new database, say called NewDB. I then try to restore the differential backup on NewDB, and after going to option to select newdb.mdf and newdb.ldf as its "Restore as" files, and click OK, I got the error "Restore failed for server 'myserver' System.data.sqlclient.sqlerror: the log or differential backup cannot be restored because no files are ready to rollforward. (Microsoft.sqlserver.smo)." What causes this error and how to fix it ? Thank you. Hi, When you restore multiple files for the same database you should use the WITH NORECOVERY option (or select NORECOVERY from the GUI) for all the files except the last one. You should specify WITH RECOVERY, the default, only on the last file of your recovery process. Once you specify WITH RECOVERY you can not apply additional files to the restore procedure. See RESTORE on BOL for more details. Hope this helps, Ben Nevarez > I am using SQL Server 2005 > I have a full backup from midnight and a differential backup from 8 am. [quoted text clipped - 11 lines] > > Thank you. Thank you for your help. In the GUI, I select "RESTORE WITH NORECOVERY" and I still got the same error. My full backup is from yesterday afternoon. I do a differential backup every 2 hours, but the one I am trying to restore is from today at noon. That should be ok, right ? Since the differential backup contains all the changes from the last full backup which is yesterday. > Hi, > [quoted text clipped - 28 lines] >> >> Thank you. It might be easier to script the command and run it in SSMS.  SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Thank you for your help. In the GUI, I select "RESTORE WITH NORECOVERY" and I still got the same error. My full backup is from yesterday afternoon. I do a differential backup every 2 hours, but the one I am trying to restore is from today at noon. That should be ok, right ? Since the differential backup contains all the changes from the last full backup which is yesterday. > Hi, > [quoted text clipped - 30 lines] >> >> Thank you. Since I am not really a DBA (I am a programmer), if I could, I would like to use the GUI. What steps that I do wrong ? Under "restore options", I do not select any of the checkbox, and under "Restore as" I edit the name of the file names to the new database file names that was created after I restored the full backup from yesterday, so here I do not use the original database file names. Ex: the original db name is 'abc', and I restored its full backup to 'abctest', so under "Restore as" I edit the name to be abctest.mdf and abctest.ldf instead of abc.mdf and abc.ldf. Thank you again. > It might be easier to script the command and run it in SSMS. > [quoted text clipped - 42 lines] >>> >>> Thank you. Make sure you restore with no recovery. After that, go through the restore steps again, only this time, specify the file for the differential backup. This time, have it restore with recovery. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Since I am not really a DBA (I am a programmer), if I could, I would like to use the GUI. What steps that I do wrong ? Under "restore options", I do not select any of the checkbox, and under "Restore as" I edit the name of the file names to the new database file names that was created after I restored the full backup from yesterday, so here I do not use the original database file names. Ex: the original db name is 'abc', and I restored its full backup to 'abctest', so under "Restore as" I edit the name to be abctest.mdf and abctest.ldf instead of abc.mdf and abc.ldf. Thank you again. > It might be easier to script the command and run it in SSMS. > [quoted text clipped - 40 lines] >>> >>> Thank you. Thank you. > Make sure you restore with no recovery. Did you mean when I restore the full backup, I should RESTORE WITH NORECOVERY ? I did that, then on SSMS it lists the database as "mydatbasename (Restoring...)". Is this right ? Then I try to restore the differential backup, and this time either I select "RESTORE WITH RECOVERY" or "RESTORE WITH NORECOVERY", I got an error "This differential backup cannot be restored because the database has not been restored to the correct earlier state". > Make sure you restore with no recovery. After that, go through the > restore [quoted text clipped - 62 lines] >>>> >>>> Thank you. You apparently restored the full backup properly, though there seems to be an issue with the differential. It's really hard to troubleshoot GUI issues. I think you should consider scripting. It will be much easier to help you. SignatureTom ---------------------------------------------------- Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS SQL Server MVP Toronto, ON Canada https://mvp.support.microsoft.com/profile/Tom.Moreau Thank you. > Make sure you restore with no recovery. Did you mean when I restore the full backup, I should RESTORE WITH NORECOVERY ? I did that, then on SSMS it lists the database as "mydatbasename (Restoring...)". Is this right ? Then I try to restore the differential backup, and this time either I select "RESTORE WITH RECOVERY" or "RESTORE WITH NORECOVERY", I got an error "This differential backup cannot be restored because the database has not been restored to the correct earlier state". > Make sure you restore with no recovery. After that, go through the > restore [quoted text clipped - 41 lines] >>>> I am using SQL Server 2005 >>>> I have a full backup from midnight and a differential backup from 8 am. >>>> I restored the full backup from midnight to a new database, say called >>>> NewDB. [quoted text clipped - 11 lines] >>>> >>>> Thank you. Thank you. Just to make sure, so when I restore the full backup, should I use RESTORE WITH NORECOVERY or RESTORE WITH RECOVERY ? If I use RESTORE WITH NORECOVERY, is it correct that on SSMS it lists the database as "mydatbasename (Restoring...)" ? > You apparently restored the full backup properly, though there seems to be > an issue with the differential. It's really hard to troubleshoot GUI [quoted text clipped - 84 lines] >>>>> >>>>> Thank you. fniles, If you need to use the GUI, that is fine. But, instead of hitting the OK button, click on the "Script" function at the top of the panel. This will create a script with the exact commands that the GUI will run. You can then execute the script, see if you still get errors, and (if so) post the script here. FWIW, you might also check to make sure that no database backups are being run that you do not expect. (Yes, this really happens, especially on servers with many managers.) This can break your connection between your full backup and the differential that you are trying to use. Here is a quick script: select database_name, backup_start_date, backup_finish_date, type from msdb.dbo.backupset where database_name = 'YourDB' and backup_start_date > DATEADD(day,-3, GETDATE()) order by backup_start_date desc RLF > Thank you. > [quoted text clipped - 84 lines] >>>>> >>>>> Thank you. Thank you everybody. Just to make sure, so when I restore the full backup, should I use RESTORE WITH NORECOVERY or RESTORE WITH RECOVERY ? If I use RESTORE WITH NORECOVERY, is it correct that on SSMS it lists the database as "mydatbasename (Restoring...)" ? > fniles, > [quoted text clipped - 107 lines] >>>>>> >>>>>> Thank you. Correct. It remains in the "Restoring" state until the recovery is complete. - RLF > Thank you everybody. > Just to make sure, so when I restore the full backup, should I use RESTORE [quoted text clipped - 119 lines] >>>>>>> >>>>>>> Thank you. Thank you. I followed your suggestion and run it from the following script and got the same error: RESTORE DATABASE [DeskDemoTest] FROM DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Desk\DeskDemo\DeskDemo_backup_200802011000.dbd' WITH FILE = 1, MOVE N'DeskDemo' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DeskDemoTest.mdf', MOVE N'DeskDemo_log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\DeskDemoTest.ldf', NOUNLOAD, STATS = 10 Regarding the 2nd item: I ran the following query: select database_name, backup_start_date, backup_finish_date, type from msdb.dbo.backupset where database_name = 'mydb' -->> this is the original database where the original backup is made, is that correct ? and backup_start_date > DATEADD(day,-3, GETDATE()) order by backup_start_date desc and it returns 477 records. Could you please tell me what I should be looking for in the query result ? Thanks > fniles, > [quoted text clipped - 107 lines] >>>>>> >>>>>> Thank you. fniles, OK, then it looks like the version of your database and the version of the differential are out of synchronization with each other. Here is someone who had a similar problem recently, and just needed to redo things in the proper order: http://www.sqlservercentral.com/Forums/Topic308260-146-1.aspx You only show restoring one backup, which is apparently the differential backup. The error message means that it is apparently not directly connected to the restore of your full backup. This could be for a number of reasons, such as: 1. Some other full backup was run between the one that you restored and the differential that you are using. If that happened, then it means that the differential is intended for the other full backup, not the one you are using. 2. Your full backup was not restored with NORECOVERY, so it has transactions that invalidate the restore chain for the differential. Also, a brief comment in this blog. http://blog.sqlauthority.com/2007/09/02/sql-server-fix-error-msg-3117-level-16-s tate-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-re ady-to-rollforward/ In your query of the backups you would get a result like this: MyDB StartDateTime99 EndDateTime99 L MyDB StartDateTime98 EndDateTime98 I MyDB StartDateTime97 EndDateTime97 L MyDB StartDateTime96 EndDateTime96 I -- if this was a D it resets the differential start point. MyDB StartDateTime95 EndDateTime95 L MyDB StartDateTime94 EndDateTime94 D What you want to make sure of for case 2 above is that there is not another database full backup (D) between the one that you are restoring and the differential (I) that you are also trying to restore. For example, in the list above you can restore D from StartDateTime 94 norecovery, then restore I from StartDateTime 98. However, if the backup at StartDateTime 96 was not a differential but was another full (D) then the differential from StartDateTime 98 would be base on 96 instead of being based on 94 and could not be restored. RLF > Thank you. > I followed your suggestion and run it from the following script and got [quoted text clipped - 137 lines] >>>>>>> >>>>>>> Thank you. Thank you very much ! You found the problem. I did have another full backup in between the one I restored and the differential backup. I use the last full backup, and it now works fine. I just want to make sure a couple of things: 1. I have 4 transaction backup after the differential backup is created. So, I want to restore the full backup with NORECOVERY, the differential backup with NORECOVERY, the 3 transaction backup that were made after the differential backup with NORECOVERY, and the last transaction backup with RECOVERY. Is that correct ? 2. Everytime I try to restore the differential or transation backup, under "Options" in the "Restore As" it always list the original database like c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\myOriginalDatabase.mdf and c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\myOriginalDatabase_log.ldf So, to restore the diff and all the transaction log I have to keep editing the "Restore As" to point to the new database. Is this correct ? Thanks ! > fniles, > [quoted text clipped - 184 lines] >>>>>>>> >>>>>>>> Thank you. fniles, Great! Other replies inline. > 1. I have 4 transaction backup after the differential backup is created. > So, I want to restore the full backup with NORECOVERY, the differential > backup with NORECOVERY, the 3 transaction backup that were made after the > differential backup with NORECOVERY, and the last transaction backup with > RECOVERY. Is that correct ? Yes, that is correct. > 2. Everytime I try to restore the differential or transation backup, under > ... > So, to restore the diff and all the transaction log I have to keep editing > the "Restore As" to point to the new database. Is this correct ? That is correct. Of course, now that you see the backup history (and if it is worth the work) you could create a query that would script out your restore command for you. Then you could paste it into the query window and hit Execute. RLF Also, after restoring the database to a new database, when I do SELECT file_id, name FROM sys.database_files; it returns the original database name like "MyOriginalDB" and "MyOriginalDB_Log" instead of "MyNewDB" and "MyNewDB_Log". Is that correct ? Thank you. > fniles, > [quoted text clipped - 184 lines] >>>>>>>> >>>>>>>> Thank you. fniles, Yes, the Logical File Name is not changed by the restore, but the Physical File Name is what is changed. After the restore you could run two ALTER DATABASE ... MODIFY FILE command to alter the logical name from MyOriginalDB to MyNewDB, then do the same for the Log. RLF > Also, after restoring the database to a new database, when I do > SELECT file_id, name FROM sys.database_files; [quoted text clipped - 4 lines] > > Thank you. Thank you very much for all your help ! To alter the Logical File name, I can also do the following, right ? In the property of the database under "Files" I changed the logical names from MyOriginalDB to be the new database name (MyNewDB). > fniles, > [quoted text clipped - 13 lines] >> >> Thank you. Should be fine. If you press the Script button, you will see the TSQL that the property change produces. RLF > Thank you very much for all your help ! > [quoted text clipped - 19 lines] >>> >>> Thank you. Hello, I've been following this thread and awhile given up on using the GUI for backup/restore. Using this syntax I do full backups twice a week. This is definitely working. The incrementals are surely there. Thing is, when I look at my backup history, only the full backups are shown. So upon restore, how would I know what logical file # to use? I suppose I could guess and try File = 1, File = 2. If Datepart(dw, Getdate()) In (3, 6) --Tuesday or Friday Begin Backup Database MyDbp To Disk = N'\\MyPath\MyFile.Bak' With Name = 'MyFile full backup', Init Else Begin Backup Database MyDbp To Disk = N'\\MyPath\MyFile.Bak' With Name = 'MyFile differential backup', Differential End Thanks, Ken |
Reply to this Message |
 Sign In
 Join
 My Latest Posts My Monitored Threads My Blog My Photo Gallery My Profile My Homepage Start New Thread Enable EMail Alerts Rate this Thread
|
©2009 Advenet LLC Privacy Policy - Terms of Use
This website includes both content owned or controlled by Advenet as well as content owned or controlled by third parties.