Backup database in CMS

jamesxu98918, 2012-05-09

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;

Rate

Share

Share

Rate

Related content

Database Mirroring FAQ: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup?

Question: Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? This question was sent to me via email. My reply follows. Can a 2008 SQL instance be used as the witness for a 2005 database mirroring setup? Databases to be mirrored are currently running on 2005 SQL instances but will be upgraded to 2008 SQL in the near future.

Robert Davis

2009-02-23

1,567 reads

Networking – Part 4

You may want to read Part 1 , Part 2 , and Part 3 before continuing. This time around I’d like to talk about social networking. We’ll start with social networking. Facebook, MySpace, and Twitter are all good examples of using technology to let…

Andy Warren

2009-02-17

1,530 reads

Speaking at Community Events – More Thoughts

Last week I posted Speaking at Community Events – Time to Raise the Bar?, a first cut at talking about to what degree we should require experience for speakers at events like SQLSaturday as well as when it might be appropriate to add additional focus/limitations on the presentations that are accepted. I’ve got a few more thoughts on the topic this week, and I look forward to your comments.

Andy Warren

2009-02-13

360 reads