SQL Server Backup – SQLXpertise

Simple Guide to restore SQL Server Backup from Azure Blog Storage using Management Studio

In previous post, we saw

1. How to implement Off-site/Cloud backup in SQL Server 2017 using Azure Blob Storage

2. How to restore SQL Server Backup from Azure Blog Storage using queries

In this post we will see how to restore SQL Server Backup from Azure Blog Storage using SQL Server Management Studio

Step 1: Open SQL Server Management Studio 2017 and connect to SQL Server on which you would like restore the database and right click on “Databases” node and click on “Restore Database…”

Step 2: Select Backup media type as “URL” and click on “Add”

Step 3: Click “Add” to add the new Azure Storage container

If you are not already signed in, sign in to Azure account and then select the Subscription, Storage account and blob container

Based on you requirement, set up the Expiration date for Shared Access policy, by default, its setup for 1 year from date of setup

Click on “Create Credential” to generate the Shared Access Signature (SAS), once SAS is generated successfully, click “Ok” to continue

Step 4: Please review the selected Azure Storage Container and Shared Access Signature and click “Ok” to continue

Please select the backup file, you wish to restore

Please review the selected backup media and click “Ok” to continue

Step 5: Based on requirement change other options in Files tab or Options tab and click “Ok” to start the restore process

Once restore is completed, we should see this restored successfully message

We should also be able to see the database restored in Object Explorer as well

Hope you find this post helpful !!!

Simple guide to restore SQL Server Backup from Azure Blog Storage

In previous post, we saw How to implement Off-site/Cloud backup in SQL Server 2017 using Azure Blob Storage, in this post we will see how to restore SQL Server Backup from Azure Blog Storage

Step 1: Create Credential – Please use below query to create Credential to access Azure Blog Storage using Shared Access Signature, This step is optional if you already have created Credential to create backup

CREATE CREDENTIAL [https://XYZ.blob.core.windows.net/YourContainerName]
WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET =
 'sv=Replace your SAS Key Here';
GO

Step 2: Restore Database from Cloud (Azure Blog Storage) – Please run below query to restore the database using SQL Server Backup stored in Azure Blog Storage, Please change the query to meet your needs

USE [master]

RESTORE DATABASE [SQL2017_DEMO]
FROM
     URL = N'https://XYZ.blob.core.windows.net/YourContainerName/BackupFile.bak'
WITH  FILE = 1,
MOVE N'SQL2017_DEMO' TO N'E:\SQLDATA\SQL2017_DEMO.mdf',
MOVE N'SQL2017_DEMO_log' TO N'E:\SQLDATA\SQL2017_DEMO_log.ldf',
NOUNLOAD,
STATS = 5

GO

Output:

We can also see the database restored in Object Explorer

Hope you find this post helpful !!!

Simple Guide to implement Off-Site/Cloud backup in SQL Server 2017 using Azure Blob Storage in 2 Steps

In this post we will see how to implement Off-Site/Cloud backup in SQL Server 2017 using Azure Blog Storage

Requirements

1. SQL Server 2017

2. Azure Account

3. Azure Blog Storage with Container provisioned and Shared Access Signature generated

4. SQL Server Management Studio (Optional, if you prefer to run using SQLCMD)

Step 1: Create Credential – Please use below query to create Credential to access Azure Blob Storage using Shared Access Signature

CREATE CREDENTIAL [https://sqlxpertise.blob.core.windows.net/sqlbackups]
	WITH IDENTITY = 'SHARED ACCESS SIGNATURE',
SECRET =
	'sv=INSERT YOUR SHARED ACCESS SIGNATURE';
GO

Step 2: Backup Database to Cloud – Run below query to backup the database to Azure Blob Storage, Please change the query to meet your needs

BACKUP DATABASE SQL2017_DEMO
    TO URL = 'https://XYZ.blob.core.windows.net/sqlbackups/BackupFileName.bak'
    WITH STATS = 10, SKIP, NOREWIND, NOUNLOAD

Output:

If we login to Azure Portal and Navigate to the Storage account and sqlbackups container, we can see the backup file

You can also use SQL Server Management studio 2017 to connect to Azure Storage as well to see the backup file

Having an offsite backup is always important and helpful.

Hope you find this post helpful !!!

sys.dm_db_log_stats–New DMF in SQL Server 2017 to keep track of your Log backup and Log changes

SQL Server 2017 introduces a new Dynamic Management Function “sys.dm_db_log_stats” to track information on log files, the most interesting information it provides is when was the last log backup completed and size of the log growth since last backup

This will be very helpful for high transaction system when log backup has to be done on regular basis, you can create your monitoring jobs to track using functions whether log backup is running or not using “log_backup_time”

Other option we can design the log backup job is to run it based on specific size of growth since last backup using “log_since_last_log_backup_mb” instead of running it on regular schedule, that will reduce the number of log backups as well on times when there are less or no changes

Sample Usage

DECLARE @DBID INT = Db_id()

SELECT
database_id,
recovery_model,
total_log_size_mb,
active_log_size_mb,
log_truncation_holdup_reason,
log_backup_time,
log_since_last_log_backup_mb
FROM sys.Dm_db_log_stats(@DBID)

Output

Hope you find this post helpful !!!

Using sys.dm_db_file_space_usage to implement Smart Backup strategy in SQL Server 2017

In this post, we will see how we can implement a Smart backup strategy based on changes made in your database since Last Backup, this will help us in implement Smart backup strategy to decide whether your database changes require a differential backup or full backup and also reducing the full backup maintenance time

SQL Server 2008 introduced a DMV sys.dm_db_file_space_usage which is helpful to see the space usage details for each file in the database.

SQL Server 2017 adds a new column in this DMV named “modified_extent_page_count”, so that we can see how many pages are modified in the data file since last full backup.

Please see below query to see the usage of the new field, we are using that to calculate the percentage of pages changed in the file

SELECT database_id,
file_id,
filegroup_id,
total_page_count,
modified_extent_page_count,
((modified_extent_page_count * 1.0)/(total_page_count * 1.0)) *
100.00
[% of Pages changed]
FROM sys.dm_db_file_space_usage;

Based on the % of Pages changes or Count of pages changed, you can implement your backup maintenance script to determine the type of backup required, differential or full backup.

Hope you this find post helpful !!!

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this:

Share this:

Like this: