What I'm trying to do is backup one of our production databases and restore them as our dev database (overwriting what's been there).
That's how I back up:
backup database [authfx] to disk = N'f:\db_backups\authfx\authfx-latest.bak'
with
noformat,
init,
name = N'authfx Latest Full Database Backup',
skip,
norewind,
nounload,
stats = 1;
Now when I try to restore other database using
restore database [dev-authfx] from disk = N'f:\db_backups\authfx\authfx-latest.bak'
with
file = 1,
nounload,
replace,
stats = 1;
it barks all over the place:
Msg 1834, Level 16, State 1, Line 10
The file 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf' cannot be overwritten. It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx' cannot be restored to 'E:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\authfx.mdf'. Use WITH MOVE to identify a valid location for the file.
Msg 1834, Level 16, State 1, Line 10
The file 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf' cannot be overwritten. It is being used by database 'authfx'.
Msg 3156, Level 16, State 4, Line 10
File 'authfx_log' cannot be restored to 'F:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\Data\authfx_log.ldf'. Use WITH MOVE to identify a valid location for the file.
Msg 3119, Level 16, State 1, Line 10
Problems were identified while planning for the RESTORE statement. Previous messages provide details.
Msg 3013, Level 16, State 1, Line 10
RESTORE DATABASE is terminating abnormally.
I understand that I need to add with move
and specify where original data and log files go for the dev
database, but is there any way I can omit this and just say: restore this database from this other backup, do not touch anything else and overwrite what's been here previously.