1. Purpose
When doing SQL Server database maintenance, when our boss asks us to move dozens of gigabytes of data files to other servers and requires minimal downtime, do we have any plans to meet these requirements?
Here we assume that the two machines are not in the same computer room, so that our solution seems more meaningful. If you are so lucky and the two machines are in the same LAN, then congratulations, you can have a lot more The program can do it.
2. Analysis and design ideas
In fact, the environment we assume has two characteristics: the first is that the database file is relatively large; the second is that our file transfer speed may be relatively slow. Maybe there is nothing we can do about the transmission speed, but we can start from the problem of file size and combine it with the characteristics of SQL Server, so that we have the following solution.
In order to minimize the downtime, we use full backup and differential backup to migrate the database. During the day, make a full backup (XXX_full.bak) of the database that needs to be migrated, and copy the backup file (you can use FTP software here) Perform breakpoint resuming) to the target server for restoration, wait until after working hours and perform a differential backup (XXX_diff.bak), then copy this differential backup to the target server, and perform differential restore based on the complete restore.
The downtime here = differential backup time + transfer differential backup file time + restore differential backup file time. Does this downtime make you feel that this time is very short?
3. Reference script
Pay attention to modifying the name of the database in the script below, as well as the absolute path.
--1: full backup
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
BACKUP DATABASE ['+@dbname+']
TO DISK = ''D:DBBackup'+@dbname+'_full.bak''
WITH NOFORMAT, NOINIT, NAME = '''+@dbname+' -Full database backup'',
SKip, NOREWIND, NOUNLOAD, STATS = 10
GO'
PRint @sql
--Generated SQL
--DataBaseName_full
BACKUP DATABASE [DataBaseName]
TO DISK = 'D:DBBackupDataBaseName_full.bak'
WITH NOFORMAT, NOINIT, NAME = 'DataBaseName-full database backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--2: Full backup and restore
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
--RESTORE '+@dbname+'_full
RESTORE DATABASE ['+@dbname+']
FROM DISK = ''D:DBBackup'+@dbname+'_full.bak'' WITH FILE = 1,
MOVE N''DataBase_Name'' TO N''D:DataBase'+@dbname+'.mdf'',
MOVE N''DataBase_Name_log'' TO N''D:DataBase'+@dbname+'_log.ldf'',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO'
print@sql
--Generated SQL
--RESTORE DataBaseName_full
RESTORE DATABASE [DataBaseName]
FROM DISK = 'D:DBBackupDataBaseName_full.bak' WITH FILE = 1,
MOVE N'DataBase_Name' TO N'D:DataBaseDataBaseName.mdf',
MOVE N'DataBase_Name_log' TO N'D:DataBaseDataBaseName_log.ldf',
NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
GO
--3: Differential backup
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
BACKUP DATABASE ['+@dbname+']
TO DISK = N''D:DBBackup'+@dbname+'_diff.bak''
WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'''+@dbname+' -Differential database backup'',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
'
print @sql
--Generated SQL
--DataBaseName_diff
BACKUP DATABASE [DataBaseName]
TO DISK = N'D:DBBackupDataBaseName_diff.bak'
WITH DIFFERENTIAL, NOFORMAT, NOINIT, NAME = N'DataBaseName-differential database backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
--4: Differential backup and restore
declare @dbname varchar(100)
declare @sql nvarchar(max)
set @dbname = 'DataBaseName'
set @sql = '
--RESTORE '+@dbname+'_full
RESTORE DATABASE ['+@dbname+']
FROM DISK = ''D:DBBackup'+@dbname+'_diff.bak'' WITH FILE = 1,
NOUNLOAD, STATS = 10
GO'
print @sql
--Generated SQL
--RESTORE DataBaseName_full
RESTORE DATABASE [DataBaseName]
FROM DISK = 'D:DBBackupDataBaseName_diff.bak' WITH FILE = 1,
NOUNLOAD, STATS = 10
GO
4. Postscript
Maybe it should be over here, but it often backfires. Sometimes the size of our database file is not tens of gigabytes, so what should we do? Are there any other solutions?
I have moved a 700G data file before, but I was given enough time to move it. I moved the data to the new server through the database job. The advantage of this is to optimize the previous database, such as Set database parameters, such as table partitions, and migrate data with as little impact as possible on the previous database. I’ll write the detailed process next time.
(Author: Listen to the Wind and Rain Source: http://gaizai.cnblogs.com/ )