Changes for page sql-tde

Last modified by Nikhil Singh on 2026/07/03 08:32
<
From version < 7.1 >
edited by Nikhil Singh
on 2025/02/28 11:30
To version < 8.1 >
edited by Nikhil Singh
on 2025/02/28 12:42
>
Change comment: There is no comment for this version

Summary

Details

Page properties
Content
... ... @@ -11,24 +11,29 @@
11 11  
12 12  5 Set encryption on for the database
13 13  
14 --- 6 Create a new credential with the SAS token
14 +
15 +6 Create credential with SAS token
16 +
17 +{{code language="sql"}}
18 +drop credential [https://zagpebslab.blob.core.windows.net/sql-backups]
15 15  CREATE CREDENTIAL [https://zagpebslab.blob.core.windows.net/sql-backups]
16 -WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
17 -SECRET = 'sp=racwdli&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=kBFwlj5S5eqBEE32LM1EFebBY0W94uEFiwOXo3R0yt4%3D';
18 -Go--
20 +WITH IDENTITY='SHARED ACCESS SIGNATURE'
21 +, SECRET = 'sp=racwdli&st=2025-02-25T13:28:42Z&se=2026-02-25T21:28:42Z&spr=https&sv=2022-11-02&sr=c&sig=kBFwlj5S5eqBEE32LM1EFebBY0W94uEFiwOXo3R0yt4%3D'
22 +GO
23 +
24 +{{/code}}
19 19  
20 ---7 --
21 21  
27 +7
28 +
22 22  {{code language="sql"}}
23 -Perform the database backup with the provided parameters
24 24  EXECUTE dba.dbo.DatabaseBackup
25 -    @Databases = 'dba',                           Replace with your database name
26 -    @URL = 'https://zagpebslab.blob.core.windows.net/sql-backups',   Azure Blob Storage URL
27 -    @BackupType = 'Full',                         Full backup
28 -    @CopyOnly = 'Y',                              Copy-only backup to avoid breaking backup chain
29 -    @Compress = 'Y',                              Compress the backup
30 -    @Verify = 'N';                                No verification of backup
31 -GO
31 + @Databases = @DatabaseName,
32 + @URL = @BackupContainerURL,
33 + @BackupType = 'Full',
34 + @CopyOnly = 'Y',
35 + @Compress = 'Y',
36 + @Verify = 'N';
32 32  {{/code}}
33 33  
34 34  The backup was unable to be done from smss to azure ( the MI does not support encrypted DBs to be backed up from smss to azure)
... ... @@ -68,7 +68,7 @@
68 68  [[image:image-20250228120622-1.png||height="236" width="542"]]
69 69  
70 70  
71 -===== 2 Enable TDE =====
76 +===== 2 Enable TDE =====
72 72  
73 73  
74 74  - Go to your Managed instance ( sqlmi-ebs-lab)
... ... @@ -84,7 +84,7 @@
84 84  -Click save
85 85  
86 86  
87 -[[image:image-20250228122106-2.png||height="251" width="511"]]
92 +[[image:image-20250228122106-2.png||height="19" width="241"]]
88 88  
89 89  
90 90  - TDE has now been enabled on the Managed instance
... ... @@ -96,16 +96,224 @@
96 96  
97 97  
98 98  
104 +=== Backup specific databases using SQL server agent jobs ===
99 99  
100 100  
107 +we are creating a job to automatically backup only the DBA databases in the instance via the SQL server agent.
101 101  
109 +The backup will be done daily at 3am. The backups will be backed up in the Azure storage account.
102 102  
103 103  
112 +1 Create the script
104 104  
105 105  
115 +=== **1. Declaring Variables** ===
106 106  
117 +{{code language="sql"}}
118 +DECLARE @DatabaseName NVARCHAR(128)
119 +DECLARE @BackupContainerURL NVARCHAR(512)
120 +{{/code}}
107 107  
122 +DECLARE @DatabaseName NVARCHAR(128) DECLARE @BackupContainerURL NVARCHAR(512)
108 108  
124 +* **@DatabaseName**: A variable to hold the name of the database during each iteration of the loop.
125 +* **@BackupContainerURL**: A variable to store the URL of the Azure Blob Storage container where the database backups will be stored.
126 +
127 +----
128 +
129 +=== **2. Setting the Azure Blob Storage URL** ===
130 +
131 +{{code language="sql"}}
132 +SET @BackupContainerURL = 'https://<your_storage_account>.blob.core.windows.net/sql-backups/'
133 +{{/code}}
134 +
135 +
136 +----
137 +
138 +=== **3. Declaring the Cursor** ===
139 +
140 +
141 +{{code language="sql"}}
142 +DECLARE db_cursor CURSOR FOR
143 +SELECT name
144 +FROM sys.databases
145 +WHERE name LIKE 'dba%' -- Only pick databases that start with 'dba'
146 +{{/code}}
147 +
148 +* **Cursor Declaration**:
149 +** The cursor db_cursor is declared to loop through the list of databases in the sys.databases system view.
150 +** The **WHERE name LIKE 'dba%'** clause ensures that only databases with names starting with dba will be processed. For example, databases like dbaTest, dbaProd, etc., will be included in the loop.
151 +** **sys.databases**: A system catalog view that contains information about each database in the SQL Server instance.
152 +
153 +----
154 +
155 +=== **4. Opening the Cursor** ===
156 +
157 +{{code language="sql"}}
158 +OPEN db_cursor
159 +FETCH NEXT FROM db_cursor INTO @DatabaseName
160 +{{/code}}
161 +
162 +OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
163 +
164 +* **OPEN db_cursor**: This opens the cursor for reading the results.
165 +* **FETCH NEXT**: The FETCH NEXT statement retrieves the first database name that meets the LIKE 'dba%' condition and stores it in the @DatabaseName variable. This will be used in the next step where the backup procedure is executed.
166 +
167 +----
168 +
169 +=== **5. Looping Through Databases** ===
170 +
171 +{{code language="sql"}}
172 +-- Loop through each database and execute the stored procedure
173 +WHILE @@FETCH_STATUS = 0
174 +BEGIN
175 + -- Print current database for logging/debugging
176 + PRINT 'Backing up database: ' + @DatabaseName
177 +
178 + -- Execute the DatabaseBackup stored procedure for each database
179 + EXECUTE dba.dbo.DatabaseBackup
180 + @Databases = @DatabaseName, -- Specify the current database
181 + @URL = @BackupContainerURL, -- Azure Blob Storage URL
182 + @BackupType = 'Full', -- Full backup type
183 + @CopyOnly = 'Y', -- Use copy-only backup (this doesn’t affect the transaction log chain)
184 + @Compress = 'Y', -- Enable compression
185 + @Verify = 'N'; -- Skip verification after backup
186 +
187 +{{/code}}
188 +
189 +
190 +* **WHILE @@FETCH_STATUS = 0**: The loop will continue as long as the FETCH command successfully retrieves a database name. @@FETCH_STATUS is a system function that returns 0 if the fetch operation is successful.
191 +* **PRINT**: This line prints the name of the database that is currently being backed up. It helps with logging or debugging purposes, as you can track which database is being processed at any given time.
192 +* (((
193 +**EXECUTE dba.dbo.DatabaseBackup**:
194 +
195 +* This calls a stored procedure named dba.dbo.DatabaseBackup for each database.
196 +* The parameters passed to the stored procedure:
197 +** **@Databases = @DatabaseName**: Specifies which database to back up (the current database being processed).
198 +** **@URL = @BackupContainerURL**: Specifies the destination Azure Blob Storage URL.
199 +** **@BackupType = 'Full'**: Specifies the backup type, which is a **Full** backup (this means all data in the database is backed up).
200 +** **@CopyOnly = 'Y'**: Specifies that this is a **copy-only backup**. This ensures that the backup does not affect the transaction log chain and does not interfere with regular backups.
201 +** **@Compress = 'Y'**: Enables compression of the backup, reducing the size of the backup file.
202 +** **@Verify = 'N'**: Specifies that the backup verification step should be skipped. (Verification can be added if needed for additional safety.)
203 +)))
204 +
205 +----
206 +
207 +=== **6. Fetch the Next Database** ===
208 +
209 +{{code language="sql"}}
210 + -- Fetch the next database in the cursor
211 + FETCH NEXT FROM db_cursor INTO @DatabaseName
212 +
213 +{{/code}}
214 +
215 +~-~- Fetch the next database in the cursor FETCH NEXT FROM db_cursor INTO @DatabaseName
216 +
217 +* **FETCH NEXT**: After executing the backup for the current database, this statement retrieves the next database from the cursor and stores it in the @DatabaseName variable. The loop will continue until all matching databases have been processed.
218 +
219 +----
220 +
221 +=== **7. Closing and Deallocating the Cursor** ===
222 +
223 +{{code language="sql"}}
224 +-- Close and deallocate the cursor to clean up resources
225 +CLOSE db_cursor
226 +DEALLOCATE db_cursor
227 +
228 +
229 +{{/code}}
230 +
231 +* **CLOSE db_cursor**: This closes the cursor once the loop finishes processing all databases.
232 +* **DEALLOCATE db_cursor**: This deallocates the cursor, freeing up any resources used by the cursor. It’s a good practice to always deallocate cursors to avoid resource leaks.
233 +
234 +
235 +===== 8. Full Script =====
236 +
237 +
238 +{{code language="sql"}}
239 +DECLARE @DatabaseName NVARCHAR(128)
240 +DECLARE @BackupContainerURL NVARCHAR(512)
241 +
242 +SET @BackupContainerURL = 'https://zagpebslab.blob.core.windows.net/sql-backups'
243 +
244 +DECLARE db_cursor CURSOR FOR
245 +SELECT name
246 +FROM sys.databases
247 +WHERE name LIKE 'dba%'
248 +
249 +OPEN db_cursor
250 +FETCH NEXT FROM db_cursor INTO @DatabaseName
251 +
252 +WHILE @@FETCH_STATUS = 0
253 +BEGIN
254 +
255 +PRINT 'Backing up database: ' + @DatabaseName
256 +
257 +EXECUTE dba.dbo.DatabaseBackup
258 + @Databases = @DatabaseName,
259 + @URL = @BackupContainerURL,
260 + @BackupType = 'Full',
261 + @CopyOnly = 'Y',
262 + @Compress = 'Y',
263 + @Verify = 'N';
264 +
265 +FETCH NEXT FROM db_cursor INTO @DatabaseName
266 +END
267 +
268 +CLOSE db_cursor
269 +DEALLOCATE db_cursor
270 +
271 +{{/code}}
272 +
273 +
274 +=== 2. Set up a new job ===
275 +
276 +
277 +- Go to SSMS and click on SQL Server Agent
278 +
279 + Drop down menu and left click jobs and select new job
280 +
281 +
282 +====== General: ======
283 +
284 +- Enter Job name (DBA databases backup)
285 +
286 +-  Owner (ebssqladmin)
287 +
288 +-  Category (Database maintenance)
289 +
290 + - Description (Description of the job)
291 +
292 +
293 +====== Steps: Create the steps for the job to follow ======
294 +
295 + - Step name (Backup only DBA database)
296 +
297 + - Type (Transact-SQL script)
298 +
299 + - Database (master)
300 +
301 + - Command (paste the script we created)
302 +
303 +
304 + Schedule: Create a schedule for the job to run
305 +
306 + - Name (DBA database backup)
307 +
308 + - Schedule Type (recurring)
309 +
310 + - Frequency (Occurs: Daily)
311 +
312 + (Recurs every: 1 day(s))
313 +
314 +
315 +
316 +
317 +
318 +
319 +
320 +
321 +
109 109  1 CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'YourStrongPasswordHere!';
110 110  GO
111 111  

Applications

Navigation

Need help?

If you need help with XWiki you can contact: