Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 restore with recovery by mistake

Author  Topic 

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-07-22 : 14:42:17
Hi,

I need to run something like this, but did not get the last part right. I completed a with Recovery. Then find out need to add more diff backup to it.

--Restore the full database backup (from backup set 1).
RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=1,
NORECOVERY;

To save time, is there a way to change it from recovery mode back to restoring? Otherwise, I have to start over.

Thanks!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-07-22 : 14:44:10
No, you need to start over. I always specify my WITH RECOVERY step separately:

RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.bak'
WITH FILE=1,
NORECOVERY;

RESTORE DATABASE AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.dif'
WITH FILE=1,
NORECOVERY;

RESTORE LOG AdventureWorks2012
FROM DISK = 'Z:\SQLServerBackups\AdventureWorksFullRM.trn'
WITH FILE=1,
NORECOVERY;

--RESTORE DATABASE AdventureWorks2012
--WITH RECOVERY

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Hommer
Aged Yak Warrior

808 Posts

Posted - 2014-07-22 : 14:55:33
great! two hours wasted but lesson learnt.
Go to Top of Page
   

- Advertisement -