Click here to Skip to main content
11,477,625 members (73,034 online)
Rate this: bad
good
Please Sign up or sign in to vote.
See more: SQL C#4.0 database Backup
How can i create the backup of sql database by c# code. Any sample code.

I am using this command to backup,it' snot working.

 cmd.CommandText = @"backup database tryDatabase.mdf to disk ='d:\BackUp1.bak' with init,stats=10 ";
Posted 1-Oct-12 10:09am
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 1

  Permalink  
Comments
footballpardeep at 1-Oct-12 15:34pm
   
Thanks for reply, i don't want to use any dll for this. i wanna do it by sql command.
footballpardeep at 1-Oct-12 16:03pm
   
I was not using initial catalog in connection string.
when i used this:
string connectionString = @"Data Source=.\SQLEXPRESS;AttachDbFilename=C:\Users\PARDEEP\Documents\Visual Studio 2010\Projects\TryingDatabaseInWPF\TryingDatabaseInWPF\tryDatabase.mdf;Initial Catalog=tryDatabase;Integrated Security=True;User Instance=True";

It worked.
footballpardeep at 1-Oct-12 16:04pm
   
But now the problem is how can i restore the database.
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 3

Hi footballpardeep,

Why you didnt ask google about it?
I hope this link[^] would help you a bit.

Regards,
RK
  Permalink  
v2
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 6

Using ADO:

//C# code to create a backup with ADO.net
using System.Data.SqlClient;


//Create a connection to the test database. Integrated security is Windows
//Authentication
string ConnectionString = @"Data Source=Localhost;" +
"Initial Catalog=test;Integrated Security=True";
SqlConnection cnn = new SqlConnection(ConnectionString);

//backup the database test in the c:\backup folder
SqlCommand cmd = new SqlCommand(@"BACKUP DATABASE [test] TO "+
@"DISK = N'C:\backup\test.bak'", cnn);
cmd.CommandType = CommandType.Text;

cnn.Open();
//Execute the command

cmd.ExecuteNonQuery();

Console.Write("Backup completed successfully");

cnn.Close();


/////////////////////////////////////////////////////////////
/////Using SMO

Imports Microsoft.SqlServer.Management.Smo
Sub Main()

'Enter the name of the database
backupmethod("test")

End Sub
'Method to make the Backup
Private Sub backupmethod(bd)
Dim Myserver As Server = New Server("localhost")
'Create the instance of the class backup
Dim backup As Backup = New Backup()
'Use windows authentication
Myserver.ConnectionContext.LoginSecure = True
Try
'Connect to the server
Myserver.ConnectionContext.Connect()
Console.WriteLine("*** Backing up ***")
Dim path As String
'Set the path of the backup
path = "C:\Testd.bak"
backup.Devices.AddDevice(path, DeviceType.File)
backup.Database = bd

'select the type of backup action (you can also make backup
'copies of records and archives)
backup.Action = BackupActionType.Database
'If the backup is not incremental set to false
backup.Incremental = False

backup.Initialize = True
backup.LogTruncation = BackupTruncateLogType.Truncate
backup.SqlBackup(Myserver)
Console.ReadKey()
'close the connection to server
Myserver.ConnectionContext.Disconnect();
Catch ex As Exception
Console.WriteLine("Connection failed.")
End Try

End Sub

References:
http://www.sqlservercentral.com/articles/Backup/91590/[^]
  Permalink  
v2
Comments
Richard Deeming at 27-Nov-14 15:25pm
   
Why are you answering a solved question from over two years ago?
Rate this: bad
good
Please Sign up or sign in to vote.

Solution 7

1 Back up

OpenFileDialog ofd = new OpenFileDialog();
SaveFileDialog sfd = new SaveFileDialog();
CashFlowDAL cfd = new CashFlowDAL();
string filepath;
if (!Directory.Exists("C:\\"))
{
filepath = "C:\\BACKUP\\" + User.compname;
}
else
{
filepath = "E:\\BACKUP\\" + User.compname;
}
string res = "";

{
if (!Directory.Exists(filepath))
{
Directory.CreateDirectory(filepath);
}
try
{

string filename = DateTime.Now.ToString(User.compname + " dd_MM_yy__hh_mm_ss.bak");
res = cfd.BackupDatabase(User.compname, filepath + "\\" + filename);
if (res != null) throw new Exception(res);
File.Copy(filepath + "\\" + filename, User.backupdir + "\\" + filename);
MessageBox.Show("Backup Completed..(In " + filepath + "\\" + filename + ")");
}
catch (Exception ex)
{
MessageBox.Show(ex.Message);
}
}


2 Restore

try
{
CashFlowDAL cfd = new CashFlowDAL();
cfd.ConnectToDB();
OpenFileDialog od = new OpenFileDialog();
od.Filter = "SQL Server database Restore files|*.bak";
od.Title = "Restore Database Backup";
if (od.ShowDialog() == DialogResult.OK)
{
this.Cursor = Cursors.WaitCursor;

string UseMaster = "USE master";
SqlCommand UseMasterCommand = new SqlCommand(UseMaster, cfd.con);
UseMasterCommand.ExecuteNonQuery();

string Alter1 = @"ALTER DATABASE [" + User.compname + "] SET Single_User WITH Rollback Immediate";
SqlCommand Alter1Cmd = new SqlCommand(Alter1, cfd.con);
Alter1Cmd.ExecuteNonQuery();

string Restore = string.Format("Restore database [" + User.compname + "] from disk='{0}'", od.FileName);

SqlCommand RestoreCmd = new SqlCommand(Restore, cfd.con);
RestoreCmd.ExecuteNonQuery();

string Alter2 = @"ALTER DATABASE [" + User.compname + "] SET Multi_User";
SqlCommand Alter2Cmd = new SqlCommand(Alter2, cfd.con);
Alter2Cmd.ExecuteNonQuery();
MessageBox.Show("Database Retored Sucessfully", "Success Message!");
}
}
catch (Exception)
{
MessageBox.Show("Database didn't Restore", "Error Message!");
}
this.Cursor = Cursors.Default;
  Permalink  

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

  Print Answers RSS
0 Sergey Alexandrovich Kryukov 194
1 Richard Deeming 180
2 OriginalGriff 160
3 Sascha Lefèvre 145
4 Tomas Takac 130
0 Sergey Alexandrovich Kryukov 7,675
1 OriginalGriff 7,256
2 Sascha Lefèvre 3,034
3 Maciej Los 2,491
4 Richard Deeming 2,305


Advertise | Privacy | Mobile
Web03 | 2.8.150520.1 | Last Updated 28 Nov 2014
Copyright © CodeProject, 1999-2015
All Rights Reserved. Terms of Service
Layout: fixed | fluid

CodeProject, 503-250 Ferrand Drive Toronto Ontario, M3C 3G8 Canada +1 416-849-8900 x 100