DoCmd.TransferSQLDatabase

Copy an entire SQL Server database.

Syntax
      DoCmd.TransferSQLDatabase(Server, Database, UseTrustedConnection,
                                Login, Password, TransferCopyData)

Key
   Server       The name of the SQL Server to which the database will be transferred.

   Database     The name of the new database on the specified server.

   UseTrustedConnection
                True if the current connection is using a login with system
                administrator privileges. If this argument is not True, you
                must specify a login and password.

   Login        The name of a login on the destination server with
                system administrator privileges.
                If UseTrustedConnection is True, this argument is ignored.

  Password      The password for the login specified in Login.

  TransferCopyData
                True if all data in the database is to be transferred to the
                destination. If this argument is not True, only the
                database schema will be transferred.

TransferSQLDatabase requires that the user has system administrator login rights on the destination server.

The destination database cannot already exist on the destination server.

There are many other methods of transferring databases between SQL Server instances, (Import/Export and SSIS) these tools may be a better choice if you want the data moves to be scripted as an overnight batch job.

Example

'Transfer the current SQL Server database to a new SQL Server database called SS64 on the server Server64. The data is copied along with the database schema.
DoCmd.TransferCompleteSQLDatabase Server:="Server64", _
Database:="SS64", UseTrustedConnection:=True, TransferCopyData:=False

“The interesting thing is that there are so few important decisions. You don’t have to go in the ‘right’ direction, you don’t have to enter the ‘right’ business. What you have to do is have made a decision as to what you’re going to do and then you just have to figure out how to succeed at it” ~ Ken Oshman

Related:

TransferDatabase - Import or export data to/from another database.
TransferSpreadsheet - Import or export data to/from a spreadsheet file.


© Copyright SS64.com 1999-2013
Some rights reserved