Solved

SQLDMO restore

Posted on 2002-04-17
5
580 Views
Last Modified: 2008-03-10
Hi,

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

kloppa
0
Comment
Question by:kloppa
  • 2
  • 2
5 Comments
 
LVL 44

Expert Comment

by:bruintje
ID: 6948599
0
 

Author Comment

by:kloppa
ID: 6948633
bruintje,

I already browsed through these articles with not much result.

thanks
kloppa
0
 
LVL 17

Accepted Solution

by:
inthedark earned 100 total points
ID: 6949631
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
 
LVL 17

Expert Comment

by:inthedark
ID: 6949638
The function ExecuteSQLOK(CN, SQL) is simple and can be replaced with

CN.Execute SQL

Also so can function ExecureRSSQLOK.
0
 

Author Comment

by:kloppa
ID: 6951285
thanks inthedark


kloppa
0

Featured Post

Gigs: Get Your Project Delivered by an Expert

Select from freelancers specializing in everything from database administration to programming, who have proven themselves as experts in their field. Hire the best, collaborate easily, pay securely and get projects done right.

Question has a verified solution.

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

Suggested Solutions

Title # Comments Views Activity
Determine Range to Select 5 48
Run code from text file in vb 1 64
passing a value with stream reader AFTER a ";" 3 66
VB6 - Convert HH:MM into Decimal 8 54
Introduction While answering a recent question about filtering a custom class collection, I realized that this could be accomplished with very little code by using the ScriptControl (SC) library.  This article will introduce you to the SC library a…
Since upgrading to Office 2013 or higher installing the Smart Indenter addin will fail. This article will explain how to install it so it will work regardless of the Office version installed.
Get people started with the utilization of class modules. Class modules can be a powerful tool in Microsoft Access. They allow you to create self-contained objects that encapsulate functionality. They can easily hide the complexity of a process from…
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…

786 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