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!!
Who is Participating?

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

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.

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.

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
Active Protection takes the fight to cryptojacking

While there were several headline-grabbing ransomware attacks during in 2017, another big threat started appearing at the same time that didn’t get the same coverage – illicit cryptomining.


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.
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

From novice to tech pro — start learning today.