Click here to Skip to main content
11,803,019 members (73,074 online)
Click here to Skip to main content

SqlServer Backup/Restore Utility

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


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)

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

Database Backup


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();

        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);

        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);

    catch (Exception exc)
		("Exception occurred.\nMessage: {0}", exc.Message));

Database Restore


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"];

        BackupDeviceItem device = new BackupDeviceItem
			(openBakFile.FileName, DeviceType.File);
        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));

        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);
    catch (Exception exc)
		("Exception occurred.\nMessage: {0}", exc.Message));


  • 9th June, 2009: Initial post


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


About the Author

Azerbaijan Azerbaijan
No Biography provided

You may also be interested in...

Comments and Discussions

GeneralRestore Utility is not working Pin
abhishektiwary198715-Aug-13 23:16
memberabhishektiwary198715-Aug-13 23:16 
QuestionThanks for this artical Pin
ashumeerut3-Aug-13 22:33
professionalashumeerut3-Aug-13 22:33 
Answersoln for sql server 2008 r2 not supporting Pin
ashumeerut3-Aug-13 22:32
professionalashumeerut3-Aug-13 22:32 
Questionproblem with sqlserver 2008r2 Pin
Member 101868333-Aug-13 20:33
memberMember 101868333-Aug-13 20:33 
Questionrestore not work Pin
soheaila25-Feb-13 7:49
membersoheaila25-Feb-13 7:49 
AnswerRe: restore not work Pin
alioglu26-Feb-13 19:13
memberalioglu26-Feb-13 19:13 
GeneralRe: restore not work Pin
soheaila26-Feb-13 20:54
membersoheaila26-Feb-13 20:54 
GeneralRe: restore not work Pin
alioglu26-Feb-13 21:58
memberalioglu26-Feb-13 21:58 
GeneralRe: restore not work Pin
soheaila26-Feb-13 22:59
membersoheaila26-Feb-13 22:59 
Questionhb Pin
birjandi15-Feb-13 3:31
memberbirjandi15-Feb-13 3:31 
thank you
Questionvary good Pin
dariush_hk17-Dec-12 8:30
memberdariush_hk17-Dec-12 8:30 
GeneralMy vote of 5 Pin
Kanasz Robert24-Sep-12 6:16
mvpKanasz Robert24-Sep-12 6:16 
Questionhow to Underline for a Asp button text hot key or short cut key... Pin
sujitchopade@ymail.com9-Apr-12 22:03
membersujitchopade@ymail.com9-Apr-12 22:03 
Questionwhich version of sql server can this code take the back up? Pin
Member 808325814-Jul-11 5:26
memberMember 808325814-Jul-11 5:26 
Generalhandling exception when perform backup Pin
rohalah12-Oct-10 13:11
memberrohalah12-Oct-10 13:11 
QuestionHow to get Backup &amp; Restore in Client Machine? Pin
Sivaooty4-Feb-10 22:25
memberSivaooty4-Feb-10 22:25 
GeneralWhy did the progress don't working when i backup Pin
lxg_831117-Nov-09 21:37
memberlxg_831117-Nov-09 21:37 
Questiondon't want to show system databases? Pin
wael32gh4-Oct-09 19:49
memberwael32gh4-Oct-09 19:49 
AnswerRe: don't want to show system databases? Pin
alioglu5-Oct-09 0:53
memberalioglu5-Oct-09 0:53 
GeneralRe: don't want to show system databases? Pin
LeleHalfon11-Nov-09 3:54
memberLeleHalfon11-Nov-09 3:54 
NewsExclusive access could not be obtained because the database is in use Pin
wael32gh4-Oct-09 6:30
memberwael32gh4-Oct-09 6:30 
GeneralSMO objects always bugs me Pin
Abhishek Sur9-Jun-09 11:01
memberAbhishek Sur9-Jun-09 11:01 
GeneralSimple and to the point Pin
ameetj859-Jun-09 10:48
memberameetj859-Jun-09 10: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.151002.1 | Last Updated 9 Jun 2009
Article Copyright 2009 by alioglu
Everything else Copyright © CodeProject, 1999-2015
Layout: fixed | fluid