DP-300 Microsoft Azure Database – Perform backup and restore a database by using database tools – VMs Part 2

3. 108, 110. database (and transaction log) backups with options in T-SQL code

In this video we’re going to see how we can perform the data backups with options in TSQL. So in the previous video we generated this so backup database, name of database to a location with and then lots of options separated by commas. So let’s see what options we got available. So we start off with backup database and the name of the database base.

We may also have file group names. So useful as I said for backing up only part of a database and then the location and then to a previously named backup device. You could also be having two disk equals and a file location like we have got here. You could also be having to tape equals and tourl equals and Https in the quotation marks we can mirror to another backup device.

If you have got the Enterprise edition of SQL Server, it must be at the same time as the primary backup device and you can have up to three secondaries. So it’s useful if you want additional backups. And then with and we have got all of these different options, we’ve got many more than these, but these are probably the main ones you need to know.

And as we’ve seen here, you separate them with commas so copy only. So that’s useful for creating a full backup, but is not treated as a full backup for the purposes of future differential or transaction log backups. It’s also required for managed instance differential so that’s all of the changes since the last full backup. I’ll talk about log backups later on. Compression or no compression.

This is only used in the Enterprise edition and it overrides the server level default if used. You can also put in a backup set description and a name. You can use Credential if you’re connecting to backups to Azure Blob and you can use Encryption and you can choose from as underscore 128, 192, 256 or triple underscore DS underscore three key or no underscore Encryption, which is the default. If you do encrypt, you will also need to use Server certificate or Server asymmetric key.

Then we have got the file underscore Snapshot. So this is used when creating a snapshot of the database files and storing them in Azure Blobs. So you can see when the expire date is. So don’t read this as expired date, it’s expire date or retain days. Then we’ve got options relating to the media set so we got no init or in. It stands for initialization whether the backup operation appends to or overrides the existing backup sets on the backup media.

So the default is no in it, so it appends. We’ve got no skip or skip checks. Whether a backup operation checks the expiration date and time of the backup sets on the media before overwriting them, the default is no scape. In other words, it checks the date and the time, no format or format. Whether the media header should be written on the volumes used for the backup operation overwriting any existing media header and backup sets. The default is no format. Be careful when using format as it renders in

time media set unusable other than what you’re doing. And by the way, format implies skip so you will be skipping the expiration date if you’re using format for error management, we’ve got no checksum or checksum, that is whether backup checksums are enabled. So this validates the backup. The default is no checksum so there’s no generation of backup checksums and then we’ve got stop on error and continue after error wherever backup stops. If there’s a page checksum error, the default is stop on error. So stop if there is an error if it doesn’t verify continue after error. Best use if there are checks on errors and you know that and you’re just trying to get back what you can stance equals x whether a percentage is displayed every x percentage.

So for instance the default is 10% so you could have a different figure in here if you want. If you are using tapes then you’ve got rewind and no rewind. Whether a tape device is released and rewound, the default is rewind and unlock and no unlocked. So whether the tape is rewound and unloaded and the default is unlocked. If we want to back up the log is very similar. Instead of backup database, it’s just backup log. We’ve got to make sure that we are using the full recovery model first because simple will not work and the with options much easier.

No recovery, no truncate. So no recovery backs up the tail of the log and leaves the database in the restoring state. So this is useful when failing over to a secondary database or when saving the tail of a log before a restore operation nor truncate the log is not truncated and requires SQL Server to attempt to back up regardless of the state of the database. This is generally used if the state of the database is suspended when the database has failed. And we’ve also got standby.

This backs up the tail of the log and leaves the database in a read only and standby state. And this file that you specify holds the rolled back changes. So you can see that there are an awful lot of options here which can be put into your backup. So it may be worth just having another look at the backup GUI and you can see that we’ve got copy only backups, we can see the destinations, we can see the backup type, so we can see differential. For instance, have a look at your media options, whether to append, where to overwrite, whether to check the media, set name and backup set expiration, whether to verify the backup when finished perform checksums, continue on error and we’ve got the transaction log things here as well and the tape drives down here.

We’ve also got name backup expiration, the compression and encryption used now. Not everybody can actually back up the database. Of course, you’ll need backup database and backup log permissions if you want to back up the log. They are already granted in the sysadmin. Fixed server role and the DB underscore owner and DB underscore backup operator fixed database roles. This is how you can perform database and transaction log backups with options.

4. 91, 92. perform a database restore with options

In this video, we’re going to look how to perform a database restore with options using the GUI. Now, for me to be able to restore database, I need to have create database permissions so that exists in the sysadmin and DB creator, fixed server roles and the DB or the owner for existing databases. So let’s see how we can get to this page. While I can restore an existing database by going to Tasks and Restore Database, when I do, notice what it says a tail log backup of this database will be taken. In other words, it will get the last transactions. Also notice we have got these backup sets to restore already checked, and we can verify the backup media and we can look at the timeline. So we have our full database backup here. Now, notice there haven’t been any additional transactions, so let’s create an additional transaction. So I’m going to go for this table and I’m going to say truncate table. So this gets rid of absolutely everything. So maybe I’ll actually do delete from DBO my table, because truncate table isn’t logged as highly as delete from table.

So if I now change that to select, you’ll see that this table is now completely empty, blank, no roles whatsoever. So oops, I’ve made a catastrophic mistake. No problem, let’s go back into the Database and Tasks and Restore Database. Notice you can also restore files and file groups, transaction logs and pages. So I can go into my timeline and say, okay, I want to restore to a particular point. So my very specific point is here, for example, so I can choose any point and go just there. So click okay. So the till log backup will mean that everything that happens later, in other words, the deletion I have just done will also be taken into account. So it will back up everything that’s been done since the last full or differential backup or transaction log backup in fact, and then restore. So it will also work out what the LSN is. So LSN shield for log sequence number.

So it will go okay, this particular point, that’s why we’re backing up. Let’s have a look at what else we’ve got. First of all, we’ve got the source. So the source is a list containing a database’s back top based on the msdb backup history. Or we got a device. So this could be tape, URL or file. Now, this is required if the backup was taken on a different SQL Server instance. Now, I can select up to 64 different devices that belong to a single media set. Once I select a device, then I can select a database on that device, so backups on the selected database, but I’m going to take this particular database. Now, next, where to restore so I can ever enter a new database name or an existing database on the drop down list. So this drop down list includes all databases on the server excluding master and tempdb. So it includes model and msdb, the restore to restore to when the last backup was taken. I’m going to change that again. I am going to say go to a specific time.

So now it will do the tail log restore as well. So here’s the restore plan. So the backup sets to restore. So the default is the recovery plan suggested to achieve the goal that I’ve just set. You can also verify the backup media that checks the integrity of the backup files prior to restoring them. The files page shows that you can relocate all files to a particular folder. So it shows the logical and original file name, the file type and the file path and name to restore as.

So that can be useful if you want to restore to different folders. In the options page, it shows overwrite the existing database, so that overwrites database files preserve the replication settings. So only relevant if a database was replicated when the backup was created and when we’re storing a published database to a different server other than the creation database. So if it’s replicated and you’re doing it elsewhere, notice all of these waves. We’ll be looking at these waves in the next video. We can restrict access to the restored database that’s with restricted user, so that only DB owner, DB creator and sysadmin members can access it. The recovery state.

Restore with recovery, that’s a default option. Choose this option if you’re in the full or bulk logged recovery mode. If you are restoring all logs at the same time. So not that you’re doing a complete restore. Restore with no recovery. This is left in the restoring state. It allows for additional backups and restore with standby. This is limited read only access and you would need to specify a standby file which allows the recovery efforts to be undone. The tail log backup.

So the bit that has happened since the last backup. So take the tail log backup before restoring. So that means you can always unwind the restore if necessary. And here you can leave the source database in the restoring state, in other words with no recovery.

And you can specify a backup file for the tail log. We got server connections, close existing connections to the destination database. So restore options may fail if there are active connections to the database. Like for instance, an open query window and then prompt before restoring each backup. The continue with restore dialog box will be displayed after each backup is restored. If you click no, then the database will be left in the restoring state. So I’m going to do it as is. So notice what I don’t have overwrite the existing database, for instance, so let’s just click OK, so it’s performing the terror and you can see it is restored, is it? Let’s go into my table and see whether all of my data is back. And the answer is yes. It is. Now this is one way to get your database back to a particular point in time. So we can right and click on the database and go tasks restore database.

But what if your database was no longer there? So what if I’ve deleted the database? Well, I can right and click on databases and restore database. So then I would have to choose what database I wanted to restore. So if I delete or drop this database might not allow me if that’s open. So there we go. And then I restored this database. Oops no backup set selected to be restored. So what I need to do is go and add my backup media. So there is my backup and I’m going to restore to this point in time. But I can add additional data if I want to additional backups. And you can see all the other options as is. Let’s click OK successfully. We restored. Let’s have a look.

Select star from DBO MyTable and few. Even though I dropped my database, I could have reattached it. It’s there. Now, suppose I was saying I want this to be in the restoring state. So I’ll just do that again and restore this database from this backup media. I could add the tail log back up as well if I so wish. So I’ll do a point in time restore as before to there, maybe a bit earlier actually, maybe I won’t do that at all. Maybe I’ll go back into these files and remove my latest log backup. So I’ve just got the one and I’ll go into options and I’ll say restore with no recovery.

Maybe I’m then going to do the tail backup restore. So click OK. And now you can see it is restoring. And look, the icon is a bit different. So now I can go back in and restore further and get additional files, say the log back up if I so wished, and then I can back this up again. And this time restore with recovery. And there we are. It is now available for use. And if I go select from DBO my table, it is now completely empty. And the reason for that is when I restored, I did a tail log backup which included all of the deletion. So it did restore everything that I did according to those backups. So I’m just going to undo that because I’d actually like some data in my database.

So I’m going to now say give me a particular time I want to go back to not use this log backup but to 1032. That’s fine. Restore that. And you can see I’ve got a problem. I cannot do this because I have got this query window still open. I need it to close at this query window before this database can be restored. And now if I go back in, select star from DBO my table, we’ve got my data back because I went back to a particular point in time. So this is how you can perform a database restore with options. So you click on an existing database and go Tasks Restore Database.

Or you go to databases and right and click and say Restore Database. And then you’ve got all of these options. You can select what files to restore, you can say where they’re going to go, and then you’ve got some additional options. And we’ll be looking at how we can replicate these in TSQL in the next video. Now, I should say that this is for VMs only for your SQL Mi.

What you could do is click on New Database and then you can say use existing data and say, I want to use a backup of the existing data. So that’s how I would do this. If I was using Azure SQL Mi, I would be using the Portal. However, unlike VMs in Azure SQL Mi and Azure SQL Database, I can’t restore all of an existing database. Whereas, as you’ve seen several times, I did do that with a VM.

5. 109. perform restore of user databases in T-SQL

In the previous video, we saw how you could restore user databases using the GUI. You could right and click on databases and go to restore database, or you could right and click on an individual database and go to tasks restore database. And there are all these different options. Now, in this video we’ll see how we can do the same thing using TSQL. So first of all, we restore database and the name of the database from and you can use your backup device or wherever you got it.

And then if you go into options, you can say with recovery and with no recovery. So no recovery is useful when you’re restoring a single file, but you need to restore more. Recovery is when you have finished restoring and you want the database to be back online. So recovery means not just recover the file, but get me back online. So we can also have a backup set file number. We can also say stop at a particular date time or stop at a mark or before a mark. So that will allow us to do the point in time restore the pitr. So we’ve got some examples on the right hand side of how we can put these together. So, restore database with file equals six no recovery, stop at and then a particular date and restore database with file equals nine recovery.

There’s a few other options like for instance with key replication with replace with restricted user, but these are the main ones. So here are some examples. Restore name a database from backup device with fire equals six no recovery stop at a particular time for a point in time restore and another one that says recovery. So in other words, this first one will not allow the database to be accessed by everybody and then the recovery will restore verify only that verifies the backup but doesn’t restore it.

Now, you can only use TSQL in a managed instance for restoring when doing a complete restore from an Azure Blob storage account. So we could have restore database, name a database from URL and then specify the URLs. Now it can only be restored onto another managed instance. So for example, it can’t be restored onto an onprem SQL server. So this is how we can restore user databases in TSQL. Restore Database name a database from where you’re storing from and then with recovery with no recovery and you can stop at a particular point for a point in time recovery.