.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
 
Sign In
Register
 
Win Surprise Gifts!!!
Congratulations!!!


Top 5 Contributors of the Month
david stephan

Home >> Code Snippets >> Visual Studio >> Post New Resource Bookmark and Share   

 Subscribe to Code Snippets

Sql Server Database Backup and Restore through C#

Posted By:abhays       Posted Date: April 29, 2014    Points: 40    Category: Visual Studio    URL: http://www.dotnetspark.com  

Sql Server Database Backup and Restore through C#
 

 

 

First up all  you have to add some namespace in your cs file:




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 




Now you add the following function  for Backup and restore database:




//Code for backup databse

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

}




//Code for restoration od database:

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

}


     

Further Readings:

Responses

No response found. Be the first to respond this post

Post Comment

You must Sign In To post reply
Find More code samples in C#, ASP.Net, Vb.Net and more Here

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend