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
Showing posts with label deleted. Show all posts
Showing posts with label deleted. Show all posts
Friday, February 24, 2012
Monday, February 13, 2012
Backup jobs
SQL backup files are not being deleted after 3 days of age, as is configured
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!
This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!
This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default...;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
Backup jobs
SQL backup files are not being deleted after 3 days of age, as is configured
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
in the backup maintenance job.
What are possible reasons for this.
New to this stuff!!!This post from Bill at MS is a pretty good start.
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q303292
This is likely to be either a permissions problem or a sharing violation
problem. The maintenance plan is run as a job, and jobs are run by the
SQLServerAgent service.
Permissions:
1. Determine the startup account for the SQLServerAgent service
(Start|Programs|Administrative tools|Services|SQLServerAgent|Startup). This
account is the security context for jobs, and thus the maintenance plan.
2. If SQLServerAgent is started using LocalSystem (as opposed to a domain
account) then skip step 3.
3. On that box, log onto NT as that account. Using Explorer, attempt to
delete an expired backup. If that succeeds then go to Sharing Violation
section.
4. Log onto NT with an account that is an administrator and use Explorer to
look at the Properties|Security of the folder (where the backups reside)
and ensure the SQLServerAgent startup account has Full Control. If the
SQLServerAgent startup account is LocalSystem, then the account to consider
is SYSTEM.
5. In NT, if an account is a member of an NT group, and if that group has
Access is Denied, then that account will have Access is Denied, even if
that account is also a member of the Administrators group. Thus you may
need to check group permissions (if the Startup Account is a member of a
group).
6. Keep in mind that permissions (by default) are inherited from a parent
folder. Thus, if the backups are stored in C:\bak, and if someone had
denied permission to the SQLServerAgent startup account for C:\, then
C:\bak will inherit access is denied.
Sharing violation:
This is likely to be rooted in a timing issue, with the most likely cause
being another scheduled process (such as NT Backup or Anti-Virus software)
having the backup file open at the time when the SQLServerAgent (i.e., the
maintenance plan job) tried to delete it.
1. Download filemon and handle from www.sysinternals.com.
2. I am not sure whether filemon can be scheduled, or you might be able to
use NT scheduling services to start filemon just before the maintenance
plan job is started, but the filemon log can become very large, so it would
be best to start it some short time before the maintenance plan starts.
3. Inspect the filemon log for another process that has that backup file
open (if your lucky enough to have started filemon before this other
process grabs the backup folder), and inspect the log for the results when
the SQLServerAgent agent attempts to open that same file.
4. Schedule the job or that other process to do their work at different
times.
5. You can use the handle utility if you are around at the time when the
job is scheduled to run.
If the backup files are going to a \\share or a mapped drive (as opposed to
local drive), then you will need to modify the above (with respect to where
the tests and utilities are run).
Finally, inspection of the maintenance plan's history report might be
useful.
Thanks,
Bill Hollinshead
Microsoft, SQL Server
--
Andrew J. Kelly SQL MVP
"huckster" <huckster@.discussions.microsoft.com> wrote in message
news:1BD61386-4219-41C4-9A10-F4AF0B690EBF@.microsoft.com...
> SQL backup files are not being deleted after 3 days of age, as is
> configured
> in the backup maintenance job.
> What are possible reasons for this.
> New to this stuff!!!
Sunday, February 12, 2012
Backup History Gone
Is there a way to recover the backup history for a database and
transaction log? I inadvertently deleted the backup history while trying
to restore and recover in a Full Recovery model. I need to do a
"point-in-time" restore of the log.
I still have copies of the mdf and ldf as they were before the process
began. I also have nightly backup and transaction log backup.
I have msdb and master backups and their log backups from nightly
backup.
When I deleted the history, the date modified on the msdb.mdf changed.
Neither did the date modified on the msdb.ldf.
Could I possibly return the backup history by restoring msdb and/or
master?
Please help!!!!
Gerald
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!If you have the full DB and the tran logs, and they are
appropriately labelled, you shouldn't have to need the
backup history. Just apply them in the right order and
you should be fine - remember to use STANDBY or NORECOVERY
until the last log file.
>--Original Message--
>Is there a way to recover the backup history for a
database and
>transaction log? I inadvertently deleted the backup
history while trying
>to restore and recover in a Full Recovery model. I need
to do a
>"point-in-time" restore of the log.
>I still have copies of the mdf and ldf as they were
before the process
>began. I also have nightly backup and transaction log
backup.
>I have msdb and master backups and their log backups from
nightly
>backup.
>When I deleted the history, the date modified on the
msdb.mdf changed.
>Neither did the date modified on the msdb.ldf.
>Could I possibly return the backup history by restoring
msdb and/or
>master?
>
>Please help!!!!
>Gerald
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
transaction log? I inadvertently deleted the backup history while trying
to restore and recover in a Full Recovery model. I need to do a
"point-in-time" restore of the log.
I still have copies of the mdf and ldf as they were before the process
began. I also have nightly backup and transaction log backup.
I have msdb and master backups and their log backups from nightly
backup.
When I deleted the history, the date modified on the msdb.mdf changed.
Neither did the date modified on the msdb.ldf.
Could I possibly return the backup history by restoring msdb and/or
master?
Please help!!!!
Gerald
*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!If you have the full DB and the tran logs, and they are
appropriately labelled, you shouldn't have to need the
backup history. Just apply them in the right order and
you should be fine - remember to use STANDBY or NORECOVERY
until the last log file.
>--Original Message--
>Is there a way to recover the backup history for a
database and
>transaction log? I inadvertently deleted the backup
history while trying
>to restore and recover in a Full Recovery model. I need
to do a
>"point-in-time" restore of the log.
>I still have copies of the mdf and ldf as they were
before the process
>began. I also have nightly backup and transaction log
backup.
>I have msdb and master backups and their log backups from
nightly
>backup.
>When I deleted the history, the date modified on the
msdb.mdf changed.
>Neither did the date modified on the msdb.ldf.
>Could I possibly return the backup history by restoring
msdb and/or
>master?
>
>Please help!!!!
>Gerald
>
>*** Sent via Developersdex http://www.developersdex.com
***
>Don't just participate in USENET...get rewarded for it!
>.
>
Subscribe to:
Posts (Atom)