2

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.
0

3 Answers 3

3

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'
0

You need the REPLACE option

RESTORE DATABASE ReportingDB FROM DISK = @filename, REPLACE

See the documentation for more details

2
  • 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, 2010 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, 2010 at 12:50
0

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.

You must log in to answer this question.

Not the answer you're looking for? Browse other questions tagged .