Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

DB restore

Posted on 2006-11-16
13
Medium Priority
?
523 Views
Last Modified: 2012-06-27
Not want to affect the online DB, can I restore the backup DB to another folder with another name by sql.
0
Comment
Question by:turbot_yu
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 7
  • 5
13 Comments
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17954914
restore database 'NewName'
FROM disk = 'CompletePath'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17954924
Sorry

    restore database 'NewName'
FROM disk = 'CompletePath'
WITH MOVE 'urMdfPhysicalName' TO 'c:\test\MdfFile.mdf',
   MOVE 'urLogPhysicalName' TO 'c:\test\LogFile.ldf'
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17954928
In order to get the Physical name use

RESTORE FILELIST ONLY
WITH DISK ='Complete path of ur backUp file '
0
NFR key for Veeam Agent for Linux

Veeam is happy to provide a free NFR license for one year.  It allows for the non‑production use and valid for five workstations and two servers. Veeam Agent for Linux is a simple backup tool for your Linux installations, both on‑premises and in the public cloud.

 
LVL 20

Expert Comment

by:Sirees
ID: 17955588
Check "Restoring a Complete Backup to a New Database on the Same Server" and "Restoring a Complete Backup to a New Server"
from this link

http://www.microsoft.com/technet/prodtechnol/sql/2000/maintain/sqlbackuprest.mspx#EMMAC
0
 

Author Comment

by:turbot_yu
ID: 17963059
I tried to

RESTORE DATABASE ming_new_by_sql FROM DISK = 'd:\db\BACKUP\mings.Bak'
WITH MOVE 'ming' TO 'd:\db\ming_bk.mdf'
MOVE 'ming_log' TO 'd:\dbming_bk_log.ldf'

but get error with

Msg 102, Level 15, State 1, Line 3
Incorrect syntax near 'MOVE'.

Any suggestion?
0
 

Author Comment

by:turbot_yu
ID: 17963081
I tried

RESTORE FILELIST ONLY
WITH DISK ='d:\db\BACKUP\ming.Bak'

but error

Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'FILELIST'.
Msg 319, Level 15, State 1, Line 2
Incorrect syntax near the keyword 'with'. If this statement is a common table expression or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.

0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17963094
I am sorry , try with  'FILELISTONLY' (no space b/e those 2 words )


Here is an Example from Books online

This example creates a new database called MyNwind2_Test. MyNwind2_Test is a copy of the existing MyNwind2 database that comprises two files: MyNwind2_data and MyNwind2_log. Because the MyNwind2 database already exists, the files in the backup need to be moved during the restore operation. The RESTORE FILELISTONLY statement is used to determine the number and names of the files in the database being restored.

USE master
GO
-- First determine the number and names of the files in the backup.
-- MyNwind_2 is the name of the backup device.
RESTORE FILELISTONLY
   FROM MyNwind_2
-- Restore the files for MyNwind2_Test.
RESTORE DATABASE MyNwind2_Test
   FROM MyNwind_2
   WITH RECOVERY,
   MOVE 'MyNwind2_data' TO 'D:\MyData\MyNwind2_Test_data.mdf',
   MOVE 'MyNwind2_log' TO 'D:\MyData\MyNwind2_Test_log.ldf'
GO


0
 

Author Comment

by:turbot_yu
ID: 17963217
Now I use the command below to backup, how to restore from ming.bak

USE ming
GO
BACKUP DATABASE ming
TO DISK = 'd:\db\BACKUP\ming.Bak'
GO


Online DB file: d:\db\ming.mdf and  d:\db\ming.ldf
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17963231
Step1.  find the physical filenames

RESTORE FILELISTONLY
FROM  DISK = 'd:\db\BACKUP\ming.Bak'


Say it gave me the files as  Ming_Data  and Ming_log.

Step2 : restore the database as a new name

RESTORE DATABASE Ming_Test
   FROM MyNwind_2
   WITH RECOVERY,
   MOVE 'Ming_data' TO 'D:\MyData\Ming_Test_data.mdf',
   MOVE 'Ming_log'  TO 'D:\MyData\Ming_Test_log.ldf'
GO
0
 

Author Comment

by:turbot_yu
ID: 17963305
I tried

RESTORE DATABASE ming_test
   FROM ming
   WITH RECOVERY,
   MOVE 'Ming' TO 'D:\db\Ming_Test_data.mdf',
   MOVE 'Ming_log'  TO 'D:\db\Ming_Test_log.ldf'
GO


got:

Msg 3206, Level 16, State 1, Line 1
No entry in sysdevices for backup device 'ming'. Update sysdevices and rerun statement.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
0
 

Author Comment

by:turbot_yu
ID: 17963312
use
RESTORE FILELISTONLY
FROM  DISK = 'd:\db\BACKUP\ming.Bak'


got

ming      D:\DB\ming.mdf      D      PRIMARY      2097152      35184372080640      1      0      0      3ED48254-3414-4A20-B79E-8099EA31F3AC      0      0      1376256      512      1      NULL      18000000024400092      54374E32-4B54-490F-82A3-3FF89BE68869      0      1
ming_log      D:\DB\ming_log.ldf      L      NULL      1310720      2199023255552      2      0      0      84604128-9BC9-43F4-A872-99D31E4DDC32      0      0      0      512      0      NULL      0      00000000-0000-0000-0000-000000000000      0      1
0
 
LVL 75

Expert Comment

by:Aneesh Retnakaran
ID: 17963421
oops sorry ...
This will work

RESTORE DATABASE ming_test
   FROM DISK = 'd:\db\BACKUP\ming.Bak' ------------------<---ming
   WITH RECOVERY,
   MOVE 'Ming' TO 'D:\db\Ming_Test_data.mdf',
   MOVE 'Ming_log'  TO 'D:\db\Ming_Test_log.ldf'
0
 
LVL 75

Accepted Solution

by:
Aneesh Retnakaran earned 2000 total points
ID: 17963440
FYI
RESTORE DATABASE ming_test FROM
   DISK = 'd:\db\BACKUP\ming.Bak' -- this is the Backup location, in your case backup is on Disk so yu need to put the path of the filename also
   WITH RECOVERY,
   MOVE 'Ming' TO 'D:\db\Ming_Test_data.mdf',  ------------ 'Ming' is the name of your datafile (the first column value u get when u run the RESTORE FILELISTONLY
   MOVE 'Ming_log'  TO 'D:\db\Ming_Test_log.ldf' ------------
0

Featured Post

Learn Veeam advantages over legacy backup

Every day, more and more legacy backup customers switch to Veeam. Technologies designed for the client-server era cannot restore any IT service running in the hybrid cloud within seconds. Learn top Veeam advantages over legacy backup and get Veeam for the price of your renewal

Question has a verified solution.

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

An alternative to the "For XML" way of pivoting and concatenating result sets into strings, and an easy introduction to "common table expressions" (CTEs). Being someone who is always looking for alternatives to "work your data", I came across this …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
This video shows, step by step, how to configure Oracle Heterogeneous Services via the Generic Gateway Agent in order to make a connection from an Oracle session and access a remote SQL Server database table.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

636 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