?
Solved

Need To Restore Database And Transaction Log Using Query Analyzer

Posted on 2005-04-15
10
Medium Priority
?
393 Views
Last Modified: 2012-05-05
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
Comment
Question by:GentooOS
  • 5
  • 5
10 Comments
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13793591
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
 

Author Comment

by:GentooOS
ID: 13807287
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
 
LVL 21

Accepted Solution

by:
Kevin3NF earned 2000 total points
ID: 13808405
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
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 

Author Comment

by:GentooOS
ID: 13808928
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13808968
Post your exact sql statement please...
0
 

Author Comment

by:GentooOS
ID: 13808982
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13809043
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
 

Author Comment

by:GentooOS
ID: 13809124
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
 
LVL 21

Expert Comment

by:Kevin3NF
ID: 13809172
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
 

Author Comment

by:GentooOS
ID: 13809616
OK. the name was different under data files from the name of the database.  Thanks that did the trick.
0

Featured Post

Hire Technology Freelancers with Gigs

Work with freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely, and get projects done right.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…
Suggested Courses

807 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question