Restoring File Backups

"After restoring files, you must restore the transaction
log backups created since the file backups were created to
bring the database to a consistent state. The transaction
log backup can be rolled forward quickly, because only the
changes that apply to the restored files are applied."
I don't have the transaction log backups created since the
file backups. I don't care about changes. I want restore
only files and (optional) last log backup.
Can I do this ?Hi,
You can do this. All you have to do is :-
1. Do a transaction log backup in your current database, provided your
recovery model is FULL or BULK_LOGGED
2. Now you can restore the full backup to a new database with NORECOVERY
option
See - RESTORE DATABASE command in books online
3. AFter the restore of FULL backup rectore the transaction log backup took
in step -1 with RECOVERY option
See - RESTORE LOG command in books online
Thanks
Hari
MCDBA
"MK" <anonymous@.discussions.microsoft.com> wrote in message
news:2e9ac01c46d6c$86a9d870$a601280a@.phx
.gbl...
> "After restoring files, you must restore the transaction
> log backups created since the file backups were created to
> bring the database to a consistent state. The transaction
> log backup can be rolled forward quickly, because only the
> changes that apply to the restored files are applied."
> I don't have the transaction log backups created since the
> file backups. I don't care about changes. I want restore
> only files and (optional) last log backup.
> Can I do this ?

Restoring encrypted databases between different servers

Hello:

I'm working with two sql servers instances, ServerA and ServerB, which run under two different service accounts on different machines. They both have a database, DatabaseA, that has some encrypted fields.

If I take a backup of DatabaseA on ServerA and restore it on ServerB, I need to re-encrypt the Database Master Key (DMK) with the Service Master Key (SMK) as follows:

OPEN MASTER KEY DECRYPTION BY PASSWORD = 'pwd used to encrypt DMK'

ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY

However, if I again take a backup on ServerB and then restore it on ServerA, I can use the DMK on ServerA without re-encrypting it with the SMK.

Shouldn't I have to re-encrypt the DMK with the SMK everytime I restore from a backup that was generated from a different server?

Thanks,

Cyndi

After the initial RESTORE and ALTER MASTER KEY … ADD ENCRYPTION BY SERVER MASTER KEY on ServerB, a redundant copy of the DBMK (protected by ServerB SMK) will be stored in the mater DB; and as long as the DBMK is the same, the copies should be synchronized. This redundant copy is there to minimize the DBMK management after the initial setup (via RESTORE).

I hope this information helps. Please let us know if you have any additional questions and/or if you have any additional feedback.

Thanks a lot,

-Raul Garcia

SDE/T

SQL Server Engine

Restoring DTS packages

I am receiving the following error message in restoring and saving DTS packages in SQL Server (SS 2000):

"Class not registered"

First time I have encountered this issue and will much appreciate suggestion on how to fix.

You can also try posting in the DTS forum:

http://groups.google.com/group/microsoft.public.sqlserver.dts?lnk=srg|||

Thanks for this suggestion.

I did post to the DTS forum, however no responses received

Restoring differential backups

Hi

We did a full backup of a DB on Server A (SQL Server 2000) and restored it on Server B (SQL server 2005). Since we have Notification Services, so we run nscontrol upgrade command to upgrade it to 2005. We want to test our application by pointing test server to Server B, while our live application is running by still pointing to Server A.

Now we want to make the states of Server A and B in sync. What can be the best approach for this, since during upgrade some data gets modified. Will differential backup work in this scenario?

Please help me.

Thanks,

Gagan

Unfortunately differential backup does not work across the sql2000->sql2005 upgrade.

Before taking differential backups in sql2005, a new full backup is required.

However, when using the FULL/BULK recovery models, you can use log backups to rollforward thru the upgrade.

Be aware that until server A is upgraded to sql2005, no backups taken from server B (now at sql2005) can be restored.

Hope that helps.

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

Restoring 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
"fniles" wrote:
> 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.
>
>|||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.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
> 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
>
>
> "fniles" wrote:
>> 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.
>>|||It might be easier to script the command and run it in SSMS.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"fniles" <fniles@.pfmail.com> wrote in message
news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
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.
"Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
> 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
>
>
> "fniles" wrote:
>> 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.
>>|||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.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
> It might be easier to script the command and run it in SSMS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
> 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.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>
>|||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.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"fniles" <fniles@.pfmail.com> wrote in message
news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
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.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
> It might be easier to script the command and run it in SSMS.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
> 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.
> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>
>|||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".
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
> 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.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
> 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.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>>
>>
>|||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.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"fniles" <fniles@.pfmail.com> wrote in message
news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
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".
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
> 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.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
> 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.
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>>
>>
>|||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
"fniles" <fniles@.pfmail.com> wrote in message
news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
> 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".
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>>
>>
>>
>|||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...)" ?
"Russell Fields" <russellfields@.nomail.com> wrote in message
news:uUgNZ9rYIHA.4448@.TK2MSFTNGP03.phx.gbl...
> 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
> "fniles" <fniles@.pfmail.com> wrote in message
> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> 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".
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>>
>>
>>
>>
>|||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...)" ?
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:OKZ5A1rYIHA.1204@.TK2MSFTNGP03.phx.gbl...
> 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.
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
> SQL Server MVP
> Toronto, ON Canada
> https://mvp.support.microsoft.com/profile/Tom.Moreau
>
> "fniles" <fniles@.pfmail.com> wrote in message
> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
> 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".
>
> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>>
>>
>>
>
>|||Correct. It remains in the "Restoring" state until the recovery is
complete. - RLF
"fniles" <fniles@.pfmail.com> wrote in message
news:Om6KgSsYIHA.600@.TK2MSFTNGP02.phx.gbl...
> 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...)" ?
> "Russell Fields" <russellfields@.nomail.com> wrote in message
> news:uUgNZ9rYIHA.4448@.TK2MSFTNGP03.phx.gbl...
>> 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
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> 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".
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>>> 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.
>>>
>>>
>>>
>>
>>
>>
>>
>|||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...
> 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
> "fniles" <fniles@.pfmail.com> wrote in message
> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> 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".
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>> 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.
>>
>>
>>
>>
>>
>|||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...
>> 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
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> 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".
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>> 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
>>
>>
>> "fniles" wrote:
>>> 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.
>>>
>>>
>>>
>>
>>
>>
>>
>|||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 !
"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...
>> 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...
>> 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
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> 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".
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>>>
>>> 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
>>>
>>>
>>>
>>>
>>> "fniles" wrote:
>>>
>>> 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.
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||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...
>> 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...
>> 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
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:Ocg2LurYIHA.1208@.TK2MSFTNGP03.phx.gbl...
>> 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".
>>
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:OOvqzciYIHA.4284@.TK2MSFTNGP03.phx.gbl...
>> 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.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:%23zFzqffYIHA.5980@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
>> news:uFU8sceYIHA.1188@.TK2MSFTNGP04.phx.gbl...
>> It might be easier to script the command and run it in SSMS.
>> --
>> Tom
>> ----
>> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
>> SQL Server MVP
>> Toronto, ON Canada
>> https://mvp.support.microsoft.com/profile/Tom.Moreau
>>
>> "fniles" <fniles@.pfmail.com> wrote in message
>> news:em6nLUeYIHA.748@.TK2MSFTNGP04.phx.gbl...
>> 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.
>> "Ben Nevarez" <BenNevarez@.discussions.microsoft.com> wrote in message
>> news:F10CDD8D-B658-44B4-A779-2DE760F8E2DF@.microsoft.com...
>>>
>>> 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
>>>
>>>
>>>
>>>
>>> "fniles" wrote:
>>>
>>> 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.
>>>
>>>
>>>
>>
>>
>>
>>
>>
>|||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...
>> 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.
>|||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...
>> 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.
>>
>|||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