Delete backup from backup device?

I dump my databases to backup devices each night.

However would like to purge old backups - say older than a week - from the device.

Is it a case that I have to drop and re-create the devices every 7 days or can SQL do this for me?

I have seen the RETAINDAYS claus but it applies to tape backups only?

ThanksTake help from database maintenance plan to delete the backups older than x days.

If you go to "Database Maintenance Plans" and follow the wizard for a new maintenance plan, you can set up an automatic backup, that will create full backups that are datetime stamped (on the file name) in the directory of your choosing. You can configure it to delete backups that are older than X weeks as well.sql

Delete an old subscription from the subscriber?

The publisher has been disabled. But on the subscriber, I'm unable to delete
the subscription. It was a merge.
I tried to use sp_dropmergesubscription, but I've been unable to find a good
syntax example. And from which place do I run it?
Thanks in advance for any help.
Leah
If this is SQL 2000 use the following script.
http://groups.google.com/group/micro...d?dmode=source
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
"Leah" <tech@.kaplooey.com> wrote in message
news:O9LBJo2GGHA.2444@.TK2MSFTNGP11.phx.gbl...
> The publisher has been disabled. But on the subscriber, I'm unable to
> delete the subscription. It was a merge.
> I tried to use sp_dropmergesubscription, but I've been unable to find a
> good syntax example. And from which place do I run it?
>
> Thanks in advance for any help.
>
> Leah
>
|||I ran the script in both places. didn't get rifd of the subscription. In the
meantime, I'm trying to re-establish merge replication, and now I get the
cant find sp_MSupdate_replication_status in the master table. Where is this
script located, so I can reinstall it?
Thanks for your help.
Leah
"Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
news:OQn9ot3GGHA.3728@.tk2msftngp13.phx.gbl...
> If this is SQL 2000 use the following script.
> http://groups.google.com/group/micro...d?dmode=source
> --
> 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
> "Leah" <tech@.kaplooey.com> wrote in message
> news:O9LBJo2GGHA.2444@.TK2MSFTNGP11.phx.gbl...
>
|||Okay - I found a source for the scripts.
http://www.webtropy.com/articles/sql...cedure.asp?SQL
I'm still trying to remove the old subscription from the subscriber.
"Leah" <tech@.kaplooey.com> wrote in message
news:eVK6y5DHGHA.984@.tk2msftngp13.phx.gbl...
>I ran the script in both places. didn't get rifd of the subscription. In
>the meantime, I'm trying to re-establish merge replication, and now I get
>the cant find sp_MSupdate_replication_status in the master table. Where is
>this script located, so I can reinstall it?
> Thanks for your help.
>
> Leah
>
> "Hilary Cotter" <hilary.cotter@.gmail.com> wrote in message
> news:OQn9ot3GGHA.3728@.tk2msftngp13.phx.gbl...
>
|||http://www.replicationanswers.com/General.asp had it:
L.
"Leah" <tech@.kaplooey.com> wrote in message
news:O9LBJo2GGHA.2444@.TK2MSFTNGP11.phx.gbl...
> The publisher has been disabled. But on the subscriber, I'm unable to
> delete the subscription. It was a merge.
> I tried to use sp_dropmergesubscription, but I've been unable to find a
> good syntax example. And from which place do I run it?
>
> Thanks in advance for any help.
>
> Leah
>

Delete all data which are more than one week old

Hi there,
How to delete all the data which are more than week old in the SQL Server?
ThanxYou must execute the following SQL for every relevant table:

DELETE FROM TABLE WHERE datediff(day, DateStampColumn, getdate()) > 7

This requires that you know all the tables you want to delete data from and they all have a date stamp column.

I suspect this isn't as simple as you wanted but there really is no simpler completely generic way to do this.|||assuming DateStampColumn has an index, when you do this --

... where datediff(day, DateStampColumn, getdate()) > 7

you might force a table scan

(i have not tested this lately, i don't know how smart the latest optimizer is)

do this instead --

... where DateStampColumn < dateadd(day,-7,getdate())

this way, the expression on the right is evaluated first, then the resulting value can be compared to the index values

remember, internally datetime values are full integers|||Thanx fren.
I think i manage to do it already with the code u gave.
Thanx again|||Michael,

Be aware that though these examples use a hypothetical "DateStampColumn", they are NOT refering to a column of datatype "TimeStamp" in your table. A TimeStamp column is sequentially incremented, and is not in any way related to the actual time or date the data was changed. You need to apply these solutions to a DateTime column, and usually the method of setting the DateTime value is trickier than the process of identifying expired records.|||Thanx for your advice.
I'll sure will look into it.
Thanx again|||Hey, Are you'll spying over there. I have a similar requirement for once a month purging one months data. This is what I've come up with. SPecs: where working from a historical setup of 6 months:

select * FROM dbo.Temp_table where DATE_TIME < dateadd(day,-161,getdate())



Date from today back 5 months

2003-12-01 00:00:01.000

PS. all other field have been deleted except datetime and where tested before actual delete was run.

Delete 1 month old records

I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***delete from #t
where a < cast(month(current_timestamp) as varchar) + '-1-' +
cast(year(current_timestamp) as varchar)
--Brian
(Please reply to the newsgroups only.)
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
>I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
> where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||Try,
delete #t
where a < cast(convert(varchar(6), getdate(), 112) + '01' as datetime)
AMB
"Test Test" wrote:

> I need to set up a job which will run the last day of every month to
> keep the data in the table for the current month only and delete
> everyhthing else (older than 1 month records would be deleted). see the
> sample case:
> create table #t (a datetime)
> insert into #t values (getdate())
> insert into #t values ('9/1/2005')
> insert into #t values ('8/20/2005')
> insert into #t values ('8/8/2005')
> insert into #t values ('7/3/2005')
> insert into #t values ('6/1/2005')
> insert into #t values ('4/1/2004')
>
> delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
> My final result in this case would be only two records (9/2005).
> Eeverything else should be deleted. My DELETE stament is not working bc
> of the time poartion I guess. Can you correct this SQL?
> Thanks for your help.
>
>
> *** Sent via Developersdex http://www.examnotes.net ***
>|||Oops, gotta chop off that month part:
delete from #t
where a < convert (char (6), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112) +
'01'
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Tom Moreau" <tom@.dont.spam.me.cips.ca> wrote in message
news:%23dwJ3cyrFHA.3440@.TK2MSFTNGP10.phx.gbl...
Try:
delete from #t
where a < convert (char (8), DATEADD(MONTH,-1,CURRENT_TIMESTAMP) , 112)
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinpub.com
.
"Test Test" <farooqhs_2000@.yahoo.com> wrote in message
news:O90arSyrFHA.1204@.TK2MSFTNGP15.phx.gbl...
I need to set up a job which will run the last day of every month to
keep the data in the table for the current month only and delete
everyhthing else (older than 1 month records would be deleted). see the
sample case:
create table #t (a datetime)
insert into #t values (getdate())
insert into #t values ('9/1/2005')
insert into #t values ('8/20/2005')
insert into #t values ('8/8/2005')
insert into #t values ('7/3/2005')
insert into #t values ('6/1/2005')
insert into #t values ('4/1/2004')
delete from #t
where a < DATEADD(MONTH,-1,CURRENT_TIMESTAMP)
My final result in this case would be only two records (9/2005).
Eeverything else should be deleted. My DELETE stament is not working bc
of the time poartion I guess. Can you correct this SQL?
Thanks for your help.
*** Sent via Developersdex http://www.examnotes.net ***|||It works! Thanks for all your help!
*** Sent via Developersdex http://www.examnotes.net ***