SSIS Package to fetch backup information of all SQL Servers on Enterprise

Question

Post reply

SSIS Package to fetch backup information of all SQL Servers on Enterprise

narayanamoorthy.a

Ten Centuries

Points: 1256
More actions
August 1, 2016 at 5:06 am

#315761

Hi Folks,
How to fetch the backup information of all SQL servers(I got 100 SQL Instances) in my domain by creating SSIS packages ?
NM

Viewing 7 posts - 1 through 6 (of 6 total)

You must be logged in to reply to this topic. Login to reply

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 1

1. Write a query that gets the information you need from the target servers

2. Use a Data Flow task to pull the query results into a table on your central server

3. Create a table on your central server with the names of all of the target servers in it

4. Use a ForEach Loop to connect to each of the servers in turn and execute the Data Flow task

John

narayanamoorthy.a Ten Centuries Points: 1256 More actions · Answer 2

I need the backup information from 100 servers on my domain like FULL, Incremental, Log,start date and finish date and how to make into SSIS ?

Phil Parkin SSC Guru Points: 246985 More actions · Answer 3

narayanamoorthy.a (8/1/2016)
I need the backup information from 100 servers on my domain like FULL, Incremental, Log,start date and finish date and how to make into SSIS ?

Can you do it in T-SQL for a single server?

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel

John Mitchell-245523 SSC Guru Points: 148809 More actions · Answer 4

I'm not writing the query for you - that's what you're paid to do. If you really don't want to do it yourself, just type "query to return backup information" into your favourite search engine.

As for the SSIS part, please let me know which part of what I posted earlier you need more help with.

John

narayanamoorthy.a Ten Centuries Points: 1256 More actions · Answer 5

John,

I have the backup script with necessary fields that I need. I am just wondering how to import it in SSIS and get the information from all 100 servers.

Phil Parkin SSC Guru Points: 246985 More actions · Answer 6

narayanamoorthy.a (8/1/2016)
John,
I have the backup script with necessary fields that I need. I am just wondering how to import it in SSIS and get the information from all 100 servers.

John has provided an outline of how to do it. Now it's up to you to try to figure it out and come back with any detailed questions you may have.

The absence of evidence is not evidence of absence.
Martin Rees

You can lead a horse to water, but a pencil must be lead.
Stan Laurel