Generate Database Creation Script Within A Stored Procedure

I'm looking to automate a wipe of our test environment to bring it up to be in-line with production at the end of every sprint.

As it stands this is currently being completed by the test enviroment grabbing the full backup and doing a restore with move&replace however this is using up most of the space we have.

The intention is to create the environment from scratch and populate only the needed tables.

I can use the 'Right Click > Tasks > Generate Scripts...' to create the framework of the database.

Is there a way to create that script from within a stored procedure so that can be used to re-create the database

Also I'm working on this bit as it stands but obviously that script will just create the database at its current size so all of the File Sizes need to be modified once the script has been generated

Cheers for any help

There are several methods you could use. In order of difficulty (easiest to hardest IMHO)

• Manually create (using generate scripts) a structure only DB populate it with just the data you need for the copy and then backup/restore it. (you could also do it structure only and code the data copy)

• Up side - This is the easiest method to code and should be fairly quick

• Down side - This requires constant maintenance as the code/data of your source database changes.

• Manually create the scripts and have the SP run them (probably using dynamic SQL) and copy the data.

• Up side - Also fairly easy to implement and doesn't require an additional "model" DB that you will copy.

• Down side - Also requires constant maintenance

• Powershell - You use a POSH script to create the new db, generate the scripts and run them.

• Up side - This is the most powerful method. It won't require much maintenance and you can probably even find a script someone else has written as a starting point.

• Down side - This requires that you know Powershell at least a little bit. (Which might be considered an up side as well)

• OLE Automation Procedures - sp_Create etc. This is going to be the most difficult/complicated method (knowing at least a little bit of this and PoSH) but once written also won't require that much maintenance.

• Up Side - Minimal maintenance and it can all be run inside of SQL

• Down side - more difficult code, you probably can't find a script already built and you have to enable OLE Automation Procedures (a security risk, if minimal).

I should probably note that my original thought was you wanted this to be re-producible. If you only have to do it once then @MaxVernon is right. Truncate the tables you don't need data in (or delete what you don't need) and shrink. You may have to do this a bit at a time depending on how constrained you are for space.

StackExchange Database Administrators Q#116600, answer score: 5