How to Backup a Remote SQL Server Database to a Local Drive

How can I backup a remote SQL Server database to a local drive?

In Microsoft SQL Server Management Studio you can right-click on the database you wish to backup and click Tasks -> Generate Scripts.

This pops open a wizard where you can set the following in order to perform a decent backup of your database, even on a remote server:

• Select the database you wish to backup and hit next,
• In the options it presents to you:
  
  
  1. In 2010: under the Table/View Options, change 'Script Data' and 'Script Indexes' to True and hit next,
  2. In 2012: under 'General', change 'Types of data to script' from 'Schema only' to 'Schema and data'
  3. In 2014: the option to script the data is now "hidden" in step "Set Scripting Options", you have to click the "Advanced" and set "Types of data to script" to "Schema and data" value
• In the next four windows, hit 'select all' and then next,
• Choose to script to a new query window

Once it's done its thing, you'll have a backup script ready in front of you. Create a new local (or remote) database, and change the first 'USE' statement in the script to use your new database. Save the script in a safe place, and go ahead and run it against your new empty database. This should create you a (nearly) duplicate local database you can then backup as you like.

If you have full access to the remote database, you can choose to check 'script all objects' in the wizard's first window and then change the 'Script Database' option to True on the next window. Watch out though, you'll need to perform a full search & replace of the database name in the script to a new database which in this case you won't have to create before running the script. This should create a more accurate duplicate but is sometimes not available due to permissions restrictions.

Backup remote SQL Server database to local

You will only be able to backup the database to a location the service account for SQL has access to. If you have access to a central share on the server/network that you can access and the service can, you might backup to that location and then browse from your computer to pull it down.

If you are just wanting the database structure you could script the database out to a file. This would let you save it locally. If you also want the data though doing a full backup is the quickest way I know of.

EDIT

I would use the T-SQL BACKUP comand and include WITH COPY_ONLY to backup the database, since you stated this is a "live" database. If a scheduled job is performing backups against the database and you break in to do an additional one you will effect the backup recovery chain of the database. Using the COPY_ONLY will allow you to get a backup of the database without requiring it in the event of a recovery need.

C# SQL Server backup of a remote database to the remote default backup location without direct access to the remote location?

Thank you @SeanLange

Pretty sure you would need to use dynamic sql in this case. Then the path will relative to where the sql is executed.

I changed my code to add:

 private static void WriteBackupSp (Server remoteServer, string dbName, out string storedProcedure)
      {
           var s = "CREATE PROCEDURE [dbo].[ProactiveDBBackup]\n";
           s += "AS\n";
           s += "BEGIN\n";
           s += "SET NOCOUNT ON\n";
           s += "BACKUP DATABASE " + dbName + " TO DISK = \'" + string.Concat (remoteServer.BackupDirectory,@"\", dbName, ".bak") + "\'\n";
           s += "END\n";
           storedProcedure = s;
      }

then changed the last switch to be:

switch (oldProactiveSql.InstanceName)
           {
                case null:
                     try
                     {
                          WriteBackupSp (srv3, oldProactiveSql.DbName, out var storedProcedure);
                          ConnectionToolsUtility.GenerateSqlConnectionString (oldProactiveSql, out var cs);

                          using (SqlConnection connection = new SqlConnection (cs))
                          {
                               using (SqlCommand command = new SqlCommand (storedProcedure, connection))
                               {
                                    connection.Open ();
                                    command.ExecuteNonQuery ();
                                    connection.Close ();
                               }
                          }
                          var execBackup = "EXEC [dbo].[ProactiveDBBackup]\n";
                          using (SqlConnection connection = new SqlConnection (cs))
                          {
                               using (SqlCommand command = new SqlCommand (execBackup, connection))
                               {
                                    connection.Open ();
                                    command.ExecuteNonQuery ();
                                    connection.Close ();
                               }
                          }
                     }
                     catch (Exception e)
                     {
                          Console.WriteLine(e);
                          Console.WriteLine(e.InnerException.Message);
                          throw;
                     }
                     break;
                default:
                     try { bkpDbFull.SqlBackup(srv2); }
                     catch (Exception e)
                     {
                          Console.WriteLine(e);
                          Console.WriteLine(e.InnerException.Message);
                          throw;
                     }
                     break;
           }

And that allowed me to take a backup of the database through the connection string to the default backup location without having credentials with access to the network path location.

C# SMO backup of remote database to local machine

No, this won't ever work - SQL Server can only ever back up to a drive physically attached to the actual SQL Server machine. You cannot under any circumstances back up a remote SQL Server to your local harddisk - just not possible (neither in SMO, or in SQL Server Management Studio).

How to backup to local drive and network drive simultaneously?

As the documentation shows, MIRROR TO is only available in the Enterprise editions of SQL Server 2005+.

If your end goal is only to have a redundant backup for safekeeping in a different location from the local disk, and they don't have to be the same backup, then you could just do them in sequence in a single batch.

BACKUP DATABASE [MGEC_STAGE] TO DISK = N'XXXX.bak' WITH INIT;
BACKUP DATABASE [MGEC_STAGE] TO DISK = N'Network path \XXX.bak' WITH INIT;

If the backups have to be identical, then you will just have to perform the backup to the local drive and copy it, using any of (among other options)

1. xp_cmdshell and the xcopy/copy command
2. Windows Task Scheduler sqlcmd command line and xcopy/copy, assuming you (i) are performing the backup using a schedule (e.g. SQL Agent) and (ii) know how long it will take, timing the copy to occur soon after
3. SSIS package, along the lines of the Windows Task Scheduler

How to backup MySQL database on a remote server?

You can specify the server name as an option to mysqldump:

mysqldump --host servername dbname > dbname.sql

---

Related Topics