Home
%3CLINGO-SUB%20id%3D%22lingo-sub-1077211%22%20slang%3D%22en-US%22%3ELesson%20Learned%20%23115%3A%20Time-saving%20in%20a%20Managed%20Instance%20restore%20process.%3C%2FLINGO-SUB%3E%3CLINGO-BODY%20id%3D%22lingo-body-1077211%22%20slang%3D%22en-US%22%3E%3CP%3EAs%20you%20know%2C%20in%20Managed%20Instance%20is%20possible%20to%20perform%20a%20backup%20and%20restore%20in%20the%20similar%20way%20that%20we%20have%20in%20OnPremise%2C%20just%20only%20we%20need%20to%20know%20that%20the%20backup%20needs%20to%20include%20WITH%20COPY_ONLY%20modifier.%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EI've%20found%20that%20restoring%20operation%20(RESTORE%20DATABASE%20databasename%20FROM%20URL%3D'%3CA%20href%3D%22https%3A%2F%2Fxxxx.blob.core.windows.net%2Fxxxx%2Fxxx.BAK%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fxxxx.blob.core.windows.net%2Fxxxx%2Fxxx.BAK%3C%2FA%3E'%20)%20is%20taking%20a%20small%20amount%20of%20time%20more%20and%20checking%20the%20ErrorLog%20of%20Managed%20Instance.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EDuring%20my%20troubleshooting%20process%2C%20I%20noticed%20that%20every%20time%20that%20I%20restored%20a%20database%20a%20command%20DBCC%20CHECKDB%20for%20the%20new%20database%20is%20executed.%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EAs%20I%20found%20some%20information%20about%20CHECKSUM%20in%20the%20different%20operations%20done%20by%20%3CA%20href%3D%22https%3A%2F%2Fdocs.microsoft.com%2Fen-us%2Fsql%2Ft-sql%2Fdatabase-console-commands%2Fdbcc-checkdb-transact-sql%3Fview%3Dsql-server-ver15%22%20target%3D%22_self%22%20rel%3D%22noopener%20noreferrer%20noopener%20noreferrer%22%3EDBCC%20CHECKDB%3C%2FA%3E%26nbsp%3B%26nbsp%3Badding%20the%20parameter%20checksum%20in%20every%20backup%20operation%20like%20this%20one%3A%26nbsp%3BBACKUP%20DATABASE%20databasename%20TO%20URL%3D'%3CA%20href%3D%22https%3A%2F%2Fxxxxx.blob.core.windows.net%2Fxxxx%2Fxxxx.BAK%22%20target%3D%22_blank%22%20rel%3D%22noopener%20nofollow%20noopener%20noreferrer%20noopener%20noreferrer%22%3Ehttps%3A%2F%2Fxxxxx.blob.core.windows.net%2Fxxxx%2Fxxxx.BAK%3C%2FA%3E'%20%3CBR%20%2F%3Ewith%20copy_only%2CCHECKSUM%20will%20prevent%20the%20execution%20of%20the%20DBCC%20CHECKDB%26nbsp%3B%3C%2FP%3E%0A%3CP%3E%26nbsp%3B%3C%2FP%3E%0A%3CP%3EEnjoy!%3C%2FP%3E%3C%2FLINGO-BODY%3E%3CLINGO-TEASER%20id%3D%22lingo-teaser-1077211%22%20slang%3D%22en-US%22%3E%3CP%3EToday%2C%20I%20found%20a%20very%20interesting%20trick%20to%20reduce%20the%20time%20spent%20in%20the%20restoring%20process%20of%20a%20database%20in%20Managed%20Instance.%26nbsp%3B%3C%2FP%3E%3C%2FLINGO-TEASER%3E

As you know, in Managed Instance is possible to perform a backup and restore in the similar way that we have in OnPremise, just only we need to know that the backup needs to include WITH COPY_ONLY modifier.

 

I've found that restoring operation (RESTORE DATABASE databasename FROM URL='https://xxxx.blob.core.windows.net/xxxx/xxx.BAK' ) is taking a small amount of time more and checking the ErrorLog of Managed Instance. 

 

During my troubleshooting process, I noticed that every time that I restored a database a command DBCC CHECKDB for the new database is executed. 

 

As I found some information about CHECKSUM in the different operations done by DBCC CHECKDB  adding the parameter checksum in every backup operation like this one: BACKUP DATABASE databasename TO URL='https://xxxxx.blob.core.windows.net/xxxx/xxxx.BAK'
with copy_only,CHECKSUM will prevent the execution of the DBCC CHECKDB 

 

Enjoy!