Restore database using SQL statement

[barbola]

RESTORE DATABASE [MYDBNAME] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MYDBNAME_db_200807212005.BAK' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10
GO

This is on a test server. I had copied this backup from the live server. The live server has the database in this folder:

d:\MSSQL\data\MYDBNAME.mdf

My test server has it in this folder:

C:\Program Files\Microsoft SQL Server\MSSQL\Data\MYDBNAME.MDF

I am getting the following errors both using the Restore function as well as using SQL.

Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'd:\MSSQL\data\MYDBNAME.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'MYDBNAME.mdf' cannot be restored to 'd:\MSSQL\data\MYDBNAME.mdf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'd:\MSSQL\data\MYDBNAME.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'MYDBNAME.ldf' cannot be restored to 'd:\MSSQL\data\MYDBNAME.ldf'. Use WITH MOVE to identify a valid location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

What do I need to do? This is kind of a rush. Thanks.

 

[mrdenny]

You need to add the MOVE option to tell SQL Server where you move the physical files to.

RESTORE DATABASE [MYDBNAME] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MYDBNAME_db_200807212005.BAK' WITH  FILE = 1,  NOUNLOAD,  REPLACE,  STATS = 10,
MOVE 'MyDBName_DAta' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDBName_Data.mdf',
MOVE 'MyDBNAme_Log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDBName_Log.ldf'
GO

Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration / Microsoft Office SharePoint Server 2007: Configuration)
MCITP Database Administrator (SQL 2005) / Database Developer (SQL 2005)

My Blog

 

[barbola]

Thanks for the info. I was able to find a solution, and got it figured out. It also had to do with the logical name.