Note: This post contains a video clip where I show quirky behaviour of the scripted offline task. Personally I rarely use SQL Server Management Studio GUI to do day-to-day tasks, I rather just load a window and start typing stuff (I learn better that way). Recently I became lazy and just loaded up the tool and found something quite quirky, let me explain.
SQL Server Management Studio (SSMS) release candidate 17.0 RC2 works side-by-side with generally available releases (16.x), but it is not recommended for production use. There are many enhancements which you can read here: https://docs.microsoft.com/en-us/sql/ssms/sql-server-management-studio-ssms-release-candidate but there is one feature I really like. It is called Presenter Mode and it is something I will use for my upcoming presentation. (Yes you read that right – might as well try presenting because apparently I like talking)
Did you know that you can run DBCC CHECKDB (WITH PHYSICAL_ONLY) and issue page restores from SSMS (SQL Server Management Studio). I never, and I probably will never because I rather use TSQL – however I have only just seen this ( yes it’s been around for a while ) and felt like blogging about it.
I clicked on restore > page.
I corrupted page 126 and look it knows about it!
Yes it’s in bad shape – Level 24 severity.
SELECT * FROM [ZoraDB].[dbo].[people]
SQL Server detected a logical consistency-based I/O error: incorrect checksum (expected: 0xa154e798; actual: 0xa144e788). It occurred during a read of page (1:126) in database ID 11 at offset 0x000000000fc000 in file ‘C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\DATA\ZoraDB.mdf’. Additional messages in the SQL Server error log or system event log may provide more detail. This is a severe error condition that threatens database integrity and must be corrected immediately. Complete a full database consistency check (DBCC CHECKDB). This error can be caused by many factors; for more information, see SQL Server Books Online.
So SSMS is so good it will restore for you – and build the restore chain.
Hitting the SCRIPT button gives us:
USE [master] RESTORE DATABASE [ZoraDB] PAGE='1:126' FROM DISK = N'C:\SQLSERVER\ZoraDB.bak' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 -- Tail Log BACKUP LOG [ZoraDB] TO DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH NOFORMAT, NOINIT, NAME = N'ZoraDB_LogBackup_2016-12-15_09-51-17', NOSKIP, NOREWIND, NOUNLOAD, STATS = 5 RESTORE LOG [ZoraDB] FROM DISK = N'C:\SQLSERVER\ZoraLog.trn' WITH FILE = 1, NORECOVERY, NOUNLOAD, STATS = 5 RESTORE LOG [ZoraDB] FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL12.MSSQLSERVER\MSSQL\Backup\ZoraDB_LogBackup_2016-12-15_09-51-17.bak' WITH NOUNLOAD, STATS = 5 GO
SELECT * FROM [ZoraDB].[dbo].[people] (21 row(s) affected)
How cool is that?