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:
-------------
@backupdrivechar(1). drive on local server to backup to.
@loggertinyint. flag to log backup information. If set to 1, then information
is logged to DBA..DBALog
@debugtinyint. If set to 1, prints commands rather than exec()ing

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

return:

results:
---------

locals:
--------
@errint, 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