DB Restoration Problem
I am working on a site redesign which attaches to an MS SQL backend. The hosting company sent me a backup copy of the DB. I am trying to restore the db into my SQL Server but keep getting an error. I created an empty DB and then backed up that DB. I then replaced the .bak from my db with the .bak file he sent to me in a zip document. I then tried to do a DB restore through enterprise manager and recieved this error:
Device activation error. The physical file name 'd:\sql\database\mssql\data\stklaweb_db_data.mdf' may be incorrect. File 'stklaweb_db_data' cannot be restored to 'd:\sql\database\mssql\data\stklaweb_db_data.mdf'. Use With Move to identify a valid location for the file. Device activation err. The physical file name 'd:\sql\database\mssql\data\stklaweb_db_data.ldf' may be incorrect. File 'stklaweb_db_data' cannot be restored to 'd:\sql\database\mssql\data\stklaweb_db_data.ldf'. Use With Move to identify a valid location for the file. RESTORE DATABASE is terminating abnormally.
I need to get this db restored asap. Any suggestions/help would be GREATLY appriciated!!!
Jason
Hi Jason,
I think the problem is that the directory path 'd:\sql\database\mssql\data' does not exist - it doesn't matter that you created an empty database - the restore command is trying to restore the files based on the directory path stored in the backup file. What you need to do is specify a new file location for the .mdf and .ldf files during restore using the WITH MOVE syntax.
Here's an example, assuming that:
- you want to store the files in the 'c:\mypath' directory (substitute your path)
- your database is named 'stklaweb_db':
- your backup file is 'c:\stklaweb_db.bak' (substitute your path/filename)
First, delete the empty database you created. (An alternative to this is to use the WITH REPLACE on the final RESTORE command below, but this is simpler).
Second, determine the logical names of the files in the backup:
RESTORE FILELISTONLY FROM DISK = 'C:\ stklaweb_db.bak'
Lastly, use the WITH MOVE syntax to move these logical filenames to valid paths. Use the values in the LogicalName column output from running the command above (I'm assuming they're 'stklaweb_db_data' and 'stklaweb_db_log':
RESTORE DATABASE stklaweb_db
FROM DISK = 'C:\stklaweb_db.bak'
WITH MOVE 'stklaweb_db_data' TO 'C:\mypath\stklaweb_db_data.mdf',
MOVE 'stklaweb_db_log' TO 'C:\mypath\stklaweb_db_data.ldf'
GO
Hopefully this should work for you - let me know how it goes.
Thanks and regards,
Paul
Hello Paul,
I was wondering what tool you use to facillitate a 'WITH MOVE' command. I have the same problem described, but I want to restore a Database backed up in SQL Server 2000 developer, and RESTORE it into an MSDE database using a Microsoft Access 2002 project. There are no prompts to use a 'WITH MOVE' clause, unless it can be done in VBA.
Any Ideas?
Thanks
Frank Srebot
Hi Paul,
I was having trouble trying to restore a SQLServer 2000 backup to SQLServer Express 2005 using the MSSQL management studio express GUI. The scripts created behind was:
RESTORE
DATABASE [KMS_DEV] FILE = N'SUP_DEVData', FILE = N'SUP_DEVLog' FROM DISK = N'D:\Public\SUP_DEV_db_200510050200.BAK' WITH FILE = 1, MOVE N'SUP_DEVData' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\KMS_DEV.mdf', MOVE N'SUP_DEVLog' TO N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\KMS_DEV_0.ldf', NOUNLOAD, REPLACE, STATS = 10 GO
But I changed it to something similar to yours:
RESTORE
DATABASE [KMS_DEV] FROM DISK = N'D:\Public\SUP_DEV_db_200510050200.BAK' WITH
MOVE 'SUP_DEVData' TO 'D:\MSSQL\KMS_DEV.mdf', MOVE
'SUP_DEVLog' TO 'D:\MSSQL\KMS_DEV_0.ldf', NOUNLOAD, REPLACE GO
And it works!!!
Thanks!
Kong-How
 Frank Srebot wrote: |
| I was wondering what tool you use to facillitate a 'WITH MOVE' command. I have the same problem described, but I want to restore a Database backed up in SQL Server 2000 developer, and RESTORE it into an MSDE database using a Microsoft Access 2002 project. There are no prompts to use a 'WITH MOVE' clause, unless it can be done in VBA. |
|
Hi Frank,
My example assumed the use of any tool that allows ad-hoc T-SQL to be issued against SQL Server (e.g. Query Analyzer, SQL Server Management Studio, osql.exe, sqlcmd.exe).
If you want to do this as a one-off, can you connect to the MSDE instance and do these restore? Or does the restore have to be done through the Access project?
I'm not familiar with using Access projects, but one suggestion comes to mind. Assuming you have the facility to at least do a restore, how about replicating the directory structure of the MSDE instance on another SQL Server instance, doing the RESTORE ... WITH MOVE there and then taking another backup, so the backup contains the correct directory paths and no WITH MOVE is necessary on the MSDE instance?
Hope this helps.
Hi Kong-How,
Glad to hear it works. Could you let me know what errors you were seeing from the SSMS-generated script?
Thanks
Hello all,
I realize I'm coming a bit late to this discussion but I've run into a similar problem and I hope there's someone out there who can still help me.
I'm trying to restore a development database from a backup of our production database. The dev database is named "siebel_dev" and the production database is named "siebel_prod". I tried running the RESTORE FILELISTONLY command this way:
RESTORE FILELISTONLY FROM DISK = "d:\sql_data\prod_backup" where "prod_backup" is the name of the backup file.
When I do this I receive this error:
"Could not locate entry in sysdatabases for database 'siebel_prod'. No entry found with that name. Make sure that the name is entered correctly."
Thanks in advance,
Steve
