Backup/Restore SQL database using C#

21. June 2009 15:59

C# SQL Server Utils  57 Comments

Microsoft’s .NET framework provides the flexibility to Backup and Restore SQL server database. A few lines to populate the names of all the server instances, create backups and then restore the backups. The application what you see here is a part of an application, so when you create backups and restore it will use the same path hardcoded in the code itself. You can change the way you like it.

Namespace you need to add:  

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

I am not going to write on and on and just make it going boring, so it's better just download the code and check the comments, they are one liners, but clears everything. 

Download Full Application Here: (290.99 kb)

Currently rated 3.8 by 17 people

Comments (57)

paras patel paras patel
9/10/2011 3:35:02 PM #

using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Common; using System.IO; how can i insert this three line in my c# project and give this namespace for the sql server plz give me help

prashant prashant
9/10/2011 4:04:09 PM #

You need to manually add the reference in your project. Right-Click [b]References[/b] in Solution Explorer and under .NET tab add the above 2 namespaces.

paras patel paras patel
9/10/2011 4:09:29 PM #

thank you sir give me reply bet I cant get it namespace in my visual studio 2005 how can I insert and give specific name of .net name space thankyou

prashant prashant
9/10/2011 4:23:49 PM #

Read here:

prashant prashant
9/10/2011 4:24:56 PM #

You need to add Microsoft.SqlServer.ConnectionInfo namespace from the .NET tab in the Add Reference dialog box.

paras patel paras patel
9/10/2011 4:33:52 PM #

this name space is not my .net references so how can I possible this

prashant prashant
9/10/2011 5:02:34 PM #

Have you got SQL server installed on your machine? If you still can't find the namespace, then check the above link in the comments from stackoverflow and download the MSI (installer) for that.

paras patel paras patel
9/10/2011 5:50:00 PM #

I get on file using Microsoft.SqlServer.Management.Common; but second file I cant get it and not download so plz give me where I will find It using Microsoft.SqlServer.Management.Smo; this file is not get

mohamed shareef.p mohamed shareef.p
11/4/2011 1:43:11 AM #

thank you...........!!!!!!!

Nguy?n Duy Nhân Nguy?n Duy Nhân
11/10/2011 3:18:42 AM #

I am doing an assignment on Backup + Restore database with Stored Procedures. Backup is ok, but Restore the error: “RESTORE cannot process database ‘db_test’ because it is in use by this session. It is recommended that the master database be used when performing this operation.”. I tried to fix but the document has not been, hope admin help with this problem. Thanks! Link source: I use C # VS2010 + sql server 2005.

Prashant Prashant
11/10/2011 10:10:03 AM #

You are using your own custom Stored Procedures to take the backup....and this is why you are using the same database you to backup and restore. Let's say this: Open a file and then try deleting it. It won't let you do that and prompt with the error message that the file is in use. Here you have to change the database to Master database and then use the in-built T-SQL function to restore or backup the database. Read gere more about using these

robin robin
1/2/2012 11:33:15 AM #

It doesnot show the names of all the server instances in windows 7,can you help me

62071072 62071072
1/27/2012 5:45:51 PM #

Hi I am doing backup of selected tables for selected database and selected server . Now i need to backup stored procedures as well for few databases . Please help how can i do this ?

Prashant Prashant
1/27/2012 10:06:40 PM #

If you taking the backup of the complete database then there is no need to backup the stored procedure separately. The above code/app will take the complete backup of the DB and not of the DB entities.

62071072 62071072
1/30/2012 3:07:29 PM #

I am not doing the complete database backup . Let me say how my interface looks .. Form contains servername where user needs to select the server when user selects server then list of dtabases will be populated for corresponding server and when user selects database then list of tables will be populated for corresponding database and user selects the tables need to backup and when he does backup the backup is done in textfile for selected tables with options tablestructure,data and table structure or only data . Now i want to provide another option if user selects storedprocedure then it should display all the stroed procedures for corresponding database and when select backup it should do backup in a textfile of all the stored procedures.

Mihai Moisei Mihai Moisei
2/6/2012 8:57:41 PM #

Hi, On your project I've added the necesary refereneces but I have an error on this line : Backup bkpDatabase = new Backup(); // the type or namespace Backup could not be found. I'm trying to make a WPF application that makes backups. Could you guide me with what I have to do to make a backup ? thank you

Prashant Prashant
2/7/2012 10:55:08 AM #

I doubt whether the assemblies I have used in the example will work with WPF. I will check and revert.

ajay dhole ajay dhole
2/8/2012 12:32:58 PM #

if database is in use then it gives me an error restore failed for 'server-name'.

arisha arisha
2/17/2012 12:10:57 PM #

i could not find backup, BackupDeviceItem and restore classes in your example can you plz help me

arisha arisha
2/17/2012 12:53:09 PM #

plz reply

arisha arisha
2/17/2012 4:42:41 PM #

im also facing same error while restoring database

Prashant Prashant
2/17/2012 9:40:34 PM #

@ajay: if database is in use you cannot restore of the database (using C# code). @arisha: I can't recall what the code I used to place the database backup. I believe it is the same working directory where the application is running.

arisha arisha
2/18/2012 3:49:42 AM #

ohhh i got backup() and restore() classes by adding an assembly reference thanks for your guidance....... still when i try to restore database it give error because when database is in use it don't restore but when database is deattach it don't come in available databases list then how we will restore it?

Prashant Prashant
2/18/2012 4:05:51 PM #

I haven't tried this...but I think that you cannot get the database backup until it is being used by some other process. If you detach it from the SQL Server then you will not be able to get the list of all the databases.

ethiraj ethiraj
3/16/2012 12:22:22 PM #

Hi , i need to take backup from attached sql server db in c# windows app,so any tell me how to done this one

Prashant Prashant
3/18/2012 11:54:38 AM #

What do you mean by attached? When we are taking the backup of the database using C#, the database is already attached to the SQL Server. Does the above application throws some error? Or you want to do something different? Let me know

sorterevolver sorterevolver
3/19/2012 4:25:37 PM #

hi Prashant; I have several question.Some of them can be easy but forgive me i couldn't find the questions in my country programmer blogers and I think I can find here. I made a program.I used visual studio 2010 to make a program with sql server 2008. 1.Now I complete my program and I want to use it in another computer .Can I use my program which has sql database in a computer even don't have sql server 2005. I think I need some program to setup at the opposite computer, but I am not sure. 2.In sql server 2008 I use management/Maintenance Plans to make a backup automatically for everyday. Now I checked it and it is working correctly. Bit if I set this program to another computer may it work without sql server 2008? That's my first comment to a foreign website,if I made an apologies sorry for that.Thanks

Prashant Prashant
3/24/2012 12:12:23 PM #

Hi sorterevolver, 1. The program will only work on the computers where you have SQL Server 2005/2008 installed. The dependencies used in the application comes up with SQL Server. Also if you have DB on different physical location but is attached to the SQL Server only then you will be able to take the DB backup. For an independent DB file, I doubt it will work. 2. We generally use SQL Agent jobs to take DB backup on timely basis. Again I suspect it will work without SQL Server. For the second question I recommend you to ask this guy here:

Hossein Hossein
3/30/2012 11:32:33 PM #

Hi Thanks for your code but i can't use it.It throw an Error when the program want to run this part ::: //bkpDatabase.SqlBackup(srvr); bkpDatabase.SqlBackup(srvr); please Help me. thanks

Prashant Prashant
3/31/2012 1:54:43 PM #

Can you please share the error with me so I can know where the problem is?

marjan marjan
4/2/2012 3:02:41 PM #

hi i have a database that has made in visualstudio2010 from adding new item to windowsform has windows authentication. now when i run your program it shows the server name in first combobox but it doesnt show anything in database combobox.and when i click update it shows can i fix this problem? thanks.

Prashant Prashant
4/5/2012 9:19:29 PM #

If you have added a Db in the application then I doubt that the DB will get populated in the combobox. To get it populated you need to attach the same DB to SQL Server.

Bhaskar Siddapuram Bhaskar Siddapuram
4/20/2012 5:52:09 PM #

// here is the code it works perfectly..hope same for you. using Microsoft.SqlServer.Management; using Microsoft.SqlServer.Management.Nmo; using Microsoft.SqlServer.Management.Smo; using Microsoft.SqlServer.Management.Smo.Agent; using Microsoft.SqlServer.Management.Smo.Broker; using Microsoft.SqlServer.Management.Smo.Mail; using Microsoft.SqlServer.Management.Smo.RegisteredServers; using Microsoft.SqlServer.Management.Smo.Wmi; using Microsoft.SqlServer.Management.Trace; using Microsoft.SqlServer.Management.Common; //if you add 2 dlls smi and common from C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies // here you can find dll's , add them to your solution bin folder. Backup sqlBackup = new Backup(); sqlBackup.Action = BackupActionType.Database; sqlBackup.BackupSetDescription = "ArchiveDataBase:" + DateTime.Now.ToShortDateString(); sqlBackup.BackupSetName = "School_" + DateTime.Now.ToShortDateString(); sqlBackup.Database = databaseName; BackupDeviceItem deviceItem = new BackupDeviceItem(destinationPath + "School_" + DateTime.Today.Day + "_" + DateTime.Today.Month + "_" + DateTime.Today.Year + "_" + DateTime.Now.Hour + "_" + DateTime.Now.Minute + "_" + DateTime.Now.Second + ".bak", 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);

Prashant Prashant
4/20/2012 6:42:57 PM #

Thanks for the share Bhaskar :)

Satish Satish
5/29/2012 6:01:55 PM #

Then Forget It

6/10/2012 12:46:10 AM #

hello Please help me how to backup from sql express in wpf with c#? Thanks :)

Prashant Prashant
6/10/2012 9:39:00 PM #

To take backup of the SQL Express DB, the process is the same as shown in the post. if you are facing any problem then let me know. Thanks

vinhhc vinhhc
7/18/2012 7:52:17 AM #

I run your project But Form load Server name (combobox) nothing! I have installed Sql Server 2005 it is SQL Server Management Studio Express 2005 for Windows XP 32 bit I to default Windows Authentication This solves the problem How do? Thanks

Prashant Prashant
7/18/2012 10:40:46 AM #

I built this on XP 32-bit and SQL 2005 only and was working fine. Did you get any exception? Use try catch block and debug the code and see if there is any problem.

vinhhc vinhhc
7/19/2012 3:49:28 PM #

I built it no error ,no warning! I don't know how to solves it The problem here is what? Is it because I have installed the Sql Server 2005 in Visual Studio. NET 2008 Thanks!

egon egon
8/3/2012 7:51:07 AM #

Hello Prashant.i'm run your project but form loading this error Error 7 The type 'Microsoft.SqlServer.Management.Sdk.Sfc.IAlienObject' is defined in an assembly that is not referenced. You must add a reference to assembly 'Microsoft.SqlServer.Management.Sdk.Sfc, Version=, Culture=neutral, PublicKeyToken=89845dcd8080cc91'. G:\down0712\DBBackup\DBBackup\DBBackup\DBBackup.cs 17 31 DBBackup i use SQL Server 2008 R2 and Visual Studio 2010. Thanks.

Prashant Prashant
8/3/2012 9:17:33 PM #

I don't know what exactly causing this error but it seems that it is because of the version of SQL you are using. I tried it when I was using SQL 2005.

nkanyiso nkanyiso
9/1/2012 3:13:32 PM #

Go to project 1 reffence 2 browse 3 C: drive 4 Program file 5 microsoft sql sever 6 select 100 7 : SDK 8 assambleis than you can get all namespace Nkanyiso Shangase from DUT

sorterevolver sorterevolver
1/18/2013 1:51:00 PM #

Thank you prashant for your answer,have a nice day.

mukesh mukesh
3/6/2013 12:44:00 AM #

how to add the refference in Visual Studio. NET 2008

Prashant Prashant
3/6/2013 4:05:00 PM #

I build this in VS 2008. If you can't see the assembly then try a searching the OS drive (C: generally) and then add it to the references. I do agree as this assembly is pretty hard to find with older versions of VS. It's a long time back so I don't exactly recall how I added it.

Izhar Mehmood Izhar Mehmood
6/13/2014 6:21:54 AM #

using the backup and restore application i am facing below given error. Backup failed for Server 'IZHAR' Please give me the solution for.

Prashant Prashant
6/14/2014 3:32:16 PM #

@Izhar: Please provide more details on the error.

dyy dyy
8/1/2014 2:27:16 AM #

backup and restore class need you to add reference Microsoft.SqlServer.Smo.Extended.dll.

vikas manhas vikas manhas
6/15/2015 9:19:32 AM #

how i add namespace in visual studio 2013 and i am using sql express 2010

Prashant Prashant
6/15/2015 10:33:02 AM #

@vikas manhas: The namespace is not available out of the box. You may need to add the assemblies explicitly. Right click on the References and select "Add References". Under .NET section you can find the assemblies that are required for this solution. Also I assume that you are using SQL Server Express 2012. There are 2 library that I can recall as of now that will make this solution work for you: 1. Microsoft.SqlServer.Smo.dll 2. Microsoft.SqlServer.ConnectionInfo.dll But if you plan to go with the new version of SQL Server than you may need 1 more library: Microsoft.SqlServer.Management.Sdk.Sfc.dll I have no idea what it does and why it is used.

Sandeep Sandeep
12/17/2015 11:35:00 AM #

i want to take sql db backup on other computer / external hard drive not on the machine on which server is installed using c#

Prashant Prashant
12/19/2015 3:02:46 PM #

@Sandeep: Change this first parameter with the complete path to your external hard drive or on another machine: This line: BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath + "\Backup.bak", DeviceType.File); To: BackupDeviceItem bkpDevice = new BackupDeviceItem("E:\Backup.bak", DeviceType.File);

anjum anjum
12/26/2015 12:19:16 PM #

my server connection is not occuring what i do and my server is not display in the first text box

Ganesh Ganesh
1/29/2016 6:25:59 AM #

Hi, i facing a problem in wpf backup and restore data if any one solution the let know .

Pratik Shirse Pratik Shirse
3/31/2016 5:30:16 PM #

we are getting error in this line can help me Backup bkpDatabase = new Backup(); and BackupDeviceItem bkpDevice = new BackupDeviceItem(DBpath + "\Backup.bak", DeviceType.File);

ashish ashish
10/20/2016 6:06:23 AM #

Incremental backup dose not work here.

Add Comment

Visit to discover Indian blogs Computers Blogs