SQL Server automatic backup script

SQL Server automatic backup script

SQL Server backup script:

Third-party backup, and differential backup at other times.

declare  @device  varchar (30); declare  @filename  varchar (50); declare  @datetime  varchar (50); declare  @weekname  varchar (50); declare  @weeknametmp  varchar (4); declare  @weekday  int ; set  @weekname=datename(weekday,getdate()); set  @datetime =  convert ( varchar (20),getdate(),112); set  @device=  'RapooOA'  + @datetime; set  @filename= 'D:\SQLBackup\RapooOA' +@datetime+ '.bak' ; set  @weeknametmp= substring (@weekname,3,1); if (@weeknametmp= '1' )               set  @weekday=1; else  if (@weeknametmp= '2' )               set  @weekday=2; else  if (@weeknametmp= '3' )                 set  @weekday=3; else  if (@weeknametmp= '4' )                 set  @weekday=4; else  if (@weeknametmp= '5' )                 set  @weekday=5; else  if (@weeknametmp= '6' )                 set  @weekday=6; else  if (@weeknametmp= 'Day' )                 set  @weekday=0; set  @datetime =  convert ( varchar (20),getdate()-@weekday,112); set  @device=  'RapooOA'  + @datetime; set  @filename= 'D:\SQLBackup\RapooOA' +@datetime+ '.bak' if (@weekname= 'Weday' )                    begin                   execute  sp_addumpdevice  'disk' ,@device,@filename;                   backup  database  RapooOA  to  @device                   end else                   begin                   backup  database  RapooOA  to  @device  with  differential                   end

Save As A. SQL file and use scheduled tasks for scheduled execution.

If the following error occurs: Execute exec sp_dropdevice RapooOA20160117 to delete it and run it again.

Message 15026, level 16, status 1, process sp_addumpdevice, row 74th

The logical device 'rapoooa20160117 'already exists.

This article permanently updates the link address: