?
Solved

Overwrite old SQL Backups

Posted on 2009-07-07
4
Medium Priority
?
674 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 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 2000 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

Optimize your web performance

What's in the eBook?
- Full list of reasons for poor performance
- Ultimate measures to speed things up
- Primary web monitoring types
- KPIs you should be monitoring in order to increase your ROI

Question has a verified solution.

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

INTRODUCTION: While tying your database objects into builds and your enterprise source control system takes a third-party product (like Visual Studio Database Edition or Red-Gate's SQL Source Control), you can achieve some protection using a sing…
In this article we will get to know that how can we recover deleted data if it happens accidently. We really can recover deleted rows if we know the time when data is deleted by using the transaction log.
Michael from AdRem Software outlines event notifications and Automatic Corrective Actions in network monitoring. Automatic Corrective Actions are scripts, which can automatically run upon discovery of a certain undesirable condition in your network.…
This tutorial will teach you the special effect of super speed similar to the fictional character Wally West aka "The Flash" After Shake : http://www.videocopilot.net/presets/after_shake/ All lightning effects with instructions : http://www.mediaf…

770 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