Automated SQL Server Backup: Poor Man's Edition

Small companies that cannot afford high-end appliances and software are resorting to custom solutions for their daily needs. One of the very important, if not the most imporant, asset of any company is their stored data. Big-time companies spend millions of dollars just to maintain their stored data. They acquire expensive software to make sure that they backup their data efficiently.

Luckily for those that can’t afford to backup their data using the above mentioned scheme, codes are existing to help them toward this end.

The Backup Script that I am sharing here is for SQL Server 2005. This TSQL is part of a maintenance solution that I have put in place for a company (TSQL Task).

Part of this script was posted in a separate blog post here.

/*******************************************

* SCRIPT TO BACKUP ALL USER DATABASES      *

* MARLON RIBUNAL                           *

* JULY 12, 2008                            *

*                                          *

*                                          *

********************************************/
-- Enable Execution of DOS Command FROM TSQL.
-- For Security Reasons, this will again be disabled
-- at the end of this script: Marlon Ribunal

EXEC sp_configure 'show advanced options', 1
GO

RECONFIGURE
GO

EXEC sp_configure 'xp_cmdshell', 1
GO

RECONFIGURE
GO

--DECLARE VARIABLES FOR THE SCRIPT EXECUTION
DECLARE @dbname VARCHAR (50) -- Database Name to be backed up (total of 76 User DBs)
DECLARE @path VARCHAR (256) -- Path for Backup files (\IPAdd\BackupSQLSERVER2005_BACKUP)
DECLARE @filename VARCHAR (256) -- Filename for Backup (CompanyAlias_DBName_YYYYMMDD.BAK)
DECLARE @filedate VARCHAR(50) -- Used for Filename (Date of Backup, YYYYMMDD)
DECLARE @folderdate VARCHAR(50) -- Used for SubDir (Date of Backup, YYYYMMDD)
DECLARE @dbnameoffile VARCHAR (256) -- CompanyAlias_DBName_YYYYMMDD
DECLARE @cmd VARCHAR (4000) -- Shell Command to create subdirectory

-- FORMAT THE DATE TO YYYYMMDD FOR USE WITH THE SUBDIR & FILENAME
-- BACKUP FILES WILL BE NAMED LIKE "CompanyAlias_DatabaseName_20080712.BAK"
SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(),112)

SET @path = N'\IPAdd\BackupSQLSERVER2005_BACKUP' + @folderdate + ''

SELECT @cmd = 'md "' + @path + '"'

--EXECUTE SHELL COMMAND 
EXEC master..xp_cmdshell @cmd, no_output

SELECT @filedate = CONVERT(VARCHAR(20), GETDATE(),112)

DECLARE cursor_CompanyAliasBak CURSOR FOR

SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master', 'model', 'msdb', 'tempdb')

OPEN cursor_CompanyAliasBak


FETCH NEXT FROM cursor_CompanyAliasBak INTO @dbname
WHILE @@FETCH_STATUS = 0

BEGIN
SET @dbnameoffile = 'CompanyAlias_' + @dbname + '_' + @filedate + '.BAK'
SET @filename = @path + @dbnameoffile


BACKUP DATABASE @dbname TO DISK = @filename

-- UNCOMMENT IF YOU WANT TO CAPTURE RECORDS IN A SEPARATE TABLE (STRUCTURE OF TABLE AS INDICATED)
-- INSERT INTO DB_Backup_Info.dbo.BackupInfo (DatabaseName, FileName, FileDate, FullPath)
-- VALUES (@dbname, @dbnameoffile, @filedate, @filename)

FETCH NEXT FROM cursor_CompanyAliasBak INTO @dbname

END

CLOSE cursor_CompanyAliasBak
DEALLOCATE cursor_CompanyAliasBak

--Turn OFF xp_cmdshell for security reason to comply with best practices -Marlon 
EXEC sp_configure 'xp_cmdshell', 0
GO

RECONFIGURE
GO

EXEC sp_configure 'show advanced options', 0
GO

RECONFIGURE
GO

 

 

 

 

 

 

The backup directory that will be created, and to which all succeeding backups will be stored, looks like this:

 

Related posts for this script:

- Backup Basics

- Create Directory in TSQL

- TSQL Cursor Basics

- Querying The Object Catalog And Information Schema Views

Edit: Remove “IP Address” in the example (replaced with “IPAdd”). Although, it is just a made up IP, I din’t realize it might be a valid IP for someone else.

-Marlon Ribunal

About these ads