ACC2000: How to Use SQL-DMO to Copy an Existing MSDE or SQL Server Database to a New Database (253435)

MORE INFORMATION

In order to copy an MSDE database or a Microsoft SQL Server database, the database files must first be detached. Then the files must be copied. Finally, the original database and the new database must be reattached to the server.

The following steps involve creating a new copy of the sample Access project NorthwindCS. To follow these steps, you must have access to a computer running either MSDE or SQL Server 7.0 that has a copy of the sample database NorthwindCS installed.

1. Create a new Access database.
2. In the Database window, click Modules, and then click New.
3. On the Tools menu, click References.
4. Click to select the Microsoft SQLDMO Object Library check box.
5. Type the following line in the Declarations section if it is not already there:

   Option Explicit
   					

6. Type the following procedure:

    
   
   Function CopySQLDB(strLogin As String, strPwd As String, _
   strSrv As String, strDatabase As String, strNewName As String)
   
   '===============================================================
   ' Parameters
   ' --------------------------------------------------------------
   ' strLogin:  Name of the Login account used.
   ' strPwd:  The Password for the login account.
   ' strSrv:  The server to which you are connecting.
   ' strDataBase:  The name of the database you want to copy.
   ' strNewName: Desired name of the new copy of the database.
   '===============================================================
   
      Dim sql As Object
      Dim db As Object
      
      Dim strMDFfilePath As String
      Dim strMDFfileName As String
      Dim strLOGfile As String
         
      On Error GoTo CopySQLDB_Err
      
      'Create Objects
      Set sql = CreateObject("SQLDMO.SQLServer")
      Set db = CreateObject("SQLDMO.Database")
      
      'Connect to the server
      sql.Connect strSrv, strLogin, strPwd
      
      'Set the database object
      Set db = sql.Databases(strDatabase, "dbo")
         
      'Determine the location of database files, primary and log
      strMDFfilePath = db.PrimaryFilePath
      strMDFfileName = _
      Trim(db.FileGroups.Item(1).DBFiles.Item(1).PhysicalName)
      strLOGfile = Trim(db.TransactionLog.LogFiles(1).PhysicalName)
   
      'Clear out the database object
      Set db = Nothing
      
      'Detach database to allow copying
      sql.DetachDB (strDatabase)
   
      'Copy database files to new names
      FileCopy strMDFfileName, strMDFfilePath & strNewName & ".mdf"
      FileCopy strLOGfile, strMDFfilePath & strNewName & "_log.ldf"
      
      'Re-attach original database
      sql.AttachDB strDatabase, strMDFfileName & "," & strLOGfile
      sql.AttachDB strNewName, strMDFfilePath & strNewName & _
      ".mdf" & "," & strMDFfilePath & strNewName & "_log.ldf"
      
      'Set database object to the new database
      Set db = sql.Databases(strNewName, "dbo")
      Debug.Print "Database Created"
      
      
   CopySQLDB_End:
      'Clear out the objects before ending
      Set db = Nothing
      Set sql = Nothing
      Exit Function
               
   CopySQLDB_Err:
      'Basic error handling.
      MsgBox Err.Description, vbInformation, "SQL OLE Automation"
      Resume CopySQLDB_End
   
   End Function
   						

7. Type the following line in the Immediate window, and then press ENTER:

   ?CopySQLDB("sa","","(local)","NorthwindCS","NwindCS")
   						

   NOTE: This example works in most cases. If your server is not local, type the name of the server to which you are connecting instead of the keyword "(local)" in the line above. In addition, this example presumes that sa is logging on with no password. You can also log on with another account that has similar permissions.
   
   If successful, you see Database Created in the Immediate window.
   
   At this point, a copy of the NorthwindCS SQL server database has been created with the name NwindCS.