I am trying to backup database using C# code and i couldnt get it working. The server is SQL server 2005
Error for backup Backup failed for Server 'BDServer\\sql1'. \r\nStackTrace : at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)\r\n at BackupRestore.Program.Main(String[] args) in C:\\Documents and Settings\\tvs1\\Desktop\\BackupRestore\\BackupRestore\\Program.cs:line 84\r\nInner1 : Cannot write property CompressionOption. This property is not available on SQL Server 2005.\r\n
using System;
using System.Collections.Generic;
using System.Linq;
using System.Text;
using System.IO;
using Microsoft.SqlServer.Management.Smo;
namespace BackupRestore
{
class Program
{
static void Main(string[] args)
{
string MachineName="BDServer\\sql1";
string DBName="TEST";
string backupName = "Backup";
Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(MachineName);
Database db = server.Databases[DBName];
//To Avoid TimeOut Exception
server.ConnectionContext.StatementTimeout = 60 * 60;
RecoveryModel recoverymodel = db.DatabaseOptions.RecoveryModel;
//Define a Backup object variable.
Backup backup = new Backup();
backup.CompressionOption = BackupCompressionOptions.On;
//Specify the type of backup, the description, the name, and the database to be backed up.
backup.Action = BackupActionType.Database;
backup.BackupSetDescription = "Full backup of " + DBName + ".";
backup.BackupSetName = backupName;
backup.Database = DBName;
//Declare a BackupDeviceItem
BackupDeviceItem bdi = default(BackupDeviceItem);
bdi = new BackupDeviceItem(@"L:\ACM\Temp\Vinu\" + backupName + ".bak", DeviceType.File);
//Add the device to the Backup object.
backup.Devices.Add(bdi);
//Set the Incremental property to False to specify that this is a full database backup.
backup.Incremental = false;
//Set the expiration date.
System.DateTime backupdate = new System.DateTime();
backupdate = new System.DateTime(2008, 10, 5);
backup.ExpirationDate = backupdate;
//Specify that the log must be truncated after the backup is complete.
backup.LogTruncation = BackupTruncateLogType.Truncate;
//Run SqlBackup to perform the full database backup on the instance of SQL Server.
try
{
backup.SqlBackup(server);
}
catch (Exception ex)
{
String str = "Error for backup " + ex.Message + Environment.NewLine;
str += "StackTrace : " + ex.StackTrace + Environment.NewLine;
if (ex.InnerException != null)
{
str += "Inner1 : " + ex.InnerException.Message + Environment.NewLine;
if (ex.InnerException.InnerException != null)
{
str += "Inner2 : " + ex.InnerException.InnerException.Message + Environment.NewLine;
}
}
Console.WriteLine(str);
Console.ReadLine();
}
//Remove the backup device from the Backup object.
backup.Devices.Remove(bdi);
}
}
}
Question
still1
Hi Guys,
I am trying to backup database using C# code and i couldnt get it working. The server is SQL server 2005
Error for backup Backup failed for Server 'BDServer\\sql1'. \r\nStackTrace : at Microsoft.SqlServer.Management.Smo.Backup.SqlBackup(Server srv)\r\n at BackupRestore.Program.Main(String[] args) in C:\\Documents and Settings\\tvs1\\Desktop\\BackupRestore\\BackupRestore\\Program.cs:line 84\r\nInner1 : Cannot write property CompressionOption. This property is not available on SQL Server 2005.\r\n
using System; using System.Collections.Generic; using System.Linq; using System.Text; using System.IO; using Microsoft.SqlServer.Management.Smo; namespace BackupRestore { class Program { static void Main(string[] args) { string MachineName="BDServer\\sql1"; string DBName="TEST"; string backupName = "Backup"; Microsoft.SqlServer.Management.Smo.Server server = new Microsoft.SqlServer.Management.Smo.Server(MachineName); Database db = server.Databases[DBName]; //To Avoid TimeOut Exception server.ConnectionContext.StatementTimeout = 60 * 60; RecoveryModel recoverymodel = db.DatabaseOptions.RecoveryModel; //Define a Backup object variable. Backup backup = new Backup(); backup.CompressionOption = BackupCompressionOptions.On; //Specify the type of backup, the description, the name, and the database to be backed up. backup.Action = BackupActionType.Database; backup.BackupSetDescription = "Full backup of " + DBName + "."; backup.BackupSetName = backupName; backup.Database = DBName; //Declare a BackupDeviceItem BackupDeviceItem bdi = default(BackupDeviceItem); bdi = new BackupDeviceItem(@"L:\ACM\Temp\Vinu\" + backupName + ".bak", DeviceType.File); //Add the device to the Backup object. backup.Devices.Add(bdi); //Set the Incremental property to False to specify that this is a full database backup. backup.Incremental = false; //Set the expiration date. System.DateTime backupdate = new System.DateTime(); backupdate = new System.DateTime(2008, 10, 5); backup.ExpirationDate = backupdate; //Specify that the log must be truncated after the backup is complete. backup.LogTruncation = BackupTruncateLogType.Truncate; //Run SqlBackup to perform the full database backup on the instance of SQL Server. try { backup.SqlBackup(server); } catch (Exception ex) { String str = "Error for backup " + ex.Message + Environment.NewLine; str += "StackTrace : " + ex.StackTrace + Environment.NewLine; if (ex.InnerException != null) { str += "Inner1 : " + ex.InnerException.Message + Environment.NewLine; if (ex.InnerException.InnerException != null) { str += "Inner2 : " + ex.InnerException.InnerException.Message + Environment.NewLine; } } Console.WriteLine(str); Console.ReadLine(); } //Remove the backup device from the Backup object. backup.Devices.Remove(bdi); } } }Help appreciated
Link to comment
Share on other sites
1 answer to this question
Recommended Posts