Whats the best method to copy a live database to a test database.

Posted on 2011-04-20
Last Modified: 2012-05-11
My application has two databases that are exactly the same.  One is a LIVE database that is actual work data.  I also have a TEST database that is exactly the same SCHEMA as the LIVE.  I allow the users to log in to either the LIVE or TEST database.  The TEST is used for training and working on new stuff that will be later implemented in the LIVE environment.

What is the best and easiest way to be able to duplicate the LIVE database over to the TEST database?  It could be a year later, and the LIVE database has current data in it, so when you playing in the TEST environment you would be best served if you had current data.

I currently have my clients just restore a back up of the LIVE to the TEST database.  But it sure would be nice if I had a script or something that I can just run from my application (Web Application) that I can say basically COPY the LIVE Database Structure and Data over to the TEST Database.

Any good ideas out there?

Cory Jorgensen
Bitco Software
Question by:derftoy
    LVL 16

    Expert Comment

    by:Kalpesh Chhatrala
    Firstly, you need to backup the live database using the below command,

    Backup database <dbname> to disk='Path\filename.bak'

    Now after you have a backup of the database you can restore it using the below command,

    Restore database <dbname> from disk='Path of your backup file\filename.bak' With Replace
    LVL 23

    Expert Comment

    - i found this article on copying database on the same server which might worth a look at:

    - and another one on generating a T-SQL script  to copy complete database schema and all of its objects.


    - use the the Copy Database Wizard
    LVL 16

    Expert Comment

    by:Kalpesh Chhatrala
    LVL 23

    Accepted Solution

    - the Copy Database Wizard link for SQL Server 2008 is here:
    LVL 38

    Expert Comment

    by:Aaron Tomosky
    Just use a batch file calling Sqlcmd. Name it copylivetotest or something. I'll get you the exact Sqlcmd code if you can't fid it but it's really simple
    LVL 4

    Author Closing Comment

    I love this idea.  You can create the wizard and schedule it.  So I can have my customers who want can create the wizard and schedule it to like run once a week and it will duplicate the databse... Awsome!

    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

    This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
    Load balancing is the method of dividing the total amount of work performed by one computer between two or more computers. Its aim is to get more work done in the same amount of time, ensuring that all the users get served faster.
    Via a live example, show how to shrink a transaction log file down to a reasonable size.
    Viewers will learn how to use the SELECT statement in SQL and will be exposed to the many uses the SELECT statement has.

    737 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

    22 Experts available now in Live!

    Get 1:1 Help Now