sql server dba

Question

sql server dba

babuannam.dba

Newbie

Points: 7
More actions
February 29, 2012 at 5:29 am

#147020

I would like to create 20 databases in an instance and I would like to give same properties to all, How to do in a single shot

Viewing 8 posts - 1 through 7 (of 7 total)

You must be logged in to reply to this topic. Login to reply

sumitagarwal781 Default port Points: 1493 More actions · Answer 1

You can create temp table and insert the all the database names. and Execute below statement to get the script of create database , copy the result in query analyzer and execute.

Select 'Create Database' name from #temptable

java

Eugene Elutin SSC Guru Points: 59322 More actions · Answer 2

script it!

Id you don't know how, create the one empty database in SSMS, then script it out.

Copy-&-Paste with replacing the db name part.

_____________________________________________
"The only true wisdom is in knowing you know nothing"
"O skol'ko nam otkrytiy chudnyh prevnosit microsofta duh!":-D
(So many miracle inventions provided by MS to us...)

How to post your question to get the best and quick help[/url]

Divine Flame SSCoach Points: 15943 More actions · Answer 3

babuannam.dba (2/29/2012)
I would like to create 20 databases in an instance and I would like to give same properties to all, How to do in a single shot

Two ways:

1. Write the script for the creation of all 20 databases with whatever properties you want to set. Execute the script.

2. Change the required properties in the model database itself. Now create those 20 databases. (Asked in Interview??)

Sujeet Singh

spaghettidba SSC Guru Points: 105732 More actions · Answer 4

Change the properties in the model database, then create the 20 databases simply using the CREATE DATABASE statement.

Every new database is created from a copy of the model database.

Don't forget to revert the model database to the initial state.

-- Gianluca Sartori

Er.karthikn SSC Journeyman Points: 94 More actions · Answer 5

Hi,

system databases: master,MODEL,msdb,tempDB.

Method 1:

In which MODEL Database is act as a template for creating a new database,

before creating your database as per your properties(need) you can modify it and then you create your 20 DB through normal create database.....

Method 2:

You can also implement this through script try that too...

method 1 simple way to implement.

with Regards

Er.karthikn

Jayanth_Kurup SSC-Insane Points: 22967 More actions · Answer 6

I frankly wouldn't advice doing it using the model database:-

The model database is a template for all future databases. Do not change it unless you want the change in all future versions.

Instead try creating a template script and execute it by passing sqlcmd variable.

This way you are not modifying a system database on an ad hoc basis and have a reusable script which can be checkin to source control

Jayanth Kurup[/url]

spaghettidba SSC Guru Points: 105732 More actions · Answer 7

Jayanth_Kurup (3/5/2012)
I frankly wouldn't advice doing it using the model database:-
The model database is a template for all future databases. Do not change it unless you want the change in all future versions.

This is the purpose of the model database. I don't see this as an issue.

Instead try creating a template script and execute it by passing sqlcmd variable.
This way you are not modifying a system database on an ad hoc basis and have a reusable script which can be checkin to source control

Good point.

-- Gianluca Sartori