• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • 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!!
3 Solutions
Aneesh RetnakaranDatabase AdministratorCommented:
run these statements on ssms

backup database shopTest to disk = 'c:\shopTest.bak'
restore database shopTest from disk =  'c:\shopTest.bak'
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
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.
Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.


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'
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
Anthony PerkinsCommented:

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.

Featured Post

Veeam Disaster Recovery in Microsoft Azure

Veeam PN for Microsoft Azure is a FREE solution designed to simplify and automate the setup of a DR site in Microsoft Azure using lightweight software-defined networking. It reduces the complexity of VPN deployments and is designed for businesses of ALL sizes.

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