Home
%3CLINGO-SUB%20id%3D%22lingo-sub-633556%22%20slang%3D%22en-US%22%3ETroubleshooting%20potential%20backup%2Frestore%20issues%20on%20Azure%20SQL%20Managed%20Instance%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-633556%22%20slang%3D%22en-US%22%3E%3CP%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2217%22%3EManaged%20Instance%20takes%20automatic%20backups%20(full%20backups%20every%20week%2C%20differential%20every%2012%20hours%2C%20and%20log%20backups%20every%205-10%20min)%20that%20you%20can%20use%20to%20restore%20a%20database%20to%20some%20point%20of%20time%20in%20past%20within%20the%20retention%20period%2C%20restore%20accidentally%20deleted%20database.%20For%20more%20information%2C%20see%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsql-database%2Fsql-database-automated-backups%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsql-database%2Fsql-database-automated-backups%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAutomated%20backups%3C%2FA%3E.%20Managed%20Instance%20also%20enables%20you%20to%20restore%20a%20database%20from%20a%20backup%20file%20placed%20on%20Azure%20Blob%20Storage%2C%20backup%20a%20database%20to%20Azure%20Blob%20Storage.%20Managed%20Instance%20currently%20don't%20support%20backup%20retention%20longer%20than%2035%20days%2C%20but%20you%20can%20use%20backups%20to%20blob%20storage%20as%20an%20alternative.%3C%2FP%3E%0A%3CP%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2220%22%3EIf%20you%20are%20experiencing%20some%20issues%20with%20any%20backup%20or%20restore%20operation%2C%20the%20following%20troubleshooting%20steps%20might%20help%20you%20to%20identify%20the%20issue.%3C%2FP%3E%0A%3CH2%20class%3D%22code-line%20code-line%20code-line%22%20id%3D%22restore-database-point-is-time-restore%22%20data-line%3D%2222%22%20id%3D%22toc-hId-1762884288%22%20id%3D%22toc-hId-1762884288%22%3ERestore%20database%20(point%20is%20time%20restore)%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2223%22%3EFind%20a%20database%20in%20the%20Azure%20portal%20and%20check%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Eearliest%20restore%20time%20value%3C%2FCODE%3E.%20Check%20is%20the%20point-in%20time%20that%20you%20used%20after%20this%20time.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2224%22%3EIf%20you%20cannot%20connect%20to%20the%20database%20that%20has%20completed%20restore%2C%20you%20might%20need%20to%20wait%20some%20additional%20time.%20The%20restored%20database%20must%20be%20registered%20in%20Azure%2C%20and%20in%20Business%20Critical%20tier%2C%20it%20should%20complete%20replication%2Fseeding%20to%20all%20secondary%20replicas.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20class%3D%22code-line%20code-line%20code-line%22%20id%3D%22restore-database-from-azure-blob-storage%22%20data-line%3D%2226%22%20id%3D%22toc-hId--789272673%22%20id%3D%22toc-hId--789272673%22%3ERestore%20database%20(from%20Azure%20blob%20storage)%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2228%22%3EIf%20you%20are%20noticing%20that%20some%20error%20is%20returned%20by%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ERESTORE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Echeck%20are%20you%20using%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsql-database%2Fsql-database-managed-instance-transact-sql-information%23restore-statement%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fsql-database%2Fsql-database-managed-instance-transact-sql-information%23restore-statement%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esupported%20syntax%20in%20this%20statement%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2228%22%3EMake%20sure%20that%20you%20are%20restoring%20a%20database%20from%20public%20blob%20storage%20protected%20with%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fstorage%2Fcommon%2Fstorage-dotnet-shared-access-signature-part-1%3Ftoc%3D%252fazure%252fstorage%252fblobs%252ftoc.json%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ESAS%20credential%3C%2FA%3E.%20Private%20IPs%20for%20blob%20storage%20and%20service%20endpoints%20are%20currently%20not%20supported.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2228%22%3EIf%20you%20are%20getting%20the%20error%205%20(Access%20Denied)%2C%20make%20sure%20that%20you%20have%20not%20denied%20the%20access%20to%20your%20Azure%20Blob%20Storage%20account%20using%20the%20firewall%3A%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116415i12D12C318CD65A32%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Storage%20firewall.png%22%20title%3D%22Storage%20firewall.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2229%22%3EVerify%20that%20you%20have%20created%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECREDENTIAL%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ewith%20the%20name%20equal%20to%20the%20URL%20of%20the%20blob%20storage%20where%20you%20want%20to%20backup%20your%20database.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2230%22%3ETry%20to%20run%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ERESTORE%20FILELISTONLY%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Estatement%20and%20check%20would%20Managed%20Instance%20return%20a%20list%20of%20the%20files%20in%20the%20backup.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2231%22%3EScript%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECREDENTIAL%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eto%20SQL%20Server%2C%20and%20restore%20a%20database%20from%20Azure%20Blob%20Storage%20account%20the%20SQL%20Server.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2232%22%3ECheck%20is%20your%20SAS%20credential%20placed%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ESECRET%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eoption%20of%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECREATE%20CREDENTIAL%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Estatement%20valid.%20The%20most%20common%20errors%20in%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fstorage%2Fcommon%2Fstorage-dotnet-shared-access-signature-part-1%23shared-access-signature-parameters%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ESAS%20token%20parameters%3C%2FA%3E%20are%3A%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2233%22%3E%3CCODE%3E%3F%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eis%20not%20removed%20from%20the%20beginning%20of%20the%20SAS%20token%20because%20the%20Azure%20portal%20generates%20SAS%20token%20with%20the%20leading%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3E%3F%3C%2FCODE%3E.%20Remove%20this%20character%20if%20you%20see%20it.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2234%22%3E%3CCODE%3Ese%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E(expiry%20date)%20property%20is%20set%20to%20some%20value%20in%20the%20past%20(note%20that%20this%20is%20UTC%20time).%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2235%22%3E%3CCODE%3Est%3C%2FCODE%3E%20(start%20date)%20property%20is%20not%20in%20the%20past%20(note%20that%20this%20is%20UTC%20time).%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2236%22%3E%3CCODE%3Esp%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E(permission)%20property%20should%20allow%20reading%20the%20file%20on%20the%20storage%20account.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%20data-line%3D%2236%22%3E%3CCODE%20style%3D%22background-color%3A%20%23f9f2f4%3B%20border-bottom-left-radius%3A%204px%3B%20border-bottom-right-radius%3A%204px%3B%20border-top-left-radius%3A%204px%3B%20border-top-right-radius%3A%204px%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23c7254e%3B%20font-family%3A%20Menlo%2CMonaco%2CConsolas%2C%26amp%3Bquot%3B%20courier%20new%26amp%3Bquot%3B%2Cmonospace%3B%20font-size%3A%2090%25%3B%20padding%3A%202px%204px%202px%204px%3B%22%3Esip%3C%2FCODE%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E(ip%20rage)%20remove%20this%20parameter%20if%20it%20is%20present%20in%20SAS%20token.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2237%22%3EIf%20you%20are%20getting%20the%20error%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E33111%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECannot%20find%20server%20certificate%20with%20thumbprint%20...%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%20you%20are%20trying%20to%20restore%20a%20backup%20of%20a%20database%20from%20another%20Managed%20Instance%2C%20check%20was%20the%20original%20database%20encrypted%20with%20TDE%20in%20the%20past.%20Follow%20this%20procedure%20to%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FTake-a-backup-of-TDE-protected-database-on-Azure-SQL-Managed%2Fba-p%2F643407%23M120%22%20target%3D%22_self%22%3Ebackup%20TDE%20protected%20database%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2238%22%3EIf%20you%20want%20to%20track%20the%20progress%20of%20the%20ongoing%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ERESTORE%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Estatement%20use%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Ft-sql%2Fstatements%2Frestore-statements-transact-sql%3Fview%3Dazuresqldb-mi-current%23restore-mi-database-progress%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Ft-sql%2Fstatements%2Frestore-statements-transact-sql%3Fview%3Dazuresqldb-mi-current%23restore-mi-database-progress%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ET-SQL%20to%20query%20Dynamic%20Management%20views%3C%2FA%3E%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2239%22%3EIf%20you%20cannot%20connect%20to%20the%20database%20that%20has%20completed%20restore%2C%20you%20might%20need%20to%20wait%20some%20additional%20time.%20The%20restored%20database%20must%20be%20registered%20in%20Azure%2C%20and%20in%20Business%20Critical%20tier%2C%20it%20should%20complete%20replication%2Fseeding%20to%20all%20secondary%20replicas.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2230%22%3EIf%20you%20you%20need%20to%20cancel%20the%20restore%20request%2C%20you%20would%20need%20to%20delete%20a%20database%20that%20you%20are%20restoring%20using%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fpowershell%2Fmodule%2Faz.sql%2Fremove-azsqlinstancedatabase%3Fview%3Dazps-2.1.0%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fpowershell%2Fmodule%2Faz.sql%2Fremove-azsqlinstancedatabase%3Fview%3Dazps-2.1.0%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20PowerShell%3C%2FA%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eor%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fcli%2Fazure%2Fsql%2Fmidb%3Fview%3Dazure-cli-latest%23az-sql-midb-delete%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fcli%2Fazure%2Fsql%2Fmidb%3Fview%3Dazure-cli-latest%23az-sql-midb-delete%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3EAzure%20CLI%3C%2FA%3E.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20class%3D%22code-line%20code-line%20code-line%22%20id%3D%22backup-database-to-azure-blob-storage%22%20data-line%3D%2241%22%20id%3D%22toc-hId-953537662%22%20id%3D%22toc-hId-953537662%22%3EBackup%20database%20(to%20Azure%20Blob%20Storage)%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2243%22%3EIf%20you%20are%20noticing%20that%20some%20error%20is%20returned%20by%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EBACKUP%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Echeck%20are%20you%20using%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-managed-instance-transact-sql-information%23backup%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fazure%2Fsql-database%2Fsql-database-managed-instance-transact-sql-information%23backup%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3Esupported%20syntax%20in%20this%20statement%3C%2FA%3E(for%20example%20mandatory%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECOPY_ONLY%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eoption).%3C%2FLI%3E%0A%3CLI%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3EMake%20sure%20that%20you%20are%20creating%20a%20backup%20of%20a%20database%20to%20public%20blob%20storage%20protected%20with%20%3CA%20style%3D%22background-color%3A%20transparent%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23146cac%3B%20text-decoration%3A%20underline%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fstorage%2Fcommon%2Fstorage-dotnet-shared-access-signature-part-1%3Ftoc%3D%252fazure%252fstorage%252fblobs%252ftoc.json%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ESAS%20credential%3C%2FA%3E.%20Private%20IPs%20for%20blob%20storage%20and%20service%20endpoints%20are%20currently%20not%20supported.%3C%2FLI%3E%0A%3CLI%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3EIf%20you%20are%20getting%20the%20error%205%20(Access%20Denied)%2C%20make%20sure%20that%20you%20have%20not%20denied%20the%20access%20to%20your%20Azure%20Blob%20Storage%20account%20using%20the%20firewall%3A%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3E%3CSPAN%20class%3D%22lia-inline-image-display-wrapper%20lia-image-align-inline%22%20style%3D%22width%3A%20400px%3B%22%3E%3CIMG%20src%3D%22https%3A%2F%2Fgxcuf89792.i.lithium.com%2Ft5%2Fimage%2Fserverpage%2Fimage-id%2F116415i12D12C318CD65A32%2Fimage-size%2Fmedium%3Fv%3D1.0%26amp%3Bpx%3D400%22%20alt%3D%22Storage%20firewall.png%22%20title%3D%22Storage%20firewall.png%22%20%2F%3E%3C%2FSPAN%3E%3C%2FP%3E%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2224%22%3EIf%20you%20cannot%20backup%20a%20database%20check%20is%20it%20protected%20with%20Transparent%20Data%20Encryption%20(TDE)%20by%20looking%20at%20the%20following%20view%3A%0A%3CPRE%3ESelect%20*%20from%20sys.dm_database_encryption_keys%3C%2FPRE%3E%0AYou%20need%20to%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FTake-a-backup-of-TDE-protected-database-on-Azure-SQL-Managed%2Fba-p%2F643407%23M120%22%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FTake-a-backup-of-TDE-protected-database-on-Azure-SQL-Managed%2Fba-p%2F643407%23M120%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Edisable%20TDE%20protection%20in%20order%20to%20take%20a%20manual%20backup%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2244%22%3EVerify%20that%20you%20have%20created%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECREDENTIAL%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Ewith%20the%20name%20equal%20to%20the%20URL%20of%20the%20blob%20storage%20where%20you%20want%20to%20backup%20your%20database.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2245%22%3EScript%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECREDENTIAL%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eand%20backup%20a%20database%20from%20the%20SQL%20Server%20to%20Azure%20Blob%20Storage%20account.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%20code-active-line%22%20data-line%3D%2246%22%3ECheck%20is%20your%20SAS%20credential%20placed%20in%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ESECRET%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eoption%20of%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECREATE%20CREDENTIAL%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Estatement%20valid.%20The%20most%20common%20errors%20in%20%3CA%20style%3D%22background-color%3A%20transparent%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23146cac%3B%20text-decoration%3A%20underline%3B%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fazure%2Fstorage%2Fcommon%2Fstorage-dotnet-shared-access-signature-part-1%23shared-access-signature-parameters%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3ESAS%20token%20parameters%3C%2FA%3E%20are%3A%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%20data-line%3D%2232%22%3E%3CCODE%3E%3F%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eis%20left%20in%20the%20beginning%20of%20the%20SAS%20token%20that%20should%20be%20removed%20in%20the%20token%20is%20generated%20using%20the%20Azure%20portal.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%20data-line%3D%2232%22%3E%3CCODE%3Ese%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E(expiry%20date)%20property%20is%20set%20to%20some%20value%20in%20the%20past%20(note%20that%20this%20is%20UTC%20time).%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%20data-line%3D%2232%22%3E%3CCODE%3Est%3C%2FCODE%3E%20(start%20date)%20property%20is%20not%20in%20the%20past.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%20data-line%3D%2232%22%3E%3CCODE%3Esp%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E(permission)%20property%20should%20allow%20creating%20and%20writing%20the%20file%20on%20the%20storage%20account.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%20data-line%3D%2236%22%3E%3CCODE%20style%3D%22background-color%3A%20%23f9f2f4%3B%20border-bottom-left-radius%3A%204px%3B%20border-bottom-right-radius%3A%204px%3B%20border-top-left-radius%3A%204px%3B%20border-top-right-radius%3A%204px%3B%20box-sizing%3A%20border-box%3B%20color%3A%20%23c7254e%3B%20font-family%3A%20Menlo%2CMonaco%2CConsolas%2C%26amp%3Bquot%3B%20courier%20new%26amp%3Bquot%3B%2Cmonospace%3B%20font-size%3A%2090%25%3B%20padding%3A%202px%204px%202px%204px%3B%22%3Esip%3C%2FCODE%3E%3CSPAN%20style%3D%22box-sizing%3A%20border-box%3B%20font-family%3A%20%26amp%3Bquot%3B%22%3E%26nbsp%3B%3C%2FSPAN%3E(ip%20rage)%20remove%20this%20parameter%20if%20it%20is%20present%20in%20SAS%20token.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%20code-active-line%22%20data-line%3D%2246%22%3ECheck%20is%20your%20database%20greater%20than%20195GB%20and%20verify%20that%20you%20are%20you%20using%20multiple%20URL%20stripes%20so%20every%20stripe%20would%20be%20less%20than%20195%20GB.%20For%20more%20information%20about%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FNative-database-backup-in-Azure-SQL-Managed-Instance%2Fba-p%2F386154%22%20target%3D%22_self%22%3Ecreating%20backups%20se%20this%20article%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2252%22%3EIf%20you%20are%20getting%20the%20error%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E3063%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EWrite%20to%20backup%20block%20blob%20device%20...%20failed.%20Device%20has%20reached%20its%20limit%20of%20allowed%20blocks.%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eor%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CSTRONG%3E3202%3C%2FSTRONG%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EWrite%20on%20'...'%20failed%3A%201117(The%20request%20could%20not%20be%20performed%20because%20of%20an%20I%2FO%20device%20error.)%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eadd%20the%20following%20options%20in%20the%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EBACKUP%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Estatement%3A%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EMAXTRANSFERSIZE%3D4194304%3C%2FCODE%3E%2C%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EBLOCKSIZE%3D65536%3C%2FCODE%3E%2C%20and%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3ECOMPRESS%3C%2FCODE%3E%20or%20use%20%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FNative-database-backup-in-Azure-SQL-Managed-Instance%2Fba-p%2F386154%22%20target%3D%22_self%22%3EURL%20striping%20in%20backup%20command%3C%2FA%3E.%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2253%22%3EIf%20you%20want%20to%20track%20the%20progress%20of%20the%20ongoing%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3EBACKUP%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Estatement%20use%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fbackup-transact-sql%3Fview%3Dazuresqldb-mi-current6%23backup_progress%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fstatements%2Fbackup-transact-sql%3Fview%3Dazuresqldb-mi-current6%23backup_progress%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ET-SQL%20to%20query%20Dynamic%20Management%20views%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20class%3D%22code-line%20code-line%20code-line%22%20id%3D%22automatic-backups%22%20data-line%3D%2256%22%20id%3D%22toc-hId--1598619299%22%20id%3D%22toc-hId--1598619299%22%3EAutomatic%20backups%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2257%22%3EIf%20you%20want%20to%20monitor%20backup%20requests%20create%20XEvent%20session%20that%20traces%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CCODE%3Esqlserver.backup_restore_progress_trace%3C%2FCODE%3E%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3Eevent.%0A%3CUL%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2258%22%3EIf%20you%20want%20to%20track%20the%20progress%20of%20the%20ongoing%20automatic%20backup%20use%3CSPAN%3E%26nbsp%3B%3C%2FSPAN%3E%3CA%20title%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Ft-sql%2Fstatements%2Frestore-statements-transact-sql%3Fview%3Dazuresqldb-mi-current%23restore-mi-database-progress%22%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fsql%2Ft-sql%2Fstatements%2Frestore-statements-transact-sql%3Fview%3Dazuresqldb-mi-current%23restore-mi-database-progress%22%20target%3D%22_blank%22%20rel%3D%22noopener%20noopener%20noreferrer%20noopener%20noreferrer%22%3ET-SQL%20to%20query%20Dynamic%20Management%20views%3C%2FA%3E%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3C%2FLI%3E%0A%3CLI%20class%3D%22code-line%20code-line%20code-line%22%20data-line%3D%2259%22%3EIf%20you%20cannot%20connect%20to%20the%20database%20that%20has%20completed%20restore%2C%20you%20might%20need%20to%20wait%20some%20additional%20time.%20The%20restored%20database%20must%20be%20registered%20in%20Azure%2C%20and%20in%20Business%20Critical%20tier%2C%20it%20should%20complete%20replication%2Fseeding%20to%20all%20secondary%20replicas.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CH2%20id%3D%22toc-hId-144191036%22%20id%3D%22toc-hId-144191036%22%3ERestoring%20dropped%20database(Preview)%3C%2FH2%3E%0A%3CUL%3E%0A%3CLI%3EMake%20sure%20that%20the%20parameters%20that%20you%20provided%20to%20the%20script%20are%20correct%20(especially%20a%20name%20of%20the%20database%20and%20the%20time%20when%20it%20is%20dropped).%20Note%20that%20dropped%20datetime%20is%20in%20UTC%20time%20zone.%3C%2FLI%3E%0A%3CLI%3EIf%20you%20are%20using%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FRestore-dropped-database-on-Azure-SQL-Managed-Instance%2Fba-p%2F386285%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3EPowerShell%20script%26nbsp%3B%3C%2FA%3Eto%20take%20the%20dropped%20time%2C%20not%20that%20this%20time%20might%20not%20be%20accurate%20and%20can%20be%20up%20to%205min%20after%20the%20actual%20drop%20time.%20Adjust%20the%20drop%20time%20if%20the%20restore%20fails.%3C%2FLI%3E%0A%3CLI%3EMake%20sure%20that%20you%20can%20restore%20the%20database%20on%20Managed%20Instance%2C%20and%20that%20you%20don't%20have%20some%20limit%20related%20to%20storage%20size%20or%26nbsp%3B%3CA%20href%3D%22https%3A%2F%2Ftechcommunity.microsoft.com%2Ft5%2FAzure-SQL-Database%2FReaching-Azure-disk-storage-limit-on-General-Purpose-Azure-SQL%2Fba-p%2F386234%22%20target%3D%22_blank%22%20rel%3D%22noopener%22%3Enumber%20of%20files%20in%20General%20purpose%20tier%3C%2FA%3E.%3C%2FLI%3E%0A%3C%2FUL%3E%0A%3CP%3EFind%20more%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Frelational-databases%2Fbackup-restore%2Fsql-server-backup-to-url-best-practices-and-troubleshooting%3Fview%3Dsql-server-2017%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3Etroubleshooting%20steps%20here%3C%2FA%3E.%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-633556%22%20slang%3D%22en-US%22%3E%3CP%3EAzure%20SQL%20Managed%20Instance%20automatically%20takes%20the%20backups%20of%20your%20databases%20and%20enables%20you%20to%20easily%20restore%20database%20either%20from%20automatic%20backups%20or%20from%20your%20backups%20placed%20on%20Azure%20Blob%20Storage.%20Here%20you%20can%20find%20some%20guides%20how%20to%20troubleshoot%20potential%20issues%20with%20backup%2Frestore%20if%20you%20experience%20some%20problem.%3C%2FP%3E%3C%2FLINGO-TEASER%3E%3CLINGO-LABS%20id%3D%22lingo-labs-633556%22%20slang%3D%22en-US%22%3E%3CLINGO-LABEL%3EBackup%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3EManaged%20Instance%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ERestore%3C%2FLINGO-LABEL%3E%3CLINGO-LABEL%3ETroubleshooting%3C%2FLINGO-LABEL%3E%3C%2FLINGO-LABS%3E
Microsoft

Managed Instance takes automatic backups (full backups every week, differential every 12 hours, and log backups every 5-10 min) that you can use to restore a database to some point of time in past within the retention period, restore accidentally deleted database. For more information, see Automated backups. Managed Instance also enables you to restore a database from a backup file placed on Azure Blob Storage, backup a database to Azure Blob Storage. Managed Instance currently don't support backup retention longer than 35 days, but you can use backups to blob storage as an alternative.

If you are experiencing some issues with any backup or restore operation, the following troubleshooting steps might help you to identify the issue.

Restore database (point is time restore)

  • Find a database in the Azure portal and check the earliest restore time value. Check is the point-in time that you used after this time.
  • If you cannot connect to the database that has completed restore, you might need to wait some additional time. The restored database must be registered in Azure, and in Business Critical tier, it should complete replication/seeding to all secondary replicas.

Restore database (from Azure blob storage)

  • If you are noticing that some error is returned by RESTORE check are you using supported syntax in this statement
  • Make sure that you are restoring a database from public blob storage protected with SAS credential. Private IPs for blob storage and service endpoints are currently not supported.
  • If you are getting the error 5 (Access Denied), make sure that you have not denied the access to your Azure Blob Storage account using the firewall:Storage firewall.png
  • Verify that you have created CREDENTIAL with the name equal to the URL of the blob storage where you want to backup your database.
  • Try to run RESTORE FILELISTONLY statement and check would Managed Instance return a list of the files in the backup.
  • Script CREDENTIAL to SQL Server, and restore a database from Azure Blob Storage account the SQL Server.
  • Check is your SAS credential placed in SECRET option of CREATE CREDENTIAL statement valid. The most common errors in SAS token parameters are:
    • ? is not removed from the beginning of the SAS token because the Azure portal generates SAS token with the leading ?. Remove this character if you see it.
    • se (expiry date) property is set to some value in the past (note that this is UTC time).
    • st (start date) property is not in the past (note that this is UTC time).
    • sp (permission) property should allow reading the file on the storage account.
    • sip (ip rage) remove this parameter if it is present in SAS token.
  • If you are getting the error 33111 Cannot find server certificate with thumbprint ... and you are trying to restore a backup of a database from another Managed Instance, check was the original database encrypted with TDE in the past. Follow this procedure to backup TDE protected database.
  • If you want to track the progress of the ongoing RESTORE statement use T-SQL to query Dynamic Management views
  • If you cannot connect to the database that has completed restore, you might need to wait some additional time. The restored database must be registered in Azure, and in Business Critical tier, it should complete replication/seeding to all secondary replicas.
  • If you you need to cancel the restore request, you would need to delete a database that you are restoring using Azure PowerShell or Azure CLI.

Backup database (to Azure Blob Storage)

  • If you are noticing that some error is returned by BACKUP check are you using supported syntax in this statement(for example mandatory COPY_ONLY option).
  • Make sure that you are creating a backup of a database to public blob storage protected with SAS credential. Private IPs for blob storage and service endpoints are currently not supported.
  • If you are getting the error 5 (Access Denied), make sure that you have not denied the access to your Azure Blob Storage account using the firewall:

Storage firewall.png

  • If you cannot backup a database check is it protected with Transparent Data Encryption (TDE) by looking at the following view:
    Select * from sys.dm_database_encryption_keys
    You need to disable TDE protection in order to take a manual backup.
  • Verify that you have created CREDENTIAL with the name equal to the URL of the blob storage where you want to backup your database.
  • Script CREDENTIAL and backup a database from the SQL Server to Azure Blob Storage account.
  • Check is your SAS credential placed in SECRET option of CREATE CREDENTIAL statement valid. The most common errors in SAS token parameters are:
    • ? is left in the beginning of the SAS token that should be removed in the token is generated using the Azure portal.
    • se (expiry date) property is set to some value in the past (note that this is UTC time).
    • st (start date) property is not in the past.
    • sp (permission) property should allow creating and writing the file on the storage account.
    • sip (ip rage) remove this parameter if it is present in SAS token.
  • Check is your database greater than 195GB and verify that you are you using multiple URL stripes so every stripe would be less than 195 GB. For more information about creating backups se this article.
  • If you are getting the error 3063 Write to backup block blob device ... failed. Device has reached its limit of allowed blocks. or 3202 Write on '...' failed: 1117(The request could not be performed because of an I/O device error.) add the following options in the BACKUP statement: MAXTRANSFERSIZE=4194304, BLOCKSIZE=65536, and COMPRESS or use URL striping in backup command.
  • If you want to track the progress of the ongoing BACKUP statement use T-SQL to query Dynamic Management views

Automatic backups

  • If you want to monitor backup requests create XEvent session that traces sqlserver.backup_restore_progress_trace event.
  • If you cannot connect to the database that has completed restore, you might need to wait some additional time. The restored database must be registered in Azure, and in Business Critical tier, it should complete replication/seeding to all secondary replicas.

Restoring dropped database(Preview)

  • Make sure that the parameters that you provided to the script are correct (especially a name of the database and the time when it is dropped). Note that dropped datetime is in UTC time zone.
  • If you are using PowerShell script to take the dropped time, not that this time might not be accurate and can be up to 5min after the actual drop time. Adjust the drop time if the restore fails.
  • Make sure that you can restore the database on Managed Instance, and that you don't have some limit related to storage size or number of files in General purpose tier.

Find more troubleshooting steps here.