This procedure backs up all user or all system DBs. It does a full / diff or log backup. It was tested on SQL 2000 & 2008.
After backing up it tests full backups (performs a test restore).
You can log actions to a temp table with the @log param.
Other params are explained in the code.
--------------------------------------------------------------------------
-- BACKUP PROCEDURE
-- v1.0
-- EXEC dbo.P_Backup_DB @DBType = 'U', @BackupType = 'D', @BackupPath = 'e:\test', @log = 1
-- select * from tempdb.dbo.BackupLog
--------------------------------------------------------------------------
USE master
GO
IF OBJECT_ID('dbo.P_Backup_DB') IS NOT NULL
DROP PROCEDURE dbo.P_Backup_DB
GO
CREATE PROCEDURE dbo.P_Backup_DB(
@DBType CHAR(1), -- U - User, S - System
@BackupType CHAR(1), -- F - Full, D - Diff, L - Log
@BackupPath NVARCHAR(500) = 'E:\Test\', -- Specify absolute path
@Log BIT = 0
)
AS
BEGIN
SET NOCOUNT ON
-- Parameter checks
IF @DBType NOT IN ('U','S')
BEGIN
RAISERROR('Invalid @DBType parameter value', 16, 1) WITH LOG
RETURN -1;
END
IF @BackupType NOT IN ('F','D','L')
BEGIN
RAISERROR('Invalid @BackupType parameter value', 16, 1) WITH LOG
RETURN -1;
END
IF @DBType = 'S' AND @BackupType IN ('D','L')
BEGIN
RAISERROR('Only Full backup is supported for system databases', 16, 1) WITH LOG
RETURN -1;
END
-- Declare variables
DECLARE @DBName VARCHAR(50) -- DB to backup
DECLARE @BKPFileName VARCHAR(256) -- filename for backup
DECLARE @TestDBName VARCHAR(50) -- DB to backup
DECLARE @FileTimeStamp VARCHAR(20) -- used to generate the filename
DECLARE @Error VARCHAR(250)
DECLARE @DBDataLogicalName VARCHAR(100) -- used for test restore
DECLARE @DBLogLogicalName VARCHAR(100) -- used for test restore
DECLARE @DBDataTestFileName VARCHAR(100) -- used for test restore
DECLARE @DBLogTestFileName VARCHAR(100) -- used for test restore
DECLARE @Sql NVARCHAR(500)
DECLARE @Sql2 NVARCHAR(500)
DECLARE @Cmd NVARCHAR(500)
DECLARE @LogText NVARCHAR(500)
-- Check if BKP folder exists and create if necessary
DECLARE @FilePath NVARCHAR(500)
DECLARE @FolderExists INT
SET @FilePath = REPLACE (@BackupPath + '\nul','\\','\')
EXEC master.dbo.xp_fileexist @FilePath, @FolderExists OUTPUT
IF @FolderExists = 0
BEGIN
SET @Cmd = 'MD '+ @BackupPath
EXEC xp_cmdshell @Cmd
END
-- Set variables
SELECT @FileTimeStamp = LEFT(REPLACE(REPLACE(REPLACE(CONVERT(VARCHAR(20),GETDATE(),120),'-',''),':','h'),' ','_'),14)
IF @Log = 1
BEGIN
IF OBJECT_ID('tempdb.dbo.BackupLog') IS NULL
BEGIN
CREATE TABLE tempdb.dbo.BackupLog (
ID INT IDENTITY(1,1),
LogText NVARCHAR(500),
ExecTime DATETIME
)
END
SET @LogText = '-------- Starting backup @DBType = ' + @DBType + ', @BackupType = ' + @BackupType
INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
VALUES (@LogText, GETDATE())
END
---------------------------------------------------------------------------------------------------------
-- USER DATABASES
---------------------------------------------------------------------------------------------------------
IF @DBType = 'U'
BEGIN
-- Go through all user DBs
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb')
AND name NOT LIKE 'Test%'
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
IF @Log = 1
BEGIN
SET @LogText = 'Processing 1: ' + @DBName
INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
VALUES (@LogText, GETDATE())
END
-- Get Data & Log logical names
IF @@VERSION LIKE '%2000%' -- use old system tables
BEGIN
SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 = 0'
SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 <> 0'
END
ELSE
BEGIN
SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 0'
SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 1'
END
EXEC sp_executesql @Sql, N'@DBDataLogicalName VARCHAR(100) OUTPUT', @DBDataLogicalName = @DBDataLogicalName OUTPUT
EXEC sp_executesql @Sql2, N'@DBLogLogicalName VARCHAR(100) OUTPUT', @DBLogLogicalName = @DBLogLogicalName OUTPUT
SET @TestDBName = 'Test' + @DBName
SET @DBDataTestFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.mdf'
SET @DBLogTestFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.ldf'
-- Check if BKP folder exists and create if necessary
SET @FilePath = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\nul'
EXEC master.dbo.xp_fileexist @FilePath, @FolderExists OUTPUT
IF @FolderExists = 0
BEGIN
SET @cmd = 'MD '+ REPLACE (@BackupPath + '\' + @DBName,'\\','\')
EXEC xp_cmdshell @cmd
END
IF @Log = 1
BEGIN
SET @LogText = 'Processing 2: ' + @DBName
INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
VALUES (@LogText, GETDATE())
END
-------------------------------------------------------------------------------------------------
IF @BackupType = 'F'
BEGIN
SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_F_' + @FileTimeStamp + '.BAK'
-- Backup
BACKUP DATABASE @DBName
TO DISK = @BKPFileName
SET @Error = @@ERROR
-- First quick check (check file)
RESTORE VERIFYONLY
FROM DISK = @BKPFileName;
SET @Error = @@ERROR
IF @Error <> 0
RAISERROR('Full backup failed for DB: %s', 16, 1, @DBName) WITH LOG
-- Second in depth check (check data)
RESTORE DATABASE @TestDBName
FROM DISK = @BKPFileName
WITH MOVE @DBDataLogicalName TO @DBDataTestFileName,
MOVE @DBLogLogicalName TO @DBLogTestFileName;
SET @Error = @@ERROR
IF @Error <> 0
RAISERROR('Test restore failed for DB: %s', 16, 1, @DBName) WITH LOG
-- The restore worked, drop the test DB
SET @Sql = N'DROP DATABASE ' + @TestDBName
EXEC sp_executesql @Sql
END
-------------------------------------------------------------------------------------------------
ELSE IF @BackupType = 'D'
BEGIN
SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_D_' + @FileTimeStamp + '.DIF'
-- Log
IF @Log = 1
BEGIN
SET @LogText = 'Will backup: ' + @DBName + ' to ' + @BKPFileName
INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
VALUES (@LogText, GETDATE())
END
-- Backup
BACKUP DATABASE @DBName
TO DISK = @BKPFileName
WITH DIFFERENTIAL
-- First quick check (check file)
RESTORE VERIFYONLY
FROM DISK = @BKPFileName;
SET @Error = @@ERROR
IF @Error <> 0
RAISERROR('Diff backup failed for DB: %s', 16, 1, @DBName) WITH LOG
END
-------------------------------------------------------------------------------------------------
ELSE IF @BackupType = 'L'
BEGIN
-- Log
IF @Log = 1
BEGIN
SET @LogText = 'Will backup: LOG ' + @DBName + ' to ' + @BKPFileName
INSERT INTO tempdb.dbo.BackupLog (LogText, ExecTime)
VALUES (@LogText, GETDATE())
END
SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_L_' + @FileTimeStamp + '.LOG'
-- Backup
BACKUP LOG @DBName
TO DISK = @BKPFileName
-- First quick check (check file)
RESTORE VERIFYONLY
FROM DISK = @BKPFileName;
SET @Error = @@ERROR
IF @Error <> 0
RAISERROR('Log backup failed for DB: %s', 16, 1, @DBName) WITH LOG
END
FETCH NEXT FROM db_cursor INTO @DBName
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
---------------------------------------------------------------------------------------------------------
-- SYSTEM DATABASES
---------------------------------------------------------------------------------------------------------
ELSE IF @DBType = 'S'
BEGIN
-- Go through all system DBs
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name IN ('master','model','msdb')
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @DBName
WHILE @@FETCH_STATUS = 0
BEGIN
-- Get Data & Log logical names
IF @@VERSION LIKE '%2000%' -- use old system tables
BEGIN
SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 = 0'
SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.dbo.sysfiles WHERE status & 0x40 <> 0'
END
ELSE
BEGIN
SET @Sql = N'SELECT @DBDataLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 0'
SET @Sql2 = N'SELECT @DBLogLogicalName = name FROM ' + @DBName + '.sys.database_files WHERE type = 1'
END
EXEC sp_executesql @Sql, N'@DBDataLogicalName VARCHAR(100) OUTPUT', @DBDataLogicalName = @DBDataLogicalName OUTPUT
EXEC sp_executesql @Sql2, N'@DBLogLogicalName VARCHAR(100) OUTPUT', @DBLogLogicalName = @DBLogLogicalName OUTPUT
SET @TestDBName = 'Test' + @DBName
SET @DBDataTestFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.mdf'
SET @DBLogTestFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_Test.ldf'
-- Check if BKP folder exists and create if necessary
SET @FilePath = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\nul'
EXEC master.dbo.xp_fileexist @FilePath, @FolderExists OUTPUT
IF @FolderExists = 0
BEGIN
SET @cmd = 'MD '+ REPLACE (@BackupPath + '\' + @DBName,'\\','\')
EXEC xp_cmdshell @cmd
END
SET @BKPFileName = REPLACE (@BackupPath + '\' + @DBName,'\\','\') + '\' + @DBName + '_F_' + @FileTimeStamp + '.BAK'
-------------------------------------------------------------------------------------------------
-- Backup
BACKUP DATABASE @DBName
TO DISK = @BKPFileName
SET @Error = @@ERROR
-- First quick check (check file)
RESTORE VERIFYONLY
FROM DISK = @BKPFileName;
SET @Error = @@ERROR
IF @Error <> 0
RAISERROR('Full backup failed for system DB: %s', 16, 1, @DBName) WITH LOG
-- Second in depth check (check data)
RESTORE DATABASE @TestDBName
FROM DISK = @BKPFileName
WITH MOVE @DBDataLogicalName TO @DBDataTestFileName,
MOVE @DBLogLogicalName TO @DBLogTestFileName;
SET @Error = @@ERROR
IF @Error <> 0
RAISERROR('Test restore failed for DB: %s', 16, 1, @DBName) WITH LOG
-- The restore worked, drop the test DB
SET @Sql = N'DROP DATABASE ' + @TestDBName
EXEC sp_executesql @Sql
FETCH NEXT FROM db_cursor INTO @DBName
END
CLOSE db_cursor
DEALLOCATE db_cursor
END
END
GO 
This articles makes the case for using SQL LiteSpeed over other backup solutions and presents some notes about why the DBA should manage backups rather than offloading to network administration.
2003-05-12
5,467 reads
This article by new contributing member Bob Musser shows you how to reduce the amount of scans that SQL Server Agent does against databases. Not for the faint of heart.
2003-02-13
4,955 reads
This article analyzes the various options available for your backup and recovery process with SQL Server 2000 as well as an enhancement to your SQL Server backup and recovery process using a highly efficient backup and restore utility that provides significant time and disk space savings called SQL LiteSpeed.
2002-12-23
11,152 reads
In this article by Greg Larsen, he shows you how to remove those pesky orphaned users that can be a security risk after restoring to a new server.
2002-11-18
16,023 reads
All of do us regular backups. Ok, all of us SHOULD do regular backups. In this article Andy comments on why having a backup plan isn't enough and why you should look at some other options for when you need to fix data errors. This really speaks to the goal of minimizing down time using low tech, readily available solutions. If you're not interested yet, we'll add this - one of the alternatives he suggests is Microsoft Access!
2002-10-28
8,394 reads