Automatic Database Backup

[eflatunn]

Hi all,

I have written an application in C# .NET which will run on another PC. The database required for the application will be created during the installation. How can I provide that the application backups the database periodically? Can I generate an SQL script which will run at installation and then backup the database automatically?

I really need advices.
Thank you...
Burcu

[Motoma]

In Enterprise Manager you can set up automated maintenance procedures for backing up, re indexing, and repairing databases.

[eflatunn]

In Enterprise Manager you can set up automated maintenance procedures for backing up, re indexing, and repairing databases.

Thank you. But, what I want to do is to realize it programmatically. I don't want that the user has to set up anything in Enterprise Manager. All the back up procedure must be organized during the installation of my application program.

An idea is to define a scheduled job on server agent by using SQL-DMO library. So, the job will back up the database periodically. But I am not sure if it is the best choice.
Any other ideas?

[Motoma]

Thank you. But, what I want to do is to realize it programmatically. I don't want that the user has to set up anything in Enterprise Manager. All the back up procedure must be organized during the installation of my application program.

An idea is to define a scheduled job on server agent by using SQL-DMO library. So, the job will back up the database periodically. But I am not sure if it is the best choice.
Any other ideas?

I am sure there is a way to set up SQL Server maintenance plans without the Enterprise Manager IDE. I am sorry, but I do not know how exactly to do this. Your best bet, if you wanted to pursue this, would be to check out the MSDN and look through all of the system stored procedures. After that, you could try the setting up a Schedule with the SQL Profiler running to see if you could find out what the IDE is calling on the server.

[Motoma]

I just found something while digging through the help files. There are four stored procedures that you may be able to use: sp_add_jobschedule sp_delete_jobshedule sp_help_jobschedule and sp_update_jobschedule.

[tijujoseph]

hai
I also want to know how can i get the backup of a database programmatically
If any one know this please help me

[eflatunn]

I included SQL-DMO library in my application. Using SQL-DMO objects, I create a job in SQL Server Agent and assign a schedule so that back up can be done periodically.

[Vidhura]

Hope the following helps

backupDir = Directory.GetCurrentDirectory() + "\\DBBackup";
if (!Directory.Exists(backupDir))
{
Directory.CreateDirectory(backupDir);
}
datePart = DateTime.Now.ToString(dateFormat);

backupFileName = backupDir + "\\DBName_" + datePart + "_" + "backup.log";

backupQuery = "use master; if exists ( select 1 from sysdevices where name = 'DBName') exec sp_dropdevice 'DBName'; " +
"exec sp_addumpdevice 'disk', 'DBName', '" + backupFileName + "' ; backup database DBName to DBName";

try
{
//gets osql and runs osql tool to execute the DB scripts
ProcessStartInfo procInfo = new ProcessStartInfo("osql.exe");
// specifies the window style
procInfo.WindowStyle = ProcessWindowStyle.Hidden;
//specifies the arguments for the process
procInfo.Arguments = Common.GetCommonProcessArguments(backupQuery);
//starts the process
Process osql = Process.Start(procInfo);

//waits for all the dbscripts to run.
osql.WaitForExit();
osql.Dispose();
}


private static string GetCommonProcessArguments(string fileName)
{
// string to be passed to osql tool
string result = " -S " + Environment.MachineName + @" -E "+ "-n" + " -Q " + Char.ToString('"') +
fileName + Char.ToString('"') + " -o " + Char.ToString('"') + CurrentPath + "\\DBScriptsLog.txt";

return result;

}