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