Restore from Differental Backup - Migrating to new Reporting Server

Question

Restore from Differental Backup - Migrating to new Reporting Server

Welsh Corgi

SSC Guru

Points: 116520
More actions
January 23, 2012 at 9:22 am

#250013

I have moved 18 Databases (1TB) to a New Reporting Server which has not gone live.
All of the Databases are updated monthly with the exception of two which are update monthly.
I want users to test the system and make sure the data looks good
The size of the databases that are large. The Complete Backup is of course large and the Differential Backups are small.
I restored from a Full Backup and I did not specify WITH NORECOVERY.
Now I want to apply the Differential backup but I can't do it.
What option can I choose so that users can test after the full backup is applied and before the differential is applied?
For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/
For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

Devendrakumar SSC-Forever Points: 42493 More actions · Answer 1

Users can verify the data till FULL restore. You have already recovered the database so there is nothing left to do.

If you can afford (in terms of time) to restore the FULL database with overwrite, you will be able restore DIFF backups as well.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 2

You can restore the full backup WITH STANDBY, that makes the DB read only (as opposed to completely inaccessible as with WITH NORECOVERY), you can then query and investigate and, once done you can restore the differential.

Note that it's read only. There is no way to make a DB read-write and still allow differentials or logs to be restored.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Robert Murphy UK1 Default port Points: 1485 More actions · Answer 3

Try RESTORE WITH STANDBY?

Robert Murphy, Microsoft MCITP (Database Administrator 2008) and MCAD Certified

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 4

GilaMonster (1/23/2012)
You can restore the full backup WITH STANDBY, that makes the DB read only (as opposed to completely inaccessible as with WITH NORECOVERY), you can then query and investigate and, once done you can restore the differential.
Note that it's read only. There is no way to make a DB read-write and still allow differentials or logs to be restored.

That is exactly what I need.:cool:

Thank you!

For better, quicker answers on T-SQL questions, click on the following...
http://www.sqlservercentral.com/articles/Best+Practices/61537/

For better answers on performance questions, click on the following...
http://www.sqlservercentral.com/articles/SQLServerCentral/66909/

Welsh Corgi SSC Guru Points: 116520 More actions · Answer 5

GilaMonster (1/23/2012)
You can restore the full backup WITH STANDBY, that makes the DB read only (as opposed to completely inaccessible as with WITH NORECOVERY), you can then query and investigate and, once done you can restore the differential.

What's wrong with my syntax?

RESTORE DATABASE MyDB

FROM DISK = 'I:MyDB_backup_201201171726.bak'

WITH REPLACE,

STANDBY,

MOVE 'MyDB' TO 'D:\SQLServer\Data\MyDb.mdf',

MOVE 'MyDB_log' TO 'D:\SQLServer\Log\MyDB.ldf'

Msg 155, Level 15, State 1, Line 4

'STANDBY' is not a recognized RESTORE option.