SQL Server – How to Backup All Databases in the Same Place

I would add TRY/CATCH blocks to handle errors and log them. The DB could be in single user, being restored or whatever.

Without this, errors can abort in a way that no errors are logged (statement, batch, scope, connection etc)

With TRY/CATCH then everything except for compile or connection aborting errors are logged? but I doubt this is the case.

I'd also use sys.databases which replaces sysdatabases and read more flags:

-- declares etc

BEGIN TRY

    DECLARE db_cursor CURSOR FOR  
    SELECT name, state, user_access
    FROM sys.databases 
    WHERE name NOT IN ('tempdb')

    OPEN db_cursor   
    FETCH NEXT FROM db_cursor INTO @name, @state, @user_access

    WHILE @@FETCH_STATUS = 0   
    BEGIN   

        SET @fileName = @path + @name + '.bak'
        IF @state = 0 AND user_access = 0
        BEGIN
            BEGIN TRY
                BACKUP DATABASE @name TO DISK = @fileName  WITH INIT
            END TRY
            BEGIN CATCH
                -- log but do not rethrow so loop continues
            END CATCH
            WAITFOR DELAY '00:00:20'
        END
        ELSE
           --log user and/or state issues

        FETCH NEXT FROM db_cursor INTO @name 
    END   

    CLOSE db_cursor   
    DEALLOCATE db_cursor

END TRY
BEGIN CATCH
  -- some useful stuff here
END CATCH

I too am not a fan of the SQL Server maintenance plans -- used them long ago and had flaky bugs here and there and I too have to maintain and support multiple DB FULL and TRANSACTIONAL log backups for several instances of SQL Server.

For DBA backup operations simplification, it may be best to take a look into using PowerShell. You may have to test and learn a little about the syntax but it's very robust. You could backup ALL DBs on ALL instances and append the SQL Server instance name with a variable so the names or your FULL backup bak file could be something like ..bak.

I think you'd have to parse certain characters out the BAK file name so using simple PowerShell replace commands (e.g. replace "\" with "_", etc.)can help with this easily, and you could loop all SQL Server instance names in one block of code too -- wouldn't even need to use separate subfolders if you didn't want to as instance name is specified at the file level. You could also create the first level subfolder for each instance as /SQLInstanceName and then each could contain the DB name within for another quick idea -- not sure if maintenance plans can do this though.

It's pretty powerful stuff that once you start using it, things get a whole lot easier.

If you want, I can send you a couple example scripts I used for these type of purposes and there is some SQL Server instance level configurations needed for enabling SQL PS, etc. too but I think I have notes for this I can provide as well.

Two articles are below for initial research, etc. Let me know if you want me to provide anything further but that's some quick thoughts I have.

• https://technet.microsoft.com/en-us/library/Dn223322(v=sql.120).aspx
• https://gallery.technet.microsoft.com/scriptcenter/How-to-back-up-SQL-Server-a997fde9