• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 397
  • Last Modified:

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?
0
GentooOS
Asked:
GentooOS
  • 5
  • 5
1 Solution
 
Kevin3NFCommented:
See Books Online topic RESTORE DATABASE and RESTORE LOG in Books Online....

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.mdf',
      MOVE 'MyNwindLog1' TO 'c:\Program Files\Microsoft SQL Server\MSSQL\Data\NewNwind.ldf'

RESTORE LOG MyNwind
   FROM MyNwindLog1 --change to physical file location if necessary
   WITH RECOVERY

0
 
GentooOSAuthor Commented:
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_old.mdf and log file is db_old_log.ldf in the same location.  Backups would be c:\program files\database\backups\db_old\db_old.bak and db_old.trn

Thanks.
0
 
Kevin3NFCommented:
RESTORE DATABASE db_new
   FROM Disk = "c:\program files\database\backups\db_old\db_old.bak"
   WITH NORECOVERY,
      MOVE 'db_old_data' TO 'c:\program files\database\db_New\db_New.mdf',
      MOVE 'db_old_log' TO 'c:\program files\database\db_new\db_new.ldf'

RESTORE LOG db_new
   FROM Disk = "c:\program files\database\backups\db_old\db_old.trn"
   WITH RECOVERY


I may be off on the restore log part...beef a long time since I had to do one manually....

This is a logical file name:
MOVE 'db_old_data'

This is a physical file name:
 'c:\program files\database\db_New\db_New.mdf'
0
Cloud Class® Course: CompTIA Cloud+

The CompTIA Cloud+ Basic training course will teach you about cloud concepts and models, data storage, networking, and network infrastructure.

 
GentooOSAuthor Commented:
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.
0
 
Kevin3NFCommented:
Post your exact sql statement please...
0
 
GentooOSAuthor Commented:
RESTORE DATABASE PCMS_New FROM DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\ERIE_PCMS_INIT\ERIE_PCMS_INIT_db_200504151209.BAK'
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.
0
 
Kevin3NFCommented:
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...
0
 
GentooOSAuthor Commented:
Why does the current location matter?  I'm restoring from at backup.

Thery are here:

C:\PCMS\ERIE_PCMS\ERIE_PCMS_INIT_Data.MDF
C:\PCMS\ERIE_PCMS\ERIE_PCMS_INIT_Log.LDF
0
 
Kevin3NFCommented:
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)
0
 
GentooOSAuthor Commented:
OK. the name was different under data files from the name of the database.  Thanks that did the trick.
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

Join & Write a Comment

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

  • 5
  • 5
Tackle projects and never again get stuck behind a technical roadblock.
Join Now