| Login

Search this Blog


Links I like to keep around


Here are the most recent postings to this blog. Use the archive calendar or search to find other entries.
Jan15

Written by:R Teachout
1/15/2008 1:03 PM 

Need to make a clone of a database structure, but have too many procedures/functions/tables to do it all manually? I believe this is a good answer to that problem:

First, restore a recent backup of the original_db as new_db

Then:

use new_db;
EXEC sp_MSforeachtable "delete from '?'"
(This basically just deletes all the data from the restored copy)
You probably have to run this multiple times for it to complete successfully, due to foreign key constraints, if you have any... so you can safely ignore errors like "can't delete due to FK_xxx"

I would probably also run the following on the database before you start putting more data in it.
EXEC sp_MSforeachtable 'UPDATE STATISTICS ? WITH FULLSCAN'
go
EXEC sp_MSforeachtable "dbcc dbreindex('?')"

After you're done, you probably also want to compact your database:
use master;
dump tran new_db with no_log;
use new_db;
dbcc shrinkdatabase (new_db, 4, notruncate )

Tags:

Your name:
Your email:
(Optional) Email used only to show Gravatar.
Your website:
Title:
Comment:
Security Code
CAPTCHA image
Enter the code shown above in the box below
Add Comment  Cancel 

How much do Windows cost, and do you have to buy each one separately?
-Unknown

Inspired by Nina