SQLDMO restore

Hi,

How can I restore an individual table from a .bak file using SQLDMO after full database backup?

kloppa
kloppaAsked:
Who is Participating?
 
inthedarkConnect With a Mentor Commented:
I created a class which I call zADO.  Here are some Functions from zADO. I have made the code handle both Access & SQL server but in the example here I cut out the Access bits to make the code easier to understand.

In a Global module I put a declaration like:

Dim ADO as New zADO

In your code open your connection as normal:

CN.Open

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)

' Now move the tables you wish to copy
OK = ADO.CopyTableOK(CN, "MySourceDB", "MyTable", "DestDB", "DestTable")

Hope this helps.......


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)

SQL = 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


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

' Backup a database


Dim SQL As String
Dim OK
Dim RS As ADODB.Recordset


    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 = ExecuteRSSQLOK(CN, RS, SQL)
    If Not OK Then
        BackupDatabaseOK = False
       
        MsgBox GetLastError(CN)
    End If
   
   
    SQL = "USE " + DatabaseName + vbCrLf ' re-issue incase last command did not read the en.d
    OK = ExecuteSQLOK(CN, SQL)


End Function
Public Function CopyTableOK(CN As ADODB.Connection, SourceDB As String, SourceTable As String, DestinationDB As String) As Boolean

' Copies a Table
OK = ADO.CopyTableOK(CN, "MySourceDB", "MyTable", "DestDB", "DestTable")

Dim SQL As String
Dim OK
Const Owner As String = "DBO"



CopyTableOK = False

' Use the Destination
SQL = "USE " + DestinationDB + vbCrLf
SQL = SQL + "GO" + vbCrLf
On Error Resume Next
CN.Execute SQL$
If Err.Number <> 0 Then
    Exit Function
End If

SQL$ = "Drop Table [" + DestinationTable + "];"
On Error Resume Next
Err.Clear
CN.Execute SQL$
' Ignore the error as tbale may not exists

' Make sure bulk copy option is on.
SQL = ""
SQL$ = SQL$ + "EXEC sp_dboption '" + DestinationDB + "','select into/bulkcopy', 'True';"
On Error Resume Next
Err.Clear
CN.Execute SQL$
If Err.Number <> 0 Then
    Exit Function
End If

' Copy the table
SQL$ = "SELECT * INTO [" + DestinationTable + "] From [" + SourceDB + "].[" _
        + Owner + "].[" + SourceTable + "];"
On Error Resume Next
Err.Clear
CN.Execute SQL$
If Err.Number <> 0 Then
    Exit Function
End If

' Make sure bulk copy option is on.
SQL = ""
SQL$ = SQL$ + "EXEC sp_dboption '" + DestinationDB + "','select into/bulkcopy', 'True';"
On Error Resume Next
Err.Clear
CN.Execute SQL$
If Err.Number <> 0 Then
    Exit Function
End If

CopyTableOK = OK

End Function


0
 
kloppaAuthor Commented:
bruintje,

I already browsed through these articles with not much result.

thanks
kloppa
0
 
inthedarkCommented:
The function ExecuteSQLOK(CN, SQL) is simple and can be replaced with

CN.Execute SQL

Also so can function ExecureRSSQLOK.
0
 
kloppaAuthor Commented:
thanks inthedark


kloppa
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.