First published on MSDN on Nov 07, 2008
One question that sometimes arises is "How can we script all database objects throught some kind of automation?"
You have several ways to do it.
For both SQL 2000 and SQL 2005 you have
Microsoft SQL Server Database Publishing Wizard
that generates a single SQL script file which can be used to recreate a database (both schema and data).
Other options are available in
Codeplex
(Open Source Community) for schema purposes:
1)
ScriptDB
It's a console application, that works for both SQL 2000 and SQL 2005.
Creates one script file for each object. You can choose to script objects with bcp.exe and you also have the option of scriting extended properties if you want to.
2)
ExportSQLScript
This is a command line tool which exports both SQL Server 2000 and 2005 objects in a single or multiple scripts files in order to create a database.
It was also designed for revision control purposes.
3)
Database Synchronizer
This command line tool works for both SQL 2000 and SQL 2005 and has three modes of operation:
- Generate scripts for an existing database
- Synchronize between two pre-existing databases
- Take a set of scripts and update the schema of a target database to make it equivalent to those scripts.
You can also do your own script using SQL-DMO or SQL-SMO to response to your own specific needs.
Finally you can read the following Microsoft Support article:
How to generate a script of the necessary database metadata to create a statistics-only databas...
Check it out!
