Create 'Vanilla Database'

Question

Post reply

Create 'Vanilla Database'

2Tall

SSChampion

Points: 14644
More actions
January 23, 2008 at 2:48 am

#178416

Hi. I would like to create a 'Vanilla Database' in order to restore data to.
We are currently in the process of migrating / cleaning data / configuring (in Migration System). When complete this will be shipped to a training system. When all the data is verified I would like to backup then restore to what will be the 'Live System' using the import wizard. However in testing the import wizard fails to import data to 'Vanilla System' (not quite so vanilla!!) as it has data in some tables (will not import duplicates).
This leads me to my question, can I delete all data from all tables globally?
Many Thanks,
Phil.
-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."
Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '
Tommy Cooper

Viewing 15 posts - 1 through 14 (of 14 total)

You must be logged in to reply to this topic. Login to reply

toniupstny Hall of Fame Points: 3986 More actions · Answer 1

You can use the stored procedure sp_msforeachtable to do truncates on each table in the database.

sp_MSforeachtable @command1 ="Truncate Table ?"

Be sure you are in the right database before running this.

Toni

Steve Jones - SSC Editor SSC Guru Points: 736270 More actions · Answer 2

Second vote for Toni's solution. Maybe script this as part of the database creation or restore)

2Tall SSChampion Points: 14644 More actions · Answer 3

Thanks Guys. Just need to fill in the blanks then (should only take me a week or two!!) 🙂

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

2Tall SSChampion Points: 14644 More actions · Answer 4

Guys I found a usefull script that did just what I was after so I have attached it as I thought it might be of use to others.

Credit to: Gregory A. Larsen

http://www.databasejournal.com/features/mssql/article.php/3441031

My original logic behind why I wanted this script is a little flawed as I could simply restore the database over the top of the 'live system'...........

Still it may be of value to someone.

Thanks,

Phil.

-------------------------------------------------------------------------------------
A neutron walks into a bar. "I'd like a beer" he says. The bartender promptly serves up a beer. "How much will that be?" asks the neutron. "For you?" replies the bartender, "no charge."

Two hydrogen atoms walk into a bar. One says, 'I think I've lost an electron.' The other says 'Are you sure?' The first says, 'Yes, I'm positive... '

Tommy Cooper

dragos_sv Default port Points: 1478 More actions · Answer 5

if your tables have PK - FK relationships, sqlserver will not allow you to truncate those tables

LE: maybe 🙂 i should have said that the command will fail against tables that have FK references

GSquared SSC Guru Points: 260824 More actions · Answer 6

If you want a clean database (no data in any tables), use "Generate Scripts" in Management Studio (Query Analyzer for SQL 2000).

Right click the database -> Tasks -> Generate Scripts

Select "Script all objects in database", walk through the wizard. Bang, you'll have a script to create a copy of the database with no data in it. Change the name of the database at the top of the script, run it on the server you want it on.

Also has the advantage of not adding a bunch to the log files for an already-populated database. (Yes, Truncate is "minimally logged", but it is logged.)

- Gus "GSquared", RSVP, OODA, MAP, NMVP, FAQ, SAT, SQL, DNA, RNA, UOI, IOU, AM, PM, AD, BC, BCE, USA, UN, CF, ROFL, LOL, ETC
Property of The Thread

"Nobody knows the age of the human race, but everyone agrees it's old enough to know better." - Anon

toniupstny Hall of Fame Points: 3986 More actions · Answer 7

Phil, I am glad you found another approach that works for you and thanks for the reference which gave additional parms, etc.

Dragos, as to PK - FK relationship restrictions, doesn't the same hold true for the standard DELETE statement?

According to BOL:

The DELETE statement may fail if it violates a trigger or attempts to remove a row referenced by data in another table with a FOREIGN KEY constraint.

Even though the original question no longer requires resolution, maybe your point is that you have to apply intelligence to the order in which tables are cleared of their data so a custom solution/script would be required?

Toni

dragos_sv Default port Points: 1478 More actions · Answer 8

Toni, i should have quoted your first post 🙂 as i mine was referencing it

while your solution was (as far as i am concerned) very good for a non related database , i just wanted to underline that it might not always work. as you said, tables should be cleared in the proper order if they were related

Dragos

toniupstny Hall of Fame Points: 3986 More actions · Answer 9

Thank you Davros. I am still very much new at this and wanted to be sure I understood the import of your post. By the way, in the 3rd post by Phil above, he has attached just such a custom script which removes the FK-dependent tables first then the parent tables.

Toni

fhanlon SSChampion Points: 13131 More actions · Answer 10

Although it is very interesting to have a 'vanilla' database with no rows in all the tables, if you are doing this just to restore from somewhere else it is not necessary. The restore works fine whether or not the new database exists.

ie if I execute RESTORE DATABASSE NEWTEST ....

The database NEWTEST does not have to preexist. Rather than remove all rows from all tables, just drop the database.

Francis

Bob Fazio SSChampion Points: 10794 More actions · Answer 11

toniupstny (1/23/2008)
You can use the stored procedure sp_msforeachtable to do truncates on each table in the database.
sp_MSforeachtable @command1 ="Truncate Table ?"
Be sure you are in the right database before running this.
Toni

Let's give a loaded shotgun to a child and tell them to be careful....

As stated there are many reasons why this won't work, but it will scramble the data.

I would suggest two things. Using SSMS you can generate the scripts for the DB which is pretty good.

Although I highly recommend using Red Gates SQL Compare.

Lowell SSC Guru Points: 323518 More actions · Answer 12

ok here's a script that creates the statements to delete or truncate where appropriate, and creates the statements in Foreign Key Hierarchy Order.

now remember there are always tables you don't want to truncate in d database...lookup tables that contain drop down list values, TBSTATES or other lookup type tables you'd NOT want to remove the data from...

use this as a base for your delete function:

nocount on

declare @level tinyint

set @level = 0

create table #tables (

id int not null primary key clustered,

TableName varchar(255) not null,

Level tinyint not null)

insert into #tables (id, TableName, Level)

select id, '[' + user_name(uid) + '].[' + rtrim(name) + ']' as TableName, 0

from sysobjects where xtype = 'U' and status > 0

while @@rowcount > 0 begin

set @level = @level + 1

update rt set Level = @level

from #tables rt

inner join sysreferences fk on fk.rkeyid = rt.id

inner join #tables ft on ft.id = fk.fkeyid

where ft.Level = @level - 1

end

print 'USE ' + DB_NAME() + '

'

select 'TRUNCATE TABLE ' + TableName from #tables where level = 0

select 'DELETE ' + TableName from #tables where level > 0 order by level

drop table #tables

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Stevishere SSC Enthusiast Points: 193 More actions · Answer 13

GSquared (1/23/2008)
If you want a clean database (no data in any tables), use "Generate Scripts" in Management Studio (Query Analyzer for SQL 2000).
Right click the database -> Tasks -> Generate Scripts
Select "Script all objects in database", walk through the wizard. Bang, you'll have a script to create a copy of the database with no data in it. Change the name of the database at the top of the script, run it on the server you want it on.
Also has the advantage of not adding a bunch to the log files for an already-populated database. (Yes, Truncate is "minimally logged", but it is logged.)

Maybe you can help me! I used this functionality as you suggested. It did not bring in any of the udfs, PKs, or FKs. Any idea what I did wrong?

Matt Miller (4) SSC Guru Points: 124210 More actions · Answer 14

Check in the extended options, there are some things that don't get scripted by default.

----------------------------------------------------------------------------------
Your lack of planning does not constitute an emergency on my part...unless you're my manager...or a director and above...or a really loud-spoken end-user..All right - what was my emergency again?