Sql-server – Restoring full and differential backups from production to a report server

restoresql server

Running this SQL query:

   RESTORE DATABASE Demo 
   FROM DISK = 'C:\DBBackup\Demo_FULL_Sunday.bak' 
   WITH  NORECOVERY 

   RESTORE DATABASE Demo 
   FROM DISK = 'C:\DBBackup\Demo_Diff_Monday.bak' 
   WITH RECOVERY

I'm getting these errors:

Msg 3117, Level 16, State 4, Line 1
The log or differential backup cannot be restored because no files are ready to rollforward.

Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

I have also tried adding WITH REPLACE but no luck. This is meant to restore to a report server, not to the original server that the backup was taken from.

I have tried Googling, however everything I read just says to make sure you are using WITH NORECOVERY.

Best Answer

Although you have WITH NORECOVERY on the 1st statement, the error is actually occurring since there is an existing database with the name Demo which cannot be overwritten without explicit instruction.

Try:

ALTER DATABASE Demo SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

RESTORE DATABASE Demo 
FROM DISK = 'C:\DBBackup\Demo_FULL_Sunday.bak' 
WITH REPLACE, NORECOVERY; 

RESTORE DATABASE Demo 
FROM DISK = 'C:\DBBackup\Demo_Diff_Monday.bak' 
WITH RECOVERY;

Be aware this will totally destroy the existing database and replace it with the contents of the backup file. Ensure you do this on the correct server, since you will have no 2nd chances.

Related Solutions

Sql-server – SQL Server 2008 Restore

Try this:

CREATE DATABASE foo;
GO
CREATE DATABASE bar;
GO
BACKUP DATABASE foo TO DISK = 'c:\temp\x.bak';
BACKUP DATABASE bar TO DISK = 'c:\temp\x.bak';
GO
RESTORE HEADERONLY FROM DISK = 'c:\temp\x.bak';

Relevant columns for this scenario:

Position  DatabaseName
--------  ------------
1         foo
2         bar

To restore foo, whether it exists or not:

RESTORE DATABASE foo FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 1, REPLACE;

To restore bar, whether it exists or not:

RESTORE DATABASE bar FROM DISK = 'c:\temp\x.bak'
  WITH FILE = 2, REPLACE;

If you don't specify WITH FILE, you will get a slew of errors, depending on whether the database in Position = 1 already exists.

The lessons here:

do not use common and generic names for backup files - name them for the database they represent and keep them separate.
use WITH INIT if you expect a backup file to only ever contain one copy of a backup.
understand what is in your backup by first running RESTORE HEADERONLY and/or RESTORE FILELISTONLY.

If you show us those two columns from the output of RESTORE HEADERONLY and what database you are actually trying to restore, we can provide you with a specific command that should work.

Sql-server – Restoring Differential Backups

In order to restore a differential backup, you need a database that is in "restoring" state. Which is to say that recovery can't have run yet. This is accomplished by adding the "norecovery" clause to your full backup restore. So, it goes a little something like this:

DECLARE @file VARCHAR(255) = '\\RemoteServer\Dir\\DBName_' + (select DATENAME(dw, DATEADD(day, DATEDIFF(day, 0, getDate()) - 1, 0))) + '.bak'
RESTORE DATABASE [DBName]
FROM DISK = @file
WITH REPLACE, NORECOVERY

--now you restore your differential backup

Best Answer

Related Solutions

Sql-server – SQL Server 2008 Restore

Sql-server – Restoring Differential Backups

Related Question