(Practically) Fool-Proof Backups

A script and batch file that practically anyone can run, no matter how non-technical. The batch file prompts you for backup path, server, and instance, and then calls the SQL script.

: -----------------------------------------------------------------------------
: Prompts user for info and calls the SQL script to do the backup.             
: -----------------------------------------------------------------------------
: Copyright 2010  Larry Leonard, Definitive Solutions Inc.                     
:                 http://www.DefinitiveSolutions.com                           
:                                                                              
: Copying and distribution of this file, with or without modification, are     
: permitted in any medium without royalty provided the copyright notice and    
: this notice are preserved.  This file is offered as-is, without any          
: warranty.                                                                    
: -----------------------------------------------------------------------------

@echo off
prompt $g
@echo.
@echo %date% %time%
@echo.


: -----------------------------------------------------------------------------
: Display free disk space.
: -----------------------------------------------------------------------------

for /f "tokens=3" %%a in ('dir ^ find /i "bytes free"') do set freeMB=%%a
set freeMB=%freeMB:~0,-8%
@echo Disk Space Free: %freeMB% MB
@echo.


: -----------------------------------------------------------------------------
: Prompt the user.
: -----------------------------------------------------------------------------

set /p   TargetServerAndInstance=Server and Instance  ("Enter" for default)?
if     "%TargetServerAndInstance%"==""  set TargetServerAndInstance="UNICORN\SQL2008EXPRESS"

set /p   BackupFolder            =Backup Folder        ("Enter" for default)?
if     "%BackupFolder%"           ==""  set BackupFolder="C:\TEMP"


: -----------------------------------------------------------------------------
: Build command string.  For information on sqlcmd.exe, see:
:    http://msdn.microsoft.com/en-us/library/ms162773(SQL.90).aspx
:
:    -a    Requests a packet of a different size
:    -S    Server\instance
:    -i    Input script name
:    -o    Output script name
:    -w    Column width
:    -m    All headers are returned with messages
:    -b    sqlcmd exits and returns a DOS ERRORLEVEL value when an error occurs
:    -r    Redirects the error message output to the screen
:    -v    Declare $(variable_name)
:    -V    Lowest severity level sqlcmd reports
:
: -----------------------------------------------------------------------------

set SqlCmdCommand=sqlcmd.exe
if not "%TargetServerAndInstance%"=="" set SqlCmdCommand=sqlcmd.exe -S %TargetServerAndInstance%
set SqlCmdCommand=%SqlCmdCommand% -r 1 -b -m-1 -V 1 -a 32767 -w 2000
set SqlCmdCommand=%SqlCmdCommand% -i BackupAllDatabases.sql
set SqlCmdCommand=%SqlCmdCommand% -o BackupAllDatabases.txt
set SqlCmdCommand=%SqlCmdCommand% -v BackupFolder=%BackupFolder%


: -----------------------------------------------------------------------------
: Call the backup SQL script.
: -----------------------------------------------------------------------------

if not exist "@BackupFolder" mkdir "@BackupFolder"
if errorlevel 1 goto l_error

@echo on
%SqlCmdCommand%
@echo off
@echo.
if errorlevel 1 goto l_error


: -----------------------------------------------------------------------------
: Done.
: -----------------------------------------------------------------------------

goto l_done

:l_error
@echo.
@echo                             *** AN ERROR HAS OCCURRED! ***

:l_done:
@echo.
@echo %date% %time%
@echo.

Notepad.exe BackupAllDatabases.txt

pause

@echo.
prompt $p$g

The SQL script backs up all user databases and their logs, and the master and msdb system databases.

-------------------------------------------------------------------------------
-- Called by BackupAllDatabases.bat to backup all databases and their logs.
-- The path set in @sBackupFolder must exist - this script will not create it.
-------------------------------------------------------------------------------
-- Copyright 2010  Larry Leonard, Definitive Solutions Inc.               
--                 http://www.DefinitiveSolutions.com                     
--                                                                        
-- Copying and distribution of this file, with or without modification, are
-- permitted in any medium without royalty provided the copyright notice and
-- this notice are preserved. This file is offered as-is, without any warranty.
-------------------------------------------------------------------------------

-------------------------------------------------------------------------------
-- MUST be set as shown to support indexes on computed columns, indexed views.
SET ANSI_NULLS ON                                 -- Deprecated: leave set ON. 
SET ANSI_PADDING ON                               -- Deprecated: leave set ON. 
SET ANSI_WARNINGS ON                              -- No trailing blanks saved. 
SET ARITHABORT ON                                 -- Math failure not ignored. 
SET CONCAT_NULL_YIELDS_NULL ON                    -- NULL plus string is NULL. 
SET NUMERIC_ROUNDABORT OFF                        -- Allows loss of precision. 
SET QUOTED_IDENTIFIER ON                          -- Allows reserved keywords. 

-- These are not, strictly speaking, required, but are generally good practice.
SET NOCOUNT ON                                    -- Minimize network traffic. 
SET ROWCOUNT 0                                    -- Reset in case it got set. 
SET XACT_ABORT ON                                 -- Make transactions behave. 
-------------------------------------------------------------------------------


-- These are the only variables which you are required to provide a value for. 
DECLARE @sBackupFolder NVARCHAR(260)
SET     @sBackupFolder = 'C:\Temp'

-- Create a file-system friendly date. 
DECLARE @sDate NVARCHAR(50)
SET @sDate = CONVERT(NVARCHAR, CAST(GETDATE() AS SMALLDATETIME), 126)
SET @sDate = REPLACE(@sDate, 'T', '_')
SET @sDate = REPLACE(@sDate, ':', '-')

-- Create the commands. 
DECLARE @sSql NVARCHAR(4000)

SET @sSql =''
 + 'IF  ''?'' NOT IN (''model'', ''tempdb'') '
 + 'AND ''?'' NOT LIKE ''ReportServer$%'' '
 + ''
 + 'BEGIN '
 + ' RAISERROR(''Starting backup of database ?.'', 10, 1) WITH NOWAIT '
 + ''
 + ' BACKUP DATABASE [?] '
 + '   TO DISK = ''' + @sBackupFolder + '\?_dat_' + @sDate + '.bak '''
 + '   WITH '
 + '   NOFORMAT, '
 + '   NOINIT, '
 + '   NAME = ''? - Full Database Backup - ' + @sDate + ''', '
 + '   SKIP, '
 + '   STATS = 10; '
 + ''
 + ' IF ''?'' NOT IN (''master'', ''msdb'') '
 + ' BEGIN '
 + ' BACKUP LOG [?] '
 + '    TO DISK = ''' + @sBackupFolder + '\?_log_' + @sDate + '.bak '''
 + '    WITH '
 + '    NOFORMAT, '
 + '    NOINIT, '
 + '    NAME = ''? - Full Log Backup - ' + @sDate + ''', '
 + '    SKIP, '
 + '    STATS = 10; '
 + ' END '
 + ''
 + 'END; '

-- Run the commands for each database. 
USE master
EXEC sp_MsForEachDb @sSql

-- Done.
GO

Pretty much anyone should be able to run this.