TSQL Solution to backup ALL databases on ALL instances of a server?

Question

I'm pretty sure what I'm wanting with my current implementation is impossible, the sys.databases catalog view which is scoped at the service level to be aware of other SQL Server instances (lol). But I've thought a lot of things were impossible with computers so hopefully I can solve my problem in a different way.

I've already created a PowerShell solution that uses SMO to backup all databases on all instances, but due to security reasons I'm unable to have that solution work without making the account for the proxy that runs the Agent job a local administrator on the server.

As an alternative I wrote a T-SQL script that will be ran as an Agent job and take either full, differential, or transaction backups on all databases in a SQL Server instance every 15 minutes.

    DECLARE @sql nvarchar(MAX) = N'';

--Check time for full backup
IF (DATENAME(weekday,getdate()) = 'Sunday' AND convert(time,getdate()) > '00:00' and convert(time,getdate()) < '00:15')
    BEGIN
        SELECT @sql += 'BACKUP DATABASE ' + QUOTENAME(name) 
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__' 
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.bak'''
                    + ' WITH COMPRESSION
'
        FROM sys.databases
        WHERE name <> 'TempDB';
    END
--Check time for differential backup
ELSE IF (convert(time,getdate()) > '00:00' and convert(time,getdate()) < '00:15')
    BEGIN
        SELECT @sql += 'BACKUP DATABASE ' + QUOTENAME(name) 
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__' 
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.diff'''
                    + ' WITH COMPRESSION, Differential
'
        FROM sys.databases
        WHERE name <> 'TempDB' and name <> 'Master';
    END
--Take transaction log backup
ELSE 
    BEGIN
        SELECT @sql += 'BACKUP LOG ' + QUOTENAME(name) 
                    + ' TO DISK = N''C:\backups\' + @@ServiceName  + '_' + name + '__' 
                    + cast(year(getdate()) as nvarchar(4)) + '_' + cast(month(getdate()) as nvarchar(2)) + '_' + cast(day(getdate()) as nvarchar(2)) + '__'
                    + cast(datepart(hour,getdate()) as nvarchar(2)) + '_' + cast(datepart(minute,getdate()) as nvarchar(2)) + '_' + cast(datepart(second,getdate()) as nvarchar(3)) +   '.trn'''
                    + ' WITH COMPRESSION
'
        FROM sys.databases
        WHERE name <> 'TempDB' and name <> 'Master';
    END
print @sql;
exec sp_Executesql @sql;

This script works fine, however it only backs up ALL databases on ONE instance. The goal is to backup ALL databases on ALL instances. An even better solution is to backup all databases, on all instances, and on all servers. From my current knowledge it seems like there are three solutions, (hopefully more based on answers)

1. Use this script in an Agent job on every instance. This will work but just seems like such an ugly solution.
2. Figure out how to give the Agent proxy the minimum amount of permissions to successfully run the PowerShell script which is instance aware
3. Look into creating a master server. I'm not familiar with this option but I know the master server is aware of other servers, and i may be able to get a list of all databases on all servers.

Answer 1

I've set up option 3, Master Server, a number of times, but never for scheduling backups. I'm able to schedule jobs for performance data collection, pushing out administrative changes/script updates via SQLCMD, and run tests against multiple versions of SQL Server on multiple instances to test for any unexpected changes made after SP/CU application (among other things).

The question is though: Is there a reason why you want to back up all databases on all instances from one server? Having all your backup eggs in one basket means that none of your backups get done for as long as that master server (or at least SQL Server Agent) is offline. Having backup jobs on each server is the standard for good reason (regardless of how they're administered).

Rather than writing your own backup statements, I highly recommend using Ola Hallengrens Maintenance Solution (the whole maintenance package is worth using). It just works, and works very, very well. It gives you options you won't have thought of, but will come to need in the future.

Armed with that script, you can iterate through a list of your instances, and execute MaintenanceSolution.sql (which you downloaded from Olas site) against each server. I've done this using a batch file that uses SQLCMD with a trusted connection to each server, and outputs results to a CSV for each server (assuming you've got privileges on each instance). If you use the Registered Servers view in SSMS, you can also execute that script against all servers in a given group at once.

Outside of that, you're looking at something like redgate SQL Backup Pro (I've no affiliation with them), Idera SQL Safe Backup (again, no affiliation) to do what you want, but those products do cost money outside of their respective trial periods.

Either of these solutions will give you far greater safety than scheduling backups for all databases on all servers from one instance.