SQL Server Express backup and restore to other database batch

Question

I am trying to outsource some processes from my live database where a third party tool is importing data which first have to run through some scripts/changes because of quality before being implemented in live DB

So I thought I will create a second DB in the same server take my Backup batch and every time I make a backup I restore the Backup in the new db, where the third party tool is importing the data. Then I use this db to make some changes on another DB, which I then update into live DB.

Batch file:

osql.exe -S (local)\WAWI -U user -P password -Q "BACKUP DATABASE eazybusiness TO DISK = 'c:\WaWi-Backups\eazybusiness.bak'"
osql.exe -S (local)\WAWI -U user -P password -d master -Q "restore database Mandant_2 FROM DISK ='c:\WaWi-Backups\eazybusiness.bak' with replace"

But when running the script, the restore command is throwing the following error

Msg '1834', '16', status '1', server 'DBServer\WAWI', line 1
The file ' C:\Program Files\Microsoft SQL Server\MSSQL11. WAWI\MSSQL\DATA\eazybusiness.mdf' can not be overwritten be. It is used by the eazybusiness database.

Msg '3156', '16', '4' status, server 'DBServer\WAWI', line 1
The file 'eazybusiness' can not in ' C:\Program Files\Microsoft SQL Server\MSSQL11. WAWI\MSSQL\DATA\eazybusiness.mdf' be restored. Use WITH MOVE to a valid location for the file to identify.

Msg '1834', '16', status '1', server 'DBServer\WAWI', line 1
The file ' C:\Program Files\Microsoft SQL Server\MSSQL11. WAWI\MSSQL\DATA\eazybusiness_log.ldf' cannot be overridden. It is used by the eazybusiness database.

Msg '3156', '16', '4' status, server 'DBServer\WAWI', line 1
The file 'eazybusiness_log' cannot be in ' C:\Program Files\Microsoft SQL Server\MSSQL11. WAWI\MSSQL\DATA\eazybusiness_log.ldf' restored be. Use WITH MOVE to a valid location for the file to identify.

Msg '3119', '16', status '1', server 'DBServer\WAWI', line 1
Problems have been found in the planning of the restore statement. Preceding Messages contain detailed information.

Msg '3013', '16', status '1', server 'DBServer\WAWI', line 1
RESTORE DATABASE is stopped abnormally. SpeakSelect voice optionsSearch this translation with BingThis is a good translationThis is a bad translation.

How can I restore the backup in another db through a batch?

Greg Greg · Accepted Answer · 2015-08-25T17:56:09

You are restoring the backup using a different name, but same file properties. since the original database exists, you are trying to overwrite the same db, which is causing the failure. You need to specify a different file name and/or location for each database data and log file, utilizing the WITH MOVE syntax.

See RESTORE documentation, specifically section "Copying a Database Using BACKUP and RESTORE".

Sample:

RESTORE DATABASE TestDB 
   FROM AdventureWorksBackups 
   WITH MOVE 'AdventureWorks2012_Data' TO 'C:\MySQLServer\testdb.mdf',
   MOVE 'AdventureWorks2012_Log' TO 'C:\MySQLServer\testdb.ldf';
GO

SQL Server Express backup and restore to other database batch

1 Answers