Video : Restore Database in SQL Server with SQL Safe Backup

Transcript

00:08

Restore Database in SQL Server

Within SQL safe ideas, enterprise, SQL server and recovery solution, there are two ways that you can quickly restore a database to an instance. The first being Instant Restore, and the second being creating a virtual database. We’ll go over instant Restore first.

Instant Restore is used to allow instant access to a database while the rest of the database data is being restored in the background. To do that within the SQL Safe console, you have to actually enable SQL Safe Instant Restore. What this does is it installs a SQL Save filter driver here, and I actually have that in my Windows service, and this would help to populate those data pages once the catalogues are initially restored here. Let’s go ahead and start an Instant Restore here. Just right-click on your instance. Click restore databases instantly. Right. I’ll just instantly restore the database to this database. My Windows 2012, we’re not going to restore all databases.

01:17

SQL Backup

I have a specific backup file that I’m going to use for a restore. Now, you can do a point in Time Restore. Now, these require that maps are generated with your Safe backups here, and I’ll show you that option and how restore works. Here. We have our backup file here. This is the point in time area. We’re just restoring a full backup. One thing I will mention here is to check Enable Network Resiliency, especially if you’re restoring off of a server that’s going across the network, whether it’s wide area or just local network. Also, in the case of an IO Blip, network Resiliency will guarantee that you can retry after a certain number of seconds, and you can fail after retrying for about five minutes here. Okay? That restore operation doesn’t continue running here, but it will pick up at the same point of failure.

02:20

So very important to enable network resilience. All right, your database policy, you can set your database name. I’m going to change this database name here just to signify. It is an Instant Restore database, and you can see that the data and log file names have changed as well. The locations look fine to me. Your cover, your state leave is fully accessible now here’s where you can do a normal SQL Safe Restore, where it just does a normal restore like it usually would natively. However, I’m going to choose SQL Safe Instant Restore. And you have a link here. How does instant restore work? Before you start doing an Instant Restore, it is important to understand how it works here. All right? I’m not going to read through this page, I’m just going to summarize it. Essentially what’s required is that you have SQL Safe Backups with backup metadata or what we call maps.

03:14

Metadata

This can be set at the global setting, tools, console options, or you can set this at the individual backup level. So that’s very important. All right? Also we can’t do an instant Restore for file based restores, but we can do point in Time Restores up through the transaction log. All right, we have our different statuses of our Instant Restores here. We initially bring the database online, populate the catalogs and tables and all the objects there, and then we start to hydrate the data. In the case you have a very large database, a terabyte or more, that SQL Server restaurant or Driver Service will populate that data in the background until it completes. All right? In the meantime, queries typically run while it’s hydrating about 20% slower if you’re running a big bunch of query, a lot of joins, a lot of outer joins on very large tables, but that goes away once and you’ll have normal performance as you would with the regular database once all the data pages are populated.

04:29

Restore Database

Let’s go ahead and go back and complete our operation here. All right? You can send notifications if it fails, succeeds, or anything in between. I’m just going to leave this blank and then now we have our summary, we can review this. We can also generate a script if we want to script this out into maybe a job or Integration Services package as part of a larger kind of ETL process or refresh process. I’m just going to click Restore here, and what you’re going to see here is the database is now online and now it’s starting to hydrate here. Okay? I’m going to go into Management Studio, refresh everything, go into databases. Here’s my IR SQL Profile trace Database, expanding out the tables, and I’m just going to select the top 10 out of this first table here, so you can see it’s still hydrating here.

05:25

It’s about 30% through, and I can query as many of these tables as I want. All right. I can also apply DML statements as well. Once this restore populates, it behaves just like any normal database, abiding by the asset concept of transactional support. All right, so now we’re done here. Very quickly, we restored how large is this database? I think it’s about 1012 gigs, something like that. So about ten gigs. Not very large in the grand scheme of things in my demo environment, but I’ve heard of customers instantly restoring multiple terabyte databases in less than about ten to 15 minutes, so that’s pretty impressive. Okay, so another way to restore a database backup very quickly is to create what’s called a virtual database. This functionality is only available within the web dashboard. All right, so the web dashboard right there, dashboard really acts as a kind of a federation of all of these SQL Server solutions here.

06:42

Virtual Database

You see all the different solutions I have here. You would select SQL Safe and navigate to Virtual database. Now, again, you can do a point in time Restore by attaching multiple backups. I’m just going to keep this simple for this demonstration here. You select the hostname here, I’ll select my Wind DC 2012. Again, you can select your backup file. I have a pretty simple setup here, just one C drive, two C backup, and then my virtual database directory, all right? I’m giving it a name here, Dmvdb, just so I know what it is and I can create that virtual database. What we’re doing here is we’re essentially attaching this backup file to the instance while not taking up the normal amount of space that backup would normally take. All right? Again, if I refresh my windyc instance here, you can see on my Dmvdb database, you can see all the tables and views that it has here, programmable objects that has lots of store procedures and whatnot, you can execute those against this database and you can run any SQL statement that you normally would.

07:54

Now, the difference here being is once you detach this virtual database from your instance, your transactions are going to be lost because you’re essentially running queries against a backup file, okay? You can query this database just like you normally would. Virtual databases are usually used for getting a quick production backup available to reporting analysts, people, developers that maybe just need to run quick tests so very easily, very quick to do that. Now, if I bring up the properties of my DM virtual database here, I can see that the total size is about a little over one gigs here. If I go to individual files, I can see that the data file is about 996 megs, 165 megs for the log file here. If I navigate to C backup when DC, I’ll see that the actual size on disk of not only my log and data files, it’s much shorter.

08:55

SQL Safe Backup

If you look on size on disk, three megs for the log file, as opposed to 165 megs, and then 5.25 megs as opposed to almost a gig for the data file. Like I said, you’re just attaching a virtual backup file to your SQL Server instance so it comes in handy for data analysts. Need a quick production refresh to run reporting reports off of, do some testing, things like that. All right? Please go to Idera.com, navigate to the SQL Safe Backup homepage to download and start for free a fully functional 14-day trial. Thank you and have a great day.