Handy procedure for making database backups a snap.
All you need to do is specify the database name and the path, and optionally add the date or date/time to the backup filename.
You can also pass in ALL for @DB to backup all databases on this SQL instance. Very handy when you need to backup a whole server and then restore the files onto another server, as in creating a development server based on production.
Making large quantities of backups is much more efficient when done via script, as contrasted with right-clicking each database in the object explorer.
CAVEAT: Always check to see if a given database is having its transaction logs backed up. If it is, keep in mind that making a full backup will render the previous transaction log backups useless!
This proc has a couple of dependencies which are easy to install:
udfAddBackslashIfNec
DoesPathExist
USE Admin
GO
IF object_id('dbo.BackupDB') Is Not Null
DROP PROC BackupDB
GO
CREATE PROC dbo.BackupDB
@DB sysname,
@Path varchar(Max),
@DateTimeFormat varchar(2) = Null,
@Mode smallint = 0
AS
/* DATE AUTHOR REMARKS
12/1/11 PPaiva Initial creation.
DESCRIPTION
Makes a backup of the database and saves to @Path. Backup
file is always initialized (WITH INIT) so it always
contains no more than one backup set.
Optionally set @Mode = 1 and simply copy/paste the script from the output pane.
@DB can be a specific database, or 'ALL' to backkup all DBs.
@Mode - 0 - Script only
1 - Execute only (make backup)
2 - Script and Execute
@DateTimeFormat
D - Date
DT - DateTime
all else - No date or time - just database name
DEBUG
master..xp_fixeddrives
master..xp_cmdshell 'dir /ogn C:\Paul\SqlData\Bak'
DEPENDENCIES
udfAddBackslashIfNec()
DoesPathExist
USAGE
-- Get syntax help by passing in an illegal value for @DateTimeFormat
Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', '?'
-- Script for backing up ALL databases
Exec BackupDB 'All', 'C:\Paul\SqlData\Bak', Null
-- Backup a specific database
Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak'
Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', 'D'
Exec BackupDB 'Admin', 'C:\Paul\SqlData\Bak', 'DT'
*/
SET NOCOUNT ON
DECLARE @FilePathName varchar(Max),
@sDate varchar(50),
@Sql varchar(Max)
-- Help info
IF IsNull(@DateTimeFormat, '') Not In ('', 'D', 'DT')
BEGIN
SET @Sql = 'Only the following values are allowed for @DateTimeFormat:
@DateTimeFormat EXAMPLE BACKUP FILENAME DATE/TIME FORMAT
--------------- --------------------------------- -------------------
Null Northwind.bak
D Northwind_2011-12-01.bak YYYY-MM-DD
DT Northwind_2011-12-01_150253.bak YYYY-MM-DD_HHMMSS'
RaisError (@Sql, 0, 1)
RETURN
END
IF @Mode Not In (0, 1, 2)
BEGIN
RaisError ('@Mode must be 0, 1, or 2.', 16, 1)
RETURN
END
-- Check for valid @Path
DECLARE @Out bit
Exec DoesPathExist @Path, @Out OUTPUT
IF @Out = 0
BEGIN
SET @Sql = 'The supplied path <' + IsNull(@Path, '(Null)') + '> is not valid.'
RaisError (@Sql, 16, 1)
RETURN
END
SET @DB = IsNull(@DB, '')
-- Check for valid DB
IF @DB <> '' AND @DB <> 'All'
IF NOT EXISTS(SELECT * FROM master.dbo.sysdatabases WHERE Name = @DB )
BEGIN
SET @Sql = 'Database <' + @DB + '> was not found on server ' + Convert(varchar, ServerProperty('ServerName')) + '.'
RaisError (@Sql, 16, 1)
RETURN
END
SET @Path = Admin.dbo.udfAddBackslashIfNec(@Path)
IF @DateTimeFormat = 'D'
-- YYYY-MM-DD
SET @sDate = '_' + Convert(varchar(10), GetDate(), 120)
ELSE IF @DateTimeFormat = 'DT'
-- YYYY-MM-DD_HHMMSS.bak'
SET @sDate = '_' + Convert(varchar(10), GetDate(), 120) + '_' +
Substring(Convert(varchar(20), GetDate(), 120), 12, 2) +
Substring(Convert(varchar(20), GetDate(), 120), 15, 2) +
Substring(Convert(varchar(20), GetDate(), 120), 18, 2)
ELSE
SET @sDate = ''
-- Determine which DBs
CREATE TABLE #BackupDB (
Name varchar(100) NOT NULL PRIMARY KEY
)
IF @DB = 'ALL'
INSERT INTO #BackupDB
SELECT Name
FROM master.dbo.sysdatabases
WHERE Name Not In ('tempdb') -- not possible to backup tempdb
ELSE
INSERT INTO #BackupDB
SELECT Name
FROM master.dbo.sysdatabases
WHERE Name = @DB
DECLARE @MaxDB varchar(100),
@ThisDB varchar(100)
SELECT @MaxDB = Max(Name),
@ThisDB = ''
FROM #BackupDB
WHILE @ThisDB < @MaxDB
BEGIN
-- Get next DB
SELECT @ThisDB = Min(Name)
FROM #BackupDB
WHERE Name > @ThisDB
SET @FilePathName = @Path + @ThisDB + @sDate + '.bak'
SET @Sql = 'BACKUP DATABASE [' + @ThisDB + ']
TO DISK = ''' + @FilePathName + ''' WITH INIT'
-- Script only
IF @Mode = 0
BEGIN
Print @Sql
Print ''
END
-- Execute only
IF @Mode = 1
BEGIN
Print 'Backup file: ' + @FilePathName
Print ''
Exec (@Sql)
END
-- Script and Execute
IF @Mode = 2
BEGIN
Print @Sql
Print ''
Exec (@Sql)
END
END