C# .NET - restore database

Introduction

The following article describes accessing a SQL Server 2005 database backup and restoring it programmatically using C#.NET 2.0 and SMO. This article provides coding samples to perform the task.

SQL Server Management Objects (SMO) is a collection of objects that are designed for programming all aspects of managing Microsoft SQL Server.

The following namespaces can be used to access SQL Server 2005 programmatically:

• Microsoft.SqlServer.management
• Microsoft.SqlServer.Management.NotificationServices
• Microsoft.SqlServer.Management.Smo
• Microsoft.SqlServer.Management.Smo.Agent
• Microsoft.SqlServer.Management.Smo.Broker
• Microsoft.SqlServer.Management.Smo.Mail
• Microsoft.SqlServer.Management.Smo.RegisteredServers
• Microsoft.SqlServer.Management.Smo.Wmi
• Microsoft.SqlServer.Management.Trace

Pre-Requisite

You need to reference the following namespaces before using this code:

• Microsoft.SqlServer.Management.Smo;
• Microsoft.SqlServer.Management.Common;

I used these two class to perform the backup and restore operations:

• Microsoft.SqlServer.Management.Smo.Backup
• Microsoft.SqlServer.Management.Smo.Restore

For more information, regarding these two class, check MSDN:

• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.backup.aspx
• http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.restore.aspx

Backup database

 Collapse | http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx#

public void BackupDatabase(String databaseName, String userName, 
            String password, String serverName, String destinationPath)
{
    Backup sqlBackup = new Backup();
    
    sqlBackup.Action = BackupActionType.Database;
    sqlBackup.BackupSetDescription = "ArchiveDataBase:" + 
                                     DateTime.Now.ToShortDateString();
    sqlBackup.BackupSetName = "Archive";

    sqlBackup.Database = databaseName;

    BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath, DeviceType.File);
    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);
    
    Database db = sqlServer.Databases[databaseName];
    
    sqlBackup.Initialize = true;
    sqlBackup.Checksum = true;
    sqlBackup.ContinueAfterError = true;
    
    sqlBackup.Devices.Add(deviceItem);
    sqlBackup.Incremental = false;

    sqlBackup.ExpirationDate = DateTime.Now.AddDays(3);
    sqlBackup.LogTruncation = BackupTruncateLogType.Truncate;

    sqlBackup.FormatMedia = false;

    sqlBackup.SqlBackup(sqlServer);
}

Restore Database

 Collapse | http://www.codeproject.com/KB/database/SQL_Server_2005_Database.aspx#

public void RestoreDatabase(String databaseName, String filePath, 
       String serverName, String userName, String password, 
       String dataFilePath, String logFilePath)
{
    Restore sqlRestore = new Restore();
    
    BackupDeviceItem deviceItem = new BackupDeviceItem(filePath, DeviceType.File);
    sqlRestore.Devices.Add(deviceItem);
    sqlRestore.Database = databaseName;

    ServerConnection connection = new ServerConnection(serverName, userName, password);
    Server sqlServer = new Server(connection);

    Database db = sqlServer.Databases[databaseName];
    sqlRestore.Action = RestoreActionType.Database;
    String dataFileLocation = dataFilePath + databaseName + ".mdf";
    String logFileLocation = logFilePath + databaseName + "_Log.ldf";
    db = sqlServer.Databases[databaseName];
    RelocateFile rf = new RelocateFile(databaseName, dataFileLocation);
    
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName, dataFileLocation));
    sqlRestore.RelocateFiles.Add(new RelocateFile(databaseName+"_log", logFileLocation));
    sqlRestore.ReplaceDatabase = true;
    sqlRestore.Complete += new ServerMessageEventHandler(sqlRestore_Complete);
    sqlRestore.PercentCompleteNotification = 10;
    sqlRestore.PercentComplete += 
       new PercentCompleteEventHandler(sqlRestore_PercentComplete);
            
    sqlRestore.SqlRestore(sqlServer);
    db = sqlServer.Databases[databaseName];
    db.SetOnline();
    sqlServer.Refresh();
}

The portion of code uses full backup features. If you want, you can perform incremental and differential backup as well.

plz visit these links

http://www.codeproject.com/KB/database/mft_jet_restore.aspx

http://midnightprogrammer.net/post/BackupRestore-SQL-database-using-C.aspx

Here’s the simplest way to do it

• Build the application
• Copy the executable (in the Bin directory) and the app.config file to the other machine. The executable and app.config need to be in the same folder
• Backup the database on your machine and restore it on the other machine
• Change app.config to point to the restored database