I have been testing the Scripter SMO object $scriptr = new-object ('Microsoft.SqlServer.Management.Smo.Scripter') ($SMOserver) to script the database objects to a text file. I am wanting to use this PowerShell script to be able to rebuild some production databases onto a test environment. The examples I have found to date are related to scripting out the database objects to a Test Flat File. I am wanting a simple way of scripting out the database and then have an automated way of running these generated scripts to build the production database on a test server environment. I am aware of the Options.ScriptDrops to set this to true to be able to Drop objects before creating them.
I notice there is no scripting of the database itself and the database options.
I would like to have a way of generating scripts for all database objects, copy these to a different server and then run these script(s) to create the database. The original and new databases should be identical except for no data within tables of the new database.
There does appear to be a definite need for something like mysqldump. It's something that I'll likely be working on when I've got time.
For scripting of the database itself and the database options, you can use the following code for that:
As for the rest such as tables and views, including dependencies, you can find the code on powershell.com or this alternative script.
Powered by IDERA