Restoring from Full Backup and Transaction Log Backups

[NWChowd]

Hi all,

I am trying to test out restoring a database from a full backup and transaction log backups.

Facts:
1) I have a full backup scheduled nightly @ 3am.
2) I have transaction log backups hourly between 5am and 10pm.
3) database name: CompanyDB
4) Recovery Mode: Full
5) backups are to disk

I would like to use the backup files (full and t.log) from the CompanyDB database to restore to a database called CompanyDBBU for testing purposes (i.e so I have something in place in case the database really does need to be recovered/restored).

Here's my script (which does not work):
RESTORE DATABASE CompanyDBBU FROM DISK = 'G:\DATA BACKUP\CompanyDB_db_200412100300.BAK' WITH NORECOVERY, REPLACE


RESTORE LOG CompanyDBBU FROM DISK = 'G:\LOG BACKUP\CompanyDB_tlog_200412100500.BAK' WITH NORECOVERY
RESTORE LOG CompanyDBBU FROM DISK = 'G:\LOG BACKUP\CompanyDB_tlog_200412100600.BAK' WITH NORECOVERY
RESTORE LOG CompanyDBBU FROM DISK = 'G:\LOG BACKUP\CompanyDB_tlog_200412100700.BAK' WITH NORECOVERY
RESTORE LOG CompanyDBBU FROM DISK = 'G:\LOG BACKUP\CompanyDB_tlog_200412100800.BAK' WITH NORECOVERY
RESTORE LOG CompanyDBBU FROM DISK = 'G:\LOG BACKUP\CompanyDB_tlog_200412100900.BAK' WITH RECOVERY


I get the following errors when I try to run the script in Query Analyzer:

Server: Msg 1834, Level 16, State 1, Line 1
The file 'E:\MSSQL\DATA\CompanyDB.mdf' cannot be overwritten. It is being used by database 'CompanyDB'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'CompanyDB_dat' cannot be restored to 'E:\MSSQL\DATA\CompanyDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 1834, Level 16, State 1, Line 1
The file 'F:\MSSQL\LOGS\CompanyDB_log.ldf' cannot be overwritten. It is being used by database 'CompanyDB'.
Server: Msg 3156, Level 16, State 1, Line 1
File 'CompanyDB_log' cannot be restored to 'F:\MSSQL\LOGS\CompanyDB_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.


Can someone give me some guidance on what the problem is?

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[Corran007]

your saying restore as a different name, but you are not specifing the new location for the files. so its trying to restore as the new database in the same location of the existing files. thats why it says with move in the error.
basically you have to tell it the new location for the new database

RESTORE DATABASE CompanyDBBU FROM DISK = 'G:\DATA BACKUP\CompanyDB_db_200412100300.BAK'
with move 'database_Data' to 'D:\Microsoft SQL Server\MSSQL\Data\database_Data.mdf',
move 'database_Log' to 'D:\Microsoft SQL Server\MSSQL\Data\database_Log.ldf',
WITH NORECOVERY, REPLACE

change the path and database name to your specific info.

 

[mrdenny]

For each restore command to need to use the MOVE flag to tell itto put the database files is a different spot. Like so:

RESTORE DATABASE CompanyDBBU  FROM  DISK = 'G:\DATA BACKUP\CompanyDB_db_200412100300.BAK' 
WITH NORECOVERY, 
   REPLACE
   MOVE 'CompanyDB_data' to 'e:\MSSQL\DATA\CompanyDBBU_data.mdf',
   MOVE 'CompanyDB_Log' to 'f:\MSSQL\LOGS\CompanyDBDU_Log.ldf'

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[NWChowd]

great!!
Do I also need to use this same syntax for restoring the transaction log backups?


======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================

 

[mrdenny]

Yes I believe so.

Denny

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

(My very old site)

 

[SQLBill]

Yes.

-SQLBill

 

[NWChowd]

it all worked. I used the same syntax on the log file portion as well. thanks.

DMill

======================================
"I wish that I may never think the smiles of the great and powerful a sufficient inducement to turn aside from the straight path of honesty and the convictions of my own mind."
-David Ricardo, classical economist
======================================