1-800-691-1991 | 9am - 8pm ET
July 20, 2020
Duration: 00:59

SQL backup and recovery for everyone

Register to watch

By registering, you are agreeing to have your personal information managed in accordance with the terms of Veeam's Privacy Policy.
By registering, you are agreeing to receive information about Veeam products and events and to have your personal information managed in accordance with the terms of Veeam's Privacy Policy.
Thank you for your interest in Veeam products!
We've sent a verification code to:
  • Incorrect verification code. Please try again.
An email with a verification code was just sent to
Didn't receive the code? Click to resend in sec
Didn't receive the code? Click to resend

ty icon

Thank you!

We have received your request and our team will reach out to you shortly.


error icon

Oops! Something went wrong.

Please try again later.

SQL backup and recovery for everyone video

Please register to get access to watch the webinar

If you are not a database administrator, this doesn’t mean you can’t have amazing SQL Server backup and recovery. In this webinar, Rick Vanover from Veeam® will present on a number of capabilities for SQL Server that can give you amazing backup and recovery techniques that you can confidently do, even if you are not a database administrator. Whether it is migrating databases to new servers, restoring databases or recovering items, you can do it with ease. The key takeaways for this webinar include:

  • How to configure backups of SQL Servers and their logs
  • How to do advanced recovery options with ease
  • How you can migrate SQL data with Veeam

Video Transcript


Rick Vanover: Alright, good afternoon. Good evening, good morning, good day, let's get started. Welcome to today's featured webinar, No Fear: SQL backup and recovery for everyone. You don't even have to be a DBA. My name is Rick Vanover, Senior Director of Product Strategy here at Veeam. I'm joined with Andrew on my team. How you doing Andrew?

Andrew Zhelezko: I'm doing pretty good, Rick. How are you?

RV: Hey, I'm doing great. This is our third cut at this live webinar here for the Americas. Let's see if this one is the best, but before we get into it, I want to give you all and we have a huge crowd on here, this is great. I want to give you a few administrative points about today's webinar. Number one: Everyone was automatically muted upon entry, but that doesn't mean you can't interact with us. You can use the go-to-webinar questions and answers in between Andrew. In my presentations, we’ll do our best to answer those real time, but we also have dedicated time at the end of the webinar when we're going to go through it. Now, the other thing is we're going to have a lot of great tips, a lot of great links and the other things through this webinar. If you want you can screenshot it, etc. But you are going to get a link to replay the webinar and download the slides. It's going to be sent as a follow-up after the email, so you don't have to take too much notes. Sit back, relax, leave the Veeamin’ to us.

RV: So the first thing I want to do is just give you a quick introduction to Veeam. One slide, why Veeam. While the reality is we think that Cloud Data Management is the market that we're after, so we want to be that most trusted provider of backup solutions that provide Cloud Data Management. So a lot of people come to Veeam for backup and recovery, right? That's why you come to Veeam, the fundamental backup and recover and replication and failover. That's why people come here. We're going to talk about SQL, so it's real specific around the application, but. The bigger story walks around this wheel of capabilities here around the ability to have mobilized data to the cloud, analytics and monitoring, orchestration and automation, as well as compliance techniques. At the center at your data, whether it lives here or up there, we've got you covered. Alright. The brief word from your sponsor is over. Let's get into it and talk about SQL Server Backup and Restore.

RV: Now the reality is we went into this topic and we're going to talk about, a little bit on how Veeam works with SQL. In fact a lot about that, but also a little bit about how SQL sits in the market and Andrew is going to set that up pretty good. But the one thing I want to highlight is not everyone's a DBA. And the subpoint underneath of that is that not every database gets the same amount of attention from each DBA. Likewise, chances are you have more SQL databases than you know about, right? So when you have these types of characteristics involved, you want to take a different approach, and that's the opportunity for Veeam, right? I'm not a DBA, I can't even spell DBA, I can barely spell S-Q-L. But the tools we have here can really help you out. Now, Andrew, when we think about backing up and restoring data, what kind of thoughts do you have going into it?

AZ: Well, the data is, you know these days it could be in different places and across all the organizations and applications. We have a saying here at Veeam that well these days every company is ID company and what we mean by that is they run a lot of applications, servers, services, stuff, environment. It's just a lot of options available on the market, whether you're running the physical things, that virtual things cloud environment says. Yes, those are a lot of options, but then what's really interesting here is that you have to take care of that yourself. And when we think about the crucial part of those applications or services, it's usually the data and the way to structure data usually is to bring to some sort of the database. Because of that, SQL-like services are really popular these days and it's not just something you that I'm talking about. They've been popular for quite a long time, but then here at Veeam we realized that the popularity of those services, and we've been thinking since the very beginning since we met inception. We've been thinking about additional features and how we at Veeam can actually talk more to the SQL people, to people who are using SQL services. And to help them out, to bring some interesting functionality that would allow them to back the data up and to recover it the way they want. And actually, let's talk about the backup of the data because we've been, you know, the history of Veeam support for SQL goes into the past.

AZ: But then before we actually talk about that, I wanted to bring a couple of SQL considerations because these days, like it mentioned before, it could be a lot of options available for you and then a lot of questions inside of your head should be, you know, should you, should be questioned beforehand and should be answered later. And I'm not just talking about the environment where you're running SQL services and the way how we run it, but also I would like to talk about just a little bit about the standalone servers or always-on availability groups or clusters. So, how would you tackle them? And sometimes it's really, it's really…it's not easy challenge to tackle, but then you've got a lot of options and you've got probably a lot of pressure coming from somewhere you know outside of the company or even from inside, such as what is your RPO or RTO for the SQL servers now. How much can you actually allow it to be down and what's the preferred method for DBAs to take a look into them? What's going on there? And actually, do you even have the DBAs, or do you take care of everything yourself? And because of that you might? You know you might consider different recovery models like simple versus full, and obviously for simple probably this is something the test environment and you don't really need to worry about. But then whenever you're going to really interesting things, it should be either for bulk logged and with those type of recovery models you should be able to get as granular as possible coming to the SQL.

AZ: Alright, let's switch a little bit and talk about Veeam products because I want you to have a full introduction. I know quite some of you already Veeam customers and you know what I'm talking about, but then just to bring everyone to the same page, right? We've got a couple of solutions and let me start from Veeam Backup & Recovery application because this is the main product that we've been developing for over 10 years. Actually I think it's 13, 14 years now and it's a really great solution and it has a lot of interesting logic, which I'm going to scan it to unveil a little bit later, but then it has all the options for virtual machines. And whenever your SQL in the virtual machine somewhere inside you should be. This is no brainer. You should be using this particular solution, but then there are options and then I'm going to list them later when you cannot do that and you have to use something talking or running inside of the gas operating system or on the guest operating system. And so this is Veeam Agent for Windows for example, which is really great. So when we developed for physical servers, we call them back in the days, we call them remaining servers. But then we realized that people are not fully going to the virtual systems north of the cloud and so a lot of options available here. And the first product I would like to introduce here is probably you haven't heard about. But then we've got two of them, Veeam Backup for Azure, Veeam Backup for AWS and I call them Veeam backup for public cloud. But then it might be more calming and with all the cloud transition it's really important to know that they exist. And it's really important to know that we talk to our customers. We hear your feedback and with adoption of infrastructure as a service, platform as a service, especially within Azure Cloud because SQL is one of the most popular options there, we're going to bring more support and more functionality into it. You just probably… sometimes we're not there yet, not there where people expect us to be. But then we're catching up and for clouds environments we’ll definitely bring something here.

AZ: Alright, then, let's go to the Veeam SQL Matrix because there are a lot of options. Again, like I said before, the how you can configure it. And then, depending of what their current environment is, you can set it up in the way that you need one product or another. And so Veeam Backup & Replication is really good for Veeam backup and for, you know, for some of the cases, I wouldn't even say the corner cases, but then some of the cases is definitely important, and then you're going to use Veeam Agent for Windows for example.

AZ: So let's talk about Veeam approach. What's actually different? What differentiates us from any other solution in the market or from more competition? First of all, we truly believe in image level backups. This is something that we, again specializing on virtual system on virtual machines. We know that it's really great that one of the benefits of virtualization that you can just take everything from VM just because of VM snapshots. And also another point here is that you can migrate that data. You can move it anywhere and then you can restore to, recover to any other platform and with that it would be also nice to have them…certain logic intelligence in place to look inside of that virtual machine and to know whether you're running Oracle or SQL or basically any other interesting services or applications. And we have that logic, we call it application aware processing. And what I mean by that is actually a couple of different technologies, which I'm going to cover on the next slides, but then it consists for us, if we're talking about the SQL specific things. It consists of application aware processing, making sure that the backup of the data is being transactional consistent, transaction log backup and transaction log truncation because we all know that for SQL, a lot of transactions are coming and sometimes you don't want to lose any of them and so point-in-time recovery would be a really nice and desirable option. And with that transaction log backup and truncation, both of them allows us to provide that functionality into action to give you a lot of flexibility. And with Veeam approach we're also, we know that always-on availability groups are really popular. Just one thing for you here. So if you're using Veeam Backup & Replication, make sure that you have all of the members of Availability Group inside of one backup job. And so Veeam has intelligence in place. It will detect what is the active mode, what has been signaled and will process them on currently. Alright, let's go to the application aware processing, where I would like to mention once again that this is a great technology and this is their set of different technology tools that we are using. But basically it's making sure for that all the applications we are processing such as SQL, Oracle, SharePoint for example, those are processed in a good and interesting way and so after because their recovery is the most important part, we can ensure that recovery whenever you need it, it could be done really nice. And then if we take a look inside of application aware processing what the…. how the process actually look like and what it brings you here?

AZ: So you can notice a couple of steps. If you go to the technical diagram you will see that Veeam talks to them, to the operating system of the virtual machine. It detects that there are some SQL services running inside, alright, and then for the purpose of application aware processing, we're going to deploy the temporary runtime coordinator, the processes that will just destroy itself just after that freeze is done. So the sequel subsystem is going to be prepared for that hot backup. And then once that everything is flushed into the disk from RAM and you know SQL isn't consistent mode, we can say hey VSS, let's go and let's actually take the snapshot of that virtual machine. And then it comes to the hypervisor level. And so we also take the snapshot of their virtual machine itself, and then we are able to access the content of the disk because disk is in read mode, while SQL Server is still working on the delta disk. And we can move the data out of it and we can form the backup file. This is application aware processing. From there kind of the general perspective here.

AZ: Let's go to the transaction log backup because this is another thing that we actually offer there and then in a couple of seconds I will show you a demo and I will show you where to look into the software to actually define this functionality available. But then what's really interesting here is that just the size of the processing, we can offer a couple of options here, right? Whether you like third party tools or SQL Native tools to do actually, to take care of the log backups so we could perform the copy. Only the backup of logs, or if you if you really need to truncate the logs and then save them somehow because you know for some highly…the biggest servers that are running it might be, you know, a lot of logs and a lot of data inside, and so you don't want to miss that data and you want to be consistent with a bullet. So yeah, we have the shipping mechanism that is inside and then whenever the backup of the virtual machine is done, we can ship those logs because they're, you know they're truncated and SQL doesn't need them anymore. But we can ship them directly to the repository or use their staging shipping server, which means that the virtual machine, it doesn't have to have the direct connection to the repository, to the backup repository, and so we're going to find a nice and interesting way to pack those, they gave files into their being proprietary format and actually ship them there.

AZ: And then I already mentioned transaction log truncation. You just. Again, those are simple options here that you will notice in Veeam UI, but then a couple of options available here and it's basically, it's up to you whether you would like to truncate logs or don't even, don't going to be truncating logs, etc. And then with that in mind, this is the three major pillars of Veeam application aware processing. But what's really good about that is that we also provide that… almost the same functionality for Veeam Agents and for Veeam Agents it’s really important to help them in place because as you can see I just listed a couple of reasons for why you might need to have those agents in place or such as you know, running the physical servers or running the public cloud, or having the independent disks somehow attached to the VM and so they cannot be snapshotted. And obviously because of that we cannot really process them. But then using agents would really help here and actually with that in mind, I would like to go to my lab and show you how it looks like.

AZ: So Rick if you could just ask me, then…

RV: I'm gonna pass that over to you and while you get that set, John dropped in a really good question around the SQL agent is good when there's no DBAs, but what if we have DBAs? What do we do? I've summarized your question, John. If you have a DBA that is protecting the database through SQL Server Maintenance Plan or SQL Server Agent Job with native SQL backups or even something else with an extended stored procedure. I recommend that you still take the image-based backup, but you just don't do any of the log management and in that situation you'll have the image-based backup and you'll actually still have some Veeam SQL recoveries, should those other mechanisms fail. The main thing is you just won't have the point-in-time recovery, but good question there, John.

AZ: Alright, I'm going to stop there on that setting, which will help you onto to actually to get it sorted. The first thing I wanted to talk about kind of the last one in the presentation. So here is the Veeam Agent for Windows. I just downloaded it from Veeam.com and then it's running a free edition as you can see. So as simple as that. But then what I'm going to do because this is my one of the SQL servers that I have here and I have the management studio with some of the system databases, but that's not the point for this presentation. Alright, I can just add a new job here and what I'm going to do is, I'm going to provide a friendly name and going to show you a couple of interesting points here. So for the backup mode I have to choose the entire computer order. Volume level backup, obviously not the file level because it is being processed differently, but then on the destination what I'm going to do is to provide Veeam backup repository. Alright to actually to select that option, so my backups would be coming to the central Veeam backup repository. And then what I'm doing is I'm providing the IP address of my Veeam backup repository. Then you can see I do not have the next steps for me available, our backup repository and schedule. But then I'm going to click next. So now Veeam Agent is talking to Veeam Backup & Replication to the major service here. And then you can see I've got a couple of additional options available now because they talked and because Veeam Agent assumed the license from Veeam Backup & Replication. And now what is cool about it, that I've got the list of repositories, doesn't really have interest from you right now, but then what has some interest for me is guest processing step and then now I've got two options available. And one of them something that we already talked about, application aware processing. Alright, so finally this agent is unlocked and it could actually do some magic about Veeam. whereabout the SQL service is running here.

AZ: So if I click on applications, in the general tab you can see here that I've got two options and this is basically something that John was asking about, so I can let Veeam Agent process transaction logs with this job recommended. Obviously, we recommend that because Veeam would be the only tool to actually talk to the SQL and to ensure that you know it's fully protected and it's fully soloed around, but then I've had another option to perform the copy only. Processing, so it lets another application use logs and this is pretty cool, because if there is a DBA with those SQL native tools and something that they know how to work and they prefer to work with that, that's fine. So with that we can perform the copy only processing, and this is also nice 'cause you'll get both the image level backup and you with one of the caveats that you won't be able to go to the point in time transaction and their recovery couldn't be done, but then you will get the SQL Image Backup, which is also nice to be, you know for test environments, for basically for any major outages. Alright, and then if I go to the SQL tab, alright, I've got a couple of options here. Actually to specify the different account with the sys admin role. Alright, let's disable that for now. And two to decide whether I would like to truncate my logs, do not truncate my logs or the backup blocks periodically, which is something because it also includes truncation, but then the logs would be also protected and so that enables point-in-time recovery to the specific transaction. And later Rick is going to talk about recovery and show you how to perform that exactly.

AZ: But then I'm going to cancel it for now and going to go to the Veeam Backup & Replication because this is the solution that there's something interesting here. So I've got this job at one group, which has three virtual machines, one of them running the main controller, one of them running my SQL Server, and one of them running the application that I'm using. So basically, it's a nice application group. Everything is interdependent here, but let me quickly start the incremental job. So while it's running I'm just going to edit and show you the differences or the same things that is going to be interesting for you. So I enabled application aware processing for this particular backup job and then what's important here is to select their guest operating system credentials. So this is something that we need to actually to talk to the inside of the virtual machine, and then we've got a nice functionality here. It's called test now. Well, I'm actually I'm running the job, so I'm not. I'm not sure if I can test it but here it is. Let's try and see whether my accounts are good for you know, for being able to talk to those virtual machines, and yeah I've got everything successful, everything right. And then I can come back to the applications because again, this is something that we have the interest in, and we've got those objects in this job and then the SQL virtual machine is my area of interest. I'm going to edit and again general, the same options. I would like my application to be processed in application about… you know with mechanism and then I would require this job to actually to wait for the successful processing, obviously recommended. Sometimes you don't want to do that. Sometimes you want to disable application processing. Well, it's not recommended, but those are the options. And then transaction logs, something again to decide whether it should be processed with this job or it should be copy only process.


AZ: And then let's go to SQL and those are the options that I have previously showed with Veeam Agent. So I'm going to cancel here. And let's actually, let's see what's going on with this job. I'm clicking on the statistics of the job because the job is selected here and I've got two parallel tasks here. The instance backup in SQL Server log back, OK, the instance backup. It's basically the general information about those VMs and how I'm processing them. OK, let's click on SQL Server here and then, I can see that's… Okay, something's going on, alright. And my main area of interest is here. So after we detected what virtual machine is and what's inside, because we made the inventory for the guest system, we decided to prepare it for the hot backup and this is the most interesting part because we need to quiz operations on the SQL Server, before we ask VMware to take the virtual snapshot. And then here it is, it just it took a couple of seconds, but then it prepared. Don't get me wrong, if people or users who are using this SQL Server, they won't notice any outage, anything. It would be just milliseconds while the VMware is switching between the…you know, between the actual disk and the delta disk. And then if we were going to the end of the session, while it's not available, let me just click left and let me go to the previous session that I was running. And so at the end of the session what will happen next, it will be the removal of VM Snapshot and then truncating transaction logs. This is something that's really important for us to do, to make sure that none of the logs are present anymore, and so we kind of, we know that there was the backup point, with all the locks so we can go back into the time, perform their perfect roll and roll back.

AZ: With that, let me kind of conclude that to click on the statistics and to show you the second option, SQL Server transaction log backup. And this is the situation that allows me to actually to see what's going on with transaction log backup. So I've got nice information here, the number of databases that are protected, unprotected or excluded. Why do you think they could be excluded? Let's actually check the settings or the information here in the statistics view and we can see the two of them and my SQL DBA and master DBA. They were using simple recovery model, which is nice and because of that they were skipped during the processing. But then two of them I've got them there, Model and Veeam ONE. Those are the databases that have interest for me and now this particular transaction log backup is processing it. So this is something that they want to talk from the backup perspective and by doing that I'm just kind of creating the layout for week to actually to build the whole story on because after that there will be some recovery, whether you need it or whether you just want to have some tests going around. But then the way how you define backup and the way how you did it, that would define a lot of recovery. So Rick, coming back to you.

RV: Yeah, cheers. Great stuff, Andrew. And I'm doing my best to keep up with these questions everyone. Hopefully that has been helpful so far. So yeah, we're back to the slide deck and I'm going to pivot a little bit and talk about recovery. Had a lot of questions about some of the system data bases, master temp DB, etc. Yes those are recoverable, but they're not going to be in the way I show you. So the short answer is yes. They get backed up by the whole image capture for sure, and that's kind of an important thing to highlight is that the recovery of Veeam is it's really a multi-level conversation, right?

RV: So a couple of things I want to highlight. One, many times SQL servers are shared across multiple databases. So, what that means is if you have an isolated database problem, what that might mean is that other databases are fine. OK, so the worst thing you could do is a whole VM recovery or a whole system recovery, right? Because all 39 other databases could be impacted. Again, not everybody is a sequel DBA. So what this means is I want to highlight some flexibility here and whole system recovery…and you know those types of things you guys get that, but when we talk about these other recovery scenarios, I want to highlight this kind of big picture down to small item recovery. So the whole system recovery, yeah, no problem. We have a data lab where we can extract things as well. Instant Recovery, which is quickly booting up the virtual machine if it failed or even a physical server. Imagine if your hardware failed. And you've backed it up with the Veeam Agent for Windows. I don't know if you know that, but you can actually boot that up as a virtual machine right now, which is a great way to recover. Then we can get into disk and file level recoveries, which get a little bit more specific and tactical on their recoveries. But then application items and that's where the Veeam Explorer for SQL is going to come in.

RV: So that's where I'm going to kind of pivot a lot of my conversation in a demo. So the Veeam Explorer for SQL and you can see here a list of databases and you know a lot of questions on TempDB sys model. The built in databases, right? They're not recoverable through the Explorer, but they are recoverable a different way. What I'm going to show you is just how powerful this Explorer is, 'cause hopefully master TempDB, those are not the ones that are messing up on you. 'Cause then if that's the case you have different problems. But anyways, when it comes to the Veeam Explorer here, imagine if you had a capability to recover point in time. Imagine if you had a capability to export this data to another database server. Imagine if you had the ability to recover just an item. Well you do. That's what Veeam Explorer can do for you.

RV: And I'm going to highlight something that you can use as a tool if you want to really get your head around this with a lab environment. That will be a great way to feel a lot more comfortable with it. And the other thing I want to highlight from a recoverability perspective is the notion of exporting data. So SQL data is actually a really good candidate for a portability story. I a lot of times, for example right here in this one, I actually I think I've done this before. You see this is the database called CBT 03. The one that I'm going to show you is called PMM 01, but they have similar but different databases, right? Some of these are there. Some of them are not. I actually use Veeam Explorer, this very tool right here that we're looking at to restore it to another server. I use this all the time. It's a great way to make databases portable. Let me apply that to something that might be relevant for you and your work, right. You have the production backup. The development team asked for the development database to be synchronized with production. Boom, done, sorted. You can do it.

RV: There's also native export capabilities. So we can capture Veeam with or capture the database with a Veeam image-based backup, is easy explained. But then you can export that backup as a BAK file, so you can even keep the database people happy. Very, very interesting stuff. And then this portability is really, really powerful. So I'm going to jump into a demo and in normal Rickatron style, the first thing I'm going to do is start breaking stuff. So let's… oh interesting. My lab died on me…. There it is. It's back, OK. I was scared for a second. Here is a SQL Server management studio. I'm going to break most of my databases here on this sequel, Green Dream Database. And in the Green Dream Database you're going to see that there is a table here called table employee. This is the table I care about now. This is all test data, but on this database here I have, let's run it again. I have on 381 rows of random data that I have here. But if you know anything about databases, you'll know that stored procedures are basically the code of a database here.

RV: So I have three stored procedures, and I'm going to start breaking stuff, so I'm going to modify this stored procedure. I'm going to turn things that are off to on and on to off. I'm going to change some numbers here. Let's change some numbers. Let's change the other numbers. Let's change other numbers. Let's change other numbers. Let's just break stuff, right? How many times have you been in a situation where you broke something, you don't know exactly what, but you do know it's broken, right? And basically I have changed so many things in this stored procedure, I'm going to go ahead and execute it and its recreated different numbers, different settings. So if I reopen this stored procedure, I can right away see that a number of the different line items are wrong. I don't know exactly which all wants to change, but what I want to highlight is, I know that the SQL Green Dream Database stored procedures broken. Sky Breaker is working. Razor Loop is fine, Gold Runner in Mountain Spark, no issues there. It's only SQL Green Dream. Rock Briars fine, all those other databases. That's where the Veeam Explorer can help you.

RV: So I've opened it up already here and let's go ahead and I want to show you something, this thing called database schema. And data. So this is a very low level restore capability, so it's kind of interesting because that table is actually OK. The employee database table that I care about. But what I really want to do is fix that data of the stored procedure. So I could do these restores and point it to different servers or different database names and use different accounts. We've had a number of questions about the security configurations. We do document all of that. I really encourage everyone to look at help center. We do a really good job there on the documentation side, but let's go ahead and keep it same source and target. Now what's going to happen here is we're going to look at the database backup from a log perspective, and then the application aware image processing perspective and also from the image-based backup. We're putting all of this together. When I've done this backup over here, what I want to highlight before I start that is that SQL Server log backup is chugging along. You know it's not missed any recovery points for the five-minute intervals that I've got. And again, a lot of people had questions about which databases, right? It says it up here, which databases are protected with the Explorer for this type of recovery, in which are excluded, so the excluded ones will be master, system, etc., model, the system databases, but the user defined databases, you'll see that it's outlined which ones, actually model is included in here. But anyways you have that very good visibility.

RV: So over here I have a list of all of the objects in that database. So you'll see that the stored procedures are there. Now I could actually come over here and look at this table. Remember I had that employee table, that employee data table and I have the option to restore it with the object by itself or I could restore the data to the object, or I could restore both. Now what's really interesting is I'm going to look at these other types of objects, right, a stored procedure. Same thing with view. I could restore an individual view within the database, right? All these individual objects. Now I hope you're impressed with my zoomit skills. But what I really want you to be impressed with is the ability to restore the individual stored procedures.

RV: Now I know that I broke this first one, Veeam Explorer for SQL SP. That's an intentional thing that I broke. So I can do one of two things. I know that I broke it. So I'm just going to go over here. I'm going to say, man, I know that this is broken, but I don't know what is working or what is not working. So I'm going to rename it to bad. I'm going to rename it. And actually save it correctly. Let's give it a name here. Bad admin. You know, we've all made those types of mistakes, right? I could just delete it as well, but this highlights a point, so I'm just going to go ahead and find it. I could change the name if I wanted to. Now it actually already knows that that stored procedure does not exist by the same name, which is why I renamed it. It's kind of like a file. You know, you rename a file, there's an underscore restored, etc. type of name. So this is finishing up. I'm going to go over here and it's probably going to sneak in here on me already. Let's refresh. You'll see I now have four stored procedures. You'll see that I have the bad admin at the bottom, but if I go over here to this one, let's modify this. Chances are my offs are on, my numbers look a lot better, so I have just that quickly restored that stored procedure. I'm going to go ahead and delete the garbage 'cause I know that I fixed my problem. Now that's one example here.

RV: But again, sequel Green Dream is a nice little test database and it's actually very excited blinking to tell me that it's done and it works successfully. Thank you Veeam Explorer for SQL Server, but I have a SQL Server agent job done here that I want to talk to you about. I call it Auto Drop SQL Green Dream. So what's going on here, I'll just show you some steps and again you don't have to be a SQL expert to get your head around this, but I've made a little step process here. Add a record. Add one more record and then delete one record. Two in, one out. It runs this every 240 minutes. OK, that's kind of cool. It's just, it's on a nice interval, not going to fill up my database, but it's a really good way for me to simulate Veeam Explorer for SQL Server. So what I can do is go over to the SQL Green Dream Database and I'll do another type of restore here and I'm going to select this point-in-time state. I could also, by the way, do the restore to another server, right? If I wanted to take SQL Green Dream to the development environment or to another environment, I could do that. But let's go ahead and do that point-in-time restore. I'm going to launch the Steam Restore Wizard with a few options, but I'm going to click the awesome button.

RV: Now the awesome button here is the ability to restore to a specific transaction. This is going to allow me that very, very granular recovery and you're going to be blown away by what you've seen. Now, like the other restore scenario, it's going to interpret the application aware image processing step that Andrew explained. It's going to interpret the contents of the image-based backup and it's also going to interpret the contents of the logs, but while this is loading and this does take a brief hot minute to load, I want to highlight something. And I have a link to this in the last slide here. Over at Veeam Hub on GitHub, I've created some SQL scripts for Veeam Explorer for SQL Server. If you want to drop in that database on a test environment or production or whichever you're more comfortable with, we have as long as you have an S drive and if you don't have an S drive you can change the letter. This will create the SQL Green Dream Database. The very same thing that I'm messing with in the lab. You have this that you can use in your environment so that SQL Server agent job will be created. All of these stored procedures will be created. All of the views will be created. The system table will be created. All these aspects of generating random data would be created, as well as the actual database. So if you want SQL Green Dream in your environment, you can run it.

RV: And actually we had a question come in, somebody asked if you do these types of backups, does SQL mark the database is backed up, right? If we're coming in with an image-based backup, does it mark the database and the answer is yes, it does. This little script here will actually say when the last databases were backed up and you could see the system databases have different options, but the main user defined ones have our own dates minus the temp one. We still can recover these, we just can't use the Veeam Explorer for those.

RV: OK, so we're going to come back over to the restore wizard. And what we'll see here momentarily is that SQL Server agent job that is adding two records, deleting one, adding two records, deleting one, that becomes really visible here. Look at this. So the SQL Server agent is exactly as I would expect every 140, every however long, running two rows inserted to that employee table, one deleted. Now the important thing I want to highlight is if I had a data loss issue like data deleted. If I go right here and restore to that transaction, this will rebuild the database right before that last row was deleted and I'm going to go ahead and do it. So what's going to happen here is that last deleted row won't have been deleted, so let's double check how many rows did we have here. We had 370, 300 and no, that's not it. 300… that's just…open it up. We had 381. We should be back to 380, so I'm going to let that go for a second. Might take just a quick moment. Yeah, so we had 381. When I'm done here, this should end up being 380 and you don't necessarily need to know which one it was.

RV: I'm going to jump in and take a look here at some of the questions. I'll thank you Andrew, for running these and actually Blake asked the question about how big they are. I'll admit, Blake, they're not huge, but I'll also tell you I'm running these demos from a dedupe appliance. OK, so that's not normal either, right? If you want the highest performance, you're going to want to use general purpose storage. Ahh, look at it go. It's actually flying through. So basically just as we're talking here, Blake, this particular database was reconstructed to that moment before we dropped that last employee data record. So actually no, it shouldn't be 381, it should be 382, right? So if I restore before delete, it would actually be one more than what it is….Final answer I'm expecting. So right now before I did that restore it was 381. What do you think? Oh, 380 I had it, I had it wrong I think somehow and how I calculated it, but you get the point that it is one off of the end. So that being said, I could probably just make it a linear progression of counting one to 100 rather than these cryptic rows, but you get the point that I'm able to restore just back.

RV: You know on how does it compare to native SQL backups, well I’d actually love you to try it because you know we’re restoring to a point in time with native SQL. I'd love to see if somebody could do it that easy. I'll also flip it around, another angle you can do on the restore is you could just say like you might not know that the problem was somebody deleted a database row. You might just know that the problem was right at 12:17. We got a report of an outage, right? So I dropped the slider to one minute before that, right? If people started complaining at 12:17, let's restore the database to 12:12. OK, so I can even do something janky like that if I wanted to. Again, I might not even know what the problem is. Let's do it. This will put both. This will actually put everything back. It will put the stored procedures back. It will put the tables back, the views back, everything will be reset. So when it comes to doing the point in time recovery to…I forget who answered that question. Is this faster than doing it DBA style? I'd actually love to see a DBA try to race me to it, right? My only practical advice for those of you in the space is…you know, for tier one databases make sure those backups go on high performance backup storage. I mean I'm using a dedupe appliance. It's not necessarily recommended. I'm using nested virtualization for all of this, right? So this isn't by any means production class, but I'm comfortable demoing it to you. So that being said, that kind of completes the demo in regards to recovering data. Oh, thank you Michael. It is faster than the DBA point-in-time recovery.

RV: Cool, but one thing I'll highlight. OK databases back. OK, so I went back a little bit. I actually think it might be back to 381. Let me double check…nah stated 380 'cause I didn't go far enough back to get anywhere but one thing I want to highlight, the last thing on a recovery. And AZ is going to talk about the upcoming enhancement in a second. Publish. Publish, what's that mean? Well, publish takes this database, SQL Green Dream, from the last image-based backup. And it's actually going to mount it on another database server. As a database, for test dev reasons, how cool is that? Have you ever had the business ask for last night’s copy the database so that they can…so that they can do some analytics or something like that, or they want to do some analysis of the different types of data that they saw in business reasons, right? You could publish that database, right? So I want to just get your head around this, right? But there's always more to it. So Andrew, I'm going to jump over to some of the questions, but what are other...what are some of the other things we want to consider?

AZ: Yeah, well, I didn't want to stop at backup and recovery. I just wanted to provide a couple of other points here and mainly free of them. Just probably we do not get a nice slide about the data labs, but I'm going to mention that anyways. But first of all, let me actually talk about application level monitoring 'cause I believe Veeam can offer you a little bit more than just playing backup and play in recovery or even you know intelligent backup and intelligent recovery. But here we might have different challenge. So the challenge is about visibility into the status of the SQL services. How can you make sure that they are running and what can you actually do manually or automatically when something is not right and we've got a solution for this. So a couple of times we already mentioned Veeam ONE as one of the tools in the Veeam arsenal, but here it is. So it basically it allows people to check a couple of things. The virtual infrastructure, the physical infrastructure and backup infrastructure. So what I mean by that is when you have a Veeam Backup & Replication configured. If you point Veeam ONE, which is another solution that we've got and basically both of them, Veeam Backup & Replication and Veeam ONE, they form Veeam Availability Suite. Some of the people already mentioned in the questions that they have their license for it and…so Veeam ONE, it provides monitoring and reporting for backup and replication, but for this case what's most important here is there monitoring for particular applications or particular virtual machines that you run in your infrastructure in your environment?

AZ: And if you just click a couple of more clicks there. Yeah, I wanted to show you this picture because this is clearly, this is the interface. This is what you will get from Veeam ONE monitor and then by going by scrolling there you would be able to go to any of the specific alarms such as this one. And those are the services running inside of the server that you have here and one of the services here is about the MS SQL, obviously. And then if you right click on that service, you can see that you…while you can start it, you can restart it, you can stop it or you can even create the alarm setting for it. And what is nice about that, for example, my SQL service is not running for more than five minutes. Veeam ONE will detect that it could notify you about it by sending an email. Or you could even perform some actions in automated way. OK, if something like that happened, I would like it to be, you know, I would like this script, which is going to restart the service in this machine and I would like Veeam ONE to actually to run this script for me and so to make sure that you will get availability 24 by 7/365 or something close to that one and then obviously their resolution type. Whether you want to have it as a manual because you would like to confirm the operation, you don't trust any system and you want to have full control over it or you want us to do it in an automatic way just because you have a script and you provided it in queue and we should... You anticipated that type of scenario that could be happening.

AZ: The second thing I would like to talk about and…Rick we need to go to the next slide for this one to actually talk about something that you were expecting from our solution to bring in future. And for those of you knowing Veeam, you know that we've been doing something like annual releases and we have recently had our annual conference, VeeamON 2020, where we gave people, audiences a sneak peek into their next release that is going to be delivered quite sometime in future. But then one of the interesting aspects of their capabilities that are coming there is the ability to instantly publish the databases and stay with me here. If you know something about Veeam that there are some of the technologies that we are famous for and one of them is Instant VM Recovery. What it means is that we can…we have the backup of the virtual machine somewhere in the file, but then what will happen in the event of disaster? So people just go to Veeam Backup & Replication console and click in some VM recovery and the VM is up and running within a minute or a couple of seconds. Or a couple of VMs because in version 10 in the previous one, we've got the ability to do that process in parallel with, for multiple VMs. Rick was actually showing a nice demo at one of the previous three months about it running 30 different virtual machines in parallel right from the backup there from one backup file, which is really impressive.

AZ: And so with saying that, let me let me actually switch your mindset to the SQL and databases because the same type of functionality is coming for SQL databases, the ability to spin up any database from the backup file without decompression or without reduplication from the backup file. And so the database should be available for you. Well, we don't…we don't actually plan to do the demo for that, but then it's going to be a huge topic next into the future, and so the ability to run the database a while the actual process of, kind of taking the database out of the backup file and streaming the data to the SQL Server being somewhere in the background, but then think about it as the instant access to the life database right from the backup file. This is something that we're going to offer quite soon.

AZ: And besides of that, probably the last point here that I wanted to make before we go to the resources and if you have any time for the remaining questions, I wanted to talk briefly about DataLabs. This is one of them. Functionality is also available within Veeam Backup & Replication but basically an ability to run the copy of your production environment to have it isolated. So isolated environment copy of production next to the production using production resources. But at the same time separated and then what it means by that, that you can spin up those SQL servers because this is something that we're talking about here, right? And then SQL servers, you can perform any test that you want for them. Whether your SQL services are running, whether your specific databases are running, whether you'd need to, let's say to testing your patch being available for the database, or the new SQL query, right, you can do that. And if you are not happy with the results, you can just discard all the changes and you've been just playing with that kind of, with the temporary data anyways. So this is the third point I wanted to make about the possible interesting capabilities for SQL servers. We've got a lot of resources for that available and just, you know, the keyword here is Veeam DataLabs. This is the functionality that allows to do that type of the testing.

RV: So good stuff, Andrew, and I had a couple of questions here. One question was, I didn't plan this demo, but I actually got to know…the question was can I restore the table to the same database but with a different name? That was a question that came up, and actually I believe you can, right? So let's take SQL Green Dream and let's break it more. So we have that table employed. But let's take that and restore it with a different name and I know that you can do this with or without the contents of the database, so I'm going to do that real quick and will actually sneak in another demo. One other question I had, but a lot of questions about the cluster support. So, short answer always on clusters, failover clusters are supported. The only type of SQL cluster that's not supported….ding, ding, ding, ding, is a CSV cluster for SQL. Yes, some people do that. Anyways, that is not supported, but what you can do is still use the agent. It does require the Veeam Agent for Windows. And so here, let's take that table and then it's going to… ha, ha, yes, we can change the name. Who asked that question? Let me see. Let me see. Let me see. Let me see, let me see. That question was from…….I can't remember who answered that question, but I'm just going to say….live, let's try. So I'm going to give this a new name here and…I can, ooh this is cute. I can change the file groups. Wow, that's pretty flexible if you needed to do that. I don't have any fancy file grouping going on this database, but if I go back over into SSMS I should have a new table and it should have…yes, the data, live let's try, and let's pull in the data up 377 rows. And why did it not have 380? Because I didn't take a more recent restore point. That's why. So it had a few less because of that. 'Cause I just picked a random restore point. I didn't pull the slider up. Good stuff.

RV: And here are some of those links that we spoke about. So what I mean by that is the GitHub. You can download, beat up on the SQL Green Dream Database on your own. You can have it in your hand and it's free. I also encourage you to download a trial of Veeam Availability Suite, so Veeam Backup & Replication, Veeam Agent for Windows, that's included there and you could use it and compare it. Does it work as a VM? Does it work as a cluster for you? Do you want to use Veeam Agent? I had one question about using it as a Nutanix VM. The answer is yes. It all starts with that trial. Transaction log backup, this Explorer backup documentation. I probably should have put cluster docs, but it's all in the help center. Once you're in help center, it's very easy to navigate all of that.

RV: So wooo, Andrew and I have done our best to bang through these questions. You guys have kept it real. I appreciate that. You know you can always bug us on Twitter. I'm @RickVanover and AZ is @andrewzhelezko. You can bug us on Twitter if you want more, but on behalf of the Veeam Global Marketing Team, the Veeam Product Strategy teams, I'd like to thank everyone for attending this webinar. That concludes today's featured broadcast.


Andrey Zhelezko
Technologist, Product Strategy, Veeam Software
Rick Vanover
Senior Director, Product Strategy, Veeam Software
Rick Vanover (Microsoft MVP, Cisco Champion, VMware vExpert) is Senior Director of Product Strategy for Veeam Software based in Columbus, Ohio. Rick’s experience includes system administration and IT management; with virtualization, cloud and storage technologies being the central theme of his career recently. Follow Rick on Twitter @RickVanover or @Veeam.
Show less Show more