Hi All,
I need a generic solution to delete backup files from filesystem in order to
reduce space.
Can this be done using the extended sp "xp_cmdshell"? If yes, can any body
show me an example on how to achieve this.
ie.., i want to write a SP and add it to SQL Jobs. I want that SP to delete
all files in a specific folder which are older then "N" number of hours.
Its really very urgent. Can anyone give me a quick solution / sample.
Regards
PrasanthIF you are using a Maintenance plan for creating the backups, then you have
an option when you are setting up the plan through the wizard where you can
use the option
"Remove files older than"
If you cannot do that, you can use a vbscript to automate the delete.
On how to do that.. check out this link..
[url]http://www.sqlservercentral.com/columnists/hji/usingvbscripttoautomatetasks.asp[/u
rl]
Hope this helps.|||As omnibuzz suggested vbscript is the way to go.
If at you are not interest in that for some reason! then try these
suggestions:
1. How about writing a batch script to do this job. THen you can call this
bat file within a sqljob and schedule it.
2. Write a windows application to do and then schedule it using Windows
Scheduler.
3. Write a Windows service to do this job.
Best Regards
Vadivel
http://vadivel.blogspot.com
"SqlBeginner" wrote:
> Hi All,
> I need a generic solution to delete backup files from filesystem in order
to
> reduce space.
> Can this be done using the extended sp "xp_cmdshell"? If yes, can any body
> show me an example on how to achieve this.
> ie.., i want to write a SP and add it to SQL Jobs. I want that SP to delet
e
> all files in a specific folder which are older then "N" number of hours.
> Its really very urgent. Can anyone give me a quick solution / sample.
> Regards
> Prasanth
Showing posts with label order. Show all posts
Showing posts with label order. Show all posts
Sunday, March 25, 2012
Wednesday, March 21, 2012
deleting backup history in order to allow for restore
Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980s
The first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>
|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
>
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980s
The first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>
|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
>
deleting backup history in order to allow for restore
Hello:
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980sThe first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> > Hello:
> >
> > Is there an automated way of having SQL delete backup jobs? I ran
> > sp_delete_backuphistory against the msbd database in SQL Server 2000
> > 8.00.2039 and I had to stop it because it was taking forever to run.
> >
> > I'm surprised that SQL does not automatically delete backup history since
> > maintenance plans for automatically deleting backups. (Any relief in SQL
> > 2005?)
> >
> > The major reason that I ask about this is because when we conduct a manual
> > restore of a database, we cannot do so by right-clicking on that database
> > in
> > Enterprise Manager. Enterprise Manager freezes when we choose All
> > Tasks...Restore. So, we end up having to instead run a script in Query
> > Analyzer to restore the database.
> >
> > Someone on this message board told me to delete backup history. But,
> > again,
> > that takes too long. And, when I tried to do a restore in Enterprise
> > Manager
> > just now, I got the same result (though I did cancel the process).
> >
> > Any ideas?
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
>sql
Is there an automated way of having SQL delete backup jobs? I ran
sp_delete_backuphistory against the msbd database in SQL Server 2000
8.00.2039 and I had to stop it because it was taking forever to run.
I'm surprised that SQL does not automatically delete backup history since
maintenance plans for automatically deleting backups. (Any relief in SQL
2005?)
The major reason that I ask about this is because when we conduct a manual
restore of a database, we cannot do so by right-clicking on that database in
Enterprise Manager. Enterprise Manager freezes when we choose All
Tasks...Restore. So, we end up having to instead run a script in Query
Analyzer to restore the database.
Someone on this message board told me to delete backup history. But, again,
that takes too long. And, when I tried to do a restore in Enterprise Manager
just now, I got the same result (though I did cancel the process).
Any ideas?
Thanks!
childofthe1980sThe first time you delete the history it will take a long time if it has
never been done before. Just let it run until it finishes otherwise you roll
it back and you are no better off than before. But once you clear out the
garbage that was in there it will only take a second or less to delete about
a weeks worth if you do it on a regular basis. So set up a scheduled job
that calls this once a week and pass in a datetime that leaves you the week
or two that you want to keep for history.
DECLARE @.Date DATETIME
SET @.Date = DATEADD(wk,-1,GETDATE())
EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
In 2005 they have added a task for the MP to dod this but it does absolutely
nothing more than what I show above and still needs to be done regulary. The
problem is there is data in 3 tables and they are not properly indexed so a
delete on lots of data takes a long time.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> Hello:
> Is there an automated way of having SQL delete backup jobs? I ran
> sp_delete_backuphistory against the msbd database in SQL Server 2000
> 8.00.2039 and I had to stop it because it was taking forever to run.
> I'm surprised that SQL does not automatically delete backup history since
> maintenance plans for automatically deleting backups. (Any relief in SQL
> 2005?)
> The major reason that I ask about this is because when we conduct a manual
> restore of a database, we cannot do so by right-clicking on that database
> in
> Enterprise Manager. Enterprise Manager freezes when we choose All
> Tasks...Restore. So, we end up having to instead run a script in Query
> Analyzer to restore the database.
> Someone on this message board told me to delete backup history. But,
> again,
> that takes too long. And, when I tried to do a restore in Enterprise
> Manager
> just now, I got the same result (though I did cancel the process).
> Any ideas?
> Thanks!
> childofthe1980s
>
>
>|||Thanks, Andrew!
childofthe1980s
"Andrew J. Kelly" wrote:
> The first time you delete the history it will take a long time if it has
> never been done before. Just let it run until it finishes otherwise you roll
> it back and you are no better off than before. But once you clear out the
> garbage that was in there it will only take a second or less to delete about
> a weeks worth if you do it on a regular basis. So set up a scheduled job
> that calls this once a week and pass in a datetime that leaves you the week
> or two that you want to keep for history.
> DECLARE @.Date DATETIME
> SET @.Date = DATEADD(wk,-1,GETDATE())
> EXEC [msdb].[dbo].[sp_delete_backuphistory] @.Date
>
> In 2005 they have added a task for the MP to dod this but it does absolutely
> nothing more than what I show above and still needs to be done regulary. The
> problem is there is data in 3 tables and they are not properly indexed so a
> delete on lots of data takes a long time.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "childofthe1980s" <childofthe1980s@.discussions.microsoft.com> wrote in
> message news:851A896D-0D1E-4A07-9202-19AA98E8970D@.microsoft.com...
> > Hello:
> >
> > Is there an automated way of having SQL delete backup jobs? I ran
> > sp_delete_backuphistory against the msbd database in SQL Server 2000
> > 8.00.2039 and I had to stop it because it was taking forever to run.
> >
> > I'm surprised that SQL does not automatically delete backup history since
> > maintenance plans for automatically deleting backups. (Any relief in SQL
> > 2005?)
> >
> > The major reason that I ask about this is because when we conduct a manual
> > restore of a database, we cannot do so by right-clicking on that database
> > in
> > Enterprise Manager. Enterprise Manager freezes when we choose All
> > Tasks...Restore. So, we end up having to instead run a script in Query
> > Analyzer to restore the database.
> >
> > Someone on this message board told me to delete backup history. But,
> > again,
> > that takes too long. And, when I tried to do a restore in Enterprise
> > Manager
> > just now, I got the same result (though I did cancel the process).
> >
> > Any ideas?
> >
> > Thanks!
> >
> > childofthe1980s
> >
> >
> >
> >
> >
>sql
Monday, March 19, 2012
deleting a tx log backup
Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> > Hi,
> >
> > I backed up my transaction log in order to perform a point in time
> > restore.
> >
> > I backed it up to an ad hoc folder. The database actually does backup the
> > log file once a night to a different location. I'm going to change this to
> > once every thirty minutes.
> >
> > Can I delete this ad hoc transaction log file backup, or do future backups
> > depend on it?
> >
> > Thanks very much for your ideas on this. I just want to be safe!
> >
> > Many thanks!
> >
> > Ant
>
>
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> > Hi,
> >
> > I backed up my transaction log in order to perform a point in time
> > restore.
> >
> > I backed it up to an ad hoc folder. The database actually does backup the
> > log file once a night to a different location. I'm going to change this to
> > once every thirty minutes.
> >
> > Can I delete this ad hoc transaction log file backup, or do future backups
> > depend on it?
> >
> > Thanks very much for your ideas on this. I just want to be safe!
> >
> > Many thanks!
> >
> > Ant
>
>
deleting a tx log backup
Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
Ant
Hello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant
|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
Ant
Hello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant
|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction log
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>
deleting a tx log backup
Hi,
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction lo
g
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>
I backed up my transaction log in order to perform a point in time restore.
I backed it up to an ad hoc folder. The database actually does backup the
log file once a night to a different location. I'm going to change this to
once every thirty minutes.
Can I delete this ad hoc transaction log file backup, or do future backups
depend on it?
Thanks very much for your ideas on this. I just want to be safe!
Many thanks!
AntHello,
If you have a FULL database backup taken after the specific trasnaction log
backup you could delete the log backup file mentioned. Otherwise you need
that specific log
backup file for while recovery.
Thanks
Hari
"Ant" <Ant@.discussions.microsoft.com> wrote in message
news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
> Hi,
> I backed up my transaction log in order to perform a point in time
> restore.
> I backed it up to an ad hoc folder. The database actually does backup the
> log file once a night to a different location. I'm going to change this to
> once every thirty minutes.
> Can I delete this ad hoc transaction log file backup, or do future backups
> depend on it?
> Thanks very much for your ideas on this. I just want to be safe!
> Many thanks!
> Ant|||Hi Hari,
Thanks fro the answer.
Best wishes
Ant
"Hari Prasad" wrote:
> Hello,
> If you have a FULL database backup taken after the specific trasnaction lo
g
> backup you could delete the log backup file mentioned. Otherwise you need
> that specific log
> backup file for while recovery.
> Thanks
> Hari
> "Ant" <Ant@.discussions.microsoft.com> wrote in message
> news:3CFCF720-ACD2-4677-AD13-C201326E2344@.microsoft.com...
>
>
Sunday, March 11, 2012
Deletes through Replication
I have an order header we'll call HEADER and detail we'll call DETAIL
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:
> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))
(catchy names !!)
When I replicate new orders, I use filters:
The HEADER article filter is like:
Select * from HEADER
where ordr_dte >= '1998-01-01'
then
The DETAIL filter is like:
Select * from DETAIL
where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num = HEADER.ord_num
and (HEADER.ordr_dte >= '1998-01-01' ))
This makes sure I only get DETAILS that match filtered HEADERS.
Everything's fine so far. Subscriber matches Publisher
Now, our order processing application sometimes deletes order records. Not
very often, but if a transaction is backed out before being posted or
something.
Anyway, the problem is that on the Publisher, the application deletes the
HEADER record, which gets deleted on the Subscriber .... FINE
Then the DETAIL gets deleted on the Publisher .... BUT ... since my
filter only deals with DETAILS that have an associated HEADER, and the HEADER
doesn't exist any more, I'm left with an orphan DETAIL record on the
subscriber.
What should I do ? Is there a better way to set this up to start with ?
if you are using merge replication have a look at join filters.
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> I have an order header we'll call HEADER and detail we'll call DETAIL
> (catchy names !!)
> When I replicate new orders, I use filters:
> The HEADER article filter is like:
> Select * from HEADER
> where ordr_dte >= '1998-01-01'
> then
> The DETAIL filter is like:
> Select * from DETAIL
> where exists (SELECT * FROM HEADER WHERE DETAIL.ord_num =
HEADER.ord_num
> and (HEADER.ordr_dte >= '1998-01-01' ))
> This makes sure I only get DETAILS that match filtered HEADERS.
> Everything's fine so far. Subscriber matches Publisher
> Now, our order processing application sometimes deletes order records. Not
> very often, but if a transaction is backed out before being posted or
> something.
> Anyway, the problem is that on the Publisher, the application deletes the
> HEADER record, which gets deleted on the Subscriber .... FINE
> Then the DETAIL gets deleted on the Publisher .... BUT ... since my
> filter only deals with DETAILS that have an associated HEADER, and the
HEADER
> doesn't exist any more, I'm left with an orphan DETAIL record on the
> subscriber.
> What should I do ? Is there a better way to set this up to start with ?
|||I'm just using regular Transactional Replication.
Is there some better filter logic I can use to avoid orphans ?
"Hilary Cotter" wrote:
> if you are using merge replication have a look at join filters.
> --
> Hilary Cotter
> Looking for a SQL Server replication book?
> http://www.nwsu.com/0974973602.html
> "HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
> news:29A8C1EA-42EC-40DE-873F-5F01C2FEBAFE@.microsoft.com...
> HEADER.ord_num
> HEADER
>
>
|||Yes, but could you post your schema for both tables?
Hilary Cotter
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
"HomeBrew" <HomeBrew@.discussions.microsoft.com> wrote in message
news:582163AF-EEC9-4633-B723-70D2B72C9E1A@.microsoft.com...[vbcol=seagreen]
> I'm just using regular Transactional Replication.
> Is there some better filter logic I can use to avoid orphans ?
> "Hilary Cotter" wrote:
Not[vbcol=seagreen]
the[vbcol=seagreen]
my[vbcol=seagreen]
with ?[vbcol=seagreen]
|||Here are the schemas for the "HEADER" and "DETAIL" tables. (1 Header to
Many Details)
Below them are the snippets from my Replication Row Filters. These tables
are part of a package, so I can't make the Detail get deleted before the
Header.
Schema for PROORD_M ("HEADER" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROORD_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[CTG_DTE] [datetime] NOT NULL ,
[ORD_CTG] [char] (6) NOT NULL ,
[NUM_OPS] [char] (8) NOT NULL ,
[CTM_NBR] [char] (12) NOT NULL ,
[CTG_DTE2] [decimal](8, 0) NOT NULL ,
[ORD_CTG2] [char] (6) NOT NULL ,
[ORD_STA] [char] (1) NOT NULL ,
[ORD_NUM2] [char] (8) NOT NULL ,
[PO_NUM] [char] (20) NULL ,
[ORDR_DTE] [datetime] NULL ,
[SHP_DTE] [datetime] NULL ,
[ORD_TYPE] [char] (1) NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROORD_M] ADD
CONSTRAINT [PROORD_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K2] UNIQUE NONCLUSTERED
([CTG_DTE],
[ORD_CTG],
[NUM_OPS]) WITH FILLFACTOR = 90 ON [PRIMARY] ,
CONSTRAINT [PROORD_M_K4] UNIQUE NONCLUSTERED
([ORD_STA],
[ORD_NUM2]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++++++++++++++++++++++++ ++++++
Schema for PROOLN_M ("DETAIL" Table, I removed many irrelevant fields in
the interest of space)
CREATE TABLE [dbo].[PROOLN_M] (
[ORD_NUM] [char] (8) NOT NULL ,
[ORD_SPSQ] [char] (6) NOT NULL ,
[ORD_SEQ] [char] (5) NOT NULL ,
[SHP_CTM] [char] (12) NOT NULL ,
[ITM_NUM2] [char] (10) NOT NULL ,
[ITM_NUM3] [char] (10) NOT NULL ,
[ORD_NUM3] [char] (8) NOT NULL ,
[OLN_STA] [char] (2) NULL ,
[ITM_NUM] [char] (10) NULL
[QTY_ORD] [int] NULL ,
[QTY_SHP] [int] NULL
) ON [PRIMARY]
GO
ALTER TABLE [dbo].[PROOLN_M] ADD
CONSTRAINT [PROOLN_M_K1] PRIMARY KEY CLUSTERED
([ORD_NUM],
[ORD_SPSQ],
[ORD_SEQ]) WITH FILLFACTOR = 90 ON [PRIMARY]
++++++++++++++++++++++++++++
Row filter for PROORD ("HEADER" Table)
SELECT <published_columns> FROM <<PROORD_M>>
WHERE ordr_dte >= '1998-01-01' or ordr_dte is null
++++++++++++++++++++++++++++
Row filter for PROOLN ("DETAIL" Table)
SELECT <published_columns> FROM <<PROOLN_M>>
WHERE exists
(SELECT * FROM AdvDbPrd.dbo.proord_m WHERE
prooln_m.ord_num = proord_m.ord_num
and (ordr_dte >= '1998-01-01' or ordr_dte is null))
Wednesday, March 7, 2012
DELETE without logging
Hi all,
Is there a way to execute a DELETE sentence with a WHERE condition (cannot use TRUNCATE TABLE) without logging in order to execute a fast deletion of records?
Thanks in advance.
God Bless.Q1 Is there a way to execute a DELETE statement with a WHERE clause (cannot use TRUNCATE TABLE) without logging?
A1 No. (Not in any kind of production setting. Also, note that truncations are not logged on a row by row basis.)
Q2 Is there a way to execute a DELETE statement with a WHERE clause in order to execute a "faster" deletion of records?
A2 Sometimes, yes. Generally optimizing large scale deletions involves on the data, indexing, the schema, and the environment. Sometimes it is a matter or having a suitable index the system can take advantage of; sometimes it may be a matter of reducing concurrency locking issues; sometimes it may be a matter of dropping excessive indexes (and later recreating needed ones); often several such factors may be involved.
Is there a way to execute a DELETE sentence with a WHERE condition (cannot use TRUNCATE TABLE) without logging in order to execute a fast deletion of records?
Thanks in advance.
God Bless.Q1 Is there a way to execute a DELETE statement with a WHERE clause (cannot use TRUNCATE TABLE) without logging?
A1 No. (Not in any kind of production setting. Also, note that truncations are not logged on a row by row basis.)
Q2 Is there a way to execute a DELETE statement with a WHERE clause in order to execute a "faster" deletion of records?
A2 Sometimes, yes. Generally optimizing large scale deletions involves on the data, indexing, the schema, and the environment. Sometimes it is a matter or having a suitable index the system can take advantage of; sometimes it may be a matter of reducing concurrency locking issues; sometimes it may be a matter of dropping excessive indexes (and later recreating needed ones); often several such factors may be involved.
Friday, February 24, 2012
Delete Snapshot Replication
When I try to drop the publication in this order:
execsp_dropsubscription @.publication = @.publicName, @.subscriber = @.servName, @.article = N'all'
exec sp_droppublication @.publication = @.publicName
I am getting the following error after dropping the publication
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Msg 16943, Level 16, State 4, Procedure sp_MSrepl_changesubstatus, Line 1271
Could not complete cursor operation because the table schema changed after the cursor was declared.
Could anyone give me a suggestion about how to avoid this error?
Thanks!
Which if the above two stored procs is raising the error, and what version of sql server are you running?|||The error is raised when trying to delete the publication with:
exec sp_droppublication @.publication = @.publicName
I am working with SQL Server 2005, service pack 1.
I hope with this information you can help me, or else please let me know.
Thanks!
|||this is a dumb suggestion, rather a shot in the dark, but can you stop/start the sql server service, and then try it again? Something about transactions/cursors that may be stuck/lingering somewhere?|||I tried that already. I finally got to work the deleting of the publication, but the error remains as reported earlier. I am thinking this is a 'MS bug' that has not been totally fixed since SQL Server 7... pretty sad, huh? If still anyone has any idea what to do, please let me know.
Thanks!
|||If you can provide a full repro of the problem, it would help us tremendously. If you can reproduce it from scratch, please post the TSQL code, otherwise this issue has been intermitent throughout the years, nothing solid for us to fix.
Other questions - are you wrapping this in a transaction? What's the db compat level? How many subscriptions are there?
Subscribe to:
Posts (Atom)