how i can backup all database (sql server 2008) in C# code ?

Question

hi

i have database on computer 1 (sql server 2008)

and i have sql server 2008 on computer 2

i need any sample code in C# that i can backup database on computer 1

to computer 2

thank's in advance

Answer 1

You can execute a query on the DB such as this:

BACKUP DATABASE [CCMData] TO  DISK = N'c:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\CCMData.bak' WITH NOFORMAT, NOINIT,  NAME = N'CCMData-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO

Note you can get the code by going into your SQL Server Management studio, get to the backup menu for the database you want to restore, set it up and just before you click OK, look on the top of the menu and you will see a Script button. Click it, it will generate an SQL script to do the behavior you want.

Answer 2

Use a BACKUP SQL command and use a UNC path as the output directory. About how to do it in C# see one of many tutorials for details.

Answer 3

I know your question is referring to C#, but I'm hoping that I may be reading a bit deeper into your actual question. If you're asking how to replicate data on a regular basis from one SQL server to another, I would strongly recommend using SQL Server Replication. If you do not need to have two way synchronization, then Snapshot Replication will probably work for you.

With Snapshot Replication, "Any changes made to the replication data at the target that may have occurred since the last transmission will be overwritten by a new transmission" (Reference). In other words, if you're looking to just push data from one server to another either on a scheduled basis or on demand, while not accounting for changes made to the subscribing database (destination), then this is probably your most feasible solution.

However, please review all of the options available and choose whichever is best suited for your needs.

Answer 4

I would take advantage of SMO to perform the backups. Below is some sample code that works.

You could add in additional code that then moves the backup files from computer 1 and places them on computer 2. If computer 2 needs the backup data included in the databases and not just backup files I would look into replication. You could also perform a Restore operation through SMO. I hope this helps!

using System;
using System.IO;
using Microsoft.SqlServer.Management.Smo;

namespace BackupDatabases
{
    class Program
    {
        static void Main()
        {

            const string path = @"C:\Backups\";

             if (!Directory.Exists(path))
                Directory.CreateDirectory(path);

            Console.WriteLine("Enter Server name");
            string server = Console.ReadLine();

            Server sqlServer = new Server(server);


            foreach (Database db in sqlServer.Databases)
            {

                Backup bk = new Backup();

                bk.Devices.AddDevice(path, DeviceType.File);
                bk.Action = BackupActionType.Database;
                bk.BackupSetDescription = "Full backup of " + db.Name;
                bk.Database = db.Name;
                bk.Initialize = true;

                Console.WriteLine("Backing up database " + db.Name);

                bk.SqlBackup(sqlServer);

                Console.WriteLine();
                Console.WriteLine("Backup complete for database " + db.Name);

            }

        }

    }
}