-
Notifications
You must be signed in to change notification settings - Fork 2
/
Copy pathADMIN_Set_Database_Recovery_Model_on_All_Databases.sql
146 lines (127 loc) · 5.91 KB
/
ADMIN_Set_Database_Recovery_Model_on_All_Databases.sql
1
2
3
4
5
6
7
8
9
10
11
12
13
14
15
16
17
18
19
20
21
22
23
24
25
26
27
28
29
30
31
32
33
34
35
36
37
38
39
40
41
42
43
44
45
46
47
48
49
50
51
52
53
54
55
56
57
58
59
60
61
62
63
64
65
66
67
68
69
70
71
72
73
74
75
76
77
78
79
80
81
82
83
84
85
86
87
88
89
90
91
92
93
94
95
96
97
98
99
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
/*
-- =============================================================================
ADMIN_Set_Database_Recovery_Model_TEST_Server.sql -
This script modifies the Recovery Model of all user databases
based on the parameter @nvRecMod (we will modify model)
or based on the setting of the model database.
We don't touch master, msdb, tempdb or %tempdb%
Copyright (C) 2024 hot2use / JohnKNess
This program is free software: you can redistribute it and/or modify
it under the terms of the GNU General Public License as published by
the Free Software Foundation, either version 3 of the License, or
(at your option) any later version.
This program is distributed in the hope that it will be useful,
but WITHOUT ANY WARRANTY; without even the implied warranty of
MERCHANTABILITY or FITNESS FOR A PARTICULAR PURPOSE. See the
GNU General Public License for more details.
You should have received a copy of the GNU General Public License
along with this program. If not, see <https://www.gnu.org/licenses/>.
-- =============================================================================
*/
/*
-- =============================================================================
Author......: hot2use / JohnKNess
Date........: 18.06.2024
Version.....: 0.2
Server......: any server
Database....: master
Owner.......: -
Table.......: -
Type........: Script
Name........: ADMIN_Set_Database_Recovery_Model_TEST_Server.sql
Description.: This script modifies the Recovery Model of all user databases
............ based on the parameter @nvRecMod (we will modify model)
............ or based on the setting of the model database.
............
............ We don't touch master, msdb, tempdb or %tempdb%
............
History.....: 18-Jun-2024 0.1 JN First created
............ 19-Jun-2024 0.2 JN Added GNU License and header
............
Editors.....: SQLAssistant (SoftTree Technologies, Inc.)
............ SSMS 19.3
-- =============================================================================
*/
/***************************************************************************************************
* @nvRecMod : The recovery model you want to set for all database
* NULL | '' = use Recovery Model set on model database (DEFAULT)
* SIMPLE = set SIMPLE Recovery Model (includes model database)
* FULL = set FULL Recovery Model (includes model database)
* BULK_LOGGED = set BULK-LOGGED Recovery Model (includes model database)
*
* @NoExec : 0 = change recovery model (DEFAULT)
* (0|1) 1 = do NOT change recovery model, only displays commands
*
* @iDebug : 0 = do NOT debug anything (DEFAULT)
* (BITWISE) 1 = debug basic features / display position markers
* 2 = debug detailed features / display detailed text
* 4 = turn on transactional execution for some statements
*
***************************************************************************************************/
DECLARE @iDebug AS INT;
DECLARE @nvRecMod AS NVARCHAR(20) = N''; -- NULL, SIMPLE, FULL, BULK_LOGGED;
DECLARE @NoExec AS BIT = 0;
DECLARE @dtStarted AS DATETIME = GETDATE();
DECLARE @tblDatabase AS TABLE (dbid INT IDENTITY(1,1), nvDBName NVARCHAR(150), nvRecMod NVARCHAR(20), nvRecModModel NVARCHAR(20), dtStarted DATETIME, dtLogged DATETIME DEFAULT GETDATE());
/*******************************************************************************
* Variables used during execution
*******************************************************************************/
DECLARE @iDBid AS INT;
DECLARE @nvDBName AS NVARCHAR(150);
DECLARE @nvSQL AS NVARCHAR(1000);
/*******************************************************************************
* Code execution.
* No modifications required past this point.
*******************************************************************************/
IF @nvRecMod = '' OR @nvRecMod IS NULL
BEGIN
SELECT @nvRecMod = sdb3.recovery_model_desc FROM sys.databases AS sdb3 WHERE sdb3.name = 'model';
SELECT '@nvRecMod assigned from model database:' + @nvRecMod;
INSERT INTO @tblDatabase (nvDBName, nvRecMod, nvRecModModel, dtStarted, dtLogged)
SELECT sdb.name, sdb.recovery_model_desc, sdb2.recovery_model_desc, @dtStarted, GETDATE()
FROM sys.databases AS sdb
JOIN sys.databases AS sdb2
ON sdb2.recovery_model_desc != sdb.recovery_model_desc
AND sdb2.name = 'model'
WHERE 1=1
AND sdb.name NOT IN ('master','msdb','tempdb','model')
AND LOWER(sdb.name) NOT LIKE '%tempdb%';
END
ELSE
BEGIN
SELECT '@nvRecMod assigned by script input:' + @nvRecMod;
INSERT INTO @tblDatabase (nvDBName, nvRecMod, nvRecModModel, dtStarted, dtLogged)
SELECT sdb.name, sdb.recovery_model_desc, sdb2.recovery_model_desc, @dtStarted, GETDATE()
FROM sys.databases AS sdb
JOIN sys.databases AS sdb2
ON sdb2.name = 'model'
WHERE 1=1
AND sdb.recovery_model_desc != @nvRecMod
AND sdb.name NOT IN ('master','msdb','tempdb')
AND LOWER(sdb.name) NOT LIKE '%tempdb%';
END
SELECT * FROM @tblDatabase;
DECLARE LoopDBs CURSOR FOR
SELECT dbid, nvDBName FROM @tblDatabase WHERE dtStarted = @dtStarted;
OPEN LoopDBs
FETCH NEXT FROM LoopDBs
INTO @iDBid, @nvDBName;
WHILE @@FETCH_STATUS = 0
BEGIN
SET @nvSQL = 'ALTER DATABASE [' + @nvDBName + '] SET RECOVERY ' + @nvRecMod + ' WITH NO_WAIT;'
IF @NoExec = 1
BEGIN
SELECT @nvSQL;
END
ELSE
BEGIN
EXEC sp_executesql @nvSQL;
END
UPDATE @tblDatabase SET nvRecMod = @nvRecMod WHERE dbid = @iDBid;
SELECT * FROM @tblDatabase
FETCH NEXT FROM LoopDBs
INTO @iDBid, @nvDBName;
END
CLOSE LoopDBs;
DEALLOCATE LoopDBs;
DELETE FROM @tblDatabase WHERE dtStarted = @dtStarted;