Improve company productivity with a Business Account.Sign Up

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 528
  • Last Modified:

DB restore

Not want to affect the online DB, can I restore the backup DB to another folder with another name by sql.
0
turbot_yu
Asked:
turbot_yu
  • 7
  • 5
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
restore database 'NewName'
FROM disk = 'CompletePath'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
Sorry

    restore database 'NewName'
FROM disk = 'CompletePath'
WITH MOVE 'urMdfPhysicalName' TO 'c:\test\MdfFile.mdf',
   MOVE 'urLogPhysicalName' TO 'c:\test\LogFile.ldf'
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
In order to get the Physical name use

RESTORE FILELIST ONLY
WITH DISK ='Complete path of ur backUp file '
0
Build your data science skills into a career

Are you ready to take your data science career to the next step, or break into data science? With Springboard’s Data Science Career Track, you’ll master data science topics, have personalized career guidance, weekly calls with a data science expert, and a job guarantee.

 
SireesCommented:
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
 
turbot_yuAuthor Commented:
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
 
turbot_yuAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
turbot_yuAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
turbot_yuAuthor Commented:
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
 
turbot_yuAuthor Commented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
 
Aneesh RetnakaranDatabase AdministratorCommented:
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
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: ZipGrep

ZipGrep is a utility that can list and search zip (.war, .ear, .jar, etc) archives for text patterns, without the need to extract the archive's contents.

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

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