[SQL Server] copying database tasks

Suppose you want to test the data in the production environment, such as modifying and testing the stored procedure, changing and optimizing the index. However, when you connect to the database, you cannot disconnect the database. How can I obtain database copies?

1. Use the copy database menu in the database task

The example is to copy the northwind database from the default instance MSSQLServer to the named instance mysqlserver. Enable the Service as follows:

Figure 1

Next, open the northwind database-> task-> copy Database

Figure 2

Open the database copy wizard-> next, select the source server and target server, and require that the target server (mysqlserver) must run the SQL Server proxy; otherwise, a prompt will appear!

There are two ways to copy a database:

Figure 3

After completing the settings, click Finish in the last step. The following error occurs:

Figure 4

Why? In fact, the permission for the proxy service on your target server (mysqlserver here) is too low. Figure 3 shows a prompt. Change the proxy service permission to the Service Startup account LocalSystem (the Integration Services Service is automatically started during this process)

Figure 5

Click Finish.

Figure 6