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 backup file

Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.Hi,
I have a database which size is 64Go, and i backup it everyday with 1 day of retention.
But the maintenance plan don't delete the file older than 1 day.
This works fine for the other db.
Do you know what is the pb ?
regards.

Does the maintenance plan generate an error log? It should be in %Install Path%\MSSQL\LOGS. Look through there. As I recall, if there is any problem anywhere in the process of making the backup, the purge old files process will not work (this is by design).

Regards,

hmscott

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 8 million records - fastest way?

This summary is not available. Please click here to view the post.

delete 30 days from todays date?

I have a column that is to be updated to todays date - 30 days if it is older than that.

I tried this code but I get error for the conversion to datetime because it is out of range. Anyone have another solution?

update DATAFILE
set [Effective Date] = [Effective Date] - CAST('yyyy-mm-30' as datetime)
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30update DATAFILE
set [Effective Date] = [Effective Date] - 30
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30|||Ok, it was so simple?

I found this code worked also.

update DATAFILE
set [Effective Date] = DATEADD(dd, -30, GetDate())
WHERE DATEDIFF ( dd , [Effective Date] , GetDate() ) >= 30

The date is supposed to be updated to todays date minus 30 days.
Its ok now. Thanks...|||Do not use addition or subtraction operations on dates. SQL Server dates or not so simple as dates in other Microsoft products where you can just add or subtract whole numbers. I have gotten spurious results this way in the past.

Use the DATEADD and DATEDIFF functions instead.

blindman

Default Versus Named Instance

I think I see what you ar asking.
Without a client side alias to make the ServerName\InstanceName format look
like a default instance, older MDAC versions (2.6 and lower, I think) cannot
connect to a named instance. This is client library issue, not an
application issue.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Matt Tinney" <matt.tinney@.css-security.com> wrote in message
news:eeAQaxv$GHA.4292@.TK2MSFTNGP02.phx.gbl...
> When makes an application require the use of a default SQL instance versus
> named instances? If named instances are allowed with an application, how
> does one specify the specific instance to use?
>
An alternative would be to have the server use a dedicated port for the name
instanced, as opposed to dynamic ports, and then specify that in the Data
Source parameter:
MyServer,11291
Anthony Thomas

"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:eo$acrbAHHA.2328@.TK2MSFTNGP02.phx.gbl...
> I think I see what you ar asking.
> Without a client side alias to make the ServerName\InstanceName format
look
> like a default instance, older MDAC versions (2.6 and lower, I think)
cannot[vbcol=seagreen]
> connect to a named instance. This is client library issue, not an
> application issue.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
> "Matt Tinney" <matt.tinney@.css-security.com> wrote in message
> news:eeAQaxv$GHA.4292@.TK2MSFTNGP02.phx.gbl...
versus
>