Support Center

Name is required.
Email address is required.
Invalid email address
Answer is required.
Exceeding max length of 5KB

New Database not backed up.

Dan Good May 25, 2017 11:15AM CDT

How do I get Minion to backup a newly created database automatically?

Up 0 rated Down
Sean McCown May 25, 2017 11:25AM CDT MinionWare Agent

Hey Dan, it should do that automatically. The new DB that’s not being backed up, was it restored from another system? Was it involved in log shipping on the other system?

Up 0 rated Down
Sean McCown Jun 05, 2017 08:38AM CDT MinionWare Agent

Hey Dan, do you still want to work on this issue?

Up 0 rated Down
Dan Good Jun 28, 2017 10:51AM CDT
Yes, I would like to still work on this and sorry for the slow response.
Up 0 rated Down
Sean McCown Jun 28, 2017 10:53AM CDT MinionWare Agent

Ok, here’s what I wrote before. Let’s start there.
Hey Dan, it should do that automatically. The new DB that’s not being backed up, was it restored from another system? Was it involved in log shipping on the other system?

Up 0 rated Down
Dan Good Jun 28, 2017 10:59AM CDT
So, I have tried this a couple of different ways.

1. The database was restored to the server running Minionware from another instance of SQL Server. Thus a new database on the MinionWare server.

When the next backup iteration occurred it ran a log backup.

2. I created a new database. It to only received a log backup.

In both cases the databases didn't receive a FULL backup until the next scheduled FULL backup.

Is there a configuration setting to capture this type of event.

We work in a very dynamic environment where by new databases are created and or dropped nearly everyday.

Thanks

Dan
Up 0 rated Down
Dan Good Jun 28, 2017 11:04AM CDT
No, the database was not involved in log shipping are any other advanced SQL Server settings. It was moved from one basic install to another. The only difference was it came from an 2008R2 system to a 2016 system.
Up 0 rated Down
Sean McCown Jun 28, 2017 11:05AM CDT MinionWare Agent

OK, I don’t think I understand, you said that the DBs didn’t get a full backup until the next scheduled full backup. What do you want it to do if not take a full backup when it’s scheduled? What am I missing?

Up 0 rated Down
Dan Good Jun 28, 2017 11:35AM CDT
In looking at the log table, [BackupLogDetails]. It appears I miss spoke with regards to the new database creation. It didn't get a log backup until after the first FULL.

However, it didn't receive a FULL backup until the next Scheduled FULL backup.

I created database ddg_util_ddg on May 25th at 11:20 AM. The first record indicating it received a FULL backup was 2017-05-27 23:05:01.663 which was the scheduled FULL backup time.

Up 0 rated Down
Sean McCown Jun 28, 2017 11:42AM CDT MinionWare Agent

Ok, and are you expecting to get a full backup before the next scheduled time? It sounds like it’s doing what it’s supposed to do. If you’d like to increase the frequency of the fulls, say to every day, then that would be a good solution.

Up 0 rated Down
Dan Good Jun 28, 2017 12:01PM CDT

Currently, our backup routine determines if that specific database has not had a FULL backup previously and will launch into a FULL backup rather than a log backup. All of our servers run LOG backups at least hourly and thus it will be a maximum of 60 minutes before the new or restored database will get a FULL backup.

I would really not want to increase the frequency of our FULL backup schedule. With 160 SQL Servers and close to 8000 databases writing that many more FULLS will defiantly require more space. Although we haven't implemented MinionWare but on a handful of servers as we are in a testing phase. It would be beneficial to be able to schedule a process to say run once an hour or 4 times a day to pickup only the "new" databases with a FULL backup.

Hopefully, I have got the noise in my head to subside. However if you felt more comfortable discussing this over the phone I would be open to that.
Up 0 rated Down
Sean McCown Jun 28, 2017 12:20PM CDT MinionWare Agent

Ok, now I see what you want to do. So I’ll explain why it works the way it does. The reasoning is that I didn’t want to have a new DB come in and mess up your log backups. Let’s say you’re running log backups every 15mins. and you restore a large DB onto the server and that DB takes a 30mins or longer to backup. Now your log backups are going to be way off, not to mention the extra resources it could take in the middle of the day. So the idea was to not hit you with a surprise full backup like that.
I’ve had one other request like this and I pointed him in the right direction, but he never sent me the code he used. We’ve got mechanisms in place to help with this. The SettingsServer table is where the scheduling happens and you’ll find some BatchPreCode and BatchPostCode cols in there. This is where you can define code to run before or after the run itself. So what you can do is write an SP that calls BackupMaster and backs up any DBs that are new. So the process would more or less look like this:
1. Query for the names of any new DBs and put them in a comma-delimited list. This is fairly easy in a single query.
2. Call BackupMaster and put that list of new DBs in the @Include param. As long as you pass it Full as a backup type, and User for the DB type you should be fine.
This is something that can easily be pushed out in the Minion DB on all the servers. You can just add it to the installer. Directions for adding things to the installer are in the install folder in the install doc.

I’m looking at the best way to include this functionality in the next major version of MB. I want to be able to give you a choice on how to handle new DBs, and even handle them differently based on their size.
Currently, the SP that you write above can be added as postcode to the log backups if you like. Or the diffs if you prefer.

Do you mind if I ask you a couple questions…
1. What were you using for backups before this?
2. What’s making you want to switch routines?
3. What do you like about MB so far? Is there anything specific that’s particularly attractive?

This is a very flexible routine and we’re always looking to make it better. So if you have any requests I’d love to hear them.

Up 0 rated Down
Dan Good Jun 28, 2017 01:12PM CDT

This helps a lot. I had thought of a couple of ways but was wanting to try and keep things internal to the backup routine.

Thank you, I really appreciate your time with this.


Up 0 rated Down
Sean McCown Jun 28, 2017 03:27PM CDT MinionWare Agent

Hey, my pleasure. I think the solution is internal to the backup routine. With all of our maint tools there are lots of solutions that include this type of fix, and just because it’s got an extra SP, doesn’t mean it isn’t an internal solution. The difference being that this solution will withstand upgrades, which is something you can’t say about everything. This way you’re free to upgrade and nothing will happen to your solution.
Also, if you do wind up writing this, feel free to send it to me and I’ll upload it to our community zone, or I can get you a login so you can do it yourself.
Let me know if there’s anything else I can do for you. I’m going to close the ticket now.

Up 0 rated Down
Dan Good Jun 28, 2017 04:55PM CDT
1. What were you using for backups before this?

Currently we use Tivoli Storage Manager with TDPSQL. The backup routine is a scheduled T-SQL script that is somewhat similar to yours in the respect that the same code works for all types of backups you just pass in the type you want. The one thing it does is it queries the backup tables in msdb to determine if a FULL backup should be run. It will even detect a broken log chain and initiate a full backup.

2. What’s making you want to switch routines?

TSM/TDPSQL - Although I have always been able to restore a requested database the process is rather cumbersome and can take longer than it should if 1) You are not restoring the database over an existing database. 2) You need to restore a database from an inactive database set. 3) Wild cards are allowed in an active backup set but are useless when dealing with an inactive set. 4) the GUI part of this is bad.

3. What do you like about MB so far? Is there anything specific that’s particularly attractive?

So, far I like the configure-ability, with everything being table driven, and the fact it is actually using SQL Server native backup commands.


Post Your Public Answer

Your name (required)
Your email address (required)
Answer (required)
556ca399015f31edc97a62de2771be1a@minionware.desk-mail.com
https://cdn.desk.com/
false
desk
Loading
seconds ago
a minute ago
minutes ago
an hour ago
hours ago
a day ago
days ago
about
false
Invalid characters found
/customer/en/portal/articles/autocomplete