September 27, 2011 at 3:00 am
I want to fetch Backup details of all linked servers....for that I have written this cursor....but its giving some errors...
DECLARE @srvname varchar(50) -- Servername
DECLARE @getsrvname CURSOR -- Declare Cursor
SET @getsrvname = CURSOR FOR SELECT name FROM master.sys.servers
OPEN @getsrvname
FETCH NEXT
FROM @getsrvname INTO @srvname
WHILE @@FETCH_STATUS = 0
BEGIN
exec('SELECT server_name,database_name,user_name, backup_start_date,backup_finish_date,type,backup_size,recovery_model from ['+@srvname +'].msdb.dbo.backupmediafamily
INNER JOIN ['+@srvname +'].msdb.dbo.backupset ON ['+@srvname +'].msdb.dbo.backupmediafamily.media_set_id = ['+@srvname +'].msdb.dbo.backupset.media_set_id
ORDER BY msdb.dbo.backupset.server_name asc')
FETCH NEXT
FROM @getsrvname INTO @srvname
END
CLOSE @getsrvname
DEALLOCATE @getsrvname
Sagar Sonawane
** Every DBA has his day!!:cool:
September 27, 2011 at 4:31 am
what's wrong with using these?
exec sp_helpserver
exec sp_helplinkedsrvlogin
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
Viewing 2 posts - 1 through 1 (of 1 total)
You must be logged in to reply to this topic. Login to reply