September 8, 2003 at 2:13 pm
I need to restore a SQL 2000 backup from ServerA to ServerB. If I copy the .bak file from ServerA to ServerB, I get an error about the location of the file. (The two servers have different logical drive locations.) If I try to restore with the 'MOVE' option, manually through Query Analyzer, I get a message that there is no entry in the Sysdevices table. Any suggestions?
I copied the code I found in Query Analyzer. The database I want to copy is called Track, and the .bak file name is track_db_200309010100.BAK.
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM MyNwind_1
-- Restore the files for MyNwind.
RESTORE DATABASE MyNwind
FROM MyNwind_1
WITH NORECOVERY,
MOVE 'MyNwind_data_1' TO 'D:\MyData\MyNwind_data_1.mdf',
MOVE 'MyNwind_data_2' TO 'D:\MyData\MyNwind_data_2.ndf'
September 8, 2003 at 2:21 pm
Do you have a backup device called MyNwind_1 created which points to your backup file.
If not use FROM DISK = <path\filename> instead of MuNwind_1
eg
FROM DISK = 'c:\track_db_200309010100.BAK'
Steven
September 9, 2003 at 8:59 am
OK - That worked, but now I have a different problem. The statement completes through Query Analyzer. The SQL Log shows the restore was complete. But Enterprise Manager shows the database as Loading and I cannot access the database. Any ideas? I copied my exact statement below.
USE master
GO
-- First determine the number and names of the files in the backup.
RESTORE FILELISTONLY
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ffres_db_200308010100.bak'
-- Restore the files for MyNwind.
RESTORE DATABASE AugTrack
FROM DISK = 'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ffres_db_200308010100.bak'
WITH NORECOVERY,
MOVE 'Ffres_Data' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Ffres_Data.mdf',
MOVE 'Ffres_Log' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\Ffres_Log.ldf'
September 9, 2003 at 9:27 am
RESTORE DATABASE AugTrack WITH RECOVERY
September 10, 2003 at 9:17 am
Duh...Thanks!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply