Restore db with move option question.
-
This is SQL Svr 2000 SP3.
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 appreciated. This is just me trying to understand the syntax is all.
-
This gets me my info:
restore filelistonly from disk='e:\mssql\backup\lyris\Lyris_db_200412150200.BAK'
Change names, and paths of course
-
Change the script to
restore database BSISUITE from disk = 'c:\BSISuite_db_200412011801.bak'
WITH MOVE 'Logical_datafile_name' TO 'c:\Program FIles\Microsoft SQL Server\MSSQL\data\BSISuite.mdf',
MOVE 'Logical_logfile_name' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\data\BSISuite_log.ldf' , REPLACE
As steve suggested, you can get the information of logical_datafile_name and logical_logfile_name from
restore filelistonly from disk = 'c:\BSISuite_db_200412011801.bak'
-
Beautiful ! Thanks, that did the trick. I knew I was just missing something.
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply