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;