MSSQL Backup and Data migration

Version: MSSQL 2008

Backup scenario: SQL migration from a server to Server B, and the data is also migrated past.

Operating environment: A server: WINDOWS7 B Server: WINDOWS8.1 auxiliary tool: VNC

First, backup from a server:

OK, the backup is complete. Download file to b server via VNC's transfer files

After downloading, start the backup and restore database on Server B:

Backup may encounter the following issues:

1. "The database in the backup set is different from the existing database",

This problem is typically caused by a path or database version issue

Workaround: Use the following SQL script to perform the overwrite action directly in MSSQL of the destination server you need to back up

1  2  RESTORE DATABASEData_center3  from DISK = 'D:\Backup\DATA_CENTER.bak'   --bak file path4  with Replace, 5MOVE'Data_center'  to 'C:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\data_center.mdf',--MDF file path6MOVE'Data_center_log'  to 'C:\Program Files\Microsoft SQL Server\mssql12. Mssqlserver\mssql\data\data_center.ldf'   --ldf file path7  

After successful execution, at this time the database has a table structure, and then follow the system to restore the database again, you can.

2. In the process of re-executing 1, "Cannot gain exclusive access to the database because the database is in use"

Execute the following sql:

1 ALTER DATABASE [datebase] SET  with ROLLBACK IMMEDIATE

Then execute 1 of the SQL script, and finally you can execute the

ALTER  Database  [  datebase]  Set   Online  

In this way, the backup task is complete.

MSSQL Backup and Data migration