Post reply

sql server dba

  • 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

  • February 29, 2012 at 5:43 am

    #1453095

    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

  • February 29, 2012 at 5:43 am

    #1453097

    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]

  • February 29, 2012 at 5:59 am

    #1453104

    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

  • February 29, 2012 at 6:21 am

    #1453121

    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

  • March 5, 2012 at 12:05 am

    #1454647

    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

  • March 5, 2012 at 12:21 am

    #1454649

    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]

  • March 5, 2012 at 1:38 am

    #1454671

    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

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

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