SQL SERVER | Database backups

By -

 Lets tackle the SQL Server database backups today:


I had a camera phone back when I was in college. I used to take pictures of everything. I had around 2000+ pictures on it. One day I dropped the phone in water. It never turned back on. I lost all the photos. 

I learned a valuable lesson that day:

Always take backups. Backup everything.

When SQL Server is configured with any Application, all the app data is stored in the databases. You don't want to end up feeling miserable when your server crash and all the data is lost. Database backups help you avoid just that.

Lets take a look at the basics:

Before you can understand backups, you need to know what gets stored in the databases and logs.

Database files = MDF file, LDF file & 0 or more NDF file.

NDF file is logically same as MDF file.


The most important question:

how much data you can afford to lose?

To understand this better, lets take a look at one example.

There's a person at your company's gate who notes in a notepad, names of people coming in and going out.

Just so he doesn't lose all the notes, every night he notes down all the entries in an Excel sheet on a computer.

From the database point of view:

Tracking all entries in a notepad = LOGGING

Making a copy of the data in notepad = BACKUP

On a particular day this is how the data looks:


Lets say, the person successfully notes down the name till 1PM and then lost his notepad.

There's no way to know what happened before 1PM. He can still track everything after that, but everything before that moment is lost.

So, he comes up with an idea:

He clicks a picture of his notepad every few hours during the day. That way if he loses his notepad, he doesn't lose everything.

So now, if he loses his notepad at 7:10 PM. He doesn't lose everything.

He can still look at 1 picture on his phone which he tool at 10:30 AM , and track that in his excel at night. However, records of person C and B are still lost.

If he wants to avoid this, he can increase the number of times he clicks a picture. Each picture is a complete view of how the data looks at that time.

From the database point of view:

Coping all entries in the excel (at 11PM) = FULL Backup

Taking a picture (at 11AM and 7:30PM) = Differential Backup


Let's say he loses his notepad at 9PM, and if he wants to go back all he needs is the last picture in his phone. Differential backups work the same way.


Clicking photos multiple times takes too much space on his phone. So, the person comes up with another idea.

Every few hours, he send an SMS to his friend with the entries that came in since the last time.

Example: at 11AM - he sends SMS of employees A and B coming in. At 7:30PM - he sends SMS of employees C coming in and B going out.


From the database point of view:

Coping all entries in the excel (at 11PM) = FULL Backup

Sending an SMS (at 11AM and 7:30PM) = LOG Backup


Lets say, he loses his notepad at 7:50PM. (how come he loses it all the time? Maybe a mischievous employee is stealing it.)

Now, in order to know all his entries, he needs to take a look at all his SMS for that day. String them together and he has all the data back till his last SMS. Log backups work the same way.


So, here's everything so far:

Assuming that following records come into the database over time:


 

Full Database backups:

  • contains the entire database 
    • data files &
    • active portion of Transaction log (as at the end of the backup)
Basic syntax for a full database backup:

BACKUP DATABASE MyDatabase TO DISK = 'E:\backups\MyDatabase_2022.bak'

Differential backups:

  • Contains all changes since the initial full backup
    • contains only the changed pages
    • and the active portion of Transaction log (as at the end of the backup)
  • It’s faster to perform a differential backup than to perform a full backup
  • A differential backup will become larger than the full backup
  • You only need the last differential backup when restoring.
  • Can't be used to restore to a point in time.

Syntax to perform a differential backup is similar to that for a full database backup, with the exception of the DIFFERENTIAL option: 

BACKUP DATABASE MyDatabase TO DISK = 'F:\backups\MyDatabase.bak' WITH DIFFERENTIAL

 


Transaction Log backups:

  • Contains only the changes since the last Log backup
  • In order to restore using Log backup, you need each and every log backup file since the last backup.
  • backs up the transaction log records that have not yet been backed upup to the last log record that exists at the time the backup ends. 
  • You can only perform a transaction log backup if your database is running the full or bulk-logged recover model
syntax to perform a transaction log backup:

BACKUP LOG MyDatabase TO DISK = '\\share\backups\MyDatabase'

 





Hope this clears up the concepts around the database backups.

All the best on your DBA journey.

 

Comments

Post a Comment

Popular posts from this blog

Index Clean-Up Scripts

By -
dropping un-used ones, adding missing ones, and fine-tuning the ones I already have. I thought I’d share some of the scripts I’ve been using to accomplish this. Un-Used Indexes Script Declare @dbid int     , @dbName varchar(100);   Select @dbid = DB_ID()     , @dbName = DB_Name();   With partitionCTE (object_id, index_id, row_count, partition_count)  As (     Select [object_id]         , index_id         , Sum([rows]) As 'row_count'         , Count(partition_id) As 'partition_count'     From sys.partitions     Group By [object_id]         , index_id )    Select Object_Name(i.[object_id]) as objectName         , i.name         , Case              When i.is_unique = 1          ...
Read more

forgot sa password and no logins are added

By -
Image
Stop the SQL services Edit the properties of the SQL Service Change the startup parameters of the SQL service by adding a  –m;  in front of the existing parameters Start the SQL services. These are now running in Single User Mode. Start CMD on tthe SQL server Start the SQLCMD command. Now we create a new user. Enter following commands CREATE LOGIN recovery WITH PASSWORD = ‘TopSecret 1′ (Remember SQL server has default strong password policy Go Now this user is created Now we grant the user a SYSADMIN roles using the same SQLCMD window. sp_addsrvrolemember ‘recovery’, ‘sysadmin’ go
Read more

The SQL Server DBA’s Guide to Teradata

By -
Being a long-time devotee of all things Microsoft SQL Server, I had quite a bit of learning to do to get up to speed on our new Teradata appliance. This guide attempts to distill some of what I have learned. I will primarily focus on what I have found most lacking: examples of how to convert SQL Server T-SQL to Teradata SQL. This guide will use the vendor-supplied sample databases:  SQL Server’s AdventureWorksDW2012 , and the  Teradata’s Express 14.0 Retail database . Also, while this is probably self explanatory, the “mufford” object referenced in the Teradata examples is simply my own personal sandbox. You’ll need to replace any “mufford” reference to whatever your development sandbox may be named. Release History: 2012-08-17 – Added UPDATE examples; reformatted 2012-08-12 – Added date examples 2012-08-08 – Initial Release SELECT Statements Simple SELECT SQL Server: Best practice is to qualify the table schema; qualifying the database is optional unless ...
Read more