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

SQL :Backing up database and then restoring it to use a test database

Hi Guys,

I am using SQL Server 2008 and I want to back up a database and then restore it with a different name (i.e Shop_test) and use it as a test database. Can some please tell me how i can do that.

Many Thanks!!
0
J3D1-KN1G1-1t
Asked:
J3D1-KN1G1-1t
3 Solutions
 
Aneesh RetnakaranDatabase AdministratorCommented:
run these statements on ssms

backup database shopTest to disk = 'c:\shopTest.bak'
go
restore database shopTest from disk =  'c:\shopTest.bak'
0
 
J3D1-KN1G1-1tAuthor Commented:
Hi Aneeshattingal,

When i'm restoring the database, am i then able to change the databasse name to shop_test?

For example i will be backing up "Shop" and then i want to restore the DB but call it "Shop_Test" so i can use it as test DB.

Many Thanks
0
 
Anthony PerkinsCommented:
>>When i'm restoring the database, am i then able to change the databasse name to shop_test?<<
You will have to change the name or as it stands (without using REPLACE) you will get an error.
0
Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

 
jmcmunnCommented:

Assuming I backed up a database called Test into C"\Test.bak, I can restore it to a new database called Test2 like so...change data directories as needed.

RESTORE DATABASE [Test2] FROM  DISK = N'C:\Test.bak' WITH  FILE = 1,  MOVE N'Test' TO N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test2.mdf',  MOVE N'Test_log' TO N'c:\Program Files (x86)\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\Test2_1.ldf'
0
 
Ramesh Babu VavillaCommented:
for your requirement, you have o go with Replication usin pull subcription,.
the other optins such as Log shipping and mirroring can be used for high availabilty,


you can also use BCP (Bulk Copy tool) for copying the database
0
 
Anthony PerkinsCommented:
sqlservr,

I suspect you posted here in error.  The question has to do with backup and restore and has nothing to do with Replication or log shipping, let alone database mirroring.
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

Ultimate Tool Kit for Technology Solution Provider

Broken down into practical pointers and step-by-step instructions, the IT Service Excellence Tool Kit delivers expert advice for technology solution providers. Get your free copy now.

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