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

Microsoft SQL server 2008

I have a one database(003) in my microsoft sql 2008 server and i would like to copy all 003 database to my test database(099).

i got backup from 003database.bak ,can i restore it on 099database? how?

Thanks
0
apollo-13
Asked:
apollo-13
  • 4
  • 2
  • 2
3 Solutions
 
apollo-13Author Commented:
0
 
Scott PletcherSenior DBACommented:
Yes, you can restore a db to a different name, but you will have to specify every file name explicitly in the restore.

The general format is:

RESTORE DATABASE [099database]
FROM DISK = 'b:\full\path\to\backup\file\033database.bak'
WITH
    MOVE '<logical_file_name1>' TO 'd:\full\path\to\data\file\099database.mdf',
    MOVE '<logical_file_name2>' TO 'l:\full\path\to\data\file\099database_log.ldf'

You must specify every logical file name in the original file in a "MOVE" clause with the new file name to restore it to.

You can get a list of all logical file names in the backup using this command:

RESTORE FILELISTONLY
FROM DISK = 'b:\full\path\to\backup\file\033database.bak'
0
 
apollo-13Author Commented:
can you please explain me how under sql server management studio?

I just want to liitle bit test on database 099 but databse 099 is old information, our new database name is 003 and i thought i can take a backup from 003 and restore it on 099 then i have a new information on 099 .

is that correct?
0
Cloud Class® Course: C++ 11 Fundamentals

This course will introduce you to C++ 11 and teach you about syntax fundamentals.

 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In SSMS right-click on 003 database and chose Tasks / Restore / Database.
Select the last full backup and in the TO: field write 099 so it would replace that database with the backup from 003. In the same screen but in the Options tab chose the Overwrite option and on the Restore table click on the (...) so you can give the new location for the files.
Press OK and the Restore will start.
0
 
Scott PletcherSenior DBACommented:
>> can you please explain me how under sql server management studio? <<

Vitor has done so.  I will not, simply because it is too error prone.  You will have to fully specify all the file locations and names every time you do a restore.  And you can't be sure after it runs what names you specified at the time you did it.  In my view, therefore, written command should always be used to do these types of restores.  It's your choice, of course, I'm just pointing out what I think will help you avoid problems in the future.

I need to make one adjustment: if the db already exists, you need to add "REPLACE" to the WITH options:

RESTORE DATABASE [099database]
FROM DISK = 'b:\full\path\to\backup\file\033database.bak'
WITH
    REPLACE,
    MOVE '<logical_file_name1>' TO 'd:\full\path\to\data\file\099database.mdf',
    MOVE '<logical_file_name2>' TO 'l:\full\path\to\data\file\099database_log.ldf'
    --MOVE ...
0
 
apollo-13Author Commented:
sorry viktor ,i cant exaclty understand ,is there a way with screenshot guiede?
0
 
Vitor MontalvãoMSSQL Senior EngineerCommented:
In SSMS right-click on 003 database and chose Tasks / Restore / Database.
Restore.PNG
Select the last full backup and in the TO: field write 099 so it would replace that database with the backup from 003.
Restore099.PNG
In the same screen but in the Options tab chose the Overwrite option and on the Restore table click on the (...) so you can give the new location for the files.
RestoreOptions.PNG
Press OK and the Restore will start.
0
 
apollo-13Author Commented:
perfeckt
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: SSL Checker

Scans your site and returns information about your SSL implementation and certificate. Helpful for debugging and validating your SSL configuration.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

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