Automatic provisionning - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2008 Forums

SQL Server Administration (2008)

Automatic provisionning

Author

Topic

prodsac
Starting Member

8 Posts

Posted - 2011-04-12 : 17:38:43

Hi,

I need to make a probably simple thing.

I need to Create 100 database from a Master one.

Is there a way to automate it?

I will:

detach DB

USE [master]
GO
EXEC master.dbo.sp_detach_db @dbname = N'MASTERDB',
@keepfulltextindexfile = N'true'
GO

copy the DB files with powershell (copy-items 01..100)

Attach the databases and change the dbowners

USE [master]
CREATE DATABASE [NEWDB01] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\NEWDB01.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\NEWDB01_log.ldf' )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N'NEWDB01'
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [NEWDB01].dbo.sp_changedbowner @loginame=N'DOMAIN\USER01',
@map=false
GO

USE [master]
CREATE DATABASE [NEWDB02] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\NEWDB02.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\NEWDB02_log.ldf' )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N'NEWDB02'
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [NEWDB01].dbo.sp_changedbowner @loginame=N'DOMAIN\USER02',
@map=false
GO

and so on..

Is there a way to push variables? Or make it simpler?

thanks

robvolk
Most Valuable Yak

15732 Posts

Posted - 2011-04-12 : 17:58:13

Better to make a backup of your database and just restore it, rather than detach, copy and attach.

prodsac
Starting Member

8 Posts

Posted - 2011-04-12 : 21:43:40

thanks, but is there a way I could change the following statement

USE [master]
CREATE DATABASE [NEWDB01] ON
( FILENAME = N'C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\NEWDB01.mdf' ),
( FILENAME = N'C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\NEWDB01_log.ldf' )
FOR ATTACH
GO
IF EXISTS ( SELECT name
FROM master.sys.databases sd
WHERE name = N'NEWDB01'
AND SUSER_SNAME(sd.owner_sid) = SUSER_SNAME() )
EXEC [NEWDB01].dbo.sp_changedbowner @loginame=N'DOMAIN\USER01',
@map=false
GO

to made it do a loop on the "NEWDB01", to make him do NEWDB01, NEWDB02, NEWDB03... NEWDB100?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 11:19:20

I would say

1. as robvolk said make one backup
2. then create a script that loops however many times you want to
3. create db and restore from source
4. If you want this to run weekly put it in sql job

you better have some good amount of space in C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA
If you don't have the passion to help people, you have no passion

prodsac
Starting Member

8 Posts

Posted - 2011-04-13 : 11:40:26

thanks.

Can you tell me why it is better to backup and copy than detach and copy?

Thanks

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 11:43:56

it is not about better. masterdb might be used by other processes. you detach it and ....

If you don't have the passion to help people, you have no passion

prodsac
Starting Member

8 Posts

Posted - 2011-04-13 : 11:57:46

Ok.
so something like that?

BACKUP DATABASE { MASTERDB | @MASTERDB_var }
TO "C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\MASTERDB.bak"
;

DROP DATABASE NEWDB01
CREATE DATABASE NEWDB01
RESTORE DATABASE { NEWDB01 | @NEWDB01_var }
FROM "C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\MASTERDB.bak"
;

DROP DATABASE NEWDB02
CREATE DATABASE NEWDB02
RESTORE DATABASE { NEWDB02 | @NEWDB02_var }
FROM "C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\MASTERDB.bak"
;

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 12:14:23

yes something like that. you probably want to make it dynamic which might involve dynamic query. what if tomorrow you want to provision 200 more databases are you going to come in there and add one more ?

If you don't have the passion to help people, you have no passion

prodsac
Starting Member

8 Posts

Posted - 2011-04-13 : 12:26:59

Yes.

That's what I was asking before, is there a way to loop in it?

Probably something like that?
-------------

CREATE TABLE DBNAMELIST (DBNAME VARCHAR(20))
INSERT INTO DBNAMELIST SELECT "NEWDB1"
INSERT INTO DBNAMELIST SELECT "NEWDB2"
INSERT INTO DBNAMELIST SELECT "NEWDB3"

DROP DATABASE NEWDB1
DROP DATABASE NEWDB2
DROP DATABASE NEWDB3

EXEC CREATE_NEWDB

CREATE PROCEDURE CREATE_NEWDB AS
DECLARE @QUERY VARCHAR(2000)
DECLARE @DBNAME VARCHAR(25)
--XP_SENDMAIL

DECLARE MAK_CURSOR CURSOR FOR
SELECT DBNAME FROM MAK.DBO.DBNAMELIST

OPEN MAK_CURSOR
FETCH NEXT FROM MAK_CURSOR INTO @DBNAME

WHILE (@@FETCH_STATUS = 0)
BEGIN
SET @QUERY = RESTORE DATABASE { "+@DBNAME" | "+@DBNAME_var" }
FROM "C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\"+ @DBNAME+".bak"

PRINT @QUERY

EXEC (@QUERY)
FETCH NEXT FROM MAK_CURSOR INTO @DBNAME

END
CLOSE MAK_CURSOR
DEALLOCATE MAK_CURSOR

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 12:42:22

exactly! plug it into a sql job and you should be good to go. test it out good though maybe debug it step by step

you might want to add IF NOT EXISTS before you add a db name in the dbconfig table list. otherwise you will gave dups and ....

If you don't have the passion to help people, you have no passion

prodsac
Starting Member

8 Posts

Posted - 2011-04-13 : 12:47:47

thanks, it' s the first time I automate anything in SQL lol

yosiasz
Master Smack Fu Yak Hacker

1635 Posts

Posted - 2011-04-13 : 13:09:19

why are you using C:\Program Files\Microsoft SQL Server\INSTANCE\MSSQL10_50.INSTANCE\MSSQL\DATA\ rather nauseating. do you have storage elsewhere. because if your server goes caputs so do all these dbs. do you have good backup? either way i would suggest putting it somewhere else.
is this real world or just a homework? dev or prod?
also you will need to add script for backup in there.

If you don't have the passion to help people, you have no passion

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources