The first line runs a SQL proc (shown below) that will backup the databases, the second line is a DOS command that loops through all the files in folder and deletes the files older than 4 days.
It's not my intent to explain all this code (although I'd be happy to if someone has a question). My intent is not to have to write all this code again.
USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabases] ******/
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
-- =============================================
-- Author: Microsoft
-- Create date: 2010-02-06
-- Description: Backup Databases for SQLExpress
-- Parameter1: databaseName
-- Parameter2: backupType F=full, D=differential, L=log
-- Parameter3: backup file location
-- =============================================
CREATE PROCEDURE [dbo].[sp_BackupDatabases]
--sp_BackupDatabases null,'f','d:\sql backup\'
@databaseName SYSNAME = NULL
,@backupType CHAR(1)
,@backupLocation NVARCHAR(200)
AS
SET NOCOUNT ON;
DECLARE @DBs TABLE (
ID INT IDENTITY PRIMARY KEY
,DBNAME NVARCHAR(500)
)
-- Pick out only databases which are online in case ALL databases are chosen to be backed up
-- If specific database is chosen to be backed up only pick that out from @DBs
INSERT INTO @DBs (DBNAME)
SELECT NAME
FROM master.sys.databases
WHERE STATE = 0
AND NAME = @DatabaseName
OR @DatabaseName IS NULL
ORDER BY NAME
-- Filter out databases which do not need to backed up
IF @backupType='F'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','AdventureWorks')
END ELSE IF @backupType='D'
BEGIN
DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') END ELSE IF @backupType='L' BEGIN DELETE @DBs where DBNAME IN ('tempdb','Northwind','pubs','master','AdventureWorks') END ELSE BEGIN RETURN END
-- Declare variables
DECLARE @BackupName varchar(100) DECLARE @BackupFile varchar(100) DECLARE @DBNAME varchar(300) DECLARE @sqlCommand NVARCHAR(1000) DECLARE @dateTime NVARCHAR(20) DECLARE @Loop int
-- Loop through the databases one by one
SELECT @Loop = min(ID)
FROM @DBs
WHILE @Loop IS NOT NULL
BEGIN
-- Database Names have to be in [dbname] format since some have - or _ in their name
SET @DBNAME = '[' + (
SELECT DBNAME
FROM @DBs
WHERE ID = @Loop
) + ']'
-- Set the current date and time n yyyyhhmmss format
SET @dateTime = REPLACE(CONVERT(VARCHAR, GETDATE(), 101), '/', '') + '_' + REPLACE(CONVERT(VARCHAR, GETDATE(), 108), ':', '')
-- Create backup filename in path\filename.extension format for full,diff and log backups
IF @backupType = 'F'
SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_FULL_' + @dateTime + '.BAK'
ELSE IF @backupType = 'D'
SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_DIFF_' + @dateTime + '.BAK'
ELSE IF @backupType = 'L'
SET @BackupFile = @backupLocation + REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + '_LOG_' + @dateTime + '.TRN'
-- Provide the backup a name for storing in the media
IF @backupType = 'F'
SET @BackupName = REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + ' full backup for ' + @dateTime
IF @backupType = 'D'
SET @BackupName = REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + ' differential backup for ' + @dateTime
IF @backupType = 'L'
SET @BackupName = REPLACE(REPLACE(@DBNAME, '[', ''), ']', '') + ' log backup for ' + @dateTime
-- Generate the dynamic SQL command to be executed
IF @backupType = 'F'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
END
IF @backupType = 'D'
BEGIN
SET @sqlCommand = 'BACKUP DATABASE ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH DIFFERENTIAL, INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
END
IF @backupType = 'L'
BEGIN
SET @sqlCommand = 'BACKUP LOG ' + @DBNAME + ' TO DISK = ''' + @BackupFile + ''' WITH INIT, NAME= ''' + @BackupName + ''', NOSKIP, NOFORMAT'
END
-- Execute the generated SQL command
EXEC (@sqlCommand)
-- Goto the next database
SELECT @Loop = min(ID)
FROM @DBs
WHERE ID > @Loop
END