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
Search
Tuesday, April 12, 2011
Sql Server Database Backup and Restore through VB.net
Subscribe to:
Post Comments (Atom)
what do you place in password when user has not password, please
ReplyDeleteJust press '' in password
ReplyDeleteIn Restoration process,
ReplyDeletedatabaseName, filePath ,serverName ,dataFilePath ,logFilePath --> which is .bak file and what you mean by filePath,dataFilePath ,logFilePath .
Please explain these,Please.
How do you verify the restoration is successful in coding,please.
ReplyDeletefilePath = Backup File location with File Name
ReplyDeleteServerName = SQL Server Name
dataFilePath = Location where to restore mdf file with File Name
logFilePath = Location where to restore ldf file with File Name.
Run the Code in Try Catch Block.
ReplyDeleteIf there are any error then you will fall into Catch Block.
Does this code work if I want to restore database to another computer(server)?
ReplyDeleteYes this work
ReplyDeleteIf Database uses Windows Authentication then?
ReplyDeleteChange the connection string
DeleteREFERENCE NOT FOUND. WHICH REFERENCE IS ADD FOR IMPORT ALL THIS CLASSES.
ReplyDeleteIt is already written.
DeleteMicrosoft.SQLServer
Hi there Dear, are you really visiting this site regularly, if
ReplyDeleteso then you will without doubt take fastidious know-how.
Here is my blog ; data backup services
Awesome! Its really amazing piece of writing, I have got much clear idea on
ReplyDeletethe topic of from this article.
Also see my webpage :: Repaire Access file
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?
ReplyDeleteYou have to create two button, one for Backup and other for Restore.
DeleteNow write the above code in respective button click event.
hello
ReplyDeletethe Microsoft.SqlServer.management namespace is unavailable , what should I do
You need to install the Management objects.
DeleteThe 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.
Will this work with SQL 2008 R2? I too cannot find the name space for management......
DeletesqlRestore.Complete += New ServerMessageEventHandler(sqlRestore_Complete)
ReplyDeletesqlRestore.PercentComplete += New PercentCompleteEventHandler(sqlRestore_PercentComplete)
I found error on these line. For Raise event.
Please Help Me.
Where you are getting above error.
Deleteneed more detail of the error.
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.
ReplyDeleteAlso see my site: ovacue fertility monitor review
Thanks for the code, however, it's bombing on me... "Message: Backup failed for Server 'Anonymous-PC'. for user Anonymous
ReplyDeleteSmoExceptionType: FailedOperationException"
Are there any settings I need to verify on SQL Server 2008? Is so, what are they? Nothing I try works.
Need more details of the error.
DeleteCheck SQL Server Log for more details of the error.
Hi, Thanks for your code, but can you explain me more about this line: sqlRestore.Devices.Add(deviceItem) ? what is the deviceItem ?
ReplyDeleteDeviceItem may be Hard Disk, USB, External Hard Disk or any other Storage device.
DeleteHow can I add a date to the backup file name?
ReplyDeleteIn above code I had already added date and time in File name.
DeletesqlBackup.BackupSetDescription = "ArchiveDataBase:" & DateTime.Now.ToShortDateString()
Hello.. Will this work with SQL Server Express? I may be moving my application's database to Express from SQL Server 2012.
ReplyDeleteYes it will work for SQL Server Express also.
DeleteThanks for the reply, I came across a few problems when copying your code into a windows form application I created.
DeleteFor 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.
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
ReplyDeleteI came across a few problems when copying your code into a windows form application I created.
ReplyDeleteFor some reason I can't find "Microsoft.SqlServer.Management.NotificationServices" reference
This comment has been removed by the author.
DeleteYou had to include Microsoft.SqlServer.Management.Nmo.NotificationServices namespace
DeleteCan you provide this code in c#.
ReplyDeleteThere are many conversion tools available online for converting code from vb.net to c# and vice versa.
DeleteCheck below link
http://www.developerfusion.com/tools/convert/vb-to-csharp/
Hre you can convert code from vb.net to c#
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....
ReplyDeleteAll above namespace exist. Add reference in visual studo Microsoft.SQLServer.SMO.
DeleteNow import above namespace in code.
we di nga ??
ReplyDeletei have an issues when use sql 2012 help me out
ReplyDeleteDim restore As New Restore()
ReplyDeleterestore.Database = txtCustomerDBName.Text
restore.Action = RestoreActionType.Database
restore.Devices.AddDevice(bkFilePath, DeviceType.File)
restore.ReplaceDatabase = False
restore.NoRecovery = False
Dim restore As New Restore()
ReplyDeleterestore.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)
How do I show backup or restore progress to user in a progressbar?
ReplyDeleteHow do I show backup or restore progress to user in a progressbar?
ReplyDeleteYes, you can show backup or restore progress to user in a progress bar if you had installed
Deletesp_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.
Dim sqlBackup As New Backup()
ReplyDeleteerror in Backup(), can you help me ?