| 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

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:
Title:
Comment:
Security Code
Enter the code shown above in the box below
Add Comment   Cancel  

I do not fear computers. I fear the lack of them.
-Isaac Asimov

Inspired by Nina