SQLSERVER2008R2 Database Automatic backup script

1 CREATE proc [dbo].[usp_autobackupdb]2 @dbnamesysname=NULL --the name of the database to be backed up, all backups are not specified3,@path nvarchar( -)='D:\' --Backup directory path4,@backup_type varchar( -)='Database' --backup type, which can be Database,log5,@backup_sysdb int=0 --whether to back up the system database, 0 for no backup, 1 for backup6  as7 SetNocount on;8 9 Declare @dbcnt int =0Ten,@sql varchar( -)="' One,@except_db varchar( +)= Case @backup_sysdb  A  when 0  Then " '"+'Master'+" '"+','+" '" +'msdb'+" '"+','+" '"+'tempdb'+" '"+','+" '"+'Model'+" '" -  when 1  Then "' End; - Declare @db_list Table(IDint Identity(1,1) not NULL, name sysname); the Declare @backup_err_list Table(IDint Identity(1,1) not NULL, name sysname); -  if  Right(@path,1)<>'\' - Set @path=@path+'\' -  + if @dbname  is NULL or @dbname inch(' All','*') - begin + --Save all database names on a temporary table A Set @sql='select name from sys.databases where name is not in ('+@except_db+');' at Insert  into @db_list(name)exec(@sql); -  - --get a total of how many databases - Select @dbcnt=Count(1) from @db_list; - --Start Loop -  while @dbcnt>0 in begin - --get the name of the last database from the temp table to Select @dbname=Name from @db_list whereId=@dbcnt; + Set @sql='Backup'+@backup_type+' '+@dbname+'To disk='+" '"+@path+@backup_type+'_'+@dbname+'.'+Convert(varchar(8),getdate(), the)+'.'+Datename(HH,GETDATE())+" '" - --Start a recurring backup the exec(@sql); * if @ @ERROR<>0 $ Insert  into @backup_err_list(name)Values(@dbname);Panax Notoginseng Set @dbcnt=@dbcnt-1 - End the End + Else A begin the Set @sql='Backup'+@backup_type+' '+@dbname+'To disk='+" '"+@path+@backup_type+'_'+@dbname+'.'+Convert(varchar(8),getdate(), the)+'.'+Datename(HH,GETDATE())+" '" + --back up only once - exec(@sql); $ if @ @ERROR<>0 $ Insert  into @backup_err_list(name)Values(@dbname); -  - End the if exists(Select *  from @backup_err_list) - SelectID, Name as 'Backup_err_dbname'  from @backup_err_list;Wuyi Else Print 'Backup Success'; the  - SetNocountoff; Wu  - GO

SQLSERVER2008R2 Database Automatic backup script