Go Premium for a chance to win a PS4. Enter to Win

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 630
  • Last Modified:

Backup and Restore database from SQL-Server

I want to do the both tasks: Backup and Restore for an SQL-Server Database from a VB program. The backup data stores in a specific location. What I have to do?

Thanks in advance.
0
bBK
Asked:
bBK
  • 2
1 Solution
 
inthedarkCommented:
I use a class to do this functions

Just open a connection

Dim CN as ADODB.Connection
Set CN=New Connection
CN.QueryString="......"
CN.Open

Dim OK

OK= BackupdatabaseOK(CN,"MyDB","C:\MyFolder\MyFile.BAK")

And restore

OK=RestoreDBOK(CN,"MyDB", "C:\MyFolder\MyFile.BAK")

I have also included a restore with move example, allowing restore of files to a new location and database name.

Hope this helps:~)


Public Function BackupDatabaseOK(CN As ADODB.Connection, DatabaseName As String, DestinationFile As String) As Boolean

' Backup a database
' returns ok indicator True or False


Dim SQL As String
Dim ok

   
    SQL = "USE master" + vbCrLf
    SQL = SQL + "EXEC sp_addumpdevice 'disk', 'TMP_Backup', '" + DestinationFile + "'" + vbCrLf
    SQL = SQL + "BACKUP DATABASE " + DatabaseName + " TO TMP_Backup" + vbCrLf
    SQL = SQL + "EXEC sp_dropdevice 'TMP_Backup'" + vbCrLf
    SQL = SQL + "USE " + DatabaseName + vbCrLf
    ok = ExecuteSQLOK(CN, SQL)
    If Not ok Then
        BackupDatabaseOK = False
    End If
   
   
    SQL = "USE " + DatabaseName + vbCrLf ' re-issue incase last command did not read the end
    ok = ExecuteSQLOK(CN, SQL)

End Function

Public Function ExecuteSQLOK(CN As ADODB.Connection, SQL As String, Optional UseTransaction As Boolean = False) As Boolean

' Execute an SQL statement
On Error Resume Next
If UseTransaction Then CN.BeginTrans
CN.Execute SQL, , adCmdText + adExecuteNoRecords


If UseTransaction Then CN.CommitTrans

If Err.Number <> 0 Then
    If UseTransaction Then
        CN.RollbackTrans
    End If
    ExecuteSQLOK = False

Else
    ExecuteSQLOK = True
End If

End Function

Public Function RestoreDatabaseOK(CN As ADODB.Connection, DatabaseName As String, SourceFile As String) As Boolean

' Restore a database don't try master

Dim SQL As String
Dim ok


SQL = "USE master" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'TRUE'" + vbCrLf
SQL = SQL + "RESTORE DATABASE " + DatabaseName + " FROM DISK = '" + SourceFile + "'" + vbCrLf
SQL = SQL + "EXEC sp_dboption '" + DatabaseName + "', 'offline', 'FALSE'" + vbCrLf

RestoreDatabaseOK = ExecuteSQLOK(CN, SQL)

End Function

Public Function RestoreWithMoveOK(CN As ADODB.Connection, _
       backupFile As String, _
       oldDBName As String, oldData As String, oldLog As String, _
       newDB As String, newData As String, newLog As String)

' Here is the SQL Server TSQL Command

'RESTORE DATABASE [NewDBName] FROM  DISK = N'D:\YourBackup.BAK' WITH  FILE = 1,  NOUNLOAD ,  STATS =
'10,  RECOVERY ,  MOVE N'YourOldDB_Data' TO N'D:\YourNewFileLocaltion\NewDBName.mdf',  MOVE N'YourOldDB_log'
'TO N'D:\YourNewFileLocaltion\NewDBName_log.ldf'

'How to use:

'backupfile = "d:\all.bak"

'olddb = "OldDBName"
'oldData = "OldDBName_data" ' find these from current database properties
'oldLog = "OldDBName_log"

'newdb = "NewName"
'newData = "d:\newdb_data.mdf"
'newLOG = "d:\newdb_log.mdf"

'OK = RestoreWithMoveOK(CN, backupFile, oldDBName, oldData, oldLog, _
  newDB, newData, newLog)
 
' another example:
'ok = ADO.RestoreWithMoveOK(CN, "C:\MyData.BAK", "MyDB", "MYDB_Data", "MyDB_LOG", "NewDB", "NewDB_Data", "NewDB_LOG")
 
Dim SQL As String

SQL = "USE MASTER" + vbCrLf
SQL = SQL + "GO" + vbCrLf
SQL = SQL + "RESTORE DATABASE [$DB$] FROM  DISK = N'$BACKUP$'"
SQL = SQL + " WITH  FILE = 1,  NOUNLOAD ,  STATS = 10,"
SQL = SQL + " RECOVERY ,  MOVE N'$OLDDATA' TO "
SQL = SQL + " N'$NEWDATAFILE$',  MOVE N'$OLDLOG$' TO '$NEWLOGFILE'"

SQL = Replace(SQL, "$DB$", newDB)
SQL = Replace(SQL, "$BACKUP$", backupFile)
SQL = Replace(SQL, "$OLDDATA$", oldData)
SQL = Replace(SQL, "$OLDLOG$", oldLog)
SQL = Replace(SQL, "$NEWDATAFILE$", newData)
SQL = Replace(SQL, "$NEWLOGFILE$", newLog)

On Error Resume Next
Err.Clear
CN.Execute SQL
If Err.Number <> 0 Then
    RestoreWithMoveOK = False
Else
    RestoreWithMoveOK = True
End If
   
End Function


0
 
bBKAuthor Commented:
Thanks, I'll check and answer you soon
0
 
bBKAuthor Commented:
Points is yours. Many thanks for quick response.

Best Regards.

NBT.
0

Featured Post

Vote for the Most Valuable Expert

It’s time to recognize experts that go above and beyond with helpful solutions and engagement on site. Choose from the top experts in the Hall of Fame or on the right rail of your favorite topic page. Look for the blue “Nominate” button on their profile to vote.

  • 2
Tackle projects and never again get stuck behind a technical roadblock.
Join Now