Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Database Cloning?


Database Cloning?

Author
Message
PompeyTID
PompeyTID
Grasshopper
Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)Grasshopper (19 reputation)

Group: General Forum Members
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
David.Poole
David.Poole
SSCarpal Tunnel
SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)SSCarpal Tunnel (4.5K reputation)

Group: General Forum Members
Points: 4484 Visits: 3182
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
Japie Botma
Japie Botma
SSC Veteran
SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)SSC Veteran (200 reputation)

Group: General Forum Members
Points: 200 Visits: 296
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
South Africa
athurgar
athurgar
SSC Veteran
SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)SSC Veteran (203 reputation)

Group: General Forum Members
Points: 203 Visits: 162
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.



Peter Wright
Peter Wright
SSC Veteran
SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)SSC Veteran (274 reputation)

Group: General Forum Members
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.


Shie Morozow
Shie Morozow
SSC Rookie
SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)SSC Rookie (43 reputation)

Group: General Forum Members
Points: 43 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.


barsuk
barsuk
Hall of Fame
Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)Hall of Fame (3.9K reputation)

Group: General Forum Members
Points: 3888 Visits: 6896

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.





Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search