SQL express automatic backup

--Step 1

--InMasterCreate a backup database stored procedure in the database.

UseMaster

Go

Create procP

@ db_name sysname , -- database name

@ Bk_pathNvarchar(1024)--Path of the backup file

 As

If isnull(@ Db_name,'') =''

Set@ Db_name =Db_name();

Declare@ DtVarchar(50 );

set @ dt = @ db_name + '_' + convert ( varchar (8 ), getdate (), 112) + '_' ;

set @ dt = @ DT + replace ( convert ( varchar (10 ), getdate (), 108 ), ':' , '' )

If right(@ Bk_path, 1) <>'/'

Set@ Bk_path = @ bk_path +'/';

Exec('Backup database ['+ @ Db_name +'] To disk = '''+ @ Bk_path + @ DT +'. Bak' with format');

Go

 

--Step 2

-- Create two Txt File -- For example D:/batcomand.txt And D:/backupdatabase.txt

--ThenBatcommand.txtThe file content is as follows::

Sqlcmd-smyfend/liangck-e-Ig:/backupdatabase. SQL

Then save.Change the extension. Bat

Note : Here -S Yes Server Name / Instance name -E Yes integration Security

ThenBackupdatabase.txtThe file content is as follows::

UseMaster

Go

ExecP'Name of the database to be backed up', 'G:/backup /'

Then save,Change the extension. SQL

-- step 3

create a task scheduler . select the task to be executed .. Click Browse .. select D:/batcommand. bat

set daily execution .. .

the backup file name is : database name _ date _ time . bak