Restoring differential backup

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-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-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
"fniles" <fniles@.pfmail.com> wrote in message
news:eJxCi2PZIHA.1208@.TK2MSFTNGP05.phx.gbl...
> 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
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uUgNZ9rYIHA.4448@.TK2MSFTNGP03.phx.gbl...
>
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.l df
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 !
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23CCXAbzZIHA.1184@.TK2MSFTNGP04.phx.gbl...
> 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-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-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
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eJxCi2PZIHA.1208@.TK2MSFTNGP05.phx.gbl...
>
|||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.
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:%23CCXAbzZIHA.1184@.TK2MSFTNGP04.phx.gbl...
> 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-state-4-the-log-or-differential-backup-cannot-be-restored-because-no-files-are-ready-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
> "fniles" <fniles@.pfmail.com> wrote in message
> news:eJxCi2PZIHA.1208@.TK2MSFTNGP05.phx.gbl...
>
|||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
|||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
"fniles" <fniles@.pfmail.com> wrote in message
news:O2zAvX1ZIHA.4440@.TK2MSFTNGP06.phx.gbl...
> 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.
|||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).
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:eIwjW62ZIHA.4180@.TK2MSFTNGP06.phx.gbl...
> 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
> "fniles" <fniles@.pfmail.com> wrote in message
> news:O2zAvX1ZIHA.4440@.TK2MSFTNGP06.phx.gbl...
>
|||Should be fine. If you press the Script button, you will see the TSQL that
the property change produces.
RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:O9fwVT3ZIHA.4476@.TK2MSFTNGP06.phx.gbl...
> 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).
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:eIwjW62ZIHA.4180@.TK2MSFTNGP06.phx.gbl...
>
|||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