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

  • The scripts for most objects are either available in SYS catalogs (such as sProcs and Views) or else the objects themselves are simple enough to reconstruct from their settings (like Synonyms and Schemas). Unfortunately there is one exception and it's huge: Tables. Their scripts aren't saved anywhere in SQL Server and they are so complex that reconstructing is a very big deal. That said, there are some SQL scripts around for scripting tables about 98%. Commented Jul 26, 2017 at 20:55

1 Answer 1


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.


Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.

Not the answer you're looking for? Browse other questions tagged or ask your own question.