GentooOS
asked on
Need To Restore Database And Transaction Log Using Query Analyzer
Using only Query Analyzer I'd like to restore a database and logs to a new database on the same server. The backups are .BAK and .TRN files located on the same server. How do I do this? What is the syntax?
ASKER
I must be messing up the physical file locaton or syntax but it is not working.
Can you give an example if the old database name is db_old and the new one is db_new. db_old files are c:\program files\database\db_old\db_o
Thanks.
Can you give an example if the old database name is db_old and the new one is db_new. db_old files are c:\program files\database\db_old\db_o
Thanks.
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
ASKER
I get this:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'db_old' is not part of database 'db_new'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'db_old' is not part of database 'db_new'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Post your exact sql statement please...
ASKER
RESTORE DATABASE PCMS_New FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ERIE_P
WITH NORECOVERY,
MOVE 'ERIE_PCMS_INIT' TO 'c:\new.mdf',
MOVE 'ERIE_PCMS_INIT_log' TO 'c:\new_log.ldf'
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'ERIE_PCMS_INIT' is not part of database 'PCMS_New'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
WITH NORECOVERY,
MOVE 'ERIE_PCMS_INIT' TO 'c:\new.mdf',
MOVE 'ERIE_PCMS_INIT_log' TO 'c:\new_log.ldf'
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'ERIE_PCMS_INIT' is not part of database 'PCMS_New'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Go into the properties of the old db currently on the server through Enterprise Manager and get the logical file names for the data and log files...
ASKER
Why does the current location matter? I'm restoring from at backup.
Thery are here:
C:\PCMS\ERIE_PCMS\ERIE_PCM
C:\PCMS\ERIE_PCMS\ERIE_PCM
Thery are here:
C:\PCMS\ERIE_PCMS\ERIE_PCM
C:\PCMS\ERIE_PCMS\ERIE_PCM
I was looking for the logical file names, not the loation of the physical files. Its the first column in the "data files" and "transaction log" tabs (i.e. Pubs and Pubs_log)
ASKER
OK. the name was different under data files from the name of the database. Thanks that did the trick.
Assuming MyNWind already exists and is your source db...
RESTORE DATABASE MyNwind_New
FROM MyNwind_1 --change to physical file location if necessary
WITH NORECOVERY,
MOVE 'MyNwind' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind
MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind
RESTORE LOG MyNwind
FROM MyNwindLog1 --change to physical file location if necessary
WITH RECOVERY