November 25, 2009 at 7:40 am
Hello,
I was able to find a piece of code from the internet that successfully backs up my SQL Express 2005 database. According to the author the code will create a new backup every night with a different name (which it does). I was wondering if someone knew how I could limit the number of backups. I would like to retain 3 backups deleting the 4th (or the oldest one).
I’ve set Windows Scheduler to call/run a batch file that runs the code below in a SQL CMD window.
Batch file code: sqlcmd -S myservername\waspdbexpress -u -i E:\Backup\FullBackup.sql
--FullBackup.sql--
DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255)
SET @Path = 'E:\Backup\'
SET @FileName = 'WaspTrackAsset_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName
BACKUP DATABASE WaspTrackAsset
TO DISK = @FullPath
WITH INIT
Here is a link to the article I used to create the code about.
November 25, 2009 at 9:44 am
You can run a command like this in the batch file using sqlcmd:
DECLARE @ReturnCode int
EXECUTE @ReturnCode = master.dbo.xp_delete_file 0, N'Path to Backups', 'Backup File Extension', 'Date To Delete Before'
IF @ReturnCode <> 0 RAISERROR('Error deleting files.', 16, 1)
Here's a link that gives a little explanation of the command, http://sqlblog.com/blogs/andy_leonard/archive/2009/03/11/xp-delete-file.aspx
Jack Corbett
Consultant - Straight Path Solutions
Check out these links on how to get faster and more accurate answers:
Forum Etiquette: How to post data/code on a forum to get the best help
Need an Answer? Actually, No ... You Need a Question
November 27, 2009 at 3:01 pm
Review this thread
http://social.msdn.microsoft.com/forums/en-US/sqltools/thread/5924074f-5f1c-444a-bd09-fc3e999f2637/
It has a interesting code there.
Regards
December 7, 2009 at 12:31 pm
A big thanks! to Jack Corbett and Support.sql for your help on this one. Since this new script has been running for a week without fail I thought it was time to share it with everyone.
Basically the code below will first backup my SQL 2005 Express database and then deletes the oldest one(s) leaving 3 or 4 copies (based on how I have it set).
--FullBackup.sql - name of my .sql file called by the DOS batch file
DECLARE @Path NVARCHAR(1000), @FileName NVARCHAR(255), @FullPath NVARCHAR(1255)
SET @Path = 'E:\Backup\'
SET @FileName = 'YourDataBaseName_' + REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') + '.bak'
SET @FullPath = @Path + @FileName
BACKUP DATABASE YourDataBaseName
TO DISK = @FullPath
WITH INIT
--Removal of old backups
DECLARE @ThreeDaysAgo VARCHAR(50)
SELECT @ThreeDaysAgo = CAST(DATEADD(d, -3, GETDATE()) AS VARCHAR)
EXEC dbo.xp_cmdshell 'dir E:\Backup\*.bak /b'
IF @@ROWCOUNT > 3
BEGIN
EXECUTE dbo.xp_delete_file 0,N'E:\Backup',N'bak',@ThreeDaysAgo
EXEC dbo.xp_cmdshell 'dir E:\Backup\ *.trn /b'
IF @@ROWCOUNT > 9
EXECUTE dbo.xp_delete_file 0,N'E:\Backups',N'trn',@ThreeDaysAgo
END
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply