Site icon IT Tutorial

SQL Server Parallel Backup Restore -2

Hi,

You need parallel backup and restore in SQL Server to do fastly.

 

I have used file sharing from remote server and I have restored SQL Server from backup file sharing of production server.

If you don’t want to transfer backup files to the test environment, or you don’t have free space on test environment then you can use this method.

You can restore SQL Server database  8 parallel with following script. Change database name and path according to your environment.

 

USE [master]
RESTORE DATABASE [DEVECI] FROM  
DISK = N'\\192.168.63.63\z$\Full_01.bak',  
DISK = N'\\192.168.63.63\z$\Full_02.bak',  
DISK = N'\\192.168.63.63\z$\Full_03.bak',  
DISK = N'\\192.168.63.63\z$\Full_04.bak',  
DISK = N'\\192.168.63.63\z$\Full_05.bak',  
DISK = N'\\192.168.63.63\z$\Full_06.bak',  
DISK = N'\\192.168.63.63\z$\Full_07.bak',  
DISK = N'\\192.168.63.63\z$\Full_08.bak' 
WITH  FILE = 1,  MOVE N'DEVECI' TO N'L:\OLAP_DATA1\SQLDATA\DEVECI.mdf',  MOVE N'DEVECI_2' TO N'L:\OLAP_DATA2\SQLDATA\DEVECI_2.ndf',  MOVE N'DEVECI_log' TO N'L:\OLAP_LOG1\SQLLOG\DEVECI_1.ldf',  MOVE N'DEVECI_log_2' TO N'L:\OLAP_LOG2\SQLLOG\DEVECI_2.ldf',  NORECOVERY,  NOUNLOAD,  STATS = 5
GO

 

or If you transfered backup files, you can restore SQL Server database 8 parallel with following query.

 

USE [master]
RESTORE DATABASE [DEVECI] FROM 
DISK = N'Z:\Full_01.bak', 
DISK = N'Z:\Full_02.bak', 
DISK = N'Z:\Full_03.bak', 
DISK = N'Z:\Full_04.bak', 
DISK = N'Z:\Full_05.bak', 
DISK = N'Z:\Full_06.bak', 
DISK = N'Z:\Full_07.bak', 
DISK = N'Z:\Full_08.bak'
WITH FILE = 1, MOVE N'DEVECI' TO N'L:\OLAP_DATA1\SQLDATA\DEVECI.mdf', MOVE N'DEVECI_2' TO N'L:\OLAP_DATA2\SQLDATA\DEVECI_2.ndf', MOVE N'DEVECI_log' TO N'L:\OLAP_LOG1\SQLLOG\DEVECI_1.ldf', MOVE N'DEVECI_log_2' TO N'L:\OLAP_LOG2\SQLLOG\DEVECI_2.ldf', NORECOVERY, NOUNLOAD, STATS = 5
GO

 

 

 

 

 

Do you want to learn Microsoft SQL Server DBA Tutorials for Beginners, then read the following articles.

https://ittutorial.org/sql-server-tutorials-microsoft-database-for-beginners/

Exit mobile version