2

I am running maintenance plans on a server, which is running several instances of SQL Server 2104.

I have set up a nightly maintenance plan to run backups for all user databases, with the backups set to create their own sub-directory.

This works absolutely fine with the current setup- with all backups residing in their own specific locations.

However, I was curious as to what would happen if databases on multiple instances shared the same database name - is there any way of adding an extra level in the maintenance plan to include the instance name?

Improve this question

2 Answers 2

Reset to default
1

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.

Improve this answer
3
  • Thanks - this seems like it might be the best way to go - I already run a powershell script to zip the backups recursively, called via SQLagent - but it definitely makes sense to look into this in more detail.
    – SeanR
    Aug 27, 2015 at 8:24
  • Just FYI... I'm surprised you are zip compressing SQL BAK files, there's an option that compresses these with SQL Server to the actual BAK file -- so you get the compression but it's transparent and you don't need a separate process. Sometimes existing BAK files with the same names the compressed files will output to will need to be deleted off the disk before you'll see the different. So if you turn on the option for backup compression, delete the existing BAK file, then backup the DB, you should see a difference.
    – IT Thug Ninja
    Sep 1, 2015 at 1:38
  • "Company Policy"...
    – SeanR
    Sep 1, 2015 at 8:03
1

I am not that huge fan of Maintenance plans to use such strategy for Backups.

What suits the best to our environment is using ola.hallengren Backup scripts.

It backups by taking the initial folder as the location define by the user and later it itself identifies the instance that it is backing up and thereafter creates the folder respectively:

Give it a try , as its simple to execute and easy to maintain.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.