Truncate of the Database needed?
-
I developed a Database, using the EM. Adding tables, fields, defaults, PKs, indexes, changing the names, types, length, etc. Filling some tables with (lookup) data, but mostly letting the tables empty. I made a backup now and then, using the EM (with overwrite existing). And now I am ready.
The question is: "Is the whole developing history still in the database"? I did setup the Database with the EM, using the defaults. And when the history is still in the Database, how can I remove it, preferably with the EM.
-
As far as I know EM does not retain any history of what you did. You just have the final database, thats it! Nothing to delete.
-
Do you mean that the backup-defaults (when making a database with the EM, without any changes) are that no history is retained (no log)?
That's fine. Thank you.
-
I'm not sure I understand your question. Are you questioning the 'Recovery Model' properties for a database, which will determine what is in the database log file. Or are you looking for something in EM that will show the history of your EM session?
-
Glynne, thank you for your answer. Let me give an explanation of what I mean.
I don't need any recovery. My database is ready and OK. It goes in production now. All the earlier changes, if they would be recorded have to be removed. I want a fresh new database, without any history logged in it. As, if I would have made it in a split of a second and the whole structure in one action with the lookup tables filled.
I think that my database has all the changes I did made in de past 4 weeks, developing this database has logged. Or not? I don't know. I think it depends on the defaults of the EM uses for a new database.
My question for now: Do you know the defaults for logging when I make a new database with the EM?
-
OK. There is no 'logging' of the DDL that would need to be removed.
-
If the db is is in full log mode, then yes... but they would hav to buy an expensive software to track those changes.
Why are you worried about that?
-
Glynne, thank you, fine to know that the DDL changes are not logged.
And the changes in the records of the lookup tables? Are these logged?
I had to show the wishes of the users to them. You know, ... when they see there values they proposed, they see that these are not valuable, so I had to remove them, than there were other wishes, etc. Are these changes logged neither? I mean after a backup (using the defaults of the EM for a new databases) are these changes still in the log?
-
RGR'us, Is my db in in full log mode (I used the defaults of EM when I made the database)?
I'am not worried, I just want to start with a clean sheet and a small log-file.
But, when they need expensive tracking software to see the changes, I probably don't have to worry.
Thanks, I backup the database and install it in the production environment where it starts to live
-
why not just script the database you created and then run the script on the new database. there won't be any history to worry about.
-
It sounds as if you want to delete all the test data in the tables, and start fresh with empty tables ?? If it's small, you can type:
truncate table <tablename>
for each table.
-
Bill and Homebrew01, thank you. These are the solutions I was looking for.
I have about 50 tables in the Database.
- Some are lookup-tables filled with data. E.g. the Country table with all the countrys in the world (and a CountryID, int, PK).
- But most tables are empty. Just having a history of added and removed fields, indexes, defaults, PK's, etc.. I used a copy of the database with test-data for testing.
I'll try both solutions. Is it possible to script the database and still keep the lookup-tables-data?
-
" ... Is it possible to script the database and still keep the lookup-tables-data? ..."
You're talking about 3 different things, and I think getting them a bit mixed up. Scripting, Change Management and Data Management
Scripting: Generates T-SQL commands that represent the code that would be needed to recreate an object. Scripting a table w/ extended properties, gives you:
CREATE TABLE [dbo].[TABLEA] (
[FLD1] [char] (10),
[FLD2] [char] (10)
GO
exec sp_addextendedproperty N'MS_Description', N'Test Table for DBA usage', N'user', N'dbo', N'table', N'TABLEA'
GO
exec sp_addextendedproperty N'MS_Description', N'Field 1 description', N'user', N'dbo', N'table', N'TABLEA', N'column', N'fld1'
GO
exec sp_addextendedproperty N'MS_Description', N'Field 2 description', N'user', N'dbo', N'table', N'TABLEA', N'column', N'fld2'
GO
You can script Tables, Stored Procs, Views etc.
You can also use "Copy Objects", but be VERY careful, because all the options are checked, a can cause problems if you don't really want them all chacked, such as copying dependant objects, replacing data, copying database users ... I always uncheck everything, and then carefully check only what I need.
Change Management: In your case, are you looking for a history of changes that were made to table structures, such as fields added & dropped, indexes added, stored procedure changes etc... ?? If so, then you need to track this yourself as you go along. This is one area where scripting is useful. Each time you change a table or SP, you can script it out to a folder, and have a history of what was done. There may be 3rd party products that do what you want. There's no way to reconstruct those changes after the fact if you didn't keep track of them at the time.
Data Management: If you want to move or manipulate data between databases, there are several ways to do it. If you have only 50 tables, then using a combination of "export data" in EM, or DTS packages should do the job. Determining the best method is tough without knowing what you're actually trying to do.
Are you trying to make a New, Empty database similar to the one you have now, with just a few tables populated ? Since your DB is small, I would do a backup & restore to a new DB name, then truncate the tables 1 by 1 except for the tables you want to leave as they are.
-
Homebrew01, thank you for your extensive explanation.
Don't forget, I'am a newbe in the field of SQLServer (coming from another branch), so I'am very pleased with the way you explain this approach. I will truncate the tables 1 by 1 except for the lookup tables, using the Query Analyzer.
Viewing 14 posts - 1 through 13 (of 13 total)
You must be logged in to reply to this topic. Login to reply