Is it possible to back up a remote bd sql server via C #?

4

I am using the Microsoft.SqlServer.Management.Smo assembly to back up a Sql Server database in C #. The problem is that in the way it is implemented I can only perform the action if I am running the application on the same database server.

Would you like to know if you can remotely backup?

My example is based on this msdn link . Here is the code:

    using Microsoft.SqlServer.Management.Common;
    using Microsoft.SqlServer.Management.Smo;

class A {
   public static void Main() {
      // Connect to the local, default instance of SQL Server. 
      Server srv = new Server();
      // Reference the AdventureWorks2012 database. 
      Database db = default(Database);
      db = srv.Databases["AdventureWorks2012"];

      // Store the current recovery model in a variable. 
      int recoverymod;
      recoverymod = (int)db.DatabaseOptions.RecoveryModel;

      // Define a Backup object variable. 
      Backup bk = new Backup();

      // Specify the type of backup, the description, the name, and the database to be backed up. 
      bk.Action = BackupActionType.Database;
      bk.BackupSetDescription = "Full backup of Adventureworks2012";
      bk.BackupSetName = "AdventureWorks2012 Backup";
      bk.Database = "AdventureWorks2012";

      // Declare a BackupDeviceItem by supplying the backup device file name in the constructor, and the type of device is a file. 
      BackupDeviceItem bdi = default(BackupDeviceItem);
      bdi = new BackupDeviceItem("Test_Full_Backup1", DeviceType.File);

      // Add the device to the Backup object. 
      bk.Devices.Add(bdi);
      // Set the Incremental property to False to specify that this is a full database backup. 
      bk.Incremental = false;

      // Set the expiration date. 
      System.DateTime backupdate = new System.DateTime();
      backupdate = new System.DateTime(2006, 10, 5);
      bk.ExpirationDate = backupdate;

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

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

      // Inform the user that the backup has been completed. 
      System.Console.WriteLine("Full Backup complete.");

      // Remove the backup device from the Backup object. 
      bk.Devices.Remove(bdi);

      // Make a change to the database, in this case, add a table called test_table. 
      Table t = default(Table);
      t = new Table(db, "test_table");
      Column c = default(Column);
      c = new Column(t, "col", DataType.Int);
      t.Columns.Add(c);
      t.Create();

      // Create another file device for the differential backup and add the Backup object. 
      BackupDeviceItem bdid = default(BackupDeviceItem);
      bdid = new BackupDeviceItem("Test_Differential_Backup1", DeviceType.File);

      // Add the device to the Backup object. 
      bk.Devices.Add(bdid);

      // Set the Incremental property to True for a differential backup. 
      bk.Incremental = true;

      // Run SqlBackup to perform the incremental database backup on the instance of SQL Server. 
      bk.SqlBackup(srv);

      // Inform the user that the differential backup is complete. 
      System.Console.WriteLine("Differential Backup complete.");

      // Remove the device from the Backup object. 
      bk.Devices.Remove(bdid);

      // Delete the AdventureWorks2012 database before restoring it
      // db.Drop();

      // Define a Restore object variable.
      Restore rs = new Restore();

      // Set the NoRecovery property to true, so the transactions are not recovered. 
      rs.NoRecovery = true;

      // Add the device that contains the full database backup to the Restore object. 
      rs.Devices.Add(bdi);

      // Specify the database name. 
      rs.Database = "AdventureWorks2012";

      // Restore the full database backup with no recovery. 
      rs.SqlRestore(srv);

      // Inform the user that the Full Database Restore is complete. 
      Console.WriteLine("Full Database Restore complete.");

      // reacquire a reference to the database
      db = srv.Databases["AdventureWorks2012"];

      // Remove the device from the Restore object.
      rs.Devices.Remove(bdi);

      // Set the NoRecovery property to False. 
      rs.NoRecovery = false;

      // Add the device that contains the differential backup to the Restore object. 
      rs.Devices.Add(bdid);

      // Restore the differential database backup with recovery. 
      rs.SqlRestore(srv);

      // Inform the user that the differential database restore is complete. 
      System.Console.WriteLine("Differential Database Restore complete.");

      // Remove the device. 
      rs.Devices.Remove(bdid);

      // Set the database recovery mode back to its original value.
      db.RecoveryModel = (RecoveryModel)recoverymod;

      // Drop the table that was added. 
      db.Tables["test_table"].Drop();
      db.Alter();

      // Remove the backup files from the hard disk.
      // This location is dependent on the installation of SQL Server
      System.IO.File.Delete("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Test_Full_Backup1");
      System.IO.File.Delete("C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\Test_Differential_Backup1");
   }
}
    
asked by anonymous 14.04.2015 / 14:41

3 answers

1

Thanks to @HStackOverflow and @ Marciano.Andrade for the workarounds, however I was able to resolve with the code I had posted.

The only part that I changed was in the Server instance having added a connection. Here's how it went.

String connectionString = "Data Source=127.0.0.0;Initial Catalog=minhaBase;User Id=sa;Password=12344567;";
SqlConnection sqlConnection = new SqlConnection(connectionString);
ServerConnection conn = new ServerConnection(sqlConnection);
// Connect to the local, default instance of SQL Server. 
Server srv = new Server(conn);
    
17.04.2015 / 15:44
5

If you want to back up a database on MS-Sql Server remotely so that backup files are created on your computer, I'm sorry to inform you that this is not possible. You can perform a remote backup by script but the files will be created only locally (in the database location, that is, on the server).

There are other ways to do this by creating scripts that reflect the database you are trying to back up, follow the SOEn link on the same issue: How can I backup a remote SQL Server database to a local drive? / p>

@Edit: To back up a SQL database remotely via C # you can connect to the base and run a script containing the backup routine you need. One way to generate this script is to do the backup manually, but before clicking OK on the backup screen, click the "Script" button in the top bar of the backup window. This will open a script with all the syntax needed to perform the backup as you want. To illustrate, here's an example I use:

C #:

public static void FazerBackup(string dataBase, string diretorio, string nomeAquivo)
{
    string connectionString = WebConfigurationManager.ConnectionStrings["ConnectionString"].ConnectionString;
    using (SqlConnection conexao = new SqlConnection(connectionString))
    using (SqlCommand comando = new SqlCommand("RealizaBackup", conexao))
    {
        comando.Parameters.AddWithValue("@DATABASE", dataBase);
        comando.Parameters.AddWithValue("@DIRETORIO", diretorio);
        comando.Parameters.AddWithValue("@NOMEARQUIVO", nomeAquivo);
        if (conexao.State != ConnectionState.Open)
            conexao.Open();
        comando.ExecuteNonQuery();
    }
}

Procedure "RealizesBackup":

CREATE PROCEDURE [dbo].[RealizaBackup]
    @database           VARCHAR(MAX) = NULL,
    @diretorio          VARCHAR(MAX) = NULL,
    @nomeArquivo        VARCHAR(MAX) = NULL
AS
BEGIN
    DECLARE  @sqlCommand NVARCHAR(MAX)
    IF RIGHT(@diretorio,1) != '\'
      SET @diretorio = @diretorio + '\'
    SELECT @sqlCommand = 'BACKUP DATABASE ' + @database + ' TO DISK = ' +
        CHAR(39) + @diretorio + @nomeArquivo + CHAR(39) + 'WITH NOFORMAT, NOINIT, NAME = N' + 
        CHAR(39) + @database + '-FULL DATABASE BACKUP' + CHAR(39) + ', SKIP, NOREWIND, NOUNLOAD, STATS = 10'
    PRINT @SQLCOMMAND
    EXEC SP_EXECUTESQL @sqlCommand 
END
    
14.04.2015 / 15:11
3

using System;
using System.Data.SqlClient;

namespace teste
{
    class Program
    {
        private const string Sqlconnection = @"Server=192.168.0.1;DataBase=master;user id=sa;password=xxx;Connection Timeout=0";

        static void Main(string[] args)
        {
            BackupFullDIff();
        }

        public static void BackupFullDIff()
        {
            var localPastaCopia = @"c:\copia";
            var sqlConn = new SqlConnection(Sqlconnection);
            sqlConn.Open();

            //BACKUP FULL
            var comandoSql =
                string.Format("BACKUP DATABASE NOMEBD TO DISK='{0}' WITH  INIT ,  " +
                              "NOUNLOAD ,  NAME = N'NOMEBD backup',  " +
                              "NOSKIP ,  STATS = 10,  NOFORMAT ", localPastaCopia + @"\minhaCopiaFull.BAK");
            var sqlComm = new SqlCommand { Connection = sqlConn, CommandText = comandoSql, CommandTimeout = 0 };
            sqlComm.ExecuteNonQuery();

            //BAKUP DIFERENCIAL
            comandoSql =
            string.Format("BACKUP DATABASE NOMEBD TO DISK='{0}' WITH  INIT ,  " +
                      "NOUNLOAD ,  DIFFERENTIAL ,  NAME = N'NOMEBD backup',  " +
                      "NOSKIP ,  STATS = 10,  NOFORMAT ", localPastaCopia + @"\minhaCopiaDIFF.BAK");
            sqlComm = new SqlCommand { Connection = sqlConn, CommandText = comandoSql, CommandTimeout = 0 };
            sqlComm.ExecuteNonQuery();

            var localPastaRestauraBd = @"c:\dados_restaura";
            //RESTORE FULL
            comandoSql =
                String.Format("RESTORE DATABASE {0} " +
                "FROM DISK='{1}' " +
                "WITH RECOVERY, " +
                "MOVE 'NOMEBD_data' TO '{2}\NOMEBD_Data.MDF', " +
                "MOVE 'NOMEBD_log'  TO '{2}\NOMEBD_Log.LDF'", "NOMEBD", localPastaCopia + @"\minhaCopiaFull.BAK", localPastaRestauraBd);
            sqlComm = new SqlCommand { Connection = sqlConn, CommandText = comandoSql, CommandTimeout = 0 };
            sqlComm.ExecuteNonQuery();

            //RESTORE FULL e DIFF
            /*
            comandoSql =
                String.Format("RESTORE DATABASE {0} " +
                "FROM DISK='{1}' " +
                "WITH NORECOVERY, " +
                "MOVE 'NOMEBD_data' TO '{2}\NOMEBD_Data.MDF', " +
                "MOVE 'NOMEBD_log'  TO '{2}\NOMEBD_Log.LDF'", "NOMEBD", localPastaCopia + @"\minhaCopiaFull.BAK", localPastaRestauraBd);
            sqlComm = new SqlCommand { Connection = sqlConn, CommandText = comandoSql, CommandTimeout = 0 };
            sqlComm.ExecuteNonQuery();

            comandoSql =
            String.Format("RESTORE DATABASE {0} " +
            "FROM DISK='{1}' " +
            "WITH RECOVERY, " +
            "MOVE 'NOMEBD_data' TO '{2}\NOMEBD_Data.MDF', " +
            "MOVE 'NOMEBD_log'  TO '{2}\NOMEBD_Log.LDF'", "NOMEBD", localPastaCopia + @"\minhaCopiaDiff.BAK", localPastaRestauraBd);
            sqlComm = new SqlCommand { Connection = sqlConn, CommandText = comandoSql, CommandTimeout = 0 };
            sqlComm.ExecuteNonQuery();
            */

        }
    }
}
    
14.04.2015 / 21:13