Execute on a subset of databases

Sometimes it is necessary to execute a command or a statement on a subset of the databases in a instance. A quick solution is to generate the commands in a SELECT on sys.database and then execute the output.

But when this has been done a few times for a recurring task the solution becomes boring and tiresome. And then a more automated solution is required.

Automating the entire statement build and execxution over a cursor and using sp_executesql looks like a nice T-SQL solution.

DECLARE @DatabaseID AS INT;
DECLARE @DatabaseName AS NVARCHAR(50);
 
DECLARE @DatabaseCursor AS CURSOR;
SET @DatabaseCursor = CURSOR LOCAL FAST_FORWARD FOR
  SELECT name, database_id
  FROM sys.databases
  WHERE databases.name LIKE 'wso2am!_%' ESCAPE ('!');
OPEN @DatabaseCursor;

FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
WHILE @@FETCH_STATUS = 0
BEGIN
  DECLARE @BackupSql NVARCHAR(4000) = N'BACKUP DATABASE [' + @DatabaseName + N'] TO DISK =''R:\BACKUP\' + @DatabaseName + '.bak'' WITH COPY_ONLY;';
  DECLARE @Msg NVARCHAR(2047) = CONVERT(nchar(27), SYSUTCDATETIME()) + N'Z  Backup [' + @DatabaseName + N']...'
  RAISERROR (@Msg, 0,0) WITH NOWAIT;
  DECLARE @BeginTime DATETIME2 = SYSUTCDATETIME();
  EXECUTE master.dbo.sp_executesql @stmt = @BackupSql;
  DECLARE @EndTime DATETIME2 = SYSUTCDATETIME();
  SET @Msg = N'  Duration = ' + CAST(DATEDIFF(s , @BeginTime, @EndTime) AS NVARCHAR(42)) + N's';
  RAISERROR (@Msg ,0,0) WITH NOWAIT;

  FETCH NEXT FROM @DatabaseCursor INTO @DatabaseName, @DatabaseID;
END
 
CLOSE @DatabaseCursor;
DEALLOCATE @DatabaseCursor;

The example above is part of cloning a subset of databases. I hope that you can use this in other situations.

RAISERROR with UNIQUEIDENTIFIER

The T-SQL type Uniqueidentifier is not a formatting possibility for at RAISERROR message. But converting the value to a string is a quick fix. This example is based on the Uniqueidentifier output from the procedure sp_add_jobschedule.

DECLARE @_schedule_uid UNIQUEIDENTIFIER = N'7f6a5df0-a68f-4b07-992f-b7595958061f';
DECLARE @_schedule_code NVARCHAR(50) = CONVERT(NVARCHAR(50), @_schedule_uid);
RAISERROR ( N'Schedule UID = %s', 0,0, @_schedule_code ) WITH NOWAIT;

The output is then

Schedule UID = 7F6A5DF0-A68F-4B07-992F-B7595958061F

A small detail is that with the conversion the value is also converted to upper-case.