Skrypt generujący polecenia backup dla całego serwera SQL

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;

100

101

102

103

104

105

106

107

108

109

110

111

112

113

114

115

116

117

118

119

120

121

122

123

124

125

126

127

128

129

130

131

132

133

134

135

136

137

138

139

140

141

142

143

144

145

146

147

148

149

150

151

152

153

154

155

156

157

158

159

160

161

162

163

164

165

166

167

168

169

170

171

172

173

174

175

176

177

178

179

180

181

182

183

184

185

186

187

188

189

190

191

192

193

194

195

196

197

198

199

200

201

202

203

204

205

206

207

208

209

210

211

212

213

214

215

216

217

218

219

220

221

222

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

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

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

me -v --db mssql &>> blog

BI subiektywnie i bez cenzury...

Skrypt generujący polecenia backup dla całego serwera SQL

ZAŁOŻENIA:

SKRYPT:

PRZYKŁADOWY OUTPUT:

SKRYPT PIERWOTNIE OPUBLIKOWANY JAKO TSQL na dziś #41

Dodaj komentarz Anuluj pisanie odpowiedzi