Skip to content

Automated SQL Server Backup: Poor Man’s Edition

April 25, 2009

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:

sampledirectorycreated1

 

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
kick it on DotNetKicks.com

About these ads
8 Comments leave one →
  1. February 19, 2010 2:35 AM

    Thanks for the useful db info.

  2. MHarr permalink
    April 27, 2009 12:56 PM

    Interesting script that you wrote. I have done somewhat similar at several client sites, and suggest a couple items to consider:

    1. Instead of a cursor, you could use the msforeachDB procedure.

    2. I would suggest sticking to the maintenance plans. Whether or not you use the wizard or use the spmaint.exe utility, you can set up one job to backup all user databases (like your cursor does). However, with your script, if one backup were to fail, then all subsequent backup operations fail. With the maintenance job the others can still be backed up. Also, the maint plan can record to history table or text file, including errors, for troubleshooting if needed.

    3. I’m not sure if your 172.68.1.3 address is internal or wan-connected (I thought 10.x.x.x, 172.16.x.x thru 172.32.x.x, 192.168.x.x were private ranges). Assuming it is public address, you should perform the backup to a local server, then use a script (vbscript or powershell script, it can be scheduled and run from another SQL Server job) to copy the files to an offsite server. Again, following the suggestion in the first step to using maint plans, you can have the maint plan keep the number of backup files to a small number on the local server, and keep more files (including monthly backups, etc) on the remote server. I like having last 2 days backup close at hand on local server, then keeping as many as possible on an offsite server (in one instance, I even sent smaller database files via email to a gmail account, and went into that account to clean up older files once a month or so).

    Hope this helps.

    • May 27, 2009 5:31 AM

      You could use a TRY CATCH block to solve that issue of, if one fails, all the rest fail. Correct?

      BEGIN TRY

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

      BACKUP DATABASE @dbname TO DISK = @filename

      END TRY
      BEGIN CATCH

      ……..
      ……….

      END CATCH

  3. April 27, 2009 3:07 AM

    Nice post…

  4. April 26, 2009 8:16 AM

    Nice Marlon, I’ve done similar “poor man backup plans” in the past. SMO makes handling the directory tree and files a little easier to manage sometimes. The comparison of enabling xp_cmdshell and allowing external access on SQLCLR is about the same though so this is a very cool and good option. It’s going into the bag of DBA scripts deffinetely :)

Trackbacks

  1. How do I automatically run nightly backups for Microsoft SQL Server 2005? - Just just easy answers
  2. How do I automatically run nightly backups for Microsoft SQL Server 2005? Drija

Leave a Reply

Fill in your details below or click an icon to log in:

WordPress.com Logo

You are commenting using your WordPress.com account. Log Out / Change )

Twitter picture

You are commenting using your Twitter account. Log Out / Change )

Facebook photo

You are commenting using your Facebook account. Log Out / Change )

Google+ photo

You are commenting using your Google+ account. Log Out / Change )

Connecting to %s

  • Archives



  • RSS SQLServerPedia

  • RSS SQL Crunch – Hot SQL Links

  • My Bookmarks

  • Business Career DBA Tool Events free ebook Humor IT News Members Only Microsoft MySQL Operating System Oracle Personal Reporting Services SQL Server SQLServerPedia Syndication SSC SSIS TSQL Tutorial Uncategorized Video


  • Software Blogs - BlogCatalog Blog Directory
  • Follow

    Get every new post delivered to your Inbox.

    Join 29 other followers

    %d bloggers like this: