Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Database Cloning? Expand / Collapse
Author
Message
Posted Thursday, February 17, 2005 12:52 AM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Tuesday, June 11, 2013 12:34 AM
Points: 19, Visits: 15
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
Post #162267
Posted Thursday, February 17, 2005 1:59 AM
SSCrazy

SSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazySSCrazy

Group: General Forum Members
Last Login: Yesterday @ 7:36 AM
Points: 2,907, Visits: 1,832
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
Newbie on www.simple-talk.com
Post #162284
Posted Friday, February 18, 2005 12:06 AM


SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Thursday, September 11, 2014 7:32 AM
Points: 130, Visits: 186
I think DTS (Import objects) will be the best.  Save the package because they might want to do it in the future again.

5ilverFox
Namakwa Sands
South Africa
Post #162629
Posted Friday, February 18, 2005 4:27 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Wednesday, May 7, 2014 5:23 AM
Points: 197, Visits: 152
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.


Post #162662
Posted Friday, February 18, 2005 9:13 AM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Tuesday, December 4, 2012 10:37 AM
Points: 274, Visits: 96

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.

 

Post #162739
Posted Friday, February 18, 2005 9:34 AM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Thursday, March 17, 2005 8:13 AM
Points: 41, Visits: 1

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. 

Post #162741
Posted Friday, February 18, 2005 11:54 AM


Hall of Fame

Hall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of FameHall of Fame

Group: General Forum Members
Last Login: Yesterday @ 5:54 PM
Points: 3,175, Visits: 6,021

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.




Post #162778
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse