Sql Server administrators often need to backup databases and preferably automated. following script will create backups of all databases other than specified,

Script will create separate directory of backups, currently this script runs daily and creates daily directory. to create physical directory using Sql we need to enable a stored procedure “xp_cmdshell” and that can only be enabled when Sql Server is configured in “advanced options”. Enabling “xp_cmdshell” is not a good idea as it is a big security issue. So make sure at end of script turn it Off (as done in script.)

This script should be scheduled in “Sql Agent Jobs”.

EXEC sp_configure 'show advanced options', 1
RECONFIGURE
EXEC sp_configure 'xp_cmdshell', 1
RECONFIGURE 
DECLARE @name VARCHAR(500) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @FolderPath VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

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

SET @FolderPath = 'MD \\office-server\AUTOBACKUPS\AutoDBBackups\' + @fileDate
SET @path = '\\office-server\AUTOBACKUPS\AutoDBBackups\' + @fileDate + '\'

EXEC xp_cmdshell @FolderPath

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

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor

DEALLOCATE db_cursor

EXEC sp_configure 'xp_cmdshell', 0
RECONFIGURE
EXEC sp_configure 'show advanced options', 0
RECONFIGURE