Restore Problems

Question

Restore Problems

Charlottecb

Hall of Fame

Points: 3932
More actions
August 9, 2005 at 2:03 am

#88428

Hi All.
SQLServer 2000.
I am having problems restoring a db. Can anyone help. Background info...
Data was accidentally deleted about a week ago. I have copied that nights back from our tape to d:\SQL2K\Data\Rstore\GSBDThursday.BAK
I have set up a new DB called restore_db in the above directory also, so that our current data is not overwritten. I want to restore the backup and subseqent logs over the restore_db so I can then extract the missing records from this db and insert them into our live db.
Using Enterprise manager, I have managed to restore the backup, but when I try to restore the logs, I get the following message...
The preceding restore operation did not specify with NO RECOVERY or WITH STANDBY. Restart the restore sequence specifying WITH NORECOVERY or WITH STANDBY for all but the final step. Restore Log is terminating abnormally.
I guess I need to use a scrpt instead of Enterprise Manager, but what is complicating it is the paths and different names of backup and DB.
Can anyone help me with this script?
Thanks
CCB

Viewing 10 posts - 1 through 9 (of 9 total)

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

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 1

You can still use EM. Just make sure that in the dialog where you specify the backup file to restore and the database to restore to you also switch to the tab called Options. There you have three options called "Recovery completion state". Instead of the default "Leave database operational. ..." check the middle option that says "Leave database nonoperational but able to restore additional transaction logs." instead (or use the third option if you like). When you are done restoring logs you take the database online.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 2

If you do want to use scripts however you need to use the WITH NORECOVERY option for every backup file that you restore except the last one.

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Charlottecb Hall of Fame Points: 3932 More actions · Answer 3

Hi Chris, I tried that but I still get the same message.

Any chance you can show me the script with the names and paths I have outlined.

Thanks very much.

CCB

Chris Hedgate One Orange Chip Points: 25041 More actions · Answer 4

This should be something similar:

RESTORE DATABASE restore_db

FROM DISK = 'C:\temp\GSBDThursday.bak'

WITH NORECOVERY, REPLACE

, MOVE 'GSBD_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\data\restore_db_Data.MDF'

, MOVE 'GSBD_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\data\restore_db_Log.LDF'

RESTORE LOG restore_db

FROM DISK = 'C:\temp\GSBDFridayLog.trn'

WITH NORECOVERY

, MOVE 'GSBD_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\data\restore_db_Data.MDF'

, MOVE 'GSBD_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\data\restore_db_Log.LDF'

RESTORE LOG restore_db

FROM DISK = 'C:\temp\GSBDSaturdayLog.trn'

WITH RECOVERY

, MOVE 'GSBD_Data' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\data\restore_db_Data.MDF'

, MOVE 'GSBD_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL$SQL2K\data\restore_db_Log.LDF'

--
Chris Hedgate http://www.hedgate.net/
Contributor to the Best of SQL Server Central volumes
Articles: http://www.sqlservercentral.com/columnists/chedgate/

Charlottecb Hall of Fame Points: 3932 More actions · Answer 5

Thanks for that Chris.

One final question.

If I want to only restore up to a certain point in the transaction log (examining my back shows I have 40 and I need to stop at 26), how do I add this to the script.

Thanks again.

CCB

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 6

Here's a sample from BOL: (you want to use the STOPAT clause)

-- Restore the database backup.

RESTORE DATABASE MyNwind

FROM MyNwind_1, MyNwind_2

WITH NORECOVERY

GO

RESTORE LOG MyNwind

FROM MyNwind_log1

WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

GO

/Kenneth

Charlottecb Hall of Fame Points: 3932 More actions · Answer 7

Thanks Kenneth.

I have done this. Now my DW when viewed from Ent Manager says loading. I cannot get into it because it thinks it is in the middle of a restore. How Do I tell the Restore tah I am now finsihed.

Thanks for you patience.

CCB

Kenneth Wilhelmsson SSC-Dedicated Points: 30043 More actions · Answer 8

Did you miss the 'with recovery' along with 'stopat'?

RESTORE LOG MyNwind

FROM MyNwind_log1

WITH RECOVERY, STOPAT = 'Jul 1, 1998 10:00 AM'

No sweat, if you're all done, in QA, just issue the following command:

RESTORE DATABASE MyDatabase

WITH RECOVERY

/Kenneth

Charlottecb Hall of Fame Points: 3932 More actions · Answer 9

Charlottecb

Hall of Fame

Points: 3932

August 9, 2005 at 9:30 am

#580782

Perfect.

Thanks guys.