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.
bBKAsked:
Who is Participating?
 
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
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.