Click here to Skip to main content
Click here to Skip to main content

SqlServer Backup/Restore Utility

, 9 Jun 2009 CPOL
Rate this:
Please Sign up or sign in to vote.
SqlServer Backup/Restore Utility helps to backup and restore database
BackupRestore_Source

Introduction 

Sometimes you need to migrate the database from the server where Management Studio is not installed (for example, when you use SQL Server Express Edition). This utility helps you to instantly backup, then restore to database server. 

Using the Application

Before running an application, edit connectionStrings section in BackupRestore.exe.config file to connect your database.  

Using the Code

At first, you need to get a list of available databases using the default connection string. The list of databases is bound to two combo boxes used for selecting database to backup or restore.

sqlConn = new SqlConnection(Properties.Settings.Default.masterConnectionString);
sqlServer = new Server(new ServerConnection(sqlConn));

dbList = new List<database>();
foreach (Database db in sqlServer.Databases)
{
        dbList.Add(db);
}

cmbBackupDb.DataSource = dbList;
cmbRestoreDb.DataSource = dbList;

Database Backup

back_rest_1.png

Database backup process is performed using the Microsoft.SqlServer.Management.Smo.Backup class. When user selects database and file to save the backup to, the following method will be executed: 

private void BackupDb()
{
    dbName = ((Database)cmbBackupDb.SelectedItem).Name;
    Backup dbBackup = new Backup();

    try
    {
        dbBackup.Action = BackupActionType.Database;
        dbBackup.Database = dbName;
        dbBackup.BackupSetName = string.Format("{0} backup set.", dbName);
        dbBackup.BackupSetDescription = string.Format("Database: {0}. Date: {1}.", 
			dbName, DateTime.Now.ToString("dd.MM.yyyy hh:m"));
        dbBackup.MediaDescription = "Disk";

        BackupDeviceItem device = new BackupDeviceItem
			(saveBakFile.FileName, DeviceType.File);
        dbBackup.Devices.Add(device);

        txtBackupSql.Text = dbBackup.Script(sqlServer);

        progBar.Visible = true;
        progBar.Value = 0;

        dbBackup.Complete += new ServerMessageEventHandler(dbBackup_Complete);
        dbBackup.PercentCompleteNotification = 10;
        dbBackup.PercentComplete += 
		new PercentCompleteEventHandler(PercentComplete);

        dbBackup.SqlBackup(sqlServer);
    }
    catch (Exception exc)
    {
        dbBackup.Abort();
        MessageBox.Show(string.Format
		("Exception occurred.\nMessage: {0}", exc.Message));
    }
    finally
    {
        sqlConn.Close();
    }
}

Database Restore

back_rest_2.png

Database restore process is performed using the Microsoft.SqlServer.Management.Smo.Restore class. When user selects database and the file to restore from, the following method will be processed:

private void RestoreDb()
{
    Database restoreDb = (Database)cmbRestoreDb.SelectedItem;
    dbName = restoreDb.Name;

    Restore dbRestore = new Restore();
    dbRestore.Database = restoreDb.Name;
    dbRestore.Action = RestoreActionType.Database;
    dbRestore.ReplaceDatabase = true;
       
    string fileLocation = ConfigurationManager.AppSettings["SqlFileLocations"];

    try
    {
        BackupDeviceItem device = new BackupDeviceItem
			(openBakFile.FileName, DeviceType.File);
        dbRestore.Devices.Add(device);
        DataTable dtFiles = dbRestore.ReadFileList(sqlServer);
        string backupDbLogicalName = dtFiles.Rows[0]["LogicalName"].ToString();

        RelocateFile dbRf = new RelocateFile
	(backupDbLogicalName, string.Format("{0}\\{1}.mdf", fileLocation, dbName));
        RelocateFile logRf = new RelocateFile(string.Format("{0}_log", 
	backupDbLogicalName), string.Format("{0}\\{1}_Log.ldf", 
	fileLocation, dbName));
        dbRestore.RelocateFiles.Add(dbRf);
        dbRestore.RelocateFiles.Add(logRf);

        string sql = string.Empty;
        StringCollection scriptColl = dbRestore.Script(sqlServer);
        foreach (string str in scriptColl)
        {
            sql += str;
        }
        txtBackupSql.Text = sql;

        progBar.Visible = true;
        progBar.Value = 0;

        dbRestore.Complete += new ServerMessageEventHandler(dbRestore_Complete);
        dbRestore.PercentComplete += 
		new PercentCompleteEventHandler(PercentComplete);
        dbRestore.SqlRestore(sqlServer);
    }
    catch (Exception exc)
    {
        dbRestore.Abort();
        MessageBox.Show(string.Format
		("Exception occurred.\nMessage: {0}", exc.Message));
    }
    finally
    {
        sqlConn.Close();
    }
}

History

  • 9th June, 2009: Initial post

License

This article, along with any associated source code and files, is licensed under The Code Project Open License (CPOL)

Share

About the Author

alioglu

Azerbaijan Azerbaijan
No Biography provided

Comments and Discussions

 
GeneralRestore Utility is not working Pinmemberabhishektiwary198716-Aug-13 0:16 
QuestionThanks for this artical Pinprofessionalashumeerut3-Aug-13 23:33 
Answersoln for sql server 2008 r2 not supporting Pinprofessionalashumeerut3-Aug-13 23:32 
Questionproblem with sqlserver 2008r2 PinmemberMember 101868333-Aug-13 21:33 
Questionrestore not work Pinmembersoheaila25-Feb-13 8:49 
AnswerRe: restore not work Pinmemberalioglu26-Feb-13 20:13 
GeneralRe: restore not work Pinmembersoheaila26-Feb-13 21:54 
GeneralRe: restore not work Pinmemberalioglu26-Feb-13 22:58 
GeneralRe: restore not work Pinmembersoheaila26-Feb-13 23:59 
Questionhb Pinmemberbirjandi15-Feb-13 4:31 
Questionvary good Pinmemberdariush_hk17-Dec-12 9:30 
GeneralMy vote of 5 PinmvpKanasz Robert24-Sep-12 7:16 
Questionhow to Underline for a Asp button text hot key or short cut key... Pinmembersujitchopade@ymail.com9-Apr-12 23:03 
Questionwhich version of sql server can this code take the back up? PinmemberMember 808325814-Jul-11 6:26 
Generalhandling exception when perform backup Pinmemberrohalah12-Oct-10 14:11 
QuestionHow to get Backup &amp; Restore in Client Machine? PinmemberSivaooty4-Feb-10 23:25 
GeneralWhy did the progress don't working when i backup Pinmemberlxg_831117-Nov-09 22:37 
Questiondon't want to show system databases? Pinmemberwael32gh4-Oct-09 20:49 
AnswerRe: don't want to show system databases? Pinmemberalioglu5-Oct-09 1:53 
GeneralRe: don't want to show system databases? PinmemberLeleHalfon11-Nov-09 4:54 
NewsExclusive access could not be obtained because the database is in use Pinmemberwael32gh4-Oct-09 7:30 
GeneralSMO objects always bugs me PinmemberAbhishek Sur9-Jun-09 12:01 
GeneralSimple and to the point Pinmemberameetj859-Jun-09 11:48 

General General    News News    Suggestion Suggestion    Question Question    Bug Bug    Answer Answer    Joke Joke    Rant Rant    Admin Admin   

Use Ctrl+Left/Right to switch messages, Ctrl+Up/Down to switch threads, Ctrl+Shift+Left/Right to switch pages.

| Advertise | Privacy | Terms of Use | Mobile
Web04 | 2.8.1411023.1 | Last Updated 9 Jun 2009
Article Copyright 2009 by alioglu
Everything else Copyright © CodeProject, 1999-2014
Layout: fixed | fluid