Sonora Communications, Inc.

MSSQL Database Backup Notes

Reference

Here, here, here and here.

About

The freebie Express versions of MSSQL do not include the SQL Server Agent component and therefore do not have scheduled backup functionality.

Here, we create a stored procedure to perform the backups and we run a backup.sql query via the Windows Scheduled Tasks tool to initiate and control the scheduled backups.

Install Management Tools

Download and install the Microsoft SQL Management Studio Express.  This seems to be correct for Microsoft SQL Server 2005 Express.

Configure

Create a folder C:\SQL_Backup for the backups to be placed in.

Create a stored procedure by executing this query:

USE [master]
GO
/****** Object: StoredProcedure [dbo].[sp_BackupDatabase] Script Date: 02/07/2007 11:40:47 ******/
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
-- =============================================
CREATE 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:\SQL_Backup\' + @databaseName + '_Full_' + @dateTime + '.BAK'''

IF @backupType = 'D'
SET @sqlCommand = 'BACKUP DATABASE ' + @databaseName +
' TO DISK = ''C:\SQL_Backup\' + @databaseName + '_Diff_' + @dateTime + '.BAK'' WITH DIFFERENTIAL'

IF @backupType = 'L'
SET @sqlCommand = 'BACKUP LOG ' + @databaseName +
' TO DISK = ''C:\SQL_Backup\' + @databaseName + '_Log_' + @dateTime + '.TRN'''

EXECUTE sp_executesql @sqlCommand
END

Create a C:\SQL_Backup\backup.sql file that will specify the databases to be backed up:

sp_BackupDatabase 'master', 'F'
GO
sp_BackupDatabase 'model', 'F'
GO
sp_BackupDatabase 'msdb', 'F'
GO
sp_BackupDatabase 'ppm', 'F'
GO
QUIT

Create a batch file to delete old backups and to run the actual backup query:

rem C:\SQL_Backup\sql_backup.bat
rem Delete old backup files that have been backed up to online backup

del c:\sql_backup\*.bak

rem Run the SQL backup stored procedure to back up databases
rem The c:\sql_backup\backup.sql query specifies which databases to back up
rem sqlcmd -S server\instance -E -i C:\SQL_Backup\backup.sql

sqlcmd -S PPMSVR\YARDIPM -E -i c:\sql_backup\backup.sql

Create a scheduled task to run a command like this prior to your nightly tape or on-line backup:

Run:

c:\sql_backup\SQL_Backup.bat

Start In:

c:\sql_backup