Solved

Overwrite old SQL Backups

Posted on 2009-07-07
4
663 Views
Last Modified: 2012-05-07
Hello,

I am using the following procedure to backup a database, with a scheduled task and sqlcmd. I also created a batch file that runs after the backup and deletes all files older than x amount of days. In the procedure, for full backups, I added the "with init" to delete all older backups, but it doesn't seem to work. When I run the backup, a new backup is created every time.

Could someone tell me what I need to change with the procedure?

Thanks, T




USE [AbraEmployeeSelfService]
GO
/****** Object:  StoredProcedure [dbo].[sp_BackupDatabase]    Script Date: 07/07/2009 15:40:43 ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO


-- =============================================
-- Author: Edgewood Solutions
-- Create date: 2007-02-07
-- Description: Backup Database
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- =============================================
ALTER PROCEDURE [dbo].[sp_BackupDatabase]  
       @databaseName sysname, @backupType CHAR(1)
AS
BEGIN
       SET NOCOUNT ON;

       DECLARE @sqlCommand NVARCHAR(1000)
       DECLARE @dateTime NVARCHAR(20)

       SELECT @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(),111),'/','') +
       REPLACE(CONVERT(VARCHAR, GETDATE(),108),':','')  

       IF @backupType = 'F'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'' WITH INIT'
       
       IF @backupType = 'D'
               SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
               ' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'
       
       IF @backupType = 'L'
               SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
               ' TO DISK = ''C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''
       
       EXECUTE sp_executesql @sqlCommand
END
 
0
Comment
Question by:Streppa09
  • 2
4 Comments
 
LVL 5

Expert Comment

by:rgc6789
ID: 24798996
Using SQL 2005 there is an easier way to do this. You can create a maintenance plan that will do the same. Open Management and right click Maintenance Plans. If it is a basic plan, like backups and transactionals, db maintenance, etc, try the wizard.
0
 

Author Comment

by:Streppa09
ID: 24800194
I am using SQL 2005 Express, in which the option doesn't appear to available in this version. My plan was get rid of the extra batch file to clean up the old DB backups, in hopes that the stored procedure could do both. I am positive that it can, but my expirence with SQL is lacking. If there is a better way to perform these functions, I am open to suggestions.

Any help would be appreciated.

Thanks, T
0
 
LVL 4

Accepted Solution

by:
vbgb earned 500 total points
ID: 24801597
If you include the current date in the filename of each backup, the Init option will not overwrite any older backup, because each time you run it it is using a different filename!

If you make the filename the same each time you do a full backup, the existing file will be overwritten.
0
 
LVL 5

Expert Comment

by:rgc6789
ID: 24805721
vbgb is correct. The easiest way here is to save them as the same name thereby writing over them each time it's run.
0

Featured Post

Backup Your Microsoft Windows Server®

Backup all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

by Mark Wills Attending one of Rob Farley's seminars the other day, I heard the phrase "The Accidental DBA" and fell in love with it. It got me thinking about the plight of the newcomer to SQL Server...  So if you are the accidental DBA, or, simp…
So every once in a while at work I am asked to export data from one table and insert it into another on a different server.  I hate doing this.  There's so many different tables and data types.  Some column data needs quoted and some doesn't.  What …
A short film showing how OnPage and Connectwise integration works.
The Email Laundry PDF encryption service allows companies to send confidential encrypted  emails to anybody. The PDF document can also contain attachments that are embedded in the encrypted PDF. The password is randomly generated by The Email Laundr…

930 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

9 Experts available now in Live!

Get 1:1 Help Now