In this topic we will restore a database from an on-premises backup apply to restoring a database from a cloud backup file.
Before you start the Restore Database Process from Azure Cloud Storage,You need Azure Storage Account, Account Key,and Credential
Create Credential
CREATE CREDENTIAL AzureStorageCred WITH IDENTITY = 'dbproxazurestorage' ,SECRET = 'HJjm/dfds+MFFmpBLQwiWR+fdsafadfsd+XDAz8uLX/asdasssdfgdsfdsfas==';
Right Click the Database in SSMS , Select Restore Database option
Select Device option and click the button […], which will open the [Select backup devices Dialog box]
Click Add, Connect to Azure Storage dialog box will appear
Pass the Storage Account & Account Key, Select the Credential which was created in the beginning.Click connect and Locate Backup file in Microsoft Azure will appear.appear. Expand the container and select the file container .On the Right side of the panel you will see all the backup files. Select the one which you want to use to restore and click OK
Chose option Files to move the files to different location and click ok to restore the Database.
Restore with T-SQL
USE [master] RESTORE DATABASE [FileTable_upgradeTest] FROM URL = N'https://dbproxazurestorage.blob.core.windows.net/dbproxsqlcontainer/FileTable_upgradeTest_backup_2015_10_28_2109.bak' WITH CREDENTIAL = N'AzureStorageCred' , FILE = 1, MOVE N'FileTable_upgradeTest' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest.mdf', MOVE N'FileTable_upgradeTest_log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf', MOVE N'FT_DF_FG' TO N'C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FT_DF_FG', NOUNLOAD, STATS = 5 GO
Enable the trace flag to see what happened in the background
Restore(FileTable_upgradeTest): Acquiring U lock on the database
Restore(FileTable_upgradeTest): RESTORE DATABASE started
Restore(FileTable_upgradeTest): Acquiring bulk-op lock on the database
Restore(FileTable_upgradeTest): Opening the backup set
VDI: "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016600\MSSQL\Binn\BackupToUrl.exe" "r" "p" "680074007400700073003A002F002F0064006200700072006F00780061007A00750072006500730074006F0072006100670065002E0062006C006F0062002E0063006F00720065002E00770069006E0064006F00770073002E006E00650074002F0064006200700072006F007800730071006C0063006F006E007400610069006E00650072002F00460069006C0065005400610062006C0065005F00750070006700720061006400650054006500730074005F006200610063006B00750070005F0032003000310035005F00310030005F00320038005F0032003100300039002E00620061006B00" "64006200700072006F00780061007A00750072006500730074006F007200610067006500" "01000000D08C9DDF0115D1118C7A00C04FC297EB010000001F1ACC427FFF1546ADDAC1E6CA9FB04D000000001200000061007A007500720065006B00650079000000106600000001000020000000B36059C427D46A1B36B3482934B09CA43B029608E2A8DC1BD080DF66ECCD9D61000000000E80000000020000200000009C0C4C853568176BA99C66E70C80BF9DC6FC9870BBE2F968A9F5D24305ED60F7500000001C931624DD1B56D2B263F84F8040CBCC1D27102B340F9BF8F22E2F85FC4643E1A6A6F99FE9580F7B7FF36230DBB9030D8F78701A40085D99DA621F738BCDB92CC2DB4A0CFB81EC664B95D47FF3BB971E400000000487BDB14306D0B03A91BF92169057917456AF946E1BC4ADECD8DC3D0E9F91BBBA83AFFF5AD0728BA3B44AC921627AA965030FE0273A17C8C782792DA6C92E67" "NOFORMAT" "530051004C003200300031003600" "C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\Log" "DB" "460069006C0065005400610062006C0065005F0075007000670072006100640065005400650073007400" "NOTRACE"
BackupToUrl process initiated with PID: 3380, for database name [FileTable_upgradeTest]
Restore(FileTable_upgradeTest): Processing the leading metadata
Restore(FileTable_upgradeTest): Planning begins
Restore is planning to used buffered i/o since the source file system used 512 byte sectors but 4096 byte sectors are in use on C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FT_DF_FG
Restore(FileTable_upgradeTest): Halting Fulltext crawls
Restore(FileTable_upgradeTest): Acquiring X lock on the database
Restore(FileTable_upgradeTest): Acquired X lock on the database
Restore(FileTable_upgradeTest): Effective options: Checksum=0, Compression=0, Encryption=0, BufferCount=7, MaxTransferSize=1024 KB
Restore(FileTable_upgradeTest): Planning is complete
Restore(FileTable_upgradeTest): Beginning OFFLINE restore
Restore(FileTable_upgradeTest): Preparing containers
Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf from page 1 to 200 (0x2000 to 0x190000)
Restore(FileTable_upgradeTest): Containers are ready
Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf (elapsed = 8 ms)
Restore(FileTable_upgradeTest): Restoring the backup set
Restore(FileTable_upgradeTest): Estimated total size to transfer = 260055552 bytes
Restore(FileTable_upgradeTest): Transferring data
Restore(FileTable_upgradeTest): 5 percent (13631488/260055552 bytes) processed
Restore(FileTable_upgradeTest): 10 percent (26214400/260055552 bytes) processed
Restore(FileTable_upgradeTest): 15 percent (39845888/260055552 bytes) processed
Error: 18456, Severity: 14, State: 38.
Login failed for user 'Lenovo-PC\user'. Reason: Failed to open the explicitly specified database 'FILETABLE_UPGRADETEST'. [CLIENT: <local machine>]
Restore(FileTable_upgradeTest): 20 percent (52428800/260055552 bytes) processed
Restore(FileTable_upgradeTest): 25 percent (66060288/260055552 bytes) processed
Restore(FileTable_upgradeTest): 30 percent (78643200/260055552 bytes) processed
Restore(FileTable_upgradeTest): 35 percent (91226112/260055552 bytes) processed
Restore(FileTable_upgradeTest): 40 percent (104857600/260055552 bytes) processed
Restore(FileTable_upgradeTest): 45 percent (117440512/260055552 bytes) processed
Restore(FileTable_upgradeTest): 50 percent (131072000/260055552 bytes) processed
Restore(FileTable_upgradeTest): 55 percent (143654912/260055552 bytes) processed
Restore(FileTable_upgradeTest): 60 percent (156237824/260055552 bytes) processed
Restore(FileTable_upgradeTest): 65 percent (169869312/260055552 bytes) processed
Restore(FileTable_upgradeTest): 70 percent (182452224/260055552 bytes) processed
Restore(FileTable_upgradeTest): 75 percent (196083712/260055552 bytes) processed
Restore(FileTable_upgradeTest): 80 percent (208666624/260055552 bytes) processed
Restore(FileTable_upgradeTest): 85 percent (221249536/260055552 bytes) processed
Restore(FileTable_upgradeTest): 90 percent (234881024/260055552 bytes) processed
Restore(FileTable_upgradeTest): 95 percent (247463936/260055552 bytes) processed
Restore(FileTable_upgradeTest): 100 percent (260055552/260055552 bytes) processed
Restore(FileTable_upgradeTest): Waiting for log zeroing to complete
Restore(FileTable_upgradeTest): Log zeroing is complete
Restore(FileTable_upgradeTest): Data transfer is complete
Restore(FileTable_upgradeTest): Backup set is restored
Starting up database 'FileTable_upgradeTest'.
The database 'FileTable_upgradeTest' is marked RESTORING and is in a state that does not allow recovery to be run.
Restore(FileTable_upgradeTest): Offline roll-forward begins
Restore(FileTable_upgradeTest): Starting filestream recovery
Restore(FileTable_upgradeTest): Processing 6 VLF headers
Restore(FileTable_upgradeTest): Processing VLF headers is complete
Restore(FileTable_upgradeTest): First LSN: 50:369:37, Last LSN: 50:386:1
Restore(FileTable_upgradeTest): Waiting for filestream recovery to complete
Restore(FileTable_upgradeTest): Filestream recovery is complete
Restore(FileTable_upgradeTest): Stop LSN: 50:386:1
Restore(FileTable_upgradeTest): Offline roll-forward is complete
Restore(FileTable_upgradeTest): Database fixup is complete
Restore(FileTable_upgradeTest): Transitioning database to ONLINE
Restore(FileTable_upgradeTest): Restarting database for ONLINE
Starting up database 'FileTable_upgradeTest'.
The tail of the log for database FileTable_upgradeTest is being rewritten to match the new sector size of 4096 bytes. 3072 bytes at offset 1295360 in file C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf will be written.
FixupLogTail(progress) zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf from 0x13d000 to 0x13e000.
Zeroing C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf from page 159 to 168 (0x13e000 to 0x150000)
Zeroing completed on C:\Program Files\Microsoft SQL Server\MSSQL13.SQL2016\MSSQL\DATA\FileTable_upgradeTest_log.ldf (elapsed = 0 ms)
Restore(FileTable_upgradeTest): PostRestoreContainerFixups begins
Restore(FileTable_upgradeTest): PostRestoreContainerFixups is complete
Restore(FileTable_upgradeTest): PostRestoreReplicationFixup begins
Restore(FileTable_upgradeTest): PostRestoreReplicationFixup is complete
Restore(FileTable_upgradeTest): Database is restarted
Restore is complete on database 'FileTable_upgradeTest'. The database is now available.
Restore(FileTable_upgradeTest): Resuming any halted Fulltext crawls
Database was restored: Database: FileTable_upgradeTest, creation date(time): 2015/10/22(00:56:00), first LSN: 50:369:37, last LSN: 50:386:1, number of dump devices: 1, device information: (FILE=1, TYPE=URL: {'https://dbproxazurestorage.blob.core.windows.net/dbproxsqlcontainer/FileTable_upgradeTest_backup_2015_10_28_2109.bak'}). Informational message. No user action required.
Restore(FileTable_upgradeTest): Writing history records
Restore(FileTable_upgradeTest): Writing history records is complete (elapsed = 114 ms)
Restore(FileTable_upgradeTest): MSDB maintenance is complete
RESTORE DATABASE successfully processed 31530 pages in 206.568 seconds (1.192 MB/sec).
Restore(FileTable_upgradeTest): RESTORE DATABASE finished