How do I delete backed-up databases?

[Bob]

Hi,

I've a cut-down version of SQL 2005 Server that doesn't have the
"maintenance plan" functionality. So I'm using an SQL Server Agent to
perform my DB backup. The script is as follows:

-------------------------------
BACKUP DATABASE [DPMDB] TO DISK = N'C:\Backup\DPMDB\RC-SERVER-5' WITH
RETAINDAYS = 7, NOFORMAT, NOINIT, NAME = N'DPMDB-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
-------------------------------

I was hoping that the "retaindays=7" would keep the backup file to a limited
size, but I'm finding it just grows and grows.

How can I delete backups that are older than 7 days?

I've tried the following command, but I'm not getting very far with it:

SQLMAINT -S RC-SEVER-5\MS$DPM2007$ -U Administrator -P xxxxxx -D DPMDB -Rpt
C:\Backup\DPMDB\DPMDB.rpt -BkUpMedia DISK -DelBkUps 200712011200

Thanks for any help on this!

Bob.

[Tibor Karaszi]

This might be a good start:

http://sqldbatips.com/showarticle.asp?ID=27

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Bob" <86c6c2e6-...@news.postalias> wrote in message
news:FAE15A7D-AB12-495E...@microsoft.com...

[Bob]

Thanks, but it looks like ExpressMaint is designed for SQL Express Edition.
I've a SQL 2005 Server that comes with Microsoft's Data Protection Manager.

Besides, it looks as though ExpressMaint is simply a front end to
SQLMAINT.EXE anyway.

So, how do I use sqlmaint.exe to accomplish what I want?

Thanks!

Bob.

[Tibor Karaszi]

> Thanks, but it looks like ExpressMaint is designed for SQL Express Edition.

It should work with any editon of SQL Server. Anyhow:

> So, how do I use sqlmaint.exe to accomplish what I want?

I want back to your original post and it seems you didn't specify a correct value for the -DelBkUps
argument. Below is a sample from Books Online:
sqlmaint -S MyServer -PlanName MyUserDBPlan -BkUpDB -BkUpMedia DISK -UseDefDir -CrBkSubDir -DelBkUps
2weeks

There doesn't seem to be much documentation for time_period, but one can probably guess the format
based on above...

--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi

"Bob" <86c6c2e6-...@news.postalias> wrote in message

news:392022F6-5420-4B73...@microsoft.com...

[Adams Qu [MSFT]]

Dear Bob,

Some Additional information:

The syntax of the parameter "-DelBkUps" should be "-DelBkUps <time period>"

<time_period> ::= number[minutes | hours | days | weeks | months]

For example: "-DelBkUps 4DAYS"

NOTE: RETAINDAYS only prevents SQL Server from overwriting the file unless
the specified number of days have elapsed. It doesn't mean that SQL Server
will automatically delete the file or erase the media after the specified
number of days. So you will have to delete the files using your own
scheduled job or use a database maintenance task.

Have a nice day!

Best regards,

Adams Qu
MCSE, MCDBA, MCTS
Microsoft Online Support

Microsoft Global Technical Support Center

Get Secure! - www.microsoft.com/security
=====================================================
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
=====================================================
This posting is provided "AS IS" with no warranties, and confers no rights.

--------------------
| From: "Bob" <86c6c2e6-...@news.postalias>
| References: <FAE15A7D-AB12-495E...@microsoft.com>
<1757C25C-1F48-4EBF...@microsoft.com>
| In-Reply-To: <1757C25C-1F48-4EBF...@microsoft.com>
| Subject: Re: How do I delete backed-up databases?
| Date: Fri, 22 Feb 2008 08:23:48 -0500
| Lines: 12
| Message-ID: <392022F6-5420-4B73...@microsoft.com>
| MIME-Version: 1.0
| Content-Type: text/plain;
| format=flowed;
| charset="iso-8859-1";
| reply-type=response
| Content-Transfer-Encoding: 7bit
| X-Priority: 3
| X-MSMail-Priority: Normal
| X-Newsreader: Microsoft Windows Mail 6.0.6000.16480
| X-MimeOLE: Produced By Microsoft MimeOLE V6.0.6000.16545
| X-MS-CommunityGroup-PostID: {392022F6-5420-4B73-B000-03E305D7A4BE}
| X-MS-CommunityGroup-ThreadID: FAE15A7D-AB12-495E-947E-EBC217DB9C19
| X-MS-CommunityGroup-ParentID: 1757C25C-1F48-4EBF-8B17-5AFD1A46CC00
| Newsgroups: microsoft.public.sqlserver.setup
| Path: TK2MSFTNGHUB02.phx.gbl
| Xref: TK2MSFTNGHUB02.phx.gbl microsoft.public.sqlserver.setup:4408
| NNTP-Posting-Host: TK2MSFTNGHUB02.phx.gbl 127.0.0.1
| X-Tomcat-NG: microsoft.public.sqlserver.setup

[Bob]

I was interested in using SQLMAINT to delete the old db backup files because
I suspected that this would also clean up any history/log information about
the back up too.

But I'm now thinking SQL Server may be keeping all this backup history
information withing the database backup file itself.

If this is true, then I guess there is no reason to use this cumbersum
SQLMAINT untility to delete the backup file; but rather simply use a batch
script to "erase" the database backup file from the command prompt (which I
see SQL Server Agent can call-up anyway).

Does this make sense?

Thanks,
Bob.

[Andrew J. Kelly]

Bob,

If you are using Data Protection Manager why are you attempting to do
backups thru SQL Server at all? Why not do it all thru DPM? But as for
your backup example there are a few issues. One is that you are sending all
the backups to a single device (file) so they simply get appended. You can
not delete individual backups from a single device. So you need to give each
backup a different unique file name if you want to delete them after x many
days. There is a stored procedure in the msdb db that is called
sp_deletebackuphistory and you should call that about once a week with a
datetime parameter that expresses how many days of backup history you wish
to maintain.

DECLARE @Date DATETIME
SET @Date = DATEADD(wk,-1,GETDATE())

EXEC [msdb].[dbo].[sp_delete_backuphistory] @Date

Here is a simple example of backing up dbs with a different file name each
time.

DECLARE @DBName NVARCHAR(100), @Device NVARCHAR(100), @Name NVARCHAR(150)

DECLARE cur_DBs CURSOR STATIC LOCAL
FOR SELECT [name]
FROM sys.databases
WHERE [name] NOT IN ('MASTER','MODEL','MSDB','TEMPDB')

OPEN cur_DBs
FETCH NEXT FROM cur_DBs INTO @DBName

WHILE @@FETCH_STATUS = 0
BEGIN

SET @Device = N'C:\Data\Backups\DD_' + @DBName + '_Full_' +
CONVERT(NVARCHAR(16),GETDATE(),112) + N'.BAK'

SET @Name = @DBName + N' Full Backup'

PRINT 'Backing up database ' + @DBName
BACKUP DATABASE @DBName TO DISK = @Device WITH INIT , NOUNLOAD ,
NAME = @Name, NOSKIP , STATS = 10, NOFORMAT

RESTORE VERIFYONLY FROM DISK = @Device WITH FILE = 1

PRINT '--------------------------------------- '

FETCH NEXT FROM cur_DBs INTO @DBName
END

CLOSE cur_DBs
DEALLOCATE cur_DBs

You still must delete the older files though.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Bob" <86c6c2e6-...@news.postalias> wrote in message

news:7F8193DF-668B-41C5...@microsoft.com...

[Bob]

Hi Andrew,

I'm trying to backup my DPM database and DPM doesn't make this particularly
easy. I either have to have a second DPM server installed or I have to
install a tape drive. Both of these choices are a bit more expensive and
cumbersum than simply using SQL Server to backup the DPM database to a disk
drive.

I presently use SQL Server Agent to run a daily scheduled (two step) job
that:

1. Runs a command script that deletes backup database files older than a
week.

2. Backs up my DPMDB to disk in a new file each day (using INIT to ensure no
appends).

I understand now that I must use the Microsoft supplied
sp_deletebackuphistory to keep the backup system tables from growing
endlessly.

However, not knowing much of anything about SQL Server and particularly
stored procedures, can you please answer the following?

1. How do I display sp_deletebackuphistory? Is this found in MS SQL Server
Management Studio?

2. How do I schedule a call to sp_deletebackuphistory? Can I do this using
a command script or maybe through an SQL Server Agent job?

Thanks,
Bob.

[Andrew J. Kelly]

> 1. How do I display sp_deletebackuphistory? Is this found in MS SQL
> Server Management Studio?

I am not sure what you mean by "Display"? The stored procedure is located
in the MSDB database and can be found under the Programability - Stored
Procedures - System Procedures folder of SSMS.

> 2. How do I schedule a call to sp_deletebackuphistory? Can I do this
> using a command script or maybe through an SQL Server Agent job?

Copy the code I posted and paste it into a Job Step in SQL Agent. You would
create a new job just like the one you did for Backups. If the schedule you
want to run this is each time you do a backup you can simply add a 3rd step
to your existing job and put it there. Just make sure to change the flow
logic of the job steps. If you want a different schedule then create a new
job with it's own schedule.

--
Andrew J. Kelly SQL MVP
Solid Quality Mentors

"Bob" <86c6c2e6-...@news.postalias> wrote in message

news:B692CD58-06EA-4CA5...@microsoft.com...

[GTC]

Hi all,

Does anyone know how to set up maintenance job that will only remove the
specific database backup file, not all databases with expiration based on
time only?

i.e. two dbs, dbA and dbB.
dbA retention period requirement is 5 days and dbB is 2 days. I do
not want dbB cleanup job remove dbA backup files in the same directory which
are older than 2 days(this is not an issue for SQL 2000). But what I can see
the 'maintenance cleanup task' is that one can only specify days to remove
and directory where the backup files locate and extention of backup files.

Thanks advanced for your help.

Glen

[Ekrem Önsoy]

How about creating two different backup folders for your dbA and dbB backup
files and then create cleanup tasks differently for each of them. Then
you'll be able to set different values for each task.

--
Ekrem Önsoy

"GTC" <G...@discussions.microsoft.com> wrote in message
news:248A7F6A-836F-4E3B...@microsoft.com...

[GTC]

Ekrem,

Thanks. That was the workaround I could think of.

SQL 2005 is backward for Database Administrator who needs support a lot of
servers. It now creates more works/jobs to do and monitor.

Thanks a lot.
Glen