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

Posted on 2011-10-03
Last Modified: 2012-05-12
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!!
Question by:J3D1-KN1G1-1t
    LVL 75

    Assisted Solution

    by:Aneesh Retnakaran
    run these statements on ssms

    backup database shopTest to disk = 'c:\shopTest.bak'
    restore database shopTest from disk =  'c:\shopTest.bak'

    Author Comment

    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
    LVL 75

    Accepted Solution

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

    Assisted Solution


    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'
    LVL 10

    Expert Comment

    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
    LVL 75

    Expert Comment

    by:Anthony Perkins

    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.

    Write Comment

    Please enter a first name

    Please enter a last name

    We will never share this with anyone.

    Featured Post

    How your wiki can always stay up-to-date

    Quip doubles as a “living” wiki and a project management tool that evolves with your organization. As you finish projects in Quip, the work remains, easily accessible to all team members, new and old.
    - Increase transparency
    - Onboard new hires faster
    - Access from mobile/offline

    Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
    In this article I will describe the Detach & Attach method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

    761 members asked questions and received personalized solutions in the past 7 days.

    Join the community of 500,000 technology professionals and ask your questions.

    Join & Ask a Question

    Need Help in Real-Time?

    Connect with top rated Experts

    7 Experts available now in Live!

    Get 1:1 Help Now