Solved

Overwrite old SQL Backups

Posted on 2009-07-07
4
662 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

Control application downtime with dependency maps

Visualize the interdependencies between application components better with Applications Manager's automated application discovery and dependency mapping feature. Resolve performance issues faster by quickly isolating problematic components.

Join & Write a Comment

This article will describe one method to parse a delimited string into a table of data.   Why would I do that you ask?  Let's say that you need to pass multiple parameters into a stored procedure to search for.  For our sake, we'll say that we wa…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
It is a freely distributed piece of software for such tasks as photo retouching, image composition and image authoring. It works on many operating systems, in many languages.
Internet Business Fax to Email Made Easy - With eFax Corporate (http://www.enterprise.efax.com), you'll receive a dedicated online fax number, which is used the same way as a typical analog fax number. You'll receive secure faxes in your email, fr…

760 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

20 Experts available now in Live!

Get 1:1 Help Now