Backup database in CMS

Sometime you know the database name which you want to backup, but you don't know which server it is on. Normally we will
1. run query on CMS to find out which server the database is on
2. then connect to the server and run backup query.

If you backup only a database, it might be easy to do the steps upper, but if you need to backup dozen of databases, it would take you some time.

Here is a script you can use to backup databases from CMS directly, we don't need to know which server it is on:
1. connect to CMS, Open the query below
2. edit the @type and dbname session with what you need, then run it

the script can create backup for all database you input, and create backup file under last bakcup file path or default backup path.

/******************************************************************
* Backup database in database farm, you can run it on the central management server
* 1. edit the script with the database list which you want to bakcup,
* 2. edit the @type with value "Full" or "Diff"
* the backup file will be created in the latest backup path. if the database don
* Type:  T-SQL
* Version:     1.00.0000
*
* Author: James Xu
* blog: http://jamessql.blogspot.com/
*
*
'*****************************/
DECLARE @dbName varchar(200)
DECLARE @type varchar(200)
DECLARE @path varchar(200)
DECLARE @sql varchar(max)
DECLARE @curtime varchar(30)
DECLARE @rc int
--edit the backup type : 'Full' - full backup, 'Diff' - Differential backup
SET @type='Full'
--SET @type='Diff'
--edit the db name here and add more as you need
DECLARE DB_cursor CURSOR FOR
SELECT 'RS' AS DBNAME
UNION
SELECT 'aaaaa' AS DBNAME
UNION
SELECT 'DataCollector' AS DBNAME
--edit the db name here and add more as you need
SET @curtime=replace(replace(replace(convert(varchar(30), GETDATE(), 120), '-', ''), ' ', ''), ':','')
OPEN DB_cursor;
FETCH NEXT FROM DB_cursor
INTO @dbName
WHILE (@@FETCH_STATUS = 0)
BEGIN
      if (exists(select * from sys.databases where name=@dbName))
      begin
            select top 1 @path=reverse(substring(reverse(physical_device_name),charindex('\',reverse(physical_device_name)),len(physical_device_name)))
            from  msdb..backupmediafamily bmf inner join msdb..backupset bs on bmf.media_set_id=bs.media_set_id
            inner join sys.databases db on bs.database_name=db.name
            inner join sys.database_mirroring dbm on db.database_id=dbm.database_id
            where bs.database_name =@dbName and bs.type='D' and isnull(dbm.mirroring_role,1) = 1  order by bs.backup_finish_date desc
           
            if (@path is null)
            begin
    exec @rc = master.dbo.xp_instance_regread   N'HKEY_LOCAL_MACHINE', N'Software\Microsoft\MSSQLServer\MSSQLServer',N'BackupDirectory', @path output
            end
           
            if (@path is not null)
            Begin
                  set @path=@path+@dbName+'_'+@type+'_'+@curtime+'.bak'
                  set @sql='BACKUP DATABASE ['+ @dbName +'] TO DISK='''
                  if (@type='Full')
     set @sql=@sql+@path+''' WITH NOFORMAT, NOINIT,  NAME = N'''+@dbname+'-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
      else
     set @sql=@sql+@path+''' WITH DIFFERENTIAL, NOFORMAT, NOINIT,  NAME = N'''+@dbname+'-Differential Database Backup'', SKIP, NOREWIND, NOUNLOAD,  STATS = 10'
                 
                  EXEC(@sql)
                  PRINT 'DONE - ' + @SQL
            end
            else
            begin
    PRINT 'There is no existing backup for database ' + @dbname
            end
                 
      end
      FETCH NEXT FROM DB_cursor
      INTO @dbName
END
CLOSE DB_cursor;
DEALLOCATE DB_cursor;