Video : Restore Database in SQL Server using Advanced Restore features in SQL Safe Backup

Transcript

00:09

Restore Database in SQL Server

In this video, I’ll be discussing SQL Safe Advanced restore features, namely Instant Restore, Virtual Database and Object Level recovery. These advanced Restore features allow you to quickly restore an entire database or specific objects out of the database that you’re interested in.

SQL Safe Backup

To start off, I’ll talk about Instant Restore. Unlike a normal restore operation, instant Restore breaks the restore operation into two different phases. In the first phase, SQL Safe simply restores a structure of the database, then brings it online. Once the database is online, we move on to the second phase called Hydration. During Hydration, SQL Safe essentially pumps the data back into the database files. Keep in mind, the database is already online at this point, so users are actually able to gain access to the database, start running queries against it as though it’s a fully functional database, even though the restore operation is still ongoing.

01:02

That is one of the key benefits of Instant Restore the ability to gain access to your database much quicker than a traditional normal Restore operation. Now, to demonstrate this feature, I’ve already performed a few traditional restore operations as well as the Instant Restore. You can see here the vast time difference. With the traditional restore operations, it took roughly around 40 seconds for the restore to complete. Now, keep in mind that’s 40 seconds for this particular database that I’m testing with. That’s also 40 seconds before users have to wait before they’re able to gain access. Now, previously, you can see here, I ran an Instant Resource operation. It took 3 seconds before the Instant Restore brought the database online and then moved into the Hydration phase. Their Hydration phase, it took another 19 seconds to complete. Overall total was about 21 seconds for the whole restore operation.

02:01

Restore SQL

Still again, much faster than you would see with a traditional restore. Now, I’m going to run through again, just so you can see how you would normally do it. So I select Restore, select database. I select my Target SQL Server instance, select the database that I want to restore. In this particular case, I’m going to go with that first one, select the backup file that I want to use for my restore operation, and I am going to rename this as IR and select, of course, Restore, because the database already exists. I want the database to be fully accessible once the restore is completed, and I am going to use the Instant Restore. Again, this is just to demonstrate the Instant Restore ability. Again, within a few seconds, you can see that the database is almost completely online. It moves onto the Hydration phase. Again, keep in mind, it took a few seconds longer maybe, but the database is still online much sooner than you would see with the traditional Normal Restore moving along.

03:10

Virtual Database

I want to talk about virtual database. Now, Virtual Database is a slightly different technology. Rather than actually restoring the database. What we’re actually doing is we’re tricking SQL Server into believing the database has been restored, when in actuality we’re reading the data out of the backup file. Now there’s a couple of benefits to using Virtual Database. The firstly is that you’re able to gain access to your database much quicker than you would with a normal restore. The second part is that if you’re limited on this page, but you need to have the database restored for whatever reason, you can use Virtual Database again because we’re reading data out of the back of file. Now that’s not to say that we’re not going to write some data to the disk, but it’s not going to be the entire size of the actual database itself. Now to demonstrate, let me first go ahead and take a look at my D drive because that’s where I’m going to be restoring the database too.

04:06

I will take a screenshot of the D drive for reference. There’s my before screenshot and I am going to go ahead and mount Virtual Database. To do so, it’s very, quite simple. Click Mount Virtual Database, select my target SQL Server instance, I’m going to use the same database, same backup file, and notice that the Virtual Database name has VDP appended to it. Basically we’re storing as a new database. You can see here that I am restoring it to the D drive. I’m going to go ahead and mount the Virtual Database and then refresh. You can see that the database is already available, it’s online. If I come back to SQL Server management studio and refresh the list of databases, you can see the VDP here. And again it is accessible. I can run my queries against it, I’m able to access it. SQL Safe basically is like I mentioned earlier, it’s reading the data out of the backup file in real time.

05:17

Real quickly, let’s take a look at the properties of this database. You can see the size of the database is roughly around close to two gigs in size. The database files are on the D drive sorry, on the D drive. Looking at the screenshot that I took earlier and then let’s go look at the actual size of the D drive now, sorry. You can see that it has increased some, but not nearly as much as you would expect given that the database is almost two gigs in size. You can see how Virtual Database is really nice there in terms of saving this space if you’re limited on it.

Object Level Recovery

Now moving along to Object Level Recovery, that’s another feature that is one of the SQL State’s advanced restore features. If you’re just interested in restoring particular objectile database, object Level Recovery is great for that. To do so, you simply right-click on the instance name, select Object Level Recovery, select your target SQL Server instance, and in this particular case I’m going to restore a different database.

06:36

Actually, let me select it from the file system instead. I’m going to browse, go to my D drive because that’s where I store my backups, and I’m going to select my backup file that I want to use. This is again, the backup file that I want to be using for the Object Level Recovery. On objects that selection on this tab, this is where you would select basically the object that you want to restore. In this particular case, I’m just going to restore the customers, take a note, the database name that we’re using here, and I’m just going to click Recover Refresh. Now, Object Rollover Recovery does take longer than you would see with the Virtual Database or Instant Restore. Well, maybe not. The entire really depends on the size of the database that you were restoring and the object that were restoring. Once the operation is complete, here we’ll go.

07:36

Take a look inside of SQL Server management studio and just see that you’re able to actually access that particular object that we just restored. So, I’m going to refresh the list of databases here, and you can see Northwind stands for Object Level Recovery. If I expand tables, you will only see that one particular table that I had selected to restore. Again, that basically covers the three different features that SQL Safe has built in terms of advanced restore features. Just to repeat, those advanced restore features are instant restore virtual Database and Object level recovery. I hope this video has been informational and thank you for watching.