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
apollo-13Asked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

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
The 7 Worst Nightmares of a Sysadmin

Fear not! To defend your business’ IT systems we’re going to shine a light on the seven most sinister terrors that haunt sysadmins. That way you can be sure there’s nothing in your stack waiting to go bump in the night.

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

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
apollo-13Author Commented:
perfeckt
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.