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.