Recover from a Data Disaster – Point in Time Recovery Method

If you are a database administrator, you will never know when your database hit a disaster. When the disaster hit your database, you will be in a panic mode and you will take action which you won't take in modest conditions. However, we need to plan for a disaster. This article provides you with the importance of Point in Time Recovery Method in order to recover data.

This is a fantastic feature in databases but you will not realize how valuable this until you come across this situation. Leaving describing these features to separate time, let us first look at two cases. Incidentally, these two incidents occurred in one organization and moreover for one database but at different times.

Incident #1:  A developer was connected to both production and development instances of databases from one SQL Server Management Studio (SSMS) instance. If you have work with SSMS, you would understand how risky this is. If not, this incident tells you how risky it is. The DBA thinking that she is working on the developer instance has deleted data in a customer table in Production.

Incident #2: In the same organization, they decided to increase the column length to 50 from 15. This column is a critical column to the business. They increased the size of the column using the design view from a table. Guess what, before typing the 0, after removing the 1 the user has saved the designed, which had resulted in the truncation of the table column to 5 from 15 with a data loss!!

Action: In both cases, we were to help. The first question was “What is the recovery model?” if it is Simple, then nothing that we could do as Simple recovery model does not keep the transaction log history and nothing can be recovered. In these scenarios, the recovery model is Full means that we can recover data to a point of time. In this mechanism, we can recover to a given time. So, we got the full back up of database and restored to a different database instance with the recovery option on. Then we got a log backup and restored on top of the previously restored database with specifying a time which is just before the disaster recovery. After the database is restored fully, customer table and the other tables were transferred to the production database.

Lesson Learnt:

• ·  Never connect to multiple databases instances from SSMS. Especially, with production and other database instances.
• ·  Never use the SSMS designer, to modify the database schema. Always use scripting for schema design changes. 
• ·  Always, use the Full recovery model for production.

• If you need more details, read the article written by me at sqlshack. Further, you can use Log shipping to avoid data disasters as discussed in the latest article. 

SQL Server ALTER TABLE ADD Column overview

In this article, we will explore SQL Server ALTER TABLE ADD Column statements to add column(s) to an existing table. We will also understand the impact of adding a column with a default value and adding and updating the column with a value later on larger tables.

Read more https://www.sqlshack.com/sql-server-alter-table-add-column-overview/

Compress Backup Information in Backup and Restore Events

In SSMS, there is report called , “Backup and Restore Events” report.

if you consider latest backups backup size are almost similar. (~  339 MB) However, if you really see the physical file size, it is 79 MB.

So the report and physical file size is different.

You can measure the file size by running a query.

SELECT 
    bs.media_set_id,
    bs.backup_finish_date,
    bs.type,
    bs.backup_size / 1024 backup_size_KB,
    bs.compressed_backup_size / 1024 compressed_backup_size_KB,
    mf.physical_device_name
FROM dbo.backupset AS bs
INNER JOIN dbo.backupmediafamily AS mf
ON bs.media_set_id = mf.media_set_id    
WHERE database_name = 'AdventureWorks2012'
ORDER BY backup_finish_date DESC;
GO

So , here you can see the correct value can be obtained you query compressed_backup_size column rather than querying backup_size column. so it seems like, SSMS report is using backup_size column rather than compressed_backup_size column which seems to be BUG!

SSMS Report Security Bug

To view SSMS reports there are few security roles you need to grant. In SSMS there are three types of reports.

1. Server Reports

2. Database Reports

3. SQL Server Agent Reports

To view Server Reports you need VIEW SERVER STATE permission. To view database reports, you need db_datareader permissions. However, if you have any other permissions, following is what you see.

As expected, Standard Reports are disabled but reports which were opened before are in the list!! and you can run those reports as well.