Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

How can I do the following with C#:

  • Completely backup an existing database.
  • Partially backup an existing database.
  • Completely restore an existing database from a backup file.
  • Partially restore an existing database from a backup file.

My database saves images on the HARDDISK sometimes I need to backup and restore the database without the stored Images (might be 50 GB).

I need partially backup that execluding the FILESTREAM data (The saved files should be not included in the backup file).

I need partially restore that retrive the database without FILESTREAM data (The stored files should be not restored).

What is the best practices to do that?

share|improve this question
add comment

migrated from stackoverflow.com Aug 27 '13 at 0:19

This question came from our site for professional and enthusiast programmers.

1 Answer

Remmber you need the following assemblies:
Microsoft.SqlServer.ConnectionInfo
Microsoft.SqlServer.Management.Sdk.Sfc
Microsoft.SqlServer.Smo
Microsoft.SqlServer.SmoExtended

More info http://technet.microsoft.com/de-de/library/microsoft.sqlserver.management.smo.aspx

using System;
using System.IO;

namespace ConsoleApplication2
{
 using Microsoft.SqlServer.Management.Common;
 using Microsoft.SqlServer.Management.Smo;

 public class SQLServerTools
 {
      public static void SqlBackup(
           // Sql Server name.
        string serverName,
           // The database name on which the backup operation runs.
        string databaseName,
           // The desired backup filename.
        string destinationBackupFilename,
           // this if you want to back partial database without files 
    bool partial)
      {
           Console.WriteLine(
             "Backup on the server {0} the database {1} to the file {2}",
             serverName,
             databaseName,
             destinationBackupFilename);

           // Connect to default sql server instance.
           var sqlServer = GetSqlServer(serverName);

           // Define a Backup object variable.
           var sqlBackup = new Backup { Database = databaseName, };

           // Specify the type of backup, the description, the name, and the database to be backed up.
           if (partial)
           {
                sqlBackup.Action = BackupActionType.Files;
                sqlBackup.DatabaseFileGroups.Add("PRIMARY");
           }
           else
           {
                sqlBackup.Action = BackupActionType.Database;
           }

           // Declare a BackupDeviceItem.
           var deviceItem = new BackupDeviceItem(destinationBackupFilename, DeviceType.File);

           // Add the device to the Backup object.
           sqlBackup.Devices.Add(deviceItem);

           sqlBackup.Initialize = true;
           sqlBackup.ContinueAfterError = false;

           // Set the Incremental property to False to specify that this is a full database backup.
           sqlBackup.Incremental = false;
           sqlBackup.PercentCompleteNotification = 1;

           // Specify that the log must be truncated after the backup is complete.
           sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

           sqlBackup.PercentComplete += ProgressEventHandler;
           sqlBackup.Complete += ProcessCompleted;

           // Run SqlBackup to perform the full database backup on the instance of SQL Server.
           sqlBackup.SqlBackup(sqlServer);

           // Remove the backup device from the Backup object.
           sqlBackup.Devices.Remove(deviceItem);

           // Disconnect and dispose the connection.
           sqlServer.ConnectionContext.Disconnect();
      }


      public static void SqlRestore(
           // Sql Server name.
        string serverName,
           // The backup file directory.
        string backupFilePath,
           // The database directory.
        string databaseFolder,
           // The desired restored database name.
        string destinationDatabaseName,
           // The backup filename.
        string backupFilename,
           // The database log name.
        string databaseLogFileName,
           // This will restore the database in partial mode for more info about partial check in the stackoverflow
        bool partial)
      {
           // Current console cursor position.

           //Define a Backup object variable.
           var sqlRestore = new Restore
                              {
                                   Database = destinationDatabaseName,
                                   NoRecovery = false,
                                   ReplaceDatabase = true,

                                   // Specify the type of backup, the description, the name, and the database to be backed up.
                                   Action = RestoreActionType.Database
                              };

           var sqlServer = GetSqlServer(serverName);

           // If the backup directory not found then get the default.
           if (string.IsNullOrEmpty(databaseFolder))
           {
                const string SqlDataDirectory = "DATA";
                databaseFolder = sqlServer.InstallDataDirectory + Path.DirectorySeparatorChar + SqlDataDirectory;
           }

           // Declare a BackupDeviceItem
           var deviceItem = new BackupDeviceItem(
             backupFilePath + Path.DirectorySeparatorChar + backupFilename,
             DeviceType.File);

           const string SqlMdfExtention = ".mdf";
           const string SqlLdfLogExtention = "_log.ldf";

           var dataFileLocation = databaseFolder + Path.DirectorySeparatorChar + destinationDatabaseName + SqlMdfExtention;
           var logFileLocation = databaseFolder + Path.DirectorySeparatorChar + destinationDatabaseName + SqlLdfLogExtention;

           sqlRestore.RelocateFiles.Add(new RelocateFile(destinationDatabaseName, dataFileLocation));
           sqlRestore.RelocateFiles.Add(new RelocateFile(databaseLogFileName, logFileLocation));

           sqlRestore.ReplaceDatabase = true;
           sqlRestore.ContinueAfterError = false;

           sqlRestore.PercentCompleteNotification = 1;
           sqlRestore.PercentComplete += ProgressEventHandler;

           if (partial)
           {
                sqlRestore.Partial = true;
                sqlRestore.ContinueAfterError = true;
           }

           sqlRestore.Complete += ProcessCompleted;

           sqlServer.ConnectionContext.StatementTimeout = 60 * 60;

           // Add the device to the Restore object.
           sqlRestore.Devices.Add(deviceItem);

           // Run SqlRestore to perform the database restore on the instance of SQL Server.
           sqlRestore.SqlRestore(sqlServer);

           var database = sqlServer.Databases[destinationDatabaseName];
           database.SetOnline();

           // Remove the restore device from the restore object.
           sqlRestore.Devices.Remove(deviceItem);

           // Disconnect and dispose the connection.
           sqlServer.ConnectionContext.Disconnect();
      }

      private static Server GetSqlServer(string servername)
      {
           // Define Server connection
           var serverConnection = new ServerConnection(servername);
           var sqlServer = new Server(serverConnection);
           sqlServer.ConnectionContext.StatementTimeout = 60 * 60;
           return sqlServer;
      }

      private static void ProgressEventHandler(object sender, PercentCompleteEventArgs e)
      {
           Console.Write(e.Percent);
      }

      private static void ProcessCompleted(object sender, ServerMessageEventArgs e)
      {
           // Write if any error is exist.
           Console.WriteLine(e.Error.Message);
      }
 }
}
share|improve this answer
    
Thanks very useful info. –  Donia Aug 26 '13 at 20:07
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.