March 4, 2010 at 4:15 pm
I Needed a training database. So I copied our main database and renamed it so it won't get confusing. But now it has all the data still in the tables. I need to delete all the data. I need to keep the fields and tables, just need to empty it out. There has got to be an easy way to do this through SQL Server Management Studio Express. But I have not been able to figure it out yet!!!
Does anyone know how to clear the data out of their database but keep the structure??
Any help would be super-duper apreciated.
Again, thank you!
Angi
March 4, 2010 at 4:53 pm
To clear all of the data from a table use TRUNCATE.
Alternatively you could use Delete from table tablename.
The differences are that TRUNCATE will reset and identity fields whereas Delete will not and Delete writes to the transaction log and will roll back if there is an issue whereas Truncate does not. IF you are deleteing a lot of data use TRUNCATE.
If you have lots of tables then you can use the database schema to retreive a list of tables and then either script an executable SQL statement or use cursors to move through the list of tables and run the TRUNCATE command on them.
Obiron
March 4, 2010 at 5:21 pm
To create a new blank database with the full schema I recommend the following:
1) Download and install SQL Compare from Redgate (trial edition is fully functional) and purchasing it is well worth the money.
2) Create a new blank database
3) Perform a schema comparison between your existing database and the new blank database
4) Validate all of the objects that you want included
5) Synchronize the databases
When the above has been completed, you will have a full copy of all objects without any data ready to go. Backup that database before continuing - so you can revert back to the empty copy at any time.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
March 4, 2010 at 5:32 pm
aaron.reese (3/4/2010)
Delete writes to the transaction log and will roll back if there is an issue whereas Truncate does not.
Not true.
Truncate is a logged operation, it can be rolled back. The difference is that delete logs the record deletions, truncate logs the page deallocations, just like with a drop table.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 5:34 pm
I like Jeff's idea. It is very useful to do it in this fashion and will save a lot of pain.
Another option is to script out the database that you wish to copy (db create and all objects). Then create your new test database based on these scripts. It is cheaper than purchasing redgate. But as Jeff said - redgate is well worth the money.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2010 at 5:44 pm
GilaMonster (3/4/2010)
aaron.reese (3/4/2010)
Delete writes to the transaction log and will roll back if there is an issue whereas Truncate does not.Not true.
Truncate is a logged operation, it can be rolled back. The difference is that delete logs the record deletions, truncate logs the page deallocations, just like with a drop table.
I had a DBA scoff at me once when I said a truncate could be rolled back. Thanks for explaining why it can be done.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2010 at 6:05 pm
CirquedeSQLeil (3/4/2010)
I had a DBA scoff at me once when I said a truncate could be rolled back.
Same. I invited him over to my desk and showed, in code, that it could be rolled back. No point or need to argue, it's easy to prove that it can be rolled back, almost as easy to prove that it is logged.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
March 4, 2010 at 6:07 pm
GilaMonster (3/4/2010)
Same. I invited him over to my desk and showed, in code, that it could be rolled back. No point or need to argue, it's easy to prove that it can be rolled back, almost as easy to prove that it is logged.
No doubt - I knew it could because I had done it. The scoff was in an interview.
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 4, 2010 at 6:44 pm
Just for the sake of it...
use tempdb
go
create table a (b int)
go
insert into a (b) values (1), (2), (3)
select * from a
begin tran
truncate table a
select * from a
rollback tran
select * from a
drop table a
maybe this shouldbe turned into a qotd
March 4, 2010 at 8:36 pm
Excellent QOD idea
Jason...AKA CirqueDeSQLeil
_______________________________________________
I have given a name to my pain...MCM SQL Server, MVP
SQL RNNR
Posting Performance Based Questions - Gail Shaw[/url]
Learn Extended Events
March 5, 2010 at 3:05 am
Angela S. (3/4/2010)
I Needed a training database. So I copied our main database and renamed it so it won't get confusing. But now it has all the data still in the tables. I need to delete all the data. I need to keep the fields and tables, just need to empty it out. There has got to be an easy way to do this through SQL Server Management Studio Express. But I have not been able to figure it out yet!!!Does anyone know how to clear the data out of their database but keep the structure??
Any help would be super-duper apreciated.
Again, thank you!
Angi
just to add, you may also need to disable triggers and integrity checks on the tables.
you can use the below mentioned query:
Exec sp_MSforeachtable 'alter table ? disable trigger all';
you can refer more here: http://solutions-guru.com/articles/sql-server/86-undocumented-procedure-spmsforeachtable-a-spmsforeachdatabase.html
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 5, 2010 at 3:05 am
It' already submitted as qotd ;-).
March 5, 2010 at 3:10 am
Sarab. (3/5/2010)
Angela S. (3/4/2010)
I Needed a training database. So I copied our main database and renamed it so it won't get confusing. But now it has all the data still in the tables. I need to delete all the data. I need to keep the fields and tables, just need to empty it out. There has got to be an easy way to do this through SQL Server Management Studio Express. But I have not been able to figure it out yet!!!Does anyone know how to clear the data out of their database but keep the structure??
Any help would be super-duper apreciated.
Again, thank you!
Angi
just to add, you may also need to disable triggers and integrity checks on the tables.
you can use the below mentioned query:
Exec sp_MSforeachtable 'alter table ? disable trigger all';
you can refer more here: http://solutions-guru.com/articles/sql-server/86-undocumented-procedure-spmsforeachtable-a-spmsforeachdatabase.html%5B/quote%5D
Have you actually tried this code when there are fk constraints?
Trying to delete all the data in all the tables is actually a big PITA. I'm not usre if you've realized it by now but you'll undoubtably want to keep some of the tables intact (like states or countries).
That's why scripting the database is such a good idea. Once the script has run you can reload the few tables you need with ETL process.
Once that's done... save all the scripts and you're done.
You can also run a backup of that new db and run a restore whenever you need a fresh copy.
What you are doing is usually trial an error because people usually don't spend a few hours analyazing what they want to keep... don't be surprised if it takes you a few hours to get right.
March 5, 2010 at 3:13 am
Yes you are right, this approach will surely help you
for fk keys
i think the below mentioned query may help in case of fk relationship
Exec sp_MSforeachtable 'alter table ? nocheck constraint all';
Regards,
Sarabpreet Singh 😎
Sarabpreet.com
SQLChamp.com
Twitter: @Sarab_SQLGeek
March 5, 2010 at 5:36 am
Jeffrey Williams-493691 (3/4/2010)
To create a new blank database with the full schema I recommend the following:1) Download and install SQL Compare from Redgate (trial edition is fully functional) and purchasing it is well worth the money.
2) Create a new blank database
3) Perform a schema comparison between your existing database and the new blank database
4) Validate all of the objects that you want included
5) Synchronize the databases
When the above has been completed, you will have a full copy of all objects without any data ready to go. Backup that database before continuing - so you can revert back to the empty copy at any time.
SQL compare comes with a lot of different programs in what they call a "Toolkit" is the Compare the only one I need and if so why do they have 2 of them named the same thing?
Viewing 15 posts - 1 through 15 (of 32 total)
You must be logged in to reply to this topic. Login to reply