I. Purpose
When maintaining the SQL Server database, when the boss asks us to move dozens of GB of data files to other servers and requires the minimum downtime, do we have any solutions to meet these requirements?
Here we assume that these two machines are not in one data center, so our solution seems more meaningful. If you are so lucky, the two machines are in the same LAN, congratulations! You can do it in a lot of ways.
Ii. Analysis and Design Ideas
In fact, we assume that the environment has two features: the first is that the database files are relatively large, and the second is that our file transfer speed may be slow. Maybe there is no way to transfer this speed, but we can start from the file size problem, combined with the features of SQL Server, so we have the following solution.
To minimize downtime, we use full backup and differential backup to migrate the database, and perform a full backup of the database to be migrated during the day (XXX_full.bak ), and copy the backup file here, you can use the FTP software for resumable data transfer) to the target server for restoration, and then perform another differential backup XXX_diff.bak after the off-duty time ), copy the differential backup to the target server and perform the differential backup on the basis of the complete restoration.
Downtime = Differential backup time + time for transferring differential backup files + time for restoring differential backup files. Does the downtime make you feel short?
Iii. Reference script
Note that you must modify the Database Name and absolute path in the script below.
-- 1: complete backup
- Declare @ dbname varchar (100)
-
- Declare @ SQL nvarchar (max)
-
- Set @ dbname = 'databasename'
-
- Set @ SQL ='
-
- -- '+ @ Dbname +' _ full
-
- 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: \ DBBackup \ DataBaseName_full.bak'
-
- With noformat, NOINIT, NAME = 'databasename-full database backup ',
-
- SKIP, NOREWIND, NOUNLOAD, STATS = 10
-
- GO
-- 2: Full Backup Recovery
- 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:\DBBackup\DataBaseName_full.bak' WITH FILE = 1,
-
- MOVE N'DataBase_Name' TO N'D:\DataBase\DataBaseName.mdf',
-
- MOVE N'DataBase_Name_log' TO N'D:\DataBase\DataBaseName_log.ldf',
-
- NORECOVERY, NOUNLOAD, REPLACE, STATS = 10
-
- GO
-- 3: Differential backup
- Declare @ dbname varchar (100)
-
- Declare @ SQL nvarchar (max)
-
- Set @ dbname = 'databasename'
-
- Set @ SQL ='
-
- -- '+ @ Dbname +' _ diff
-
- 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: \ DBBackup \ DataBaseName_diff.bak'
-
- With differential, NOFORMAT, NOINIT, NAME = n' DataBaseName-DIFFERENTIAL Database Backup ',
-
- SKIP, NOREWIND, NOUNLOAD, STATS = 10
-
- GO
-- 4: Differential Backup Recovery
- 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:\DBBackup\DataBaseName_diff.bak' WITH FILE = 1,
-
- NOUNLOAD, STATS = 10
-
- GO
Iv. Postscript
It may end here, but it is often counterproductive. Sometimes our database file size is not dozens of GB. What should we do? Are there other solutions?
I have moved GB of data files before, but I have enough time to move the files. I moved the data to the new server through database jobs, the advantage is to optimize the previous database, such as setting database parameters, such as table partitions, and migrate data if the impact on the previous database is as small as possible. The detailed process should be written later.
Edit recommendations]