;)
Working in the development/testing environments that I do, this is an error I see frequently as developers are moving/copying staging or test databases from one SQL instance to another.
The error can pop up when you try to restore a database on top of an existing database of the same name:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing ‘Orders’ database. (Microsoft.SqlServer.Smo)
You get a similar error in other circumstances, such as when the existing database has been detached, but the files still exist in the target folder:
System.Data.SqlClient.SqlError: File “Orders” cannot be restored over the existing “C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Orders.mdf”. Reissue the RESTORE statement using WITH REPLACE to overwrite pre-existing files, or WITH MOVE to identify an alternate location. (Microsoft.SqlServer.Smo)
It’s an easy fix if you know what the problem is, but if you haven’t encountered it before, it can be frustrating. The second error is a little more useful in that it gives you the solution (for both problems): use the WITH REPLACE option. This can be accomplished in TSQL like this:
RESTORE DATABASE [Orders]
FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Orders.bak’
WITH REPLACE
If you’re using SQL Server Management Studio(SSMS), select the “Overwrite the existing database (WITH REPLACE)” option on the Options tab:
Keep in mind that, as the name suggests, this WILL replace your existing database! Make sure this is what you want to do.
MOVE Option
Another error that is common when restoring a database in SQL 2005 SSMS is this one:
System.Data.SqlClient.SqlError: Directory lookup for the file “C:\Data\Orders.mdf” failed with the operating system error 2(The system cannot find the file specified.). (Microsoft.SqlServer.Smo)
This would happen frequently when using the GUI in management studio to restore a database, using the default options. The problem here is that the file locations on the source server do not exist on the target server. The solution is to use the use the WITH MOVE option. This can be done in TSQL as follows:
RESTORE DATABASE [Orders] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Orders.bak’ WITH MOVE N’Orders’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Orders.mdf’, MOVE N’Orders_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Orders_1.ldf’
If you’re using the the GUI, there’s no option to check, you just have to go into the “Restore As” column and change the file path to one that exists on the target machine:
It’s interesting, but this issue seems to be “resolved”, if you will, in the 2008 version of SSMS. A restore attempt to a target path that does not exists succeeds, with no error. Using the SQL Profiler, we can take a look at the TSQL that is issued in this case and see why:
RESTORE DATABASE [Orders] FROM DISK = N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\Backup\Orders.bak’ WITH FILE = 1, MOVE N’Orders’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Orders.mdf’, MOVE N’Orders_log’ TO N’C:\Program Files\Microsoft SQL Server\MSSQL10.MSSQLSERVER\MSSQL\DATA\Orders_1.ldf’, NOUNLOAD, STATS = 10
Notice the MOVE statement – it means: I don’t care what your original file path was, move the files to this location. So, it appears this is just a change in SSMS 2008 vs. earlier versions: it will automatically use the MOVE statement to put the files in the default locations on the target server. As far as I know, this was not the case in earlier versions…I like the change, this was a very frequent error.
