Jon Galloway

In the first post of this series, I discussed scripting database restore statements. It seems simple

I favor a simpler approach: writing dynamic SQL inside a stored proc and then calling that stored proc in a job, OSQL, whatever; i.e., it's reusable and doesn't care what the "context" is. Here's what I use:

/*

 This script makes a backup and appends a Date + Time string at the end. It

accepts several (hopefully obvious) parameters allowing you

to choose between backing up a single database or all databases

on the SQL Server. You can also have it make a new folder and write

all backups to that folder.

 Scott Whigham - http://www.LearnSqlServer.com

*/

USE Master

IF ( SELECT OBJECTPROPERTY ( OBJECT_ID('sp_BackupAllDatabases'), 'IsProcedure') ) = 1

  DROP PROC sp_BackupAllDatabases

GO

CREATE  PROC sp_BackupAllDatabases  

(

  @BackupFolderLocation NVARCHAR(128) = 'D:\'

, @BackupExtension CHAR(3) = 'BAK'

, @BackupThisDatabaseOnly SYSNAME = NULL

, @IncludeSecondsInTimeStamp BIT = 1

, @MakeNewFolder BIT = 1

, @FolderBaseName SYSNAME = 'SQLServerDatabaseBackups'

)

AS  

SET NOCOUNT ON  

DECLARE @Counter TINYINT, @DateTime DATETIME, @DBName SYSNAME,  @CmdShellString SYSNAME

, @DateString VARCHAR(8), @TimeString VARCHAR(12), @BackupFileName VARCHAR(512)

SELECT @DateTime = GETDATE(), @DateString = CONVERT(VARCHAR(8), @DateTime, 112)  

, @TimeString = CONVERT(VARCHAR(12), @DateTime, 14)  

IF ( @IncludeSecondsInTimeStamp = 0)

  SET @TimeString = LEFT(REPLACE(@TimeString,':',''), 4)  -- REPLACE pulls out the ":" out of the TimeString; LEFT gets the first four characters

ELSE

 SET @TimeString = LEFT(REPLACE(@TimeString,':',''), 6)  

IF ( @MakeNewFolder=1) BEGIN

  IF ( @FolderBaseName IS NOT NULL)

     BEGIN

        DECLARE @ReturnCode INT

        SET @CmdShellString = 'md ' + @BackupFolderLocation + @FolderBaseName + '_' +  @DateString + '_' + @TimeString

        EXEC @ReturnCode = xp_cmdshell @CmdShellString

        IF ( @ReturnCode = 0 )

           SET @BackupFolderLocation = @BackupFolderLocation + @FolderBaseName + '_' +  @DateString + '_' + @TimeString + '\'

        ELSE

           BEGIN

              RAISERROR ('Unable to create this folder. Backup is terminating', 16, 1)

              RETURN -100

           END

     END

  ELSE

     BEGIN

        RAISERROR ('You said that you wanted to create a folder but did not pass in the folder name (@FolderBaseName) - try again', 16, 1)

        RETURN -100

     END

END

IF (@BackupThisDatabaseOnly IS NOT NULL) BEGIN -- User wants only to back up a single database:

  SELECT @BackupFileName = @BackupThisDatabaseOnly + '_' +  @DateString + '_' + @TimeString + '.' + @BackupExtension  

  EXEC ('BACKUP DATABASE [' + @BackupThisDatabaseOnly + '] TO DISK ='''  

     + @BackupFolderLocation + @BackupFileName + '''')  

  RETURN

END

DECLARE @HoldingTable TABLE ( ID INT IDENTITY ( 1, 1 ), DatabaseName SYSNAME )  

INSERT INTO @HoldingTable  

  SELECT name FROM master.dbo.sysdatabases  

  WHERE name NOT IN ( 'tempdb', 'model', 'northwind', 'pubs', 'AdventureWorks' )  

SELECT @Counter = MIN ( ID ) FROM @HoldingTable  

WHILE @Counter IS NOT NULL BEGIN  

  SELECT @DBName = DatabaseName FROM @HoldingTable WHERE ID = @Counter  

  SELECT @BackupFileName = @DBName + '_' +  @DateString + '_' + @TimeString + '.' + @BackupExtension

  EXEC ('BACKUP DATABASE [' + @DBName + '] TO DISK ='''  

     + @BackupFolderLocation + @BackupFileName + '''')  

  DELETE FROM @HoldingTable WHERE ID = @Counter  

  SELECT @Counter = MIN ( ID ) FROM @HoldingTable  

END  

GO

EXEC sp_BackupAllDatabases

Incase anyone lands on here looking for a free backup solution, I'd recommend you check out Jasper Smith's ExpressMaint utility:

http://www.sqldbatips.com/showarticle.asp?ID=29

PingBack from http://technote.thedeveloperside.com/?p=67

interesting

When you are looking for free restore deleted file websites and web pages, be sure to utilize all of the sources at your disposal.