Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

Here's a script that attempts to take a daily backup from the previous day and restore it over a reporting database. My problem is how do I set it to override the files, so I don't have to specify the file names? When I run this I encounter a series of errors, as below.

----Restore Database
ALTER DATABASE ReportingDB SET SINGLE_USER
GO
DECLARE @filename VARCHAR(1000)
select @filename = 'F:\DailyBackup\LiveDB_backup_' + cast(datepart(yyyy, getdate()) as varchar(4)) + '' + substring(cast( 100 + datepart(mm, getdate()) as char(3)), 2, 2) + substring(cast( 100 + datepart(day, getdate()) as char(3)), 2, 2) + '0000.bak'
select @filename
RESTORE DATABASE ReportingDB FROM DISK = @filename WITH REPLACE
GO
ALTER DATABASE ReportingDB SET MULTI_USER
GO


(1 row(s) affected)
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "D:\DB\LiveDB.mdf" failed with the operating system error 21(error not found).
Msg 3156, Level 16, State 3, Line 4
File 'LiveDB' cannot be restored to 'D:\DB\LiveDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 5133, Level 16, State 1, Line 4
Directory lookup for the file "D:\DB\LiveDB_log.LDF" failed with the operating system error 21(error not found).
Msg 3156, Level 16, State 3, Line 4
File 'LiveDB_log' cannot be restored to 'D:\DB\LiveDB_log.LDF'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.

When I try WITH MOVE:

MOVE 'LiveDB' TO 'F:\ReportingDB\ReportingDB.mdf',

MOVE 'LiveDB_log' TO 'F:\ReportingDB\ReportingDB_log.ldf'

(1 row(s) affected)
Msg 1834, Level 16, State 1, Line 4
The file 'F:\ReportingDB\ReportingDB.mdf' cannot be overwritten.  It is being used by database 'ReportingDB'.
Msg 3156, Level 16, State 4, Line 4
File 'LiveDB' cannot be restored to 'F:\ReportingDB\ReportingDB.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 4
The file 'F:\ReportingDB\ReportingDB_log.ldf' cannot be overwritten.  It is being used by database 'ReportingDB'.
Msg 3156, Level 16, State 4, Line 4
File 'LiveDB_log' cannot be restored to 'F:\ReportingDB\Reporting_log.ldf'.     Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 4
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 4
RESTORE DATABASE is terminating abnormally.
share|improve this question

migrated from stackoverflow.com Sep 10 '10 at 2:17

This question came from our site for professional and enthusiast programmers.

3 Answers 3

up vote 3 down vote accepted

It looks like your original backup was taken from a database located at 'D:\DB\LiveDB.mdf' and you are now trying to restore over a database located at 'F:\ReportingDB\ReportingDB.mdf', so you'll need the combination of both the MOVE and REPLACE options.

RESTORE DATABASE ReportingDB 
    FROM DISK = @filename WITH REPLACE, 
    MOVE 'LiveDB' TO 'F:\ReportingDB\ReportingDB.mdf',
    MOVE 'LiveDB_log' TO 'F:\ReportingDB\ReportingDB_log.ldf'
share|improve this answer

You need the REPLACE option

RESTORE DATABASE ReportingDB FROM DISK = @filename, REPLACE

See the documentation for more details

share|improve this answer
    
I tried with replace, but getting a new set of errors now. Have updated the question. thanks for the link but it doesn't explain override –  user38553 Sep 9 '10 at 10:20
    
@cfdev9 - can you get this restore to work from the Management Studio GUI? If so, you can script your actions from there - use the "script" button at the top of the restore window –  Ed Harper Sep 9 '10 at 12:50

Msg 1834, Level 16, State 1, Line 4 The file 'F:\ReportingDB\ReportingDB.mdf' cannot be overwritten. It is being used by database 'ReportingDB'.

The above error is probably due to the fact that the database is in use as you try to restore. This will prevent the database being restored as such use the following command

ALTER DATABASE DATABASENAME SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE

Note that this should be all in one step if you are running a scheduled job.

This will force all other users to disconnect (so their reports will fail if they are being run)

you may also need to include the following

ALTER DATABASE DATABASENAME SET MULTI_USER

to allow multiple connections afterwards although the restore should set it to the same as the database it was backed up from.

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.