Full SQL Server database migration

Method 1:
1. Stop the source database and copy the two physical files (MDF and log) of the database to the destination.
2. Open the Enterprise Manager of the target SQL Server database and append the database file as a local database. However, you must change the database name to a different name, such as xxbak.
3. Create an empty database with the same name as the source database and use the default value.
4. Create the actual login name of the database in "security"-"login", enter the access password, select the corresponding database in "database access", and check the "db_owner" option.
Because the login name of the database cannot be modified after the database is attached, the login name corresponding to the database name is often empty, so that the tables in the database cannot be accessed.
5. export data from the attached backup database to the new empty database:
After selecting the source data source and target data source, next select "copy objects and data between SQL Server databases". This is very important. Do not select the default "Copy tables and views from source database ", in this way, data will not be copied.

___________________________________________________________________
Method 2:
1. Select the database to be transferred in the SQL Server Enterprise Manager, right-click it, and select all tasks-> back up the database.
Backup and database selection-complete,
Purpose -- back up to -- click Add
File Name-enter a custom backup database file name under the hard disk of the SQL Server server (the suffix is generally BAK)
Rewrite -- select to override existing media
Click OK.
If the generated backup database file is larger than 1 MB, compress it with a compression tool before transmission to the Internet.

2. If the target SQL Server database does not exist, create a new database first;
Select the newly created database, right-click it, and select all tasks-> Restore database
Restore-> from device-> select device-> disk-> Add (find the name of the backup database file to be imported)-> OK
Restore backup set-> database-complete
Click OK. The database has been imported successfully.
(If you restore a database on an existing SQL Server database, you may encounter another user using it and the restoration operation fails, choose "manage"> "current activity"> "Lock/object"> "find the lock process number in the Database"> use the kill process number in the query analyzer to kill the lock, and then restore)

3. The recovered database data should be complete, but the user name access may be abnormal.