MS SQL Stored procedure to generate a script create a copy of existing database

Question

I want some help with creating a copy of my database in SQL Server 2008. So I found this article http://blog.sqlauthority.com/2011/05/07/sql-server-2008-2008-r2-create-script-to-copy-database-schema-and-all-the-objects-data-schema-stored-procedure-functions-triggers-tables-views-constraints-and-all-other-database-objects/
which tells how to generate a script that creates a copy of the entire database including the data, key constraints, triggers etc, this is great but I want to automate the process such that once every month or so I can run the stored procedure that will generate this script to create a copy of the database with all the current data and run the script to create a copy of database. Is this possible to do? Please help.

Thanks

score 0 · Answer 1 · answered May 09 '14 at 19:34

Assuming it would be acceptable to perform a backup and restore of the database - this is what the code might look like to BACKUP and RESTORE AdventureWorks

BACKUP DATABASE [AdventureWorks2012] TO  DISK = N'd:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH NOFORMAT, NOINIT,  NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Note that the "copied" database's name is AdventureWorks2012_Copy

RESTORE DATABASE [AdventureWorks2012_Copy] FROM  DISK = N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\Backup\AdventureWorks2012.bak' 
WITH  FILE = 1,  MOVE N'AdventureWorks2012_Data' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Copy_Data.mdf',  
MOVE N'AdventureWorks2012_Log' TO N'D:\Program Files\Microsoft SQL Server\MSSQL11.MSSQLSERVER\MSSQL\DATA\AdventureWorks2012_Copy_log.ldf',  NOUNLOAD,  STATS = 5

GO

The code is a little verbose as a result of it being generated by scripting SSMS GUI functionality.

You may also want to consider whether or not you want to delete the .bak files, or what process you might want to put in place to do this repeatedly.

MS SQL Stored procedure to generate a script create a copy of existing database

1 Answers1