SQL Server automatically backs up database scripts

SQL Server 2008 automatically backs up database scripts, builds the target file name, and calls the stored procedure. The task can be executed by the scheduled task.

 

Copy the following code to the scheduled task and make a few adjustments.

 

-- Create a file name

Declare @ filename varchar (1024)

Declare @ path varchar (1024)
Set @ Path = n'd: \ backup \';

Declare @ extension_name varchar (16)
Set @ extension_name = n'bak ';

Set @ filename = convert (varchar (1024), getdate (), 120)
Set @ filename = Replace (@ filename ,':','')
Set @ filename = Replace (@ filename ,'-','')
Set @ filename = Replace (@ filename ,'','')

Set @ filename = @ filename + '_' + convert (varchar (3), datepart (MS, getdate () + N'. '+ @ extension_name

-- Obtain the complete target file, and the database will be backed up to this
Set @ filename= @ Path + @ filename

-- Select @ filename

 

-- Start backup,
Mydb is the database to be backed up. The compression parameter indicates compression, saving disk space.

Backup Database[Mydb] to disk = @ filename with noformat, noinit, name = n' mydb-full database backup ', Skip, norewind, nounload, stats = 10, compression

 

-- Delete the backup file 15 days ago

Declare @ olddate datetime
Select @ olddate = getdate ()-15

-- Execute deletion (available in SQL 2008)
Execute master. DBO. xp_delete_file0, @ path, @ extension_name, @ olddate, 1

Go