Hopefully you know that SSMS is a separate install from the main SQL Server install. You can find the binaries from Microsoft (https://docs.microsoft.com/en-us/sql/ssms/download-sql-server-management-studio-ssms) Using SSMS version 18.2, whilst playing around with the options I noticed a dark theme was available, easy to say I got a little excited, then I didn’t.
Scaling up and down your SQL Database is something that is quite common to do. I want to discuss the impact of moving up and down tiers, in terms of your transactions and connections.
If you do – shame on you and shame on me because I do.
I was VERY excited when I read the following tweet (below) from Bob Ward regarding SQL Server Diagnostics capability. What is it you are asking? It is an extension to SQL Server Management Studio (SSMS) where it gives you the ability to Upload / Analyse dump files created by SQL Server.
A small but nice little feature I have been using recently can be found within Query Store.
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.
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:
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
(21 row(s) affected)
How cool is that?