The topic for this months T-SQL Tuesday #104 (hosted by Bert Wagner Blog/Twitter) is:
“code you’ve written that you would hate to live without.”
I will share with you a simple snippet that I have used over the years and look forward to reading everyone’s code!
The Scenario
Sometimes we need to loop through all the user databases and take some action. For example – moving database files or migrating databases. The basic logic can be used to loop through a list of databases on a SQL instance and perform some kind of operation on them sequentially.
The Implementation
This is not meant to be wildly original or fancy or complex but rather something mundane that I keep using over the years to do my job.
In this example I will show taking databases offline and back online. The 2 components of this are:
- Cursor
- Dynamic SQL
They can be paired well to accomplish many DBA tasks. Sometimes we need to do something row by row instead of set based and also need dynamic SQL for run-time variables. Let us now walk through the code.
We start with taking a look at the databases on our SQL Instance. Take note of the database IDs because we use this later the determine system vs user database.
|
1 2 |
SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases; |
Here is a cursor stub I like to use:
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 |
/* Stub code for creating a fast forward cursor https://msdn.microsoft.com/en-us/library/ms180169.aspx */ /* declare and initialize variables */ DECLARE @variable INT SET @variable = 0; DECLARE cur_DoThings CURSOR FAST_FORWARD READ_ONLY FOR --TODO: select statement for what you want to iterate through OPEN cur_DoThings FETCH NEXT FROM cur_DoThings INTO @variable WHILE @@FETCH_STATUS = 0 BEGIN --TODO: main part - do something based on record in cursor. Logic goes here. FETCH NEXT FROM cur_DoThings INTO @variable END CLOSE cur_DoThings DEALLOCATE cur_DoThings |
Now let’s see it in action. We are going to first look at the debugging prints before executing.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 |
DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX); DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases WHERE database_id > 4 OPEN curDBs FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc WHILE @@FETCH_STATUS = 0 BEGIN PRINT @name + ': ' + CAST(@database_id AS VARCHAR); PRINT 'Taking database offline'; SELECT @sql = 'ALTER DATABASE ' + @name + ' SET OFFLINE'; PRINT @sql; --EXEC sys.sp_executesql @sql; FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc END CLOSE curDBs DEALLOCATE curDBs |
The debugging output looks like this:
Now we un-comment the call to sp_executesql and the work is done. Here is the state of the databases now:
Looking good so far. Now let’s use the same snippet and logic to return the databases to be online.
Running this again shows us which databases are online and offline. Note that the system databases are still online and only the user database have been taken down.
|
1 2 |
SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases; |
This is the snippet of code to bring them back online.
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 |
DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX); DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases WHERE database_id > 4 OPEN curDBs FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc WHILE @@FETCH_STATUS = 0 BEGIN PRINT @name + ': ' + CAST(@database_id AS VARCHAR); PRINT 'Setting multi user mode'; SELECT @sql = 'ALTER DATABASE ' + @name + ' SET MULTI_USER'; PRINT @sql; --EXEC sys.sp_executesql @sql; PRINT 'Setting online'; SELECT @sql = 'ALTER DATABASE ' + @name + ' SET ONLINE'; PRINT @sql; --EXEC sys.sp_executesql @sql; FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc END CLOSE curDBs DEALLOCATE curDBs |
Here is the debugging output from the above – it is the cursor generated dynamic SQL to bring the databases back online.
Once again un-comment the call to sp_executesql and the work is done.
Here is all the code in 1 location:
|
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 |
/************************************************************************************** Set databases offline and then back online Start with setting all the user databases offline Then bring them back online ***************************************************************************************/ USE master; GO /* Take all user databases offline */ SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases; DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX); DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases WHERE database_id > 4 OPEN curDBs FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc WHILE @@FETCH_STATUS = 0 BEGIN PRINT @name + ': ' + CAST(@database_id AS VARCHAR); PRINT 'Taking database offline'; SELECT @sql = 'ALTER DATABASE ' + @name + ' SET OFFLINE'; PRINT @sql; EXEC sys.sp_executesql @sql; FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc END CLOSE curDBs DEALLOCATE curDBs /* Bring user databases back online */ --Set DBs online and multi user SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases; DECLARE @name nvarchar(128), @database_id int, @user_access tinyint, @user_access_desc nvarchar(60), @state tinyint, @state_desc nvarchar(60), @sql NVARCHAR(MAX); DECLARE curDBs CURSOR FAST_FORWARD READ_ONLY FOR SELECT name, database_id, user_access, user_access_desc, [state], state_desc FROM sys.databases WHERE database_id > 4 OPEN curDBs FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc WHILE @@FETCH_STATUS = 0 BEGIN PRINT @name + ': ' + CAST(@database_id AS VARCHAR); PRINT 'Setting multi user mode'; SELECT @sql = 'ALTER DATABASE ' + @name + ' SET MULTI_USER'; PRINT @sql; EXEC sys.sp_executesql @sql; PRINT 'Setting online'; SELECT @sql = 'ALTER DATABASE ' + @name + ' SET ONLINE'; PRINT @sql; EXEC sys.sp_executesql @sql; FETCH NEXT FROM curDBs INTO @name, @database_id, @user_access, @user_access_desc, @state, @state_desc END CLOSE curDBs DEALLOCATE curDBs |
Hopefully this helps you make the job easier! Simply substitute out the dynamic SQL and adjust the cursor definition to do the job you need to do.
Thanks for reading!
If you liked this post then you might also like: Migration – How to Move SQL Server Database Files to a New Location
[…] Jeff Mlakar offers a solution for taking all databases on an instance offline (and then back online) again. […]
[…] can run this programmatically over your databases using the techniques I laid out […]
[…] if you liked this then you might like T-SQL Tuesday #104 – Code You Would Hate to Live Without […]
[…] If you liked this post then you might also like: T-SQL Tuesday #104 – Code You Would Hate to Live Without […]
[…] If you liked this post then you might also like: T-SQL Tuesday #104 – Code You Would Hate to Live Without […]