Restoring a DB from a BAK and Trans. Log file backups

[LucieLastic]

hi All

I wish to take the latest copy of our database and restore it on a development server. It consists of a full backup (made on Sunday) and many transaction log backups since then. In Enterprise Manager, once I've restored the full backup, is there any way of restoring all the transaction log backups (in order) in one go or do I have to keep opening the restore dialog for each one?

many thanks in advance
lou

 

[LucieLastic]

Btw, we're using SQL Server 2000.

 

[SQLBill]

You need to use the WITH NORECOVERY command for ALL restores EXCEPT the last one. Refer to the BOL, use the Index tab and enter RESTORE DATABASE, then choose the entry for Transact SQL (under location).

The WITH NORECOVERY leaves the database open for additional restores (differentials, logs). WITH RECOVERY closes the database and will not allow any more restores.

Using Enterprise Manager maintenance plans, the option is (I believe) Leave Database Non Operational and Leave Database Operational.

-SQLBill
BOL=Books OnLine=Microsoft SQL Server's HELP
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine

 

[JayKusch]

Bill is correct in making sure you restore the DB and all BUT the last TLog w/ the with NoRecovery option. Then setting the last tlog backup w/ the WITH Recovery option.

If you do not want to apply each Tlog manually thru Enterprise Manager, you could create a script to do it. Something like ...

RESTORE LOG DB_Name
FROM DISK = 'C:\MyDir\TLog_Backup1.bak'
WITH NoRecovery

RESTORE LOG DB_Name
FROM DISK = 'C:\MyDir\TLog_Backup2.bak'
WITH NoRecovery

... Add the other Tlog names here

RESTORE LOG DB_Name
FROM DISK = 'C:\MyDir\TLog_Backup1.bak'
WITH NoRecovery

RESTORE LOG DB_Name
FROM DISK = 'C:\MyDir\TLog_Backup99.bak'
WITH RECOVERY

Of course you will need to place the correct database name for DB_Name and you will have to set your path correctly to where your TLog backs are.

Thanks

J. Kusch

 

[LucieLastic]

hi

Thanks for the replies. I tried to restore the database using the .BAK file 3 times with each option in the 'Recovery Completion state' but I keep getting the error:

"Microsoft SQL-DMO (ODBC SQLState: 42000)
-----------------------------------------------------------
An internal consitency error occurred. Contact Technical Support for assistance. RESTORE DATABASE is terminating abnormally."

Why is this occurring, could the .BAK file be corrupted? It's a 17gig file and the error always occurs near the end of the restore.

lou