Sql-server – SQL Server: Simple database restore with overwrite

backuprestoresql server

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.

Best Answer

Unfortunately not. SQL Server will always attempt to restore to exactly what is recorded in the backup file, unless you explicitly specify otherwise (with MOVE in this case).

The REPLACE option only works if you're replacing files owned by the database you're restoring (which makes sense, You may have said you're fine killing DB_A by restoring over it, but SQL doesn't really know how you feel about killing DB_B as well).

If this is something you'll be doing regularly then, then write a small sql script with everything stated, save it and use that every time you want to do the restore. This is how I do our overnight restores for dev/UAT/Training from prod.

Related Question