Backup my deleted records

Hi all,

I have a db with 15 tables and I want to keep records that have been deleted.

Now I don't know where to start:
Must I keep the same structure as the main db or can I also dump all the data in one table?

what are the advantages and disatvantages of the named possibilities.

If someone knows anything else please help me out.

Thnx in advanceBecause the structure of the 15 tables will be different per table, you cannot dump them into one table. What you could do is create a new table per existing table and fill the new table with a trigger. Disadvantage: takes time and your database size will grow. Advantage: you can keep your records and have a good overview. Second possibility is not to actually delete the records, but to use a column to indicate whether a record is deleted or not. Disadvantage: another column, possibly problems with unique constraints, optimizers use incorrect data. Advantage: less administration as the first solution.|||thnx for your reply,

I 'll think I'll go with the suggestion to create a table per each existing table.|||This is a common concept...not only deletes but updates...

Script the base...add 3 additional columns and add a trigger to the base...something like:

CREATE TRIGGER Company_UpdTr ON Company
FOR UPDATE, DELETE
AS

If Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)
BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'U'
,(Select Inserted.Updated_By from Inserted
Where Deleted.Company_Name = Inserted.Company_Name)
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END

If Not Exists (Select 1 From Inserted) And Exists (Select 1 From Deleted)

BEGIN

Insert Into Company_H (
HIST_ADD_TYPE
,HIST_ADD_BY
,HIST_ADD_SYSUSER_BY
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
)
Select
'D'
,user
,user
,Company_Name
,Active_Ind
,Psft_Company_Id
,FEIN
,Has_NonQual_Taxes
,Has_Qual_Taxes
,Created_By
,Created_Ts
,Updated_By
,Updated_Ts
From Deleted
END|||Another way of doing this is.

Add a column called Deteted to all tables (it will have value of 0 by default)
Whenever u delete a record, just ser deleted to 1.
Then after a few months, when you want to archieve the db, copy all those records with deleted=1 to your archive tables.

This is a change that has to be done at the application level.
If your application is already built, then you can handle this issue only at the database level, thus you will have to go for triggers.

Regards

Benny

backup log files empty?

I'm using Lumigent Log Explorer to view logs of a database. But the result I
got was 'No log records found that match your selection'. I did not use any
filters.
In Enterprise Manager, View Content can list all the backups including log
backups in a backup file fine. From my understanding, no log backups would
have been made if no transactions had taken place. Is that right? Does SQL
back up logs anyway no matter whether or not there is a transaction?
Anybody want to help me understand this?
Thanks a lot.
Bing
Hi
If you tell it to make a backup, and there were no log records, SQL Server
still will makes a backup of some meta data that it always backups. LSN will
increment. You will notice a file < 50kb.
Regards
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> I'm using Lumigent Log Explorer to view logs of a database. But the
> result I
> got was 'No log records found that match your selection'. I did not use
> any
> filters.
> In Enterprise Manager, View Content can list all the backups including log
> backups in a backup file fine. From my understanding, no log backups
> would
> have been made if no transactions had taken place. Is that right? Does
> SQL
> back up logs anyway no matter whether or not there is a transaction?
> Anybody want to help me understand this?
> Thanks a lot.
> Bing
|||Ah, I see. Thanks much for the very helpful information.
Bing
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> If you tell it to make a backup, and there were no log records, SQL Server
> still will makes a backup of some meta data that it always backups. LSN will
> increment. You will notice a file < 50kb.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
>
>

backup log files empty?

I'm using Lumigent Log Explorer to view logs of a database. But the result
I
got was 'No log records found that match your selection'. I did not use any
filters.
In Enterprise Manager, View Content can list all the backups including log
backups in a backup file fine. From my understanding, no log backups would
have been made if no transactions had taken place. Is that right? Does SQL
back up logs anyway no matter whether or not there is a transaction?
Anybody want to help me understand this?
Thanks a lot.
BingHi
If you tell it to make a backup, and there were no log records, SQL Server
still will makes a backup of some meta data that it always backups. LSN will
increment. You will notice a file < 50kb.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> I'm using Lumigent Log Explorer to view logs of a database. But the
> result I
> got was 'No log records found that match your selection'. I did not use
> any
> filters.
> In Enterprise Manager, View Content can list all the backups including log
> backups in a backup file fine. From my understanding, no log backups
> would
> have been made if no transactions had taken place. Is that right? Does
> SQL
> back up logs anyway no matter whether or not there is a transaction?
> Anybody want to help me understand this?
> Thanks a lot.
> Bing|||Ah, I see. Thanks much for the very helpful information.
Bing
"Mike Epprecht (SQL MVP)" wrote:

> Hi
> If you tell it to make a backup, and there were no log records, SQL Server
> still will makes a backup of some meta data that it always backups. LSN wi
ll
> increment. You will notice a file < 50kb.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
>
>

backup log files empty?

I'm using Lumigent Log Explorer to view logs of a database. But the result I
got was 'No log records found that match your selection'. I did not use any
filters.
In Enterprise Manager, View Content can list all the backups including log
backups in a backup file fine. From my understanding, no log backups would
have been made if no transactions had taken place. Is that right? Does SQL
back up logs anyway no matter whether or not there is a transaction?
Anybody want to help me understand this?
Thanks a lot.
BingHi
If you tell it to make a backup, and there were no log records, SQL Server
still will makes a backup of some meta data that it always backups. LSN will
increment. You will notice a file < 50kb.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"bing" <bing@.discussions.microsoft.com> wrote in message
news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> I'm using Lumigent Log Explorer to view logs of a database. But the
> result I
> got was 'No log records found that match your selection'. I did not use
> any
> filters.
> In Enterprise Manager, View Content can list all the backups including log
> backups in a backup file fine. From my understanding, no log backups
> would
> have been made if no transactions had taken place. Is that right? Does
> SQL
> back up logs anyway no matter whether or not there is a transaction?
> Anybody want to help me understand this?
> Thanks a lot.
> Bing|||Ah, I see. Thanks much for the very helpful information.
Bing
"Mike Epprecht (SQL MVP)" wrote:
> Hi
> If you tell it to make a backup, and there were no log records, SQL Server
> still will makes a backup of some meta data that it always backups. LSN will
> increment. You will notice a file < 50kb.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> IM: mike@.epprecht.net
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
> "bing" <bing@.discussions.microsoft.com> wrote in message
> news:60139E6F-5AB8-43C1-B883-0CCD515819FF@.microsoft.com...
> > I'm using Lumigent Log Explorer to view logs of a database. But the
> > result I
> > got was 'No log records found that match your selection'. I did not use
> > any
> > filters.
> >
> > In Enterprise Manager, View Content can list all the backups including log
> > backups in a backup file fine. From my understanding, no log backups
> > would
> > have been made if no transactions had taken place. Is that right? Does
> > SQL
> > back up logs anyway no matter whether or not there is a transaction?
> >
> > Anybody want to help me understand this?
> >
> > Thanks a lot.
> >
> > Bing
>
>

backup full-text db

hi,
i try to back up a full-text database
however, the size of database quit big
around 46xxx records need 95XMB
how can i compress the backup size ?
Thanks
is this SQL 2005? How big is your database to begin with?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
Looking for a FAQ on Indexing Services/SQL FTS
http://www.indexserverfaq.com
"Q" <q@.q.com> wrote in message
news:%23rJNGDoWFHA.1152@.tk2msftngp13.phx.gbl...
> hi,
> i try to back up a full-text database
> however, the size of database quit big
> around 46xxx records need 95XMB
> how can i compress the backup size ?
> Thanks
>