derftoy
asked on
Whats the best method to copy a live database to a test database.
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?
Thanks,
Cory Jorgensen
Bitco Software
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?
Thanks,
Cory Jorgensen
Bitco Software
- i found this article on copying database on the same server which might worth a look at:
http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.
- and another one on generating a T-SQL script to copy complete database schema and all of its objects. http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
OR
- use the the Copy Database Wizard
http://msdn.microsoft.com/en-us/library/ms188664(v=sql.90).aspx
http://weblogs.asp.net/mschwarz/archive/2004/08/26/220735.
- and another one on generating a T-SQL script to copy complete database schema and all of its objects. http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
OR
- use the the Copy Database Wizard
http://msdn.microsoft.com/en-us/library/ms188664(v=sql.90).aspx
another way with script to copy database step by step
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
http://blog.sqlauthority.com/2007/08/21/sql-server-2005-create-script-to-copy-database-schema-and-all-the-objects-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
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
ASKER
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!
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