Search

Tuesday, April 12, 2011

Sql Server Database Backup and Restore through VB.net


First you have to add some namespace in your vb file: Imports Microsoft.SqlServer.management Imports Microsoft.SqlServer.Management.NotificationServices Imports Microsoft.SqlServer.Management.Smo Imports Microsoft.SqlServer.Management.Smo.Agent Imports Microsoft.SqlServer.Management.Smo.Broker Imports Microsoft.SqlServer.Management.Smo.Mail Imports Microsoft.SqlServer.Management.Smo.RegisteredServers Imports Microsoft.SqlServer.Management.Smo.Wmi Imports Microsoft.SqlServer.Management.Trace 'Code for backup database Public Sub BackupDatabase(databaseName As String, userName As String, password As [String], serverName As [String], destinationPath As [String]) Dim sqlBackup As New Backup() sqlBackup.Action = BackupActionType.Database sqlBackup.BackupSetDescription = "ArchiveDataBase:" & DateTime.Now.ToShortDateString() sqlBackup.BackupSetName = "Archive" sqlBackup.Database = databaseName Dim deviceItem As New BackupDeviceItem(destinationPath, DeviceType.File) Dim connection As New ServerConnection(serverName, userName, password) Dim sqlServer As New Server(connection) Dim db As Database = 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) End Sub 'Code for restoration od database: Public Sub RestoreDatabase(databaseName As [String], filePath As [String], serverName As [String], userName As [String], password As [String], dataFilePath As [String], _ logFilePath As [String]) Dim sqlRestore As New Restore() Dim deviceItem As New BackupDeviceItem(filePath, DeviceType.File) sqlRestore.Devices.Add(deviceItem) sqlRestore.Database = databaseName Dim connection As New ServerConnection(serverName, userName, password) Dim sqlServer As New Server(connection) Dim db As Database = sqlServer.Databases(databaseName) sqlRestore.Action = RestoreActionType.Database Dim dataFileLocation As [String] = Convert.ToString(dataFilePath + databaseName) & ".mdf" Dim logFileLocation As [String] = Convert.ToString(logFilePath + databaseName) & "_Log.ldf" db = sqlServer.Databases(databaseName) Dim rf As New RelocateFile(databaseName, dataFileLocation) sqlRestore.RelocateFiles.Add(New RelocateFile(databaseName, dataFileLocation)) sqlRestore.RelocateFiles.Add(New RelocateFile(Convert.ToString(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() End Sub

47 comments:

  1. what do you place in password when user has not password, please

    ReplyDelete
  2. In Restoration process,

    databaseName, filePath ,serverName ,dataFilePath ,logFilePath --> which is .bak file and what you mean by filePath,dataFilePath ,logFilePath .

    Please explain these,Please.

    ReplyDelete
  3. How do you verify the restoration is successful in coding,please.

    ReplyDelete
  4. filePath = Backup File location with File Name
    ServerName = SQL Server Name
    dataFilePath = Location where to restore mdf file with File Name
    logFilePath = Location where to restore ldf file with File Name.

    ReplyDelete
  5. Run the Code in Try Catch Block.
    If there are any error then you will fall into Catch Block.

    ReplyDelete
  6. Does this code work if I want to restore database to another computer(server)?

    ReplyDelete
  7. If Database uses Windows Authentication then?

    ReplyDelete
  8. REFERENCE NOT FOUND. WHICH REFERENCE IS ADD FOR IMPORT ALL THIS CLASSES.

    ReplyDelete
  9. Hi there Dear, are you really visiting this site regularly, if
    so then you will without doubt take fastidious know-how.
    Here is my blog ; data backup services

    ReplyDelete
  10. Awesome! Its really amazing piece of writing, I have got much clear idea on
    the topic of from this article.
    Also see my webpage :: Repaire Access file

    ReplyDelete
  11. i'm a new vb user, i want to know is that the code for 'restoration and 'backup need put in a button?if no is that enter all the code before Form1_load? where it will save the backup file?

    ReplyDelete
    Replies
    1. You have to create two button, one for Backup and other for Restore.

      Now write the above code in respective button click event.

      Delete
  12. hello

    the Microsoft.SqlServer.management namespace is unavailable , what should I do

    ReplyDelete
    Replies
    1. You need to install the Management objects.

      The Management Objects Collection package includes several key elements of the SQL Server 2005 management API, including Analysis Management Objects (AMO), Replication Management Objects (RMO), and SQL Server Management Objects (SMO). Developers and DBAs can use these components to programmatically manage SQL Server 2005.

      Delete
    2. Will this work with SQL 2008 R2? I too cannot find the name space for management......

      Delete
  13. sqlRestore.Complete += New ServerMessageEventHandler(sqlRestore_Complete)

    sqlRestore.PercentComplete += New PercentCompleteEventHandler(sqlRestore_PercentComplete)

    I found error on these line. For Raise event.

    Please Help Me.

    ReplyDelete
    Replies
    1. Where you are getting above error.
      need more detail of the error.

      Delete
  14. Hi there it's me, I am also visiting this web site regularly, this web site is genuinely nice and the viewers are in fact sharing pleasant thoughts.
    Also see my site: ovacue fertility monitor review

    ReplyDelete
  15. Thanks for the code, however, it's bombing on me... "Message: Backup failed for Server 'Anonymous-PC'. for user Anonymous
    SmoExceptionType: FailedOperationException"

    Are there any settings I need to verify on SQL Server 2008? Is so, what are they? Nothing I try works.

    ReplyDelete
    Replies
    1. Need more details of the error.
      Check SQL Server Log for more details of the error.

      Delete
  16. Hi, Thanks for your code, but can you explain me more about this line: sqlRestore.Devices.Add(deviceItem) ? what is the deviceItem ?

    ReplyDelete
    Replies
    1. DeviceItem may be Hard Disk, USB, External Hard Disk or any other Storage device.

      Delete
  17. How can I add a date to the backup file name?

    ReplyDelete
    Replies
    1. In above code I had already added date and time in File name.
      sqlBackup.BackupSetDescription = "ArchiveDataBase:" & DateTime.Now.ToShortDateString()

      Delete
  18. Hello.. Will this work with SQL Server Express? I may be moving my application's database to Express from SQL Server 2012.

    ReplyDelete
    Replies
    1. Yes it will work for SQL Server Express also.

      Delete
    2. Thanks for the reply, I came across a few problems when copying your code into a windows form application I created.
      For some reason I can't find "Microsoft.SqlServer.Management.NotificationServices" reference and in order to get access to ServerConection class I had to import "Microsoft.SqlServer.Management.Common", after that if I run the backup module I get an error saying cannot open backup device, UAC is off and the SQL user has read/write access to the location I have chosen. Any help would be greatly appreciated.

      Delete
  19. Boss am interested much on taking a backup and not restoring, in a way that you restore the backup manually when the need arise.... (is there any need of defining and putting expiry date of that generated backup file ) ??? am i bit confused, or if you can jst guide me how to re structure the code so that it suits my requirements, in this way i hope the code might be abit short as all un-necessary lines of codes will be removed

    ReplyDelete
  20. I came across a few problems when copying your code into a windows form application I created.
    For some reason I can't find "Microsoft.SqlServer.Management.NotificationServices" reference

    ReplyDelete
    Replies
    1. This comment has been removed by the author.

      Delete
    2. You had to include Microsoft.SqlServer.Management.Nmo.NotificationServices namespace

      Delete
  21. Can you provide this code in c#.

    ReplyDelete
    Replies
    1. There are many conversion tools available online for converting code from vb.net to c# and vice versa.
      Check below link
      http://www.developerfusion.com/tools/convert/vb-to-csharp/

      Hre you can convert code from vb.net to c#

      Delete
  22. Not able to find all above namespace in "C:\Program Files\Microsoft SQL Server\90\SDK\Assemblies" folder I am using sql 2005 express edition and vb2008. I tried to download but no luck.. please Help....

    ReplyDelete
    Replies
    1. All above namespace exist. Add reference in visual studo Microsoft.SQLServer.SMO.
      Now import above namespace in code.

      Delete
  23. i have an issues when use sql 2012 help me out

    ReplyDelete
  24. Dim restore As New Restore()
    restore.Database = txtCustomerDBName.Text
    restore.Action = RestoreActionType.Database
    restore.Devices.AddDevice(bkFilePath, DeviceType.File)
    restore.ReplaceDatabase = False
    restore.NoRecovery = False

    ReplyDelete
  25. Dim restore As New Restore()
    restore.Database = txtCustomerDBName.Text
    restore.Action = RestoreActionType.Database
    restore.Devices.AddDevice(bkFilePath, DeviceType.File)
    restore.ReplaceDatabase = False
    restore.NoRecovery = False

    Dim dt As DataTable = restore.ReadFileList(sqlServer)
    For Each aRow As DataRow In dt.Rows
    If aRow("Type") = "L" Then
    restore.RelocateFiles.Add(New RelocateFile(aRow("LogicalName"), ConfigurationManager.AppSettings("databaseSQLServerFileLogDirectory") & txtCustomerDBName.Text & "_Log.ldf"))
    ElseIf aRow("Type") = "D" Then
    restore.RelocateFiles.Add(New RelocateFile(aRow("LogicalName"), ConfigurationManager.AppSettings("databaseSQLServerFileDataDirectory") & txtCustomerDBName.Text & ".mdf"))
    End If
    Next

    restore.SqlRestore(sqlServer)

    ReplyDelete
  26. How do I show backup or restore progress to user in a progressbar?

    ReplyDelete
  27. How do I show backup or restore progress to user in a progressbar?

    ReplyDelete
    Replies
    1. Yes, you can show backup or restore progress to user in a progress bar if you had installed
      sp_who2k5
      into your master database. You have to run below command:
      sp_who2k5 1, 1

      The result will include all avtive transaction. Search the backup in the requestCommand field. The aptly named percentComplete field will give you the progress of the backup.

      Delete
  28. Dim sqlBackup As New Backup()

    error in Backup(), can you help me ?

    ReplyDelete