Chris Auer (ch********@gmail.com) writes:
Hopeing this is something I can accomplish. I use MS-SQL for close to
100 CMS web servers. Everytime I create a new website I have to create
a new database and then use the export from SQL to SQL option and copy
all data / SP's / etc... Just no security information. My question is
can I automate this? Can I create a script that will run all this?
Of course you can. The best would be to have the source for the database
under version control, and then build new databases from that. It can
be as simple as a BAT file getting the source from the VCS and then
loading the files with OSQL. It can be far more sophisticated tool as
well. The SQL Server Resource Kit comes with a Stored Procedure builder
that I have to confess not having tried myself. In our shop we use our
own tool DBBUILD, actually available for free on
http://www.abaperls.se/abaperls.
But the very most simplest may simply to be to create a database with
everything you need, and then take a backup of that database. When you
need a new customer database, you restore this database to the new name:
RESTORE newdb FROM DISK = C:\backup\templatedb.bak WITH
MOVE 'datafile' TO = C:\MSSQL\data\newdb.mdf,
MOVE 'logfile' TO = C:\MSSQL\log\newdb.ldf,
REPLACE
This can be put in stored procedure that builds the statment dynamically
given the name of the new database.
--
Erland Sommarskog, SQL Server MVP,
es****@sommarskog.se
Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techinf...2000/books.asp 
