SQL Functions – PATINDEX() « Sql And Me

SQL Functions – PATINDEX() « Sql And Me:

Backup mirror databases script

-- Authro: George Wen
-- this script is to be run on database mirror and will backup all principle databases.

DECLARE @dbname varchar(200)
DECLARE @tsql varchar(500)

if OBJECT_ID('tempdb..#databases') is not null drop table #databases

select distinct d.name as dbname
into #databases
from sys.database_mirroring m join sys.databases d on m.database_id = d.database_id
where mirroring_role = 1

SELECT @dbname = MIN(dbname) FROM #databases

while @dbname is not null
begin
  select @tsql = 'BACKUP DATABASE ' + @dbname + ' TO  DISK = N''E:\MSSQL10\MSSQL\Backup\' + @dbname + CONVERT(varchar(10),getdate(),120) + '.bak'
  exec(@tsql)
  --print @tsql
  select @dbname = min(dbname) from #databases where dbname > @dbname

end