Personal Folder

Go to the server tree > Management > Server Agent > Jobs. Right click for creating a new job.



Push the new button to create step on backuping database.



Script below doing command: create a database backup, stored on harddisk media, stored on specified FOLDER_PATH, save in .BAKfile format, with report. Please check xp_sqlmaint documentation for further explanation.

EXEC master.dbo.xp_sqlmaint '-S [SERVER_NAME] -U sa -P [PASSWORD] -D [DATABASE_NAME] -CkDB -CkAl -CkCat -BkUpMedia DISK -BkUpDB [C:\FOLDER_PATH] -BkExt BAK -DelBkUps 2weeks -BkUpOnlyIfClean -Rpt [C:\FOLDER_PATH\FILE_NAME.txt']



Tab notification define notification report when certain condition occurs.



There is home work in this routine. We still can not run batch file from this sql job schedule. Currently I execute batch file as a service, or we can use third party scheduler software. May be next time…

----

A little documentation of xp_sqlmaint stored procedure:

• -S ServerName
  
• -U LoginID
• -P password
• -D DatabaseName
• -Rpt text_file Path and name of the report file.
• -CkDB | -CkDBNoIdx Advises SQL Server to check database integrity.. -CkDBNoIdx skips indexes during the integrity check.
• -CkAl | -CkAlNoIdx -CkAl advises SQL Server to run DBCC NEWALLOC statement for all tables and indexes. -CkAlNoIdx omits checking of indexes with DBCC NEWALLOC
• -CkCat Check consistency between system tables
• -BkUpDB [backup_path] Specifies that full database backup must be performed. Backup_path is the full path to the backup destination files.
• -BkUpMedia Specify backup media, can be disk or tape.
  
• -DelBkUps
• -BkUpOnlyIfClean Advises SQLMaint to backup database only if integrity checks did not find any errors.
• -VrfyBackup Executes RESTORE VERIFYONLY at completion of each backup to ensure that backups that were just created are readable.