Post reply

Database Cloning?

  • February 17, 2005 at 12:52 am

    #163687

    Hi fellow DBA's,

    While I've been a DBA for a few years now, I'm rather green when it comes to SQL Server so please forgive the basic nature of this query.

    Some developer types would like a database to be cloned, ie copied with all the objects, users, permissions, etc just without the data, so that only the bare bones/schema exists in the new one. Just wondering the best way of doing this?

    I figured using DTS as it's worked good for the noddy N'wind DB, but is this the best way?

    Thanks in advance,

    Jules

  • February 17, 2005 at 1:59 am

    #542728

    I have two database servers where the logins have been transferred so they both have the same sid values.

    This means that I can simply do an automated BACKUP and RESTORE and know that the database will work on both machines.

    In your case, you simply need to add a script to TRUNCATE all tables after performing your RESTORE.

    LinkedIn Profile

  • February 18, 2005 at 12:06 am

    #543015

    I think DTS (Import objects) will be the best.  Save the package because they might want to do it in the future again.

    5ilverFox
    Consulting DBA / Developer
    South Africa

  • February 18, 2005 at 4:27 am

    #543044

    You could always just script out all of the databse objects, logins, tiggers DRI etc from the primary DB. Modify the script if the location or DB name needs to be changed. Save the script and you have a reproducable (and documentable - is that a real word :blush soultion.

  • February 18, 2005 at 9:13 am

    #543106

    I would generate a script on the database to be cloned, create a new db and execute the script on that DB.

    In SQL EM, right click on the db to be cloned and select the 'Generate a script' option. Click the Show All button on the General tab then tick 'Script All Objects'. Go to the  Options tab and tick all Security Scripting and Table Scripting Options.

    Don't save the script, the cloned database will probably change and it's easy to generate another script.

     

  • February 18, 2005 at 9:34 am

    #543108

    If SQL server logins need to be exported to a new server, as in arthurgar situation, all SQL user, including users of other database will be scripted.  You may not want all users in on the 2nd server,  In addition, I found that the script will change users' default server to Master database, which is a problem when relying on users to connect to their default database. 

  • February 18, 2005 at 11:54 am

    #543137

    AFAIK there are 2 stored proc (by MS)  which do that for you. So you would have the exact clone of your database without data. Unfortunately, I used it last time long time ago, so cannot locate them at the moment.

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

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