En este video tutorial de mi canal de Youtube te voy a mostrar cómo podes hacer para disminuir los tiempos que se consumen al hacer un Backup de tus bases de datos SQL
Código de ejemplo usado
DROP TABLE IF EXISTS #TUNING_BACKUP
CREATE TABLE #TUNING_BACKUP (ID INT IDENTITY PRIMARY KEY,
TEST VARCHAR(300),
TIME_SEC FLOAT)
GO
DECLARE @STARTTIME DATETIME = GETDATE()
BACKUP DATABASE [StackOverflow2010]
TO DISK =N'F:\TMP\BKP_STACK.bak' WITH NO_COMPRESSION,INIT,format
DECLARE @FINISHTIME FLOAT = DATEDIFF(SECOND,@STARTTIME,GETDATE())
INSERT INTO #TUNING_BACKUP (TEST,TIME_SEC)
VALUES ('BKP COMUN',@FINISHTIME )
go
DECLARE @STARTTIME DATETIME = GETDATE()
BACKUP DATABASE [StackOverflow2010]
TO DISK =N'F:\TMP\BKP_STACK.bak',
DISK =N'F:\TMP\BKP_STACK2.bak',
DISK =N'F:\TMP\BKP_STACK3.bak',
DISK =N'F:\TMP\BKP_STACK4.bak',
DISK =N'e:\TMP\BKP_STACK5.bak',
DISK =N'e:\TMP\BKP_STACK6.bak',
DISK =N'e:\TMP\BKP_STACK7.bak',
DISK =N'e:\TMP\BKP_STACK8.bak'
WITH NO_COMPRESSION,INIT,FORMAT
DECLARE @FINISHTIME FLOAT = DATEDIFF(SECOND,@STARTTIME,GETDATE())
INSERT INTO #TUNING_BACKUP (TEST,TIME_SEC)
VALUES ('BKP MULTIPLE FILES',@FINISHTIME )
go
DECLARE @STARTTIME DATETIME = GETDATE()
BACKUP DATABASE [StackOverflow2010]
TO DISK =N'F:\TMP\BKP_STACK.bak',
DISK =N'F:\TMP\BKP_STACK2.bak',
DISK =N'F:\TMP\BKP_STACK3.bak',
DISK =N'F:\TMP\BKP_STACK4.bak',
DISK =N'e:\TMP\BKP_STACK5.bak',
DISK =N'e:\TMP\BKP_STACK6.bak',
DISK =N'e:\TMP\BKP_STACK7.bak',
DISK =N'e:\TMP\BKP_STACK8.bak'
WITH COMPRESSION,INIT,FORMAT,
BUFFERCOUNT = 50, BLOCKSIZE = 65536
DECLARE @FINISHTIME FLOAT = DATEDIFF(SECOND,@STARTTIME,GETDATE())
INSERT INTO #TUNING_BACKUP (TEST,TIME_SEC)
VALUES ('BKP MULTIPLE FILES, COMPRESSION,BUFFERCOUNT,MAXTRANSFER',@FINISHTIME )
go
select * from #TUNING_BACKUP
Resultado de las pruebas
