Menu
I am getting an error using SQL Server 2012 when restoring a backup made with a previous version (SQL Server 2008). I actually have several backup files of the same database (taken at different times in the past). The newest ones are restored without any problems; however, one of them gives the following error:
System.Data.SqlClient.SqlError: Directory lookup for the file "C:\PROGRAM FILES\MICROSOFT SQL SERVER\MSSQL.1\MSSQL\DATA\MYDB_ABC.MDF" failed with the operating system error 3(The system cannot find the path specified.). (Microsoft.SqlServer.SmoExtended)
This is a x64 machine, and my database file(s) are in this location: c:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL.
I do not understand why it tries to restore on MSSQL.1 and not MSSQL11.MSSQLSERVER.
329
In the left navigation bar, right-click on Databases and then click Restore Database. In the Source section, select Device and click the button with three dots. In the pop up window that opens, click Add and browse for your backup file. Click OK.
If you receive an error that the database is in use, try to set the user to single user mode: USE master; GO ALTER DATABASE Database_name SET SINGLE_USER WITH ROLLBACK IMMEDIATE; Then try the restore with recovery command again. Also, make sure you are on the latest service pack or cumulative update.
Sounds like the backup was taken on a machine whose paths do not match yours. Try performing the backup using T-SQL instead of the UI. Also make sure that the paths you're specifying actually exist and that there isn't already a copy of these mdf/ldf files in there.
RESTORE DATABASE MYDB_ABC FROM DISK = 'C:\path\file.bak' WITH MOVE 'mydb' TO 'c:\valid_data_path\MYDB_ABC.mdf', MOVE 'mydb_log' TO 'c:\valid_log_path\MYDB_ABC.ldf'; When restoring, under Files, check 'Relocate all files to folder'
30
If you love us? You can donate to us via Paypal or buy me a coffee so we can maintain and grow! Thank you!
Donate Us With
