Schedule Generation of All Object Creation Scripts for All Specified SQL Server Databases
How to schedule generation of all object creation scripts
Introduction
You can use the Microsoft provided utility sqlpubwiz to create a script file which looks like the file created in SQL Server Management Studio when you choose:
Tasks > Generate Scripts > Choose Objects (Entire database and all objects)
Batch File Code
You can then create a calling batch file which calls the above ScriptDB multiple times for different databases/instances. For example...
This batch can then be scheduled using Windows Scheduler.
Because each script file is named based on today's date, you won't overwrite any earlier scripts if you schedule once per day. If you want to create a script file including the time of day (so the schedule can run more than once a day), include "t1
" in the "set filea
" line as needed.
History
- 22nd April, 2022: Initial version