AUTOMATIC BACKUP

Posted by Macson under C# on 4/28/2009 | Views : 5964 | Status : [Member] | Replies : 2
i have to take automatic backup in sql server 2ooo after one day
i am using asp.net,c#,sql server




Responses

Posted by: Vuyiswamb on: 4/28/2009 [Member] [MVP] [Administrator] NotApplicable

Up
0
Down
Good Afternoon Macson

I was one of the People who would like to Code everything, i would invoke a DTS package from C# and it was pretty cool, but that comes with Bugs and errors that you can get answers for you. Some other things i better kept in SQL. What you are asking can be done with DTS packages and create a Bat File to execute this on Certain intervals. If i may ask , why are you still using SQL2000 ?

Here are the Links where you can create DTS's
http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/

http://msdn.microsoft.com/en-us/library/cc917688.aspx

http://msdn.microsoft.com/en-us/library/aa933550(SQL.80).aspx

http://www.databasejournal.com/features/mssql/article.php/3086891/SQL-Server-2000-DTS-Part-1.htm

http://www.databasejournal.com/features/mssql/article.php/3089011/SQL-Server-2000-DTS-Part-2---Creating-DTS-Packages-using-Wizards.htm

http://www.devarticles.com/c/a/ASP/Creating-DTS-Packages-With-SQL-Server-2000/4/

Things are a bit easier in SQL 2005 and 2008

Thank you For Posting at Dotnetfunda

Vuyiswa Maseko

Thank you for posting at Dotnetfunda
[Administrator]

Macson, if this helps please login to Mark As Answer. | Alert Moderator

Posted by: Tridev_2005 on: 10/22/2009 [Member] Starter

Up
0
Down
It is a sample C# (VS2005) 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 2000.
This code should 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 Box
2) Set backup Start date & Backup Time
3) After Finishing this then please check manually it will working or not
4) 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 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 = "";
}
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 Feb18, 2000 -
 Download source file

Macson, if this helps please login to Mark As Answer. | Alert Moderator

Login to post response