Oops I dropped the wrong DB

Yep that damn situation :”)

Probably one of the worst fears of any developer is to accidently drop the wrong database (2 min silence if it’s a production DB) because data being the new oil no one wants it to spill off and get wasted instead of being used efficiently. Think of your new internship in a firm, everything going on smoothly and on one unfortunate morning you typed a DB command which ruined everything (Scary sound in the background). No one wants such a day Right?

So today we’ll be learning some of the basic house keeping concepts which could save you from these disasters. First one being a proactive measure and later one being reactive. In this blog we’ll be working with MS-SQL on SSMS( since it’s a industry standard service) but I believe other management tools too have these features(if not then its the right time to switch).

Proactive measures

So proactive as the name suggest involves the preventive measures we should take beforehand to minimize the disaster(yep not less). Some of the examples could be think/check before you execute but we can’t rely on them completely as humans do commit mistakes for sure. So lets see another method that is backup and restore options because 👇

In this case prevention is easier than cure

So before starting lets understand some terms:

• Full Backup : This is one of the simplest form of backup. It includes complete backup of all the objects of database i.e. tables, procedures, functions, views, indexes etc. It’s recommended to take full backup at least once.
• Differential Backup : Consider it as a backup which only takes care of data that has been changed from the last full backup. It’s use case completely depends on the amount of change. while restoring we first mount the latest full backup and then the differential backup associated with the full back up.
• Transaction log : Transaction logs contains the commands which user ran on system to structure the DB as it looks like at that point of time. Transaction log backup saves the commands which are not included in the previous log backup. It enables the database to be recovered in specific point in time.

So now we are familiar with different back-up types lets see backup in action.

Manual Backups

• Visit SSMS portal and connect to your SQL Server instance.
• Right click on the DB -> Tasks -> Back Up

• Your selected DB will be reflected in Database option and backup type is selected by default Full. It’s recommended to have backup in the form of full and then differential. Choose the local destination to save the backups. You can also save the backups on azure cloud using URL option. Read more about Media Option and Backup options here.

• Click OK.

Jump to Restore section below.

Automated Backup

Now we know the manual process but this seems boring to repeat the steps every day before doing actual transactions on the tables.

For the same let’s see how we automate this task. For versions other than SQL server express edition we have an inbuilt functionality of scheduling under maintenance. But we’ll be covering for SQL server express edition since it’s free for individual developers.

So in all the possible ways you’ll be doing two things

1) Creating a stored procedure to take the backup

2) By some way running that stored procedure automatically daily.

Let’s see how the stored procedure would look like

• Open the DB -> Programmability -> Stored Procedures

• Now create the Backup stored procedure

Use [elibraryDB]
GO
SET ANSI_NULL ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE PROCEDURE dbbackup
AS
BEGIN
DECLARE @path VARCHAR(256)
DECLARE @fileName VARCHAR(256)
SET @path = 'C:\Work\Backup\'
BEGIN
SET @fileName = @path + 'elibraryDB.BAK'
BACKUP DATABASE elibraryDB to DISK = @fileName
END
END
GO

• Run it and you’ll find a new file created under Stored Procedure folder.

Now as we are done with stored procedure it’s time to find a way to schedule it to run automatically every day(or whatever frequency you like).

If you DB is hosted on Azure then you could make use of (Azure Data Factory) ADF to schedule it. Read more about ADF here.

• Create a new pipeline and search stored procedure under activities.

• Create a linked service to your DB and then select the stored procedure.

• Once everything connect click on trigger and add the schedule.

• Set the details so that it runs daily in the morning(or your preferred time).

• In the end publish the pipeline.

Restore

Let’s say by mistake something happens to your DB. What you need to do is simple restore.

• Right click on Database folder and click restore Database.

• Choose Device and locate the DB backup.

• Click OK and your DB will be restored.

Hope this good habit will save you on D day and will help you to be a more reliable asset to the company. Feel free to leave any other life saving tips you have in the comment section.