Shulou(Shulou.com)06/03 Report--

For production data, data security is critical, and any loss of data can have serious consequences. As a copy of data, backup can reduce the loss to the lowest point according to the backup database and transaction log file restored to the latest time point when the database fails or is destroyed.

For traditional backup, the database is usually backed up to local disk/tape storage using the backup that comes with the database or third-party backup software. The premise of this backup method is that there needs to be a large storage pool to store our backup files according to the life cycle of the backup files. This means we need to prepare a large storage pool. Given the resources required to prepare a storage pool, many of your friends will ask, Can I back up my database to the cloud? The answer is yes. So let's talk about how to back up SQL Server databases to the cloud and the benefits of backing up to the cloud.

The benefits of backing up databases to azure storage are as follows:

Flexible, reliable, unlimited off-site storage: Storing backups on Microsoft Azure Blob Services is a convenient, flexible, and easy-to-access approach to off-site storage. Creating off-site storage for SQL Server backups is as simple as modifying existing scripts/jobs. Off-site storage locations should generally be located away from production database locations to prevent a single disaster that affects both off-site and production database locations. By choosing a georeplication Blob store, you have an extra layer of protection in the event of a disaster that could affect an entire area. In addition, backup copies are available anytime, anywhere, and can be easily accessed to perform restores. Backup archiving: The Microsoft Azure Blob Storage Service provides a better alternative to the common tape storage method when archiving backups. Choosing tape storage may require physical transport of data to offsite facilities and some media protection measures. Storing backups in the Microsoft Azure Blob Store provides an instant, highly available, durable archiving solution. No hardware management overhead: There is no hardware management overhead associated with Microsoft Azure services. Microsoft Azure services manage hardware and support georeplication to provide redundancy and protect against hardware failures. Currently, SQL Server instances running in Microsoft Azure virtual machines can be backed up to the Microsoft Azure Blob Storage service by creating additional disks. However, there is a limit to the number of disks that can be attached to a Microsoft Azure virtual machine. The limit is 16 disks for very large instances and fewer disks for smaller instances. By allowing direct backups to the Microsoft Azure Blob Store, you can bypass the 16 disk limit.

In addition, backup files currently stored in Microsoft Azure Blob Storage Service are directly available to on-premises SQL Server or other SQL Servers running in Microsoft Azure virtual machines without database attach/detach or download and attach VHD. Cost equity: You only have to pay for the services you use. It can be used as a cost-effective off-site backup archiving solution.

However, it is important to note that backing up SQL Server to Azure Storage requires ensuring that the database SQL Server 2012 SP1 CU2 and above.

With that said, let's take a look at how to backup a database to Azure Storage.

First we need to prepare a storage account:

Get the access key. In this example, we use the access key to access the storage account:

Create an account in SQL using T-SQL as follows:

IF NOT EXISTS(SELECT * FROM sys.credentials WHERE credential_identity = ' bkuptour')CREATE CREDENTIAL bkuptourl WITH IDENTITY = 'sql12bak', SECRET = '5amfZJpKLcR2lAfEBZod18VYbxZOPUbtKH9RowDQ3Ixv5sGMnSj5Lo/UbPb/zCCRKh/kAxnOaOS9oJELTVVoTg==';

Once created, we can create a container in the storage account to store backup files:

Once created, you can back up the database to Storage Account using T-SQL as follows:

DECLARE @DB_name VARCHAR(50) -- database name DECLARE @BackupLoc VARCHAR(256) -- path for backup files DECLARE @BackupfileName VARCHAR(256) -- filename for backup DECLARE @fileDate VARCHAR(20) -- used for file name-- specify database backup container locationSET @BackupLoc = 'https://sql12bak.blob.core.chinacloudapi.cn/test/' set @fileDate= replace(replace(convert(nvarchar(50),getdate()),' ','_'),':','_')DECLARE db_cursor CURSOR FOR SELECT name FROM master.sys.databases WHERE database_id 2 and state=0OPEN db_cursor FETCH NEXT FROM db_cursor INTO @DB_name WHILE @@FETCH_STATUS = 0 BEGIN SET @BackupfileName = @BackupLoc + @DB_name + '_' + @fileDate + '.BAK' BACKUP DATABASE @DB_name TO URL = @BackupfileName WITH CREDENTIAL = 'bkuptourl',COMPRESSION print 'BACKUP DATABASE '+@DB_name+ ' TO URL ='''+ @BackupfileName +''' WITH CREDENTIAL = ''bkuptourl'',COMPRESSION ' FETCH NEXT FROM db_cursor INTO @DB_name END CLOSE db_cursor DEALLOCATE db_cursor

The backup is complete, as shown in the following figure:

Welcome to subscribe "Shulou Technology Information " to get latest news, interesting things and hot topics in the IT industry, and controls the hottest and latest Internet news, technology news and IT industry trends.