Using SMO to backup SQL Server 2005 Database with C# Windows Forms Application, Back up failed

Question

This is my code to back up my database I followed this tutorial, as you can see, the codes are identical. The error comes after I fill in the variables. The error I get is

Backup failed for Server 'RITZEL-PC\SQLEXPRESS'.

Back up function:

    public void BackupDatabase(String databaseName, String userName, String password,        String serverName, String destinationPath)
    {
        Backup sqlBackup = new Backup();

        sqlBackup.Action = BackupActionType.Database;
        sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString();
        sqlBackup.BackupSetName = "Archive";

        sqlBackup.Database = databaseName;

        BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
        ServerConnection connection = new ServerConnection(serverName, userName, password);
        Server sqlServer = new Server(connection);

        Database db = sqlServer.Databases[databaseName];

        sqlBackup.Initialize = true;
        sqlBackup.Checksum = true;
        sqlBackup.ContinueAfterError = true;

        sqlBackup.Devices.Add(deviceItem);
        sqlBackup.Incremental = false;

        sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
        sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

        sqlBackup.FormatMedia = false;

        sqlBackup.SqlBackup(sqlServer);
    }

Button click

    private void BackUp_Btn_Click(object sender, EventArgs e)
    {
        String databaseName = @"D:\MY_THESIS\WORKING FILES\NNIT-RMS.mdf";
        String userName = "NNIT-Admin";
        String password = "password";
        String serverName = @"RITZEL-PC\SQLEXPRESS";
        String destinationPath = @"D:\";

        BackupDatabase(databaseName,userName,password,serverName,destinationPath);

    }

Database Information I am not allowed to post images yet but I have uploaded a screen shot. See here: http://img268.imageshack.us/img268/9250/sqlg.jpg

The databaseName should be the logical database name of the database when it's attached to the SQL Server Express - e.g. MYDATABASE - and not the entire file name of the .mdf file. I don't think SMO allows you to back up a separate .mdf file — marc_s
– marc_s, Commented Jan 15, 2012 at 10:26
If you would look at the screen shot I posted above, I placed a screen shot of the database information of the database I want to backup. That is the name that appears on the database node in sqlserver2005. Am I doing it wrong? — RMSP
– RMSP, Commented Jan 15, 2012 at 10:35
I'm saying: you cannot backup a "stand-alone" .mdf file with SMO. You need to attach the .mdf file to your SQL Server Express, give it a logical database name, and then use that logical database name for your SMO backup. This whole "attach .mdf on the fly" feature is a bit of a mess and quite frankly should not be used - and will be discontinued in SQL Server 2012 ("Denali") — marc_s
– marc_s, Commented Jan 15, 2012 at 10:37

Bearington · Accepted Answer · 2012-07-24 15:25:50Z

1

In order to backup a database, the process SQL Server is running as need to have the correct folder permissions on the folder the backup is targeted at.

From my experience the unless the SQL server process is running as the local admin it wont have permission to write to the root directory.

If you open SSMS and try to backup the database to your destination path you might encounter the same issue.

Try changing the backup folder to something other than the root of the drive or your my documents folder.

answered Jul 24, 2012 at 15:25

Bearington

1122 silver badges9 bronze badges

Sign up to request clarification or add additional context in comments.

Comments

Dr. Rajesh Rolen · Accepted Answer · 2012-02-08 15:04:14Z

0

Don' assign again the string value u just remove them and call the backup like this:

BackupSqlDatabase("YourDataBaseName","sa","password","ServerName","C:\\YourDataBaseName.bak");

edited Feb 8, 2012 at 15:04

Dr. Rajesh Rolen

14.3k42 gold badges111 silver badges180 bronze badges

answered Feb 8, 2012 at 10:30

Mushito

1

Collectives™ on Stack Overflow

Using SMO to backup SQL Server 2005 Database with C# Windows Forms Application, Back up failed

2 Answers 2

Comments

Comments

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

2 Answers 2

Comments

Comments

Your Answer

Sign up or log in

Post as a guest

Related