Automatic SQL Server backup utility using SQLServerAgent

By Joshy George

Introduction

It is a sample C # (vs2005) application for automatic SQL Server backup utility using SQLSERVERAGENT. ihave used SQL-DMO DLL. this article will show you how to create a automatic backup in SQL Server 2000.

This code shocould work on any PC use VB. NET and installed SQL Server 2000 (any edition or client components for SQL Server 2000.

Sqldmo (which installed always bt ms SQL Server 2000 or ms SQL Server Client tools

To do:

1) first enter your SQL Server username and password on corresponding text box2) set backup Start Date & backup time3) after finishing this then please check manually it will working or not4) manual working procedure: 1) run SQL Sever Enterprise Manager 2) Select management option 3) Open SQL Server Agent 4) open jobs window 5) Check whether job item exist or not 6) right click on newly created job item then, we will get one 7) popup menu, then select start job 8) after finish the job then check folder "D: \ backup "bkp file created or not
 

Important Functions

Add reference to SQL-DMO DLL

You can do this by Right clicking the project in Solution Explorer, then selecting 'add reference', COM components and the latest version of "Microsoft sqldmo Object Library ".

Available Server

Public void displayserverlist (ComboBox cbolistname) {try {sqldmo. application osqlserverdmoapp = new sqldmo. application (); info. informationlayer info = new info. informationlayer (); sqldmo. namelist onamelist; onamelist = osqlserverdmoapp. listavailablesqlservers (); For (INT intindex = 0; intindex <= onamelist. count-1; intindex ++) {If (onamelist. item (intindex as object )! = NULL) {cbolistname. items. add (onamelist. item (intindex ). tostring () ;}} if (cbolistname. items. count> 0) cbolistname. selectedindex = 0; else cbolistname. TEXT = "(local)";} catch {} available databasespublic void displaydatabases (ComboBox cbodatabase, info. informationlayer info) {try {sqldmo. _ sqlserver = new sqldmo. sqlserverclass (); cbodatabase. items. clear (); sqlserver. connect (info. str Servername, info. strloginname, info. strpwd); foreach (sqldmo. Database dB in sqlserver. databases) {If (db. Name! = NULL) cbodatabase. items. add (dB. name);} cbodatabase. sorted = true; If (cbodatabase. items. count = 0) cbodatabase. TEXT = "";} catch (exception ERR) {info. errormessagedatalayer = err. message ;}} create job on Server Agent: Public void createjob_ SQL (info. informationlayer info) {try {sqldmo. _ sqlserver = new sqldmo. sqlserverclass (); sqldmo. job sqljob = new sqldmo. job (); sqldmo. jobschedule sqlschedule = new sqldmo. jobschedule (); sqlserver. connect (info. strservername, info. strloginname, info. strpwd); Switch (sqlserver. jobserver. status) {Case sqldmo_svcstatus_type.sqldmosvc_stopped: sqlserver. jobserver. start (); sqlserver. jobserver. autostart = true; break;} sqljob. name = info. strdatabasename; sqljob. description = "check and backup" + info. strdatabasename; sqlserver. jobserver. jobs. add (sqljob); sqljob. category = "database maintenance"; sqldmo. jobstep ajobstep = new sqldmo. jobstep (); ajobstep. name = "Step 2: backup the Database"; ajobstep. stepid = 1; ajobstep. databasename = info. strdatabasename; ajobstep. subsystem = "tsql"; // ------ >>> If backup folder is not found then create backup folder. string directoryname = "D: \ backup"; if (directory. exists (directoryname) = false) {system. io. directory. createdirectory (directoryname) ;}// ------ >>> string sext; sext = "Exec master. DBO. xp_sqlmaint '-s "+ info. strservername + "-U" + info. strloginname + "-P" + info. strpwd + "-d" + info. strdatabasename + "-ckdb-ckal-ckcat-bkupmedia disk-bkupdb D: \ backup-bkext Bak-delbkups 2 weeks-plugin-rpt d: \ backup \ backdb_checks.txt '"; ajobstep. command = sext; ajobstep. onsuccessaction = sqldmo_jobstepaction_type.sqldmojobstepaction_quitwithsuccess; ajobstep. onfailaction = sqldmo_jobstepaction_type.sqldmojobstepaction_quitwithfailure; sqljob. jobsteps. add (ajobstep); sqljob. applytotargetserver (info. strservername); ajobstep. doalter (); sqljob. refresh (); ajobstep. refresh ();} catch (exception ERR) {info. errormessagedatalayer = err. message ;}}}

create job shedule on Server Agent:

Public void createshedule_ SQL (info. informationlayer info) {try {// It will take bkp every week 2 day sqldmo. job sqljob = new sqldmo. job (); sqldmo. _ sqlserver = new sqldmo. sqlserverclass (); sqldmo. jobschedule sqlschedule = new sqldmo. jobschedule (); sqlserver. connect (info. strservername, info. strloginname, info. strpwd); sqljob = sqlserver. jobserver. jobs. item (info. strdatabasename); // create A new jobschedule object sqlschedule. name = "weekly backup"; sqlschedule. schedule. frequencytype = sqldmo. sqldmo_frequency_type.sqldmofreq_weekly; sqlschedule. schedule. frequencyinterval = 2; sqlschedule. schedule. frequencyrecurrencefactor = 2; // start on feb18, 2000-at 12.55 sqlschedule. schedule. activestartdate = info. intstartdate; sqlschedule. schedule. activestarttimeofday = info. intstarttime; /// This schedule has no end time or end date sqlschedule. schedule. activeenddate = 99991231; sqlschedule. schedule. activeendtimeofday = 235959; // Add the schedule to the job sqljob. beginalter (); sqljob. jobschedules. add (sqlschedule); sqljob. doalter (); // sqljob. jobschedules. refresh (); info. errormessagedatalayer = "new SQL job [databasename =" + info. strdatabasename + "] sucessfully created. ";} Catch (exception ERR) {info. errormessagedatalayer = err. message ;}} syntax (SQL Server 2000) xp_sqlmaint 'Switch _ string' [[-s SERVER_NAME [\ instance_name] [-u login_id [-P Password] {[-D database_name |-planname |-planid guid] [-RPT text_file] [-to operator_name] [-htmlrpt html_file [-delhtmlrpt] [-rmunusedspace limit free_percent] [-ckdb |-ckdbnoidx] [-ckal |-ckalnoid X] [-ckcat] [-example sample_percent] [-rebldidx free_space] [-writehistory] [{-bkupdb [backup_path] |-bkuplog [backup_path]} {-bkupmedia {disk [path [[-delbkups] [-crbksubdir] [-usedefdir] | TAPE} [-region] [-vrfybackup]}] time_periodnumber [minutes | hours | days | weeks | months] syntax (SQL Server 7.0) sqlmaint [-?] | [[-S server] [-u login_id [-P Password] {[-D database_name |-planname |-planid guid] [-RPT text_file [-deltxtrpt] [-to operator_name] [-htmlrpt html_file [-delhtmlrpt] [-rmunusedspace limit free_percent] [-ckdb |-ckdbnoidx] [-ckal |-ckalnoidx] [-cktxtal] [- ckcat] [-updsts] [-example sample_percent] [-rebldidx free_space] [-writehistory] [{-bkupdb [backup_path] |-bkuplog [backup_path]} {-bkupmedia {disk [[-delbkups] [-crbksubdir] [-usedefdir] | TAPE} [-bkuponlyifclean] [-vrfybackup]}]

download dbmaintain.zip