Backup and Restore DB in multiple channels

Before taking the backup please check the database size

CREATE TABLE #databases

(

DATABASE_NAME VARCHAR(50),

DATABASE_SIZE FLOAT,

REMARKS VARCHAR(100)

)

INSERT #Databases EXEC ('EXEC sp_databases');

SELECT @@SERVERNAME AS SERVER_NAME,

       DATABASE_NAME,

       SYSMFM.source_file_name_main,

       SYSMFM.physical_name_main,

       SYSMFL.source_file_name_log,

       SYSMFL.physical_name_log,

       DATABASE_SIZE AS '(KB)',

       ROUND(DATABASE_SIZE / 1024, 2) AS '(MB)',

       ROUND((DATABASE_SIZE / 1024) / 1024, 2) AS '(GB)',

       SYSDB.compatibility_level,

       SYSDB.create_date,

       SYSDB.database_id,

       SYSDB.collation_name,

       SYSDB.recovery_model,

       SYSDB.recovery_model_desc,

       SYSDB.user_access,

       SYSDB.user_access_desc,

       SYSDB.state,

       SYSDB.state_desc

  FROM #databases AS DB

       INNER JOIN sys.databases AS SYSDB ON DB.DATABASE_NAME = SYSDB.name

       INNER JOIN (SELECT database_id,

                          name AS source_file_name_main,

                          physical_name AS physical_name_main

                     FROM sys.master_files AS SYSMF

                    WHERE SYSMF.file_id = 1) AS SYSMFM ON SYSMFM.database_id = SYSDB.database_id

       INNER JOIN (SELECT database_id,

                          name AS source_file_name_log,

                          physical_name AS physical_name_log

                     FROM sys.master_files AS SYSMF

                    WHERE SYSMF.file_id = 2) AS SYSMFL ON SYSMFL.database_id = SYSDB.database_id

WHERE SYSDB.database_id = DB_ID('Hallengren')

 ORDER BY DATABASE_SIZE desc;

DROP TABLE #databases;

Before taking the backup please check the backup size:

USE Hallengren

GO

EXEC sp_spaceused @updateusage = N'TRUE'

GO

The reserved one is the closely to the backup size

Then take the actual backup and restore it in the other site:

-- Source –

-- Backup the database in multi channel with multi pieces

BACKUP DATABASE Hallengren TO

DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file1.bak',

DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file2.bak',

DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file3.bak',

DISK = 'C:\SQL-Backup\Adhoc\Hallengren_file4.bak'

WITH INIT , NOUNLOAD , NAME = 'Hallengren backup', NOSKIP , STATS = 10, NOFORMAT

-- Destination --

-- Create the empty database with rightly located mdf, ldf and ndfs

-- Then restore the database with below command

RESTORE DATABASE Hallengren FROM

DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file1.bak'

,DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file2.bak'

,DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file3.bak'

,DISK = 'C:\SQL-Backup\Adhoc\SQL003\Hallengren_file4.bak'

WITH REPLACE,

MOVE 'Hallengren' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL003\MSSQL\DATA\Hallengren.mdf',

MOVE 'Hallengren_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL11.SQL003\MSSQL\DATA\Hallengren_log.ldf'

GO