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 SQL Server Integration Services) these tools might be a better choice if you want the data moves to be scripted as an overnight batch job.

Examples

'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 © 1999-2024 SS64.com
Some rights reserved