Automate Backup of SQL Server Database Structure I have a data warehouse which isn't huge only about 16Gb in a Microsoft SQL Server Standard Edition 2014 database. However, now that it forms part of our production system I don't like developing on it because it can affect our end users.

Therefore what I would like to do is automate a nightly process to:


Backup the structure of warehouse1 
Restore the structure to a warehouse2 on the same server


So in effect I end up with a mirror of warehouse1 database structure in Warehouse2 database. I can then use this database for development. 

As all of the data is retrieved from other locations I don't want to back the data up I just want to do the structure.

Does this seem like the most appropriate method of achieving what I want to do or is there an alternative? If this does sound right would you be able to suggest how I would go about achieving this?

Thanks in advance,

Andrew
Since you are using SQL Server 20141 you could look at using DBCC CLONEDATABASE to create a statistics-only copy of the warehouse database.

See support.microsoft.com for details on the command; however it essentially boils down to:

DBCC CLONEDATABASE (warehouse1, warehouse2);


From the page linked above:


  All files in the target database will inherit the size and growth settings from the model database. File name convention: The file names for the destination database will follow the source_file_name _underscore_random number convention. If the generated file name already exists in the destination folder, DBCC CLONEDATABASE will fail.


You could wrap this in code that drops the current clone, if one exists, then creates the new clone.  This could be added to a SQL Server Agent Job that executes nightly, or weekly, as required.



1 - you'll need service pack 2 or above for SQL Server 2014