September 30, 2013 at 9:55 am
What is the best way to remove all data/views/logons from a database ?
The scenario is this: We have a database called 'X' to hold out client data and to interact with our in house applications. Each customer has their own server with database 'X' installed. The database on each of the servers is the same format, that is, it has the same table structures/stored procs.
I have to create a new database 'X' on a new server for a new customer - so I have restored one of our current clients database 'X' backups to the new server and now have the database but obviously with data that i need to get rid of completely whilst keeping the table structures, constraints, seeding values (if any present ). Also this has restored incorrect users under the Security\Users folder of the database. Do i just manually delete these ?
I have used the following to disable referential integrity, truncate tables, and then renebale referential integrity
- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'TRUNCATE TABLE ?'
GO
- enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
If anybody can offer any advice of the above topic I would to hear from you !
🙂
September 30, 2013 at 9:57 am
That truncate will fail. Disabling the foreign keys is not sufficient, tables with foreign keys can't be truncated no matter what the state of that constraint.
Edit: Oh, and truncate will reset the identity values back to what the table was created with.
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
September 30, 2013 at 10:14 am
Woukln't it be easier to generate object scripts from an existing database, then run those scripts in the new database ? That would build empty tables. Add what you need, rather then removong what you don't need. ?
September 30, 2013 at 10:14 am
I have eventually ran this:
-- disable referential integrity
EXEC sp_MSForEachTable 'ALTER TABLE ? NOCHECK CONSTRAINT ALL'
GO
EXEC sp_MSForEachTable 'ALTER TABLE ? DISABLE TRIGGER ALL'
go
EXEC sp_MSForEachTable 'delete from ?'
GO
--enable referential integrity again
EXEC sp_MSForEachTable 'ALTER TABLE ? CHECK CONSTRAINT ALL'
GO
and it ran successfully in ssms but i don't know yet if its done what i need it to do....any thoughts on this method and what would be the correct methodology for the situation I have. ?
September 30, 2013 at 11:37 am
could you do "truncate table" instead of delete ? Should be faster, and no logging since you don't care about data recovery.
September 30, 2013 at 11:41 am
homebrew01 (9/30/2013)
could you do "truncate table" instead of delete ? Should be faster, and no logging since you don't care about data recovery.
Faster yes...but truncate is a fully logged operation.
Also, as noted above, if there are foreign keys you can't truncate a table.
_______________________________________________________________
Need help? Help us help you.
Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.
Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.
Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/
September 30, 2013 at 12:08 pm
homebrew01 (9/30/2013)
could you do "truncate table" instead of delete ? Should be faster, and no logging since you don't care about data recovery.
There is no such thing as a non-logged operation in SQL Server.
He can't truncate because there are foreign keys and because he doesn't want to reset the identity seed.
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
September 30, 2013 at 3:12 pm
After a bit more digging around i did come across from Dave Pinal. Looks like what i need to do i think but i can choose to script the schema as opposed to data and schema as in the link.
Thanks everyone once again
September 30, 2013 at 3:18 pm
PearlJammer1 (9/30/2013)
Thanks everyone once again
That's what I suggested earlier, but not sure if it will meet your requirement to keep seeding values.
September 30, 2013 at 4:55 pm
Indeed it was. I wasn't sure of the methodology though so I found that post. Thanks for putting me on track. Would the seeding values just go to default values?
September 30, 2013 at 8:02 pm
I'm pretty sure the seeding would start back at 1 .... I have not tested.
You want the seeding for Table_1 in Database_B to continue with the current value in Table_1 in database_A ??
I expect you will have to manually set that.
October 1, 2013 at 1:56 am
i found here what i expect
October 1, 2013 at 2:03 am
A small suggestion .. better to maintain everyting in a Database template ( every sql object requires to be available in new DB for new customer.)
-------Bhuvnesh----------
I work only to learn Sql Server...though my company pays me for getting their stuff done;-)
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply