CODEWITHSUNDEEP
sql-server-2005backup
dstr
dstr

Reputation: 8928

How to create only one backup file for a SQL Server database

I've created a Back Up Database Task for a few selected databases in my server. What I want to do is to have only one backup file for any database. The new one could overwrite the old one or create a new one and delete the old one, doesn't matter. I've checked Backup set will expire: 2 days but but evidently this doesn't do what I thought it'd do, it keeps creating new backup files every day. How can accomplish this?

Upvotes: 3

Views: 2592

Answers (3)

zb226
zb226

Reputation: 10500

Even if this is a very old question and probably targeted SQL Server 2008, I think the accepted answer is misleading because the "History Cleanup Task" is meant for cleaning various logs, but not backup files itself.

For that purpose you can use a "Maintenance Cleanup Task" instead:

enter image description here

Here's the article where the above screenshot is from, which covers this topic (see "Step seven").

Also, here's another article covering the same for SQL Server 2019.

Upvotes: 0

SQLMenace
SQLMenace

Reputation: 135001

Use a history cleanup task and then pick, remove everything older than 1 day or whatever your desired time frame is

alt text

Upvotes: 0

TheAlbear
TheAlbear

Reputation: 5585

I wouldn’t set the backup to expire in 2 days, as this means that you can only restore the backup for two days once the backup expires you can no longer rebuild the database using it.

In the same why you built a maintenance plan to backup the database you can create a maintenance plan to clean up the system and delete backup over x days old. then just run it after your backup plan.

Upvotes: 1

Related Questions