Stored Procedure to Backup all files

if object_id( 'dbsp_fullbackup') is NOT NULL
	drop procedure dbsp_fullbackup
go
create procedure dbsp_fullbackup 
	@backupdrive varchar(1) = 'c',
	@logger tinyint = 0,
	@debug tinyint = 0
as
/*
*************************************************************
name: dbsp_fullbackup
description:
  perform a complete backup of all databases on the server except
the model, tempdb, pubs, and northwind. 

usage: exec dbsp_fullbackup 'd:', 1, 1
select * from dbalog

author: steve jones

input params:
-------------
@backupdrive	char(1). drive on local server to backup to.
@logger			tinyint. flag to log backup information. If set to 1, then information
					is logged to DBA..DBALog
@debug			tinyint. If set to 1, prints commands rather than exec()ing

output params:
--------------

return:

results:
---------

locals:
--------
@err		int, holds error value
@dbname 	sysname, name of the database to backup
@cmd 		varchar(255), holds the command string to be executed
@yr 		varchar(4), timestamp year
@mon 		varchar(2), timestamp month
@day 		varchar(2), timestamp day
@hr 		varchar(2), timestamp hour
@min 		varchar(2), timestamp minute
@sec 		varchar(2), timestamp  seconds
@tmstmp 	varchar(14) timestamp value
@bulkcopy int. flag for bulk copy option being set
@detached int, flag for a detached database
@emergency int, flag for emergency mode set for suspect DB
@load 	int, flag for database being loaded
@recovery int, flag for database being recovered
@offline int, flag for database set as offline
@shutdown int, flag for database having a problem at startup
					and being shutdown.
@suspect int, flag for database begin set as suspect
@trunc int, flag for database having the truncate log checkpoint
				set
@readonly int, flag database being set as Read-Only

modifications:
--------------

*************************************************************
*/
set quoted_identifier off
begin
set nocount on
declare	@err int,
			@dbname sysname,
			@cmd varchar(255),
			@yr varchar(4),
			@mon varchar(2),
			@day varchar(2),
			@hr varchar(2),
			@min varchar(2),
			@sec varchar(2),
			@tmstmp varchar(14),
			@bulkcopy int,
			@detached int,
			@emergency int,
			@load int,
			@recovery int,
			@offline int,
			@shutdown int,
			@suspect int,
			@trunc int,
			@readonly int

select @err = 0
/*
check parameters and exit if not correct.
*/
if @backupdrive Is NULL
 select @err = -1
if @err = -1
 begin
  raiserror( 'parameter error:usage:exec dbsp_fullbackup', 12, 1)
  return @err
 end
	
/*
Compute and set the timestamp for the backup to start. While not exact, this
section should complete within a minute, so close enough.
*/
select @yr = datepart(yyyy, getdate())

if len(datepart(mm, getdate())) = 1
	select @mon = '0' + cast( datepart(mm, getdate()) as char(1))
else
	select @mon = cast( datepart(mm, getdate()) as char(2))

if len(datepart(dd, getdate())) = 1
	select @day = '0' + cast( datepart(dd, getdate()) as char(1))
else
	select @day = cast( datepart(dd, getdate()) as char(2))

if len(datepart(hh, getdate())) = 1
	select @hr = '0' + cast( datepart(hh, getdate()) as char(1))
else
	select @hr = cast( datepart(hh, getdate()) as char(2))

if len(datepart(mi, getdate())) = 1
	select @min = '0' + cast( datepart(mi, getdate()) as char(1))
else
	select @min = cast( datepart(mi, getdate()) as char(2))

if len(datepart(ss, getdate())) = 1
	select @sec = '0' + cast( datepart(ss, getdate()) as char(1))
else
	select @sec = cast( datepart(ss, getdate()) as char(2))

select @tmstmp = @yr + @mon + @day + @hr + @min + @sec

/*
Log the start process
*/
if @logger = 1
 insert dba..dbalog(entrydt, cat, msg) values( getdate(), 'Backups', 'Started:' + @tmstmp)

/*
 If it does not exist, create the directories for backups
*/
select @cmd = 'exec master..xp_cmdshell ''if not exist ' + @backupdrive + ':\mssql md "' + @backupdrive + ':\mssql"'''
if @debug = 1
  print @cmd
else
  exec (@cmd)
select @cmd = 'exec master..xp_cmdshell ''if not exist ' + @backupdrive + ':\mssql\backup md "' + @backupdrive + ':\mssql\backup"'''
if @debug = 1
  print @cmd
else
exec (@cmd)
	
/*
create a cursor with all the user database names
*/
Create table #mydbs
 ( dbname char( 50), 
   size char( 20), 
   dbowner char( 50), 
   dbid int, 
   crdate datetime, 
   status varchar( 1000),
	lvl char( 4)
 )

Insert #mydbs  Exec sp_helpdb

declare dbnamecursor cursor for 
	select o.dbname 
	from #mydbs o
		where o.dbname not in ('master', 'msdb', 'northwind', 'pubs', 'tempdb')
		order by o.dbname

/*
Open the cursor and begin looping
*/
open dbnamecursor
fetch dbnamecursor into @dbname
	
while @@fetch_status = 0 
 begin
	if @logger = 1
 		insert dba..dbalog(entrydt, cat, msg) values( getdate(), 'Backups', 'Working:' + rtrim( @dbname))

	--	create directory for this database
	select @cmd = 'exec master..xp_cmdshell ''if not exist "' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '" md "' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '"'''
   if @debug = 1
     print @cmd
   else
	  exec (@cmd)

	--	rename files for tape backup
	select @cmd = 'exec master..xp_cmdshell ''ren ' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\*.bak *.ba1'''
select 'test'
   if @debug = 1
     print @cmd
   else
	  exec (@cmd)
	select @cmd = 'exec master..xp_cmdshell ''ren ' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\*.trn *.tr1'''
   if @debug = 1
     print @cmd
   else
	  exec (@cmd)
		
	/*
	set variable options for this database
	*/
	select @detached = databaseproperty(@dbname, 'isdetached')
	select @emergency = databaseproperty(@dbname, 'isemergencymode')
	select @load = databaseproperty(@dbname, 'isinload')
	select @recovery = databaseproperty(@dbname, 'isinrecovery')
	select @offline = databaseproperty(@dbname, 'isoffline')
	select @shutdown = databaseproperty(@dbname, 'isshutdown')
	select @suspect = databaseproperty(@dbname, 'issuspect')
	select @readonly = databaseproperty(@dbname, 'isreadonly')
	select @bulkcopy = (databaseproperty(@dbname, 'isbulkcopy'))
	select @trunc = (databaseproperty(@dbname, 'istrunclog'))
			
/*
-- debugging info
	print (@bulk)
	print (@dbo)
	print (@detached)
	print (@emergency)
	print (@load)
	print (@recovery)
	print (@offline)
	print (@readonly)
	print (@shutdown)
	print (@single)
	print (@suspect)
	print (@trunc)
*/			
	if @detached = 0
	 begin
	 	if @emergency = 0
		 begin
 			if @load = 0
			 begin
				if @recovery = 0
			 	 begin
	 				if @offline = 0
	 				 begin
	 					if @shutdown = 0
						 begin
	 						if @suspect = 0
	 						 begin
	  							if @bulkcopy = 0
			  					 begin
	 								if @trunc = 0
			 						 begin
										print 'database is not marked truncate on checkpoint'
										select @cmd = 'backup database ' + quotename(rtrim( @dbname), '') + ' to disk = ''' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'' with init, noformat, noskip'
                              if @debug = 1
                                print @cmd
                              else
	  										exec (@cmd)
										select 'error', @@error
										if @@error > 0
										 begin
											print 'there was an error'
											--	truncate log
											select @cmd = 'backup transaction ' + quotename(rtrim( @dbname), '') + ' with no_log'
                                 if @debug = 1
                                   print @cmd
                                 else
	   										exec (@cmd)
											--	do full backup of database
											select @cmd = 'backup database ' + quotename( rtrim( @dbname), '') + ' to disk = ''' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'' with init, noformat, noskip'
                                 if @debug = 1
                                   print @cmd
                                 else
			   							  exec (@cmd)
										 end
			 						 end
			 						else
			 						 begin
										print 'database is marked truncate on checkpoint'
										--	do full backup of database
										select @cmd = 'backup database ' + quotename( rtrim( @dbname), '') + ' to disk = ''' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'' with init, noformat, noskip'
                              if @debug = 1
                                print @cmd
                              else
                     			  exec (@cmd)
										--	change dboption
										select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''trunc. log on chkpt.'', ''false'''
                              if @debug = 1
                                print @cmd
                              else
  										  exec (@cmd)
										if @logger = 1
									 		insert dba..dbalog(entrydt, cat, msg) values( getdate(), 'Backups', 'Reset: ' + rtrim( @dbname))
			 						 end
			  					 end
			  					else
			  					 begin
									print 'database is marked for bulk operations'
			 						--	check truncate on checkpoint
									if @trunc = 0
									 begin
										--	do full backup of database
										select @cmd = 'backup database ' + quotename( rtrim( @dbname), '') + ' to disk = ''' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'' with init, noformat, noskip'
                              if @debug = 1
                                print @cmd
                              else
										  exec (@cmd)
										--	change dboption
									 	select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''select into/bulkcopy'', ''false'''
                              if @debug = 1
                                print @cmd
                              else
									 	  exec (@cmd)
									 end
									else
									 begin
										--	do full backup of database
										select @cmd = 'backup database ' + quotename( rtrim( @dbname), '') + ' to disk = ''' + @backupdrive + ':\mssql\backup\' + rtrim( @dbname) + '\' + rtrim( @dbname) + '_' + @tmstmp + '.bak'' with init, noformat, noskip'
                              if @debug = 1
                                print @cmd
                              else
										  exec (@cmd)
										--	change dboptions
										select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''select into/bulkcopy'', ''false'''
                              if @debug = 1
                                print @cmd
                              else
										  exec (@cmd)
										select @cmd = 'exec sp_dboption ''' + rtrim( @dbname) + ''', ''trunc. log on chkpt.'', ''false'''
                              if @debug = 1
                                print @cmd
                              else
										  exec (@cmd)
									 end
								 end
							 end
							else
							 begin
								print 'database is suspect and is not available for backup operations'
							 end
							end
						else
						 begin
							print 'database is shutdown and is not available for backup operations'
						 end
					 end
					else
					 begin
						print 'database is offline and is not available for backup operations'
					 end
				 end
				else
				 begin
					print 'database is in recovery and is not available for backup operations'
				 end
			 end
			else
			 begin
				print 'database is marked for load and is not available for backup operations'
			 end
		 end
		else
		 begin
			print 'database is in emergency mode and is not available for backup operations'
		 end
	 end
	else
	 begin
		print 'database is detached and is not available for backup operations'
	 end
		
	--	get next user db
	fetch dbnamecursor into @dbname
 end

close dbnamecursor
deallocate dbnamecursor
drop table #mydbs
	
--	backup master and msdb
	
--	create directory for databases if they don't exist
select @cmd = 'exec master..xp_cmdshell ''if not exist ' + @backupdrive + ':\mssql\backup\master md ' + @backupdrive + ':\mssql\backup\master'''
if @debug = 1
  print @cmd
else
  exec (@cmd)
select @cmd = 'exec master..xp_cmdshell ''if not exist ' + @backupdrive + ':\mssql\backup\msdb md ' + @backupdrive + ':\mssql\backup\msdb'''
if @debug = 1
  print @cmd
else
  exec (@cmd)

--	rename files for tape backup
--print @cmd -- exec (("exec master..xp_cmdshell 'ren " + @backupdrive + ":\backup\msdb\*.bak *.ba1'")--'
--print @cmd -- exec (("exec master..xp_cmdshell 'ren " + @backupdrive + ":\backup\master\*.bak *.ba1'")--'
		
--	do backup master
select @cmd = 'backup database master to disk = ''' + @backupdrive + ':\mssql\backup\master\master_' + @tmstmp + '.bak'' with init, noformat, noskip'
if @debug = 1
  print @cmd
else
  exec (@cmd)
		
--	do backup msdb
select @cmd = 'backup database msdb to disk = ''' + @backupdrive + ':\mssql\backup\msdb\msdb_' + @tmstmp + '.bak'' with init, noformat, noskip'
if @debug = 1
  print @cmd
else
  exec (@cmd)

end
return @err

go
if object_id( 'dbsp_fullbackup') is null
 select 'error:dbsp_fullbackup not created'
else
 select 'dbsp_fullbackup created'
go