Stored procedure for backing up all databases

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

-- =============================================

-- Author:

-- Company: Manatix Technologies B.V.

-- Website: http://www.manatix.com

-- Create date: <14-09-2008>

-- Description:

-- =============================================

create PROCEDURE BackupSQLDatabases

AS

BEGIN



/* Declarations & Variables */

DECLARE @path VARCHAR (500) /* Path of the Backup Files */

DECLARE @folderdate VARCHAR (75) /* The subdir for my backups with Format YYYYMMDD */

DECLARE @cmd VARCHAR (4000) /* The command to create Subdir */

DECLARE @DBName varchar(255)

DECLARE @DATABASES_Fetch int

DECLARE @PreciceDateTime varchar(255)

DECLARE @ServerName varchar(50)

DECLARE @NetworkBackupShare varchar(75)



--Network or local disk path you wish to use, such as D:\Backup

SET @NetworkBackupShare = N'D:\Backup'



--Create a dynamic path for the backup of the databases based on datetime



EXEC sp_configure 'show advanced options', 1;

RECONFIGURE;

EXEC sp_configure 'xp_cmdshell', 1;

RECONFIGURE;

/* gives us YYYYMMDD

hour hh

minute mi, n

second ss, s

*/

SELECT @folderdate = CONVERT(VARCHAR(20), GETDATE(), 112) + N'-' + N'H' + CONVERT(VARCHAR(20), DATEPART(hour, GETDATE()), 112) + N'M' + CONVERT(VARCHAR(20), DATEPART(minute, GETDATE()), 112) + N'S' + CONVERT(VARCHAR(20), DATEPART(second, GETDATE()), 112)

--PRINT @folderdate



SET @ServerName = (SELECT @@servername)

/* Path as C:\Backup\YYYYMMDD */

SET @path = @NetworkBackupShare + N'\' + @ServerName + N'\' + @folderdate + N'\'

/* Create the command that will do the creation of the folder*/

SET @cmd = N'mkdir ' + @path

--PRINT @cmd

/* Create the new directory */

EXEC master.dbo.xp_cmdshell @cmd , no_output

/* now I can direct all the backup file to the created subdirectory like,

SET filename = path [other_variable/s] ‘.BAK‘ */

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

--Now let's actually do the backups to the path created above

DECLARE DATABASES_CURSOR CURSOR FOR
select

DATABASE_NAME = db_name(s_mf.database_id)

from

sys.master_files s_mf

where

-- ONLINE

s_mf.state = 0



-- Only look at databases to which we have access

and has_dbaccess(db_name(s_mf.database_id)) = 1



-- Not master, tempdb or model

and db_name(s_mf.database_id) not in ('Master','tempdb','model')

group by s_mf.database_id

order by 1


OPEN DATABASES_CURSOR

FETCH NEXT FROM DATABASES_CURSOR INTO @DBName

WHILE @@FETCH_STATUS = 0

BEGIN
declare @DBFileName varchar(256)

set @DBFileName = --datename(dw, getdate()) + ' - ' +

replace(replace(@DBName,':','_'),'\','_') + '.BAK'

PRINT @path

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

@DBFileName + ''' WITH NOFORMAT, INIT, NAME = N''' +

@DBName + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 100')



FETCH NEXT FROM DATABASES_CURSOR INTO @DBName


END

CLOSE DATABASES_CURSOR

DEALLOCATE DATABASES_CURSOR

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

EXEC sp_configure 'xp_cmdshell', 0;

EXEC sp_configure 'show advanced options', 0;

RECONFIGURE

END







---- USAGE
--USE [master]
--GO
--
--DECLARE @return_value int
--
--EXEC @return_value = [dbo].[BackupSQLDatabases]
--
--SELECT 'Return Value' = @return_value
--
--GO