This is a SCRIPT generator. It does not execute code.

The output can be used as a script to backup databases, enter into a SQL job to run backups, etc.

 /*
DESCRIPTION:
The following code generates a SQL script to do a FULL backup ALL databases to the specified location. It also creates a folder for each database on that location
CONFIGURATION
Just change the value for @LOCATION. example: SET @LOCATION = 'c:\temp'
If @EachFolder = 1 it makes a subfolder for each databases
If @EachFolder = 0 it does not. all BAK in same folder
Compatibility list: MSSQL2012 MSSQL2008 MSSQL2005
DOES NOT WORK MSSQL2000
*/
DECLARE @LOCATION VARCHAR(2000)
SET @LOCATION = 'F:\SQL Backups\BeforeUpgrade'
DECLARE @EachFolder BIT
-- If @EachFolder = 1 it makes a subfolder for each databases
-- If @EachFolder = 0 it does not. all BAK in same folder
SET @EachFolder = 0
-- ***** DO NOT MODIFY BELOW THIS LINE ********
DECLARE @mydb VARCHAR(2000)
DECLARE @rowcount INT
DECLARE @table table (rownum int identity(1,1),name sysname)
 
 
USE MASTER
PRINT 'DECLARE @loc VARCHAR(2000)'
PRINT 'set @loc = '''+@LOCATION+''''
PRINT 'DECLARE @mydb VARCHAR(2000)'
PRINT 'DECLARE @subdir VARCHAR(2000)'
PRINT 'DECLARE @sufixbk VARCHAR(20)'
PRINT 'DECLARE @full_bk_name VARCHAR(2000)'
PRINT 'DECLARE @bk_name VARCHAR(2000)'
PRINT 'DECLARE @msg VARCHAR(2000)'
PRINT 'DECLARE @backupSetId AS INT'
PRINT ''
 
-- Optional : you can modify the SELECT list for the while loop
-- This SELECT statement determines what databases to generate the backup scripts
 
INSERT INTO @table
SELECT name FROM sysdatabases WHERE VERSION is not null
and databasepropertyex(name,'Status') = 'online'
and name not in ('tempdb')
-- and name in ('database1','database2','database3')
 
SET @rowcount =1
WHILE @rowcount < = (select count(name) from @table)
BEGIN
 
SELECT @mydb = name from @table where rownum =@rowcount
 
 
PRINT '--*************** processing '+@mydb+' ************'
PRINT ' '
PRINT 'set @subdir = '''+ @mydb+''''
IF (@EachFolder=1) PRINT 'set @loc = @loc +''\'' +@subdir + ''\'''
PRINT 'set @sufixbk = CONVERT(VARCHAR(20),GETDATE(),112)'
PRINT 'set @bk_name = '''+@mydb+ '''+''_'' + @sufixbk + ''.bak'''
PRINT 'set @full_bk_name = @loc + ''\'' + @bk_name'
IF (@EachFolder=1) PRINT 'EXECUTE master.dbo.xp_create_subdir @loc'
PRINT 'BACKUP DATABASE ['+@mydb+'] TO DISK = @full_bk_name WITH NOFORMAT, NOINIT, NAME =@bk_name, SKIP, REWIND, NOUNLOAD, STATS = 10'
PRINT ',BUFFERCOUNT = 2200, BLOCKSIZE = 65536, MAXTRANSFERSIZE=2097152'
PRINT 'select @backupSetId = position from msdb..backupset where database_name='''+@mydb+''' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name='''+@mydb+''')'
PRINT 'set @msg =''Verify failed. Backup information for database ['+@mydb+'] not found.'''
PRINT 'if @backupSetId is null begin raiserror(@msg, 16, 1) end'
PRINT 'RESTORE VERIFYONLY FROM DISK = @full_bk_name WITH FILE = @backupSetId, NOUNLOAD, NOREWIND'
PRINT ' '
 
SET @rowcount = @rowcount+1
END