468,161 Members | 2,090 Online
Bytes | Developer Community
New Post

Home Posts Topics Members FAQ

Post your question to a community of 468,161 developers. It's quick & easy.

replicate a database automation

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?

Thanks for any help, and thanks for reading.
Chris Auer
ch********@methodfactoy.com

Jul 20 '05 #1
2 1263
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
Jul 20 '05 #2

"Chris Auer" <ch********@gmail.com> wrote in message
news:ci********@odbk17.prod.google.com...
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?

Yes and in fact you had better.

Personally I've seen one setup this way and it was a farking mess. ANY
schema change was a royal pain to make.

Backups were complicated, etc.

So if you have to go this way, make sure everything you ever need to do is
well scripted. Ad hoc changes/maintenance will be real tough.


Thanks for any help, and thanks for reading.
Chris Auer
ch********@methodfactoy.com

Jul 20 '05 #3

This discussion thread is closed

Replies have been disabled for this discussion.

Similar topics

4 posts views Thread by | last post: by
6 posts views Thread by Joe | last post: by
9 posts views Thread by advance scout | last post: by
1 post views Thread by gcdp | last post: by
reply views Thread by kamranasdasdas | last post: by
By using this site, you agree to our Privacy Policy and Terms of Use.