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.