Backup and restore SQL database using SMO

Question

0.00/5 (No votes)

See more:

Wellcome
I Want To Do Backup and Restore SQL Database Using SMO Library in vb.net
I used Certain codes But failed attempts
Please advise a specific code
Greetings

What I have tried:

VB

Imports Microsoft.SqlServer.Management.Smo
Imports Microsoft.SqlServer.Management.Common

Private Sub btnBackup_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnBackup.Click
   Dim oConn As New ServerConnection(<Ostora>, <sa>, <9080123>)
   Dim oServer As New Server(oConn)
   Dim oBackup As New Backup
   oBackup.Devices.AddDevice("d:\backup.bak", DeviceType.File)
   oBackup.Database = om_db
   oBackup.Action = BackupActionType.Database
   oBackup.Initialize = True
   oBackup.PercentCompleteNotification = 10
   AddHandler bkp.PercentComplete, AddressOf ProgressEventHandler
   oBackup.SqlBackup(oServer)
End Sub

Private Sub btnRestore_Click(ByVal sender As System.Object, ByVal e As System.EventArgs) Handles btnRestore.Click
   Dim oConn As New ServerConnection(<Ostora>, <sa>, <9080123>)
   Dim oServer As New Server(oConn)
   Dim oRestore As New Restore
   oRestore.Devices.AddDevice("d:\backup.bak", DeviceType.File)
   oRestore.Database = om_db
   oRestore.Action = RestoreActionType.Database
   oRestore.ReplaceDatabase = True
   oRestore.PercentCompleteNotification = 10
   AddHandler res.PercentComplete, AddressOf ProgressEventHandler
   oRestore.SqlRestore(oServer)
End Sub

Posted 27-Apr-20 0:25am

Mohamed Aboelkheer

Updated 27-Apr-20 2:23am

phil.o

v2

Add a Solution

2 solutions

Solution 2

Although most examples use SMO to backup and restore, this is not needed at all and can lead to problems when using a different SQL Server version as Microsoft sometimes changes the SMO dll's and functionality.

Here are some examples that do not use SMO, taken from:
Sql Server Database backup from my local pc using vb.net windows application[^]

Dim sqlConn As New SqlConnection("Data Source=(local);Initial Catalog=master;User ID=sa;Trusted_Connection=true")

sqlConn.Open()

Dim sCommand = "BACKUP DATABASE [DatabaseName] TO DISK = N'E:\Backup\Backup.bak' WITH COPY_ONLY"

Using sqlCmd As New SqlCommand(sCommand, sqlConn)
    sqlCmd.ExecuteNonQuery()
End Using

sCommand = "RESTORE DATABASE [DatabaseName] FROM DISK = N'E:\Backup\TestBackup.bak' WITH REPLACE"

Using sqlCmd As New SqlCommand(sCommand, sqlConn)
    sqlCmd.ExecuteNonQuery()
End Using

If the restore does not work correctly, try USE Master first, or do not use the WITH REPLACE.
It might also help to DROP the existing database first.
Also see: sql server - Restore SQL Database with Replace option - Stack Overflow[^]

Posted 27-Apr-20 2:24am

RickZeeland

Updated 27-Apr-20 23:21pm

v3

Comments

Mohamed Aboelkheer 27-Apr-20 21:47pm

This code has been tried
The backup process was completed successfully, but the restore process is successful once, and with the experiment restored it fails and the computer must be restarted. Please advice.
Do I need to restart the server after the restore process?

RickZeeland 28-Apr-20 1:27am

A restart should not be needed, what version of SQL Server are you using ? it could be that you need a slightly different restore command. You can also test the command from SQL Server Management Studio.

Mohamed Aboelkheer 28-Apr-20 4:45am

I use sql server 2012

RickZeeland 28-Apr-20 5:23am

I updated the solution with some tips, I'm afraid I can't be of much help further as we stopped using SQL Server a long time ago. We are now using PostgreSQL which luckily does not have these kind of backup and restore issues and is also much easier to bundle with your application.

Mohamed Aboelkheer 28-Apr-20 6:19am

Thanks a lot for your help

Add a Solution

Add your solution here

Treat my content as plain text, not as HTML

Preview 0

…

Existing Members

Sign in to your account

...or Join us

Download, Vote, Comment, Publish.

Your Email
Password
Forgot your password?

Your Email
This email is in use. Do you need your password?
Optional Password

I have read and agree to the Terms of Service and Privacy Policy
Please subscribe me to the CodeProject newsletters

When answering a question please:

Read the question carefully.
Understand that English isn't everyone's first language so be lenient of bad spelling and grammar.
If a question is poorly phrased then either ask for clarification, ignore it, or edit the question and fix the problem. Insults are not welcome.
Don't tell someone to read the manual. Chances are they have and don't get it. Provide an answer or move on to the next question.

Let's work to help developers, not make them feel stupid.

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

OriginalGriff · Accepted Answer · 2020-04-27T00:38:00

Solution 1

Have a look at this: Backing up an SQL Database in C#[^] - it's the code I use. It's in C#, but it's pretty obvious and it explains a number of the other pitfalls you need to be aware of when backing up SQL DBs.

Posted 27-Apr-20 0:38am

OriginalGriff

Comments

Mohamed Aboelkheer 27-Apr-20 21:46pm

This code has been tried
The backup process was completed successfully, but the restore process is successful once, and with the experiment restored it fails and the computer must be restarted. Please advice.
Do I need to restart the server after the restore process?