T-SQL Script to Backup all User Databases

T-SQL script to backup all user databases in SQL.

-- cleanup history
declare @dt datetime 

-- get the current date/time, subtract 7 days.
set @dt = DATEADD(d, -7, getdate())

-- delete backup history
exec msdb.dbo.sp_delete_backuphistory @dt
-- delete job history
EXEC msdb.dbo.sp_purge_jobhistory  @oldest_date=@dt
-- delete log entries
EXECUTE msdb..sp_maintplan_delete_log null,null,@dt

--GO

-- Create the directories for the database backups
declare @strBackupPath nvarchar(500)
-- set the root backup folder.
set @strBackupPath = rtrim('C:\SQL Data\MSSQL10_50.MSSQLSERVER\MSSQL\Backup\ ')

DECLARE @DirTree TABLE (subdirectory nvarchar(255), depth INT)
INSERT INTO @DirTree(subdirectory, depth)
EXEC master.sys.xp_dirtree @strBackupPath

-- delete all child folders
delete from @DirTree where depth <> 1

-- declare a cursor to iterate through our list of databases
-- retrieve only user databases, owner_sid <> 0x01
declare curDatabases cursor FAST_FORWARD for
       select [name]
       from sys.databases
       where owner_sid <> 0x01
OPEN curDatabases

-- declare a variable to hold the database name.
declare @dbName nvarchar(500) 

-- get the 1st record
fetch next from curDatabases into @dbName

WHILE @@FETCH_STATUS = 0
begin
       -- build the backup path for the current database
       declare @strDatabaseBackupPath nvarchar(500)
       set @strDatabaseBackupPath = @strBackupPath + @dbName
       
       -- determine if the subdirectory needs to be created.
       if (not exists(select 1 from @DirTree where subdirectory = @dbName))
       begin       
              -- create the subdirectory
              EXEC master.dbo.xp_create_subdir @strDatabaseBackupPath              
       end       

       -- delete the old backup files.
       EXECUTE master.dbo.xp_delete_file 0
              , @strDatabaseBackupPath
              , N'bak'
              , @dt

       -- declare a variable that we'll use to get the individual date parts.
       declare @lDatePart int
       -- get the current date/time
       declare @dtNow datetime
       set @dtNow = GETDATE()

       -- declare our variable to hold the backup date/time stamp string.
       declare @strBackupTimeStamp nvarchar(50)
       -- initialize the Backup date/time stamp string.
       set @strBackupTimeStamp = '_backup'
       -- build the rest of the date/time stamp string.
       -- year
       set @strBackupTimeStamp += '_'
       set @strBackupTimeStamp += convert(varchar, datepart(year, @dtNow)) 
       -- month
       set @strBackupTimeStamp += '_'
       set @lDatePart = datepart(month , @dtNow)
       if (@lDatePart < 10) set @strBackupTimeStamp += '0'
       set @strBackupTimeStamp += convert(varchar, @lDatePart)
       -- date
       set @strBackupTimeStamp += '_'
       set @lDatePart = datepart(dd, @dtNow)
       if (@lDatePart < 10) set @strBackupTimeStamp += '0'
       set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
       -- time
       set @strBackupTimeStamp += '_'
       -- hour
       set @lDatePart = datepart(hour, @dtNow)
       if (@lDatePart < 10) set @strBackupTimeStamp += '0'
       set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
       -- minute
       set @lDatePart = datepart(minute, @dtNow)
       if (@lDatePart < 10) set @strBackupTimeStamp += '0'
       set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
       -- seconds
       set @lDatePart = datepart(second, @dtNow)
       if (@lDatePart < 10) set @strBackupTimeStamp += '0'
       set @strBackupTimeStamp += CONVERT(varchar, @lDatePart)
       -- nanoseconds
       set @strBackupTimeStamp += '_'
       set @strBackupTimeStamp += left(datename(ns, SYSDATETIME()), 7)
       
       -- declare a variable for the backup name.
       declare @strBackupName nvarchar(500)
       -- build the backup name
       set @strBackupName = @dbName
       set @strBackupName += @strBackupTimeStamp       
       
       -- build the full database backup path.
       set @strDatabaseBackupPath += '\' + @strBackupName + '.bak'
       
       --print @strBackupName
       --print @strDatabaseBackupPath
       --print ''

       -- do the backup on the database
       --BACKUP DATABASE @dbName 
       --       TO DISK = N'E:\Backup\ReportServer\ReportServer_backup_2010_11_08_133443_7479175.bak' 
       --       WITH NOFORMAT, NOINIT,  
       --       NAME = N'ReportServer_backup_2010_11_08_133443_7323186', 
       --       SKIP, REWIND, NOUNLOAD,  STATS = 10
       BACKUP DATABASE @dbName 
              TO DISK = @strDatabaseBackupPath 
              WITH NOFORMAT, NOINIT,  
              NAME = @strBackupName, 
              SKIP, REWIND, NOUNLOAD,  STATS = 10

       -- get the next record
       fetch next from curDatabases into @dbName
end

-- close & deallocate the cursor
CLOSE curDatabases
DEALLOCATE curDatabases