Automatic SQL Server Backup Utility using Sqlserver Agent
It is a sample C# (Visual Studio) application for Automatic Sql server Backup Utility using sqlserveragent. I have used SQL-DMO dll. This article will show you how to create a automatic backup in Sql server.
This code should work on any PC use VB.NET and installed SQL Server(any edition or Client Components for SQL Server.
SQLDMO (Which installed always bt MS SQL Serveror MS SQL Server Client Tools
To do:
- First enter your SQL Server username and password on corresponding Text Box.
- Set backup Start date & Backup Time
- After Finishing this then please check manually it will working or not
- Manual working procedure:
- Run Sql sever enterprise Manager
- Select management Option
- Open Sql server agent
- Open Jobs window
- Check whether job item exist or not
- Right click on newly created job item then, we will get one
- Popup menu, then select start job
- After finish the job then check folder "D:\backup" bkp file created or not
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 databases
public void dIsplayDatabases(ComboBox cboDatabase,Info.informationLayer info)
{
try
{
SQLDMO._SQLServer SQLServer = new SQLDMO.SQLServerClass();
cboDatabase.Items.Clear();
SQLServer.Connect(info.strServerName,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 = "<No databases found>";
}
catch (Exception err)
{
info.ErrorMessageDataLayer = err.Message;
}
}
Create Job on Server Agent:
public void CreateJob_Sql(Info.informationLayer info)
{
try
{
SQLDMO._SQLServer 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 2weeks -BkUpOnlyIfClean -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 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 Mar22, 2015 - 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)
xp_sqlmaint 'switch_string'
[
[-S server_name[\instance_name]]
[-U login_ID [-P password]]
{
[ -D database_name | -PlanName name | -PlanID guid ]
[-Rpt text_file]
[-To operator_name]
[-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
[-RmUnusedSpace threshold_percent free_percent]
[-CkDB | -CkDBNoIdx]
[-CkAl | -CkAlNoIdx]
[-CkCat]
[-UpdOptiStats sample_percent]
[-RebldIdx free_space]
[-WriteHistory]
[
{-BkUpDB [backup_path] | -BkUpLog [backup_path] }
{-BkUpMedia
{DISK [ [-DelBkUps <time_period>]
[-CrBkSubDir ] [ -UseDefDir ]
]
| TAPE
}
}
[-BkUpOnlyIfClean]
[-VrfyBackup]
]
}
]
time_period
number[minutes | hours | days | weeks | months]
Syntax (SQL Server)
sqlmaint
[-?] |
[
[-S server]
[-U login_ID [-P password]]
{
[ -D database_name | -PlanName name | -PlanID guid ]
[-Rpt text_file [-DelTxtRpt <time_period>] ]
[-To operator_name]
[-HtmlRpt html_file [-DelHtmlRpt <time_period>] ]
[-RmUnusedSpace threshold_percent free_percent]
[-CkDB | -CkDBNoIdx]
[-CkAl | -CkAlNoIdx]
[-CkTxtAl]
[-CkCat]
[-UpdSts]
[-UpdOptiStats sample_percent]
[-RebldIdx free_space]
[-WriteHistory]
[
{-BkUpDB [backup_path] | -BkUpLog [backup_path] }
{-BkUpMedia
{DISK [ [-DelBkUps <time_period>]
[-CrBkSubDir ] [ -UseDefDir ]
]
| TAPE
}
}
[-BkUpOnlyIfClean]
[-VrfyBackup]
]
}
]
Best SQL 2014 Hosting Recommendation
ASPHostPortal.com
ASPHostPortal.com provides our customers with Plesk Panel, one of the most popular and stable control panels for Windows hosting, as free. You could also see the latest .NET framework, a crazy amount of functionality as well as Large disk space, bandwidth, MSSQL databases and more. All those give people the convenience to build up a powerful site in Windows server. We offers SQL 2014 hosting starts from $5/month only. We also guarantees 30 days money back and guarantee 99.9% uptime. If you need a reliable affordable SQL 2014 Hosting, we should be your best choice.