Restore with move question.

Last Post 17 Dec 2004 11:00 AM by talltop. 1 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Markus_SQL
New Member
New Member

--
15 Dec 2004 06:15 AM
I have a backup of a prod database made via db maint plan. Then I copy the backup file to a 'test' server and want to restore it via Transact-SQL and the drive letter will change. I have done this a million times in Ent manager without a problem. However, when I use Transact_SQL I am having problems...

restore database BSISUITE from disk = 'c:\BSISuite_db_200412011801.bak'
WITH MOVE 'E:\Program Files\Microsoft SQL Server\mssql\data\BSISuite.mdf' TO 'c:\Program FIles\Microsoft SQL Server\MSSQL\data\BSISuite.mdf',
MOVE 'E:\Program Files\Microsoft SQL Server\MSSQL\data\BSISuite_log.ldf TO 'c:\Program Files\Microsoft SQL Server\MSSQL\data\BSISuite_log.ldf'

it says that the .mdf logical file is not a part of the database. This is the original file name as best I can tell. It tells me to run a restore filelistonly but I don't understand the syntax from BOL. I don't use a dump device to create the db backup I use maint. plans to produce files...

Any help would be apprecited. This is just me trying to understand the syntax is all.
talltop
New Member
New Member

--
17 Dec 2004 11:00 AM
You can also change the logical files if you need to as well with:

ALTER DATABASE MyDatabase
MODIFY FILE (NAME = logical_datafile_name, NEWNAME = new_logical_name...)
GO
ALTER DATABASE MyDatabase
MODIFY FILE (NAME = logical_logfile_name, NEWNAME = new_logical_name...)
GO


----------------------------------
Travis Lee Alltop
SQL Server DBA
You are not authorized to post a reply.

Acceptable Use Policy