Solved

Backup and Restore database from SQL-Server

Posted on 2003-12-02
3
618 Views
Last Modified: 2008-03-17
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
Comment
Question by:bBK
  • 2
3 Comments
 
LVL 17

Accepted Solution

by:
inthedark earned 150 total points
ID: 9860561
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
 

Author Comment

by:bBK
ID: 9865569
Thanks, I'll check and answer you soon
0
 

Author Comment

by:bBK
ID: 9880060
Points is yours. Many thanks for quick response.

Best Regards.

NBT.
0

Featured Post

Networking for the Cloud Era

Join Microsoft and Riverbed for a discussion and demonstration of enhancements to SteelConnect:
-One-click orchestration and cloud connectivity in Azure environments
-Tight integration of SD-WAN and WAN optimization capabilities
-Scalability and resiliency equal to a data center

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Introduction In a recent article (http://www.experts-exchange.com/A_7811-A-Better-Concatenate-Function.html) for the Excel community, I showed an improved version of the Excel Concatenate() function.  While writing that article I realized that no o…
This article describes some techniques which will make your VBA or Visual Basic Classic code easier to understand and maintain, whether by you, your replacement, or another Experts-Exchange expert.
Get people started with the process of using Access VBA to control Excel using automation, Microsoft Access can control other applications. An example is the ability to programmatically talk to Excel. Using automation, an Access application can laun…
Show developers how to use a criteria form to limit the data that appears on an Access report. It is a common requirement that users can specify the criteria for a report at runtime. The easiest way to accomplish this is using a criteria form that a…

839 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question