SQL Server database minimal downtime migration Solution

Source: Internet
Author: User

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

 
 
  1. Declare @ dbname varchar (100)
  2.  
  3. Declare @ SQL nvarchar (max)
  4.  
  5. Set @ dbname = 'databasename'
  6.  
  7. Set @ SQL ='
  8.  
  9. -- '+ @ Dbname +' _ full
  10.  
  11. Backup database ['+ @ dbname +']
  12.  
  13. To disk = 'd: \ DBBackup \ '+ @ dbname +' _ full. Bak''
  14.  
  15. With noformat, NOINIT, NAME = ''' + @ dbname + '-full database backup '',
  16.  
  17. SKIP, NOREWIND, NOUNLOAD, STATS = 10
  18.  
  19. GO'
  20.  
  21. Print @ SQL

-- Generated SQL

 
 
  1. -- DataBaseName_full
  2.  
  3. Backup database [DataBaseName]
  4.  
  5. To disk = 'd: \ DBBackup \ DataBaseName_full.bak'
  6.  
  7. With noformat, NOINIT, NAME = 'databasename-full database backup ',
  8.  
  9. SKIP, NOREWIND, NOUNLOAD, STATS = 10
  10.  
  11. GO

-- 2: Full Backup Recovery

 
 
  1. declare @dbname varchar(100)  
  2.  
  3. declare @sql nvarchar(max)  
  4.  
  5. set @dbname = 'DataBaseName' 
  6.  
  7. set @sql = '  
  8.  
  9. --RESTORE '+@dbname+'_full  
  10.  
  11. RESTORE DATABASE ['+@dbname+']   
  12.  
  13. FROM DISK = ''D:\DBBackup\'+@dbname+'_full.bak'' WITH FILE = 1,   
  14.  
  15. MOVE N''DataBase_Name'' TO N''D:\DataBase\'+@dbname+'.mdf'',   
  16.  
  17. MOVE N''DataBase_Name_log'' TO N''D:\DataBase\'+@dbname+'_log.ldf'',   
  18.  
  19. NORECOVERY, NOUNLOAD, REPLACE, STATS = 10  
  20.  
  21. GO'  
  22.  
  23. print @sql 

-- Generated SQL

 
 
  1. --RESTORE DataBaseName_full  
  2.  
  3. RESTORE DATABASE [DataBaseName]   
  4.  
  5. FROM DISK = 'D:\DBBackup\DataBaseName_full.bak' WITH FILE = 1,   
  6.  
  7. MOVE N'DataBase_Name' TO N'D:\DataBase\DataBaseName.mdf',   
  8.  
  9. MOVE N'DataBase_Name_log' TO N'D:\DataBase\DataBaseName_log.ldf',   
  10.  
  11. NORECOVERY, NOUNLOAD, REPLACE, STATS = 10  
  12.  
  13. GO 

-- 3: Differential backup

 
 
  1. Declare @ dbname varchar (100)
  2.  
  3. Declare @ SQL nvarchar (max)
  4.  
  5. Set @ dbname = 'databasename'
  6.  
  7. Set @ SQL ='
  8.  
  9. -- '+ @ Dbname +' _ diff
  10.  
  11. Backup database ['+ @ dbname +']
  12.  
  13. To disk = n' D: \ DBBackup \ '+ @ dbname +' _ diff. Bak''
  14.  
  15. With differential, NOFORMAT, NOINIT, NAME = n''' + @ dbname + '-DIFFERENTIAL Database Backup '',
  16.  
  17. SKIP, NOREWIND, NOUNLOAD, STATS = 10
  18.  
  19. GO
  20.  
  21. '
  22.  
  23. Print @ SQL

-- Generated SQL

 
 
  1. -- DataBaseName_diff
  2.  
  3. Backup database [DataBaseName]
  4.  
  5. To disk = n'd: \ DBBackup \ DataBaseName_diff.bak'
  6.  
  7. With differential, NOFORMAT, NOINIT, NAME = n' DataBaseName-DIFFERENTIAL Database Backup ',
  8.  
  9. SKIP, NOREWIND, NOUNLOAD, STATS = 10
  10.  
  11. GO

-- 4: Differential Backup Recovery

 
 
  1. declare @dbname varchar(100)  
  2.  
  3. declare @sql nvarchar(max)  
  4.  
  5. set @dbname = 'DataBaseName' 
  6.  
  7. set @sql = '  
  8.  
  9. --RESTORE '+@dbname+'_full  
  10.  
  11. RESTORE DATABASE ['+@dbname+']   
  12.  
  13. FROM DISK = ''D:\DBBackup\'+@dbname+'_diff.bak'' WITH FILE = 1,   
  14.  
  15. NOUNLOAD, STATS = 10  
  16.  
  17. GO'  
  18.  
  19. print @sql 

-- Generated SQL

 
 
  1. --RESTORE DataBaseName_full  
  2.  
  3. RESTORE DATABASE [DataBaseName]   
  4.  
  5. FROM DISK = 'D:\DBBackup\DataBaseName_diff.bak' WITH FILE = 1,   
  6.  
  7. NOUNLOAD, STATS = 10  
  8.  
  9. 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]

Related Article

Contact Us

The content source of this page is from Internet, which doesn't represent Alibaba Cloud's opinion; products and services mentioned on that page don't have any relationship with Alibaba Cloud. If the content of the page makes you feel confusing, please write us an email, we will handle the problem within 5 days after receiving your email.

If you find any instances of plagiarism from the community, please send an email to: info-contact@alibabacloud.com and provide relevant evidence. A staff member will contact you within 5 working days.

A Free Trial That Lets You Build Big!

Start building with 50+ products and up to 12 months usage for Elastic Compute Service

  • Sales Support

    1 on 1 presale consultation

  • After-Sales Support

    24/7 Technical Support 6 Free Tickets per Quarter Faster Response

  • Alibaba Cloud offers highly flexible support services tailored to meet your exact needs.