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 programmaticall y. 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 programmaticall y. 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_jobsched ule sp_delete_jobsh edule sp_help_jobsche dule and sp_update_jobsc hedule.

[tijujoseph]

hai
I also want to know how can i get the backup of a database programmaticall y
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.GetCu rrentDirectory( ) + "\\DBBackup ";
if (!Directory.Exi sts(backupDir))
{
Directory.Creat eDirectory(back upDir);
}
datePart = DateTime.Now.To String(dateForm at);

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
ProcessStartInf o procInfo = new ProcessStartInf o("osql.exe") ;
// specifies the window style
procInfo.Window Style = ProcessWindowSt yle.Hidden;
//specifies the arguments for the process
procInfo.Argume nts = Common.GetCommo nProcessArgumen ts(backupQuery) ;
//starts the process
Process osql = Process.Start(p rocInfo);

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


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

return result;

}