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