Wrzucam skrypcik popełniony już jakiś czas temu. Potrzeba wykonania szybko backupu, wraz z jego zrównolegleniem do kilku plików i unikalną nazwą zaowocowały poniższym kodem.
ZAŁOŻENIA:
- generowanie dla ścieżek sieciowych (lub lokalnych dyskowych) poprzez parametr,
- wygenerować polecenia backup wszystkich baz na serwerze tak by można było je przekopiować i uruchomić (skrypt sam nie uruchamia backupów),
- przyspieszenie wykonywania backupów poprzez zrównoleglenie ich wykonywania do kilku plików na raz (o nazwie *_partX gdzie X to numer pliku) – ilość plików ma być konfigurowalna (jak pokazują testy, backup przez sieć 1Gbit do 6 plików przyspiesza ich robienie ponad dwukrotnie),
- w nazwie plików maja znaleźć się nazwa serwera (i instancji) oraz data wykonania przy czym włączenie ma być parametryzowane,
- skrypt ma również wygenerować backup baz systemowych (bez zrównoleglania) – włączenie przez parametr,
- opcjonalnie konfiguracja polecenia STATS czyli co ile procent SQL ma zwracać status wykonania backupu,
- baza musi być w trybie ONLINE by skrypt ja wziął pod uwagę.
SKRYPT:
DECLARE @CZY_BACKUPOWAC_SYSTEMOWE_BAZY BIT = 1;
--czy zrobic backup baz master/model/msdb?
DECLARE @ZROWNOLEGLENIE_PLIKOW_BAK TINYINT = 6;
--czyli na ile plikow zrownoleglic backup? 6 optymalne w testach empirycznych dla sieci 1Gbit
--bazy systemowe zrzucane sa jednak zawsze dla jednego pliku
DECLARE @SCIEZKA_BACKUPU VARCHAR(MAX) = 'C:\BACKUP\';
--pelna sciezka do udzialu sieciowego w ktorej znajdzie sie backup. Koniecznie z pojedynczym \ na koncu!
--TODO: napisac o koniecznoscui uprawnien
DECLARE @CZY_NAZWAC_BACKUP_OD_INSTANCJI BIT =1;
--czy w nazwie backupu ma byc zawarta rowniez nazwa serwera i instancji
DECLARE @CZY_DODAC_DO_NAZWY_AKTUALNA_DATE_I_CZAS BIT = 1;
--czy w nazwie ma rowniez znajdowac sie data uruchomienia backupu
DECLARE @PARAMETR_STATS_PODCZAS_BACKUPU VARCHAR(3) = '20';
--czyli co ile % ma pokazywać się status z wykonywania backupu
/*
MAIN CODE
*/
DECLARE @SQL VARCHAR(MAX);
DECLARE @RESULTS TABLE
(
id INT IDENTITY
PRIMARY KEY ,
SQLBACKUP VARCHAR(MAX)
);
DECLARE @NOW DATETIME;
DECLARE @NOW_STRING_FILENAME VARCHAR(MAX);
DECLARE @SERVERNAME VARCHAR(MAX) = REPLACE(@@SERVERNAME, '\', '_') + '_';
IF @CZY_BACKUPOWAC_SYSTEMOWE_BAZY = 1
BEGIN
--master
SET @NOW = GETDATE();
SET @NOW_STRING_FILENAME = '_'
+ REPLACE(REPLACE(CONVERT(VARCHAR(MAX), @NOW, 20), ' ', '_'), ':',
'');
SET @SQL = 'BACKUP DATABASE [master] TO DISK = N'''
+ @SCIEZKA_BACKUPU
+ CASE WHEN @CZY_NAZWAC_BACKUP_OD_INSTANCJI = 1 THEN @SERVERNAME
ELSE ''
END + 'master'
+ CASE WHEN @CZY_DODAC_DO_NAZWY_AKTUALNA_DATE_I_CZAS = 1
THEN @NOW_STRING_FILENAME
ELSE ''
END
+ '.bak'' WITH NOFORMAT, NOINIT, NAME = N''master-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = '
+ @PARAMETR_STATS_PODCZAS_BACKUPU;
INSERT INTO @RESULTS
( SQLBACKUP )
VALUES ( @SQL );
--RESET @SQL
SET @SQL = NULL;
--model
SET @NOW = GETDATE();
SET @NOW_STRING_FILENAME = '_'
+ REPLACE(REPLACE(CONVERT(VARCHAR(MAX), @NOW, 20), ' ', '_'), ':',
'');
SET @SQL = 'BACKUP DATABASE [model] TO DISK = N''' + @SCIEZKA_BACKUPU
+ CASE WHEN @CZY_NAZWAC_BACKUP_OD_INSTANCJI = 1 THEN @SERVERNAME
ELSE ''
END + 'model'
+ CASE WHEN @CZY_DODAC_DO_NAZWY_AKTUALNA_DATE_I_CZAS = 1
THEN @NOW_STRING_FILENAME
ELSE ''
END
+ '.bak'' WITH NOFORMAT, NOINIT, NAME = N''model-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = '
+ @PARAMETR_STATS_PODCZAS_BACKUPU;
INSERT INTO @RESULTS
( SQLBACKUP )
VALUES ( @SQL );
--msdb
SET @SQL = 'BACKUP DATABASE [msdb] TO DISK = N''' + @SCIEZKA_BACKUPU
+ CASE WHEN @CZY_NAZWAC_BACKUP_OD_INSTANCJI = 1 THEN @SERVERNAME
ELSE ''
END + 'msdb'
+ CASE WHEN @CZY_DODAC_DO_NAZWY_AKTUALNA_DATE_I_CZAS = 1
THEN @NOW_STRING_FILENAME
ELSE ''
END
+ '.bak'' WITH NOFORMAT, NOINIT, NAME = N''msdb-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = '
+ @PARAMETR_STATS_PODCZAS_BACKUPU;
INSERT INTO @RESULTS
( SQLBACKUP )
VALUES ( @SQL );
END;
--IF @CZY_BACKUPOWAC_SYSTEMOWE_BAZY = 1
DECLARE @DATABASES_TO_BACKUP TABLE
(
dbname sysname PRIMARY KEY
);
DECLARE @currdb sysname;
DECLARE @FriendlyDatabaseFilename VARCHAR(MAX);
DECLARE @i INT;
INSERT INTO @DATABASES_TO_BACKUP
( dbname
)
SELECT name
FROM sys.databases
WHERE name NOT IN ( 'master', 'model', 'msdb', 'tempdb' )
AND source_database_id IS NULL
AND state_desc = 'ONLINE';
WHILE EXISTS ( SELECT TOP 1
1
FROM @DATABASES_TO_BACKUP )
BEGIN
SELECT TOP 1
@currdb = dbname
FROM @DATABASES_TO_BACKUP
ORDER BY dbname;
--trzeba podmienic wystapienia wszelkich zastrzezonych znakow w nazwie bazy jesli zamierzamy jej uzyc w nazwie pliku
SET @FriendlyDatabaseFilename = @currdb;
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '<',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '>',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, ':',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '"',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '/',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '\',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '|',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '?',
'_');
SET @FriendlyDatabaseFilename = REPLACE(@FriendlyDatabaseFilename, '*',
'_');
SET @NOW = GETDATE();
SET @NOW_STRING_FILENAME = '_'
+ REPLACE(REPLACE(CONVERT(VARCHAR(MAX), @NOW, 20), ' ', '_'), ':',
'');
SET @SQL = 'BACKUP DATABASE [' + @currdb + '] TO ';
IF @ZROWNOLEGLENIE_PLIKOW_BAK > 1 --zapisujemy do kilku plikow
BEGIN
SET @i = 0;
WHILE ( @i < @ZROWNOLEGLENIE_PLIKOW_BAK )
BEGIN
SET @SQL += CHAR(13) + CHAR(10)
+ CASE WHEN @i = 0 THEN ''
ELSE ','
END;--nowy wiersz i przecinek jesli to nie pierwszy plik
SET @SQL += 'DISK = N''' + @SCIEZKA_BACKUPU
+ CASE WHEN @CZY_NAZWAC_BACKUP_OD_INSTANCJI = 1
THEN @SERVERNAME
ELSE ''
END + @FriendlyDatabaseFilename
+ CASE WHEN @CZY_DODAC_DO_NAZWY_AKTUALNA_DATE_I_CZAS = 1
THEN @NOW_STRING_FILENAME
ELSE ''
END + '_part' + CAST(@i + 1 AS VARCHAR(MAX))
+ '.bak''';
SET @i += 1;
END;
SET @SQL += CHAR(13) + CHAR(10)
+ ' WITH NOFORMAT, NOINIT, NAME = N''' + @currdb
+ '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = '
+ @PARAMETR_STATS_PODCZAS_BACKUPU;
END;
ELSE
BEGIN
SET @SQL += 'DISK = N''' + @SCIEZKA_BACKUPU
+ CASE WHEN @CZY_NAZWAC_BACKUP_OD_INSTANCJI = 1
THEN @SERVERNAME
ELSE ''
END + @FriendlyDatabaseFilename
+ CASE WHEN @CZY_DODAC_DO_NAZWY_AKTUALNA_DATE_I_CZAS = 1
THEN @NOW_STRING_FILENAME
ELSE ''
END
+ '.bak'' WITH NOFORMAT, NOINIT, NAME = N''model-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = '
+ @PARAMETR_STATS_PODCZAS_BACKUPU;
END;
INSERT INTO @RESULTS
( SQLBACKUP )
VALUES ( @SQL );
DELETE FROM @DATABASES_TO_BACKUP
WHERE dbname = @currdb;
SET @SQL = NULL;
SET @FriendlyDatabaseFilename = NULL;
SET @currdb = NULL;
END;
SELECT *
FROM @RESULTS;
PRZYKŁADOWY OUTPUT:
Dla parametrów identycznych jak w skrypcie powyżej i bazy AdventureWorks2012
BACKUP DATABASE [AdventureWorks2012] TO DISK = N'C:\BACKUP\WINSQL_SQL2016ENT_AdventureWorks2012_2017-03-27_200836_part1.bak' ,DISK = N'C:\BACKUP\WINSQL_SQL2016ENT_AdventureWorks2012_2017-03-27_200836_part2.bak' ,DISK = N'C:\BACKUP\WINSQL_SQL2016ENT_AdventureWorks2012_2017-03-27_200836_part3.bak' ,DISK = N'C:\BACKUP\WINSQL_SQL2016ENT_AdventureWorks2012_2017-03-27_200836_part4.bak' ,DISK = N'C:\BACKUP\WINSQL_SQL2016ENT_AdventureWorks2012_2017-03-27_200836_part5.bak' ,DISK = N'C:\BACKUP\WINSQL_SQL2016ENT_AdventureWorks2012_2017-03-27_200836_part6.bak' WITH NOFORMAT, NOINIT, NAME = N'AdventureWorks2012-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 20
