This blog is moved to
http://amalhashim.wordpress.com

Friday, May 22, 2009

Database Backup/Restore using C#

Add a reference of SQLDMO Object



// The application object for getting server list
SQLDMO.Application sqlApp = new SQLDMO.ApplicationClass();
//NameList variable for server name collection
SQLDMO.NameList sqlServers = null;
//get all available SQL Servers
sqlServers = sqlApp.ListAvailableSQLServers();
List servers = new List();
for (int i = 0; i < sqlServers.Count; i++)
{
object srv = sqlServers.Item(i + 1);
if (srv != null)
{
servers.Add(srv);
}
}

/// Connecting
SQLDMO.SQLServer srv = new SQLDMO.SQLServerClass();
srv.Connect(stringServer, userName, password);

/// Getting All databases
List databases = new List();
foreach (SQLDMO.Database db in srv.Databases)
{
if (db.Name != null)
databases.Add(db.Name);
}

///Backup
//create an instance of a server class
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.Connect(stringServer, userName, password);
//create a backup class instance
SQLDMO.Backup bak = new SQLDMO.BackupClass();
//set the backup device = files property ( easy way )
bak.Devices = bak.Files;
//set the files property to the File Name text box
bak.Files = "filepath";
//set the database to the chosen database
bak.Database = "databasetobackup";
//perform the backup
bak.SQLBackup(srv);


///Restore
//create an instance of a server class
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.Connect(stringServer, userName, password);
//create a restore class instance
SQLDMO.Restore res = new SQLDMO.RestoreClass();
//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files = "filepath";
//set the database to the chosen database
res.Database = "database";
// Restore the database
res.ReplaceDatabase = true;
res.SQLRestore(srv);

No comments: