SQL Server: Database Backup & Restore Interview Questions and Answers (Day-5)

How to turn off logging of all successful backups in your SQL Server error logs?

Using trace flag 3226.

T-SQL script to take a database backup.

Backup to multiple files using T-SQL.

T-SQL script to restore a database.

T-SQL script to set RECOVERY FULL model.

T-SQL script to take Database Log backup.

T-SQL script to restore a multiple transaction log backups.

Consider a situation where database backup size is 80 GB and you have three different disks and each has 30 GB free space.

Now the question is, can you split your database backups?

Yes, it is possible. We can split the backup files into different places and the same can be restored.

We have configured every Sunday 11pm FULL backup. Every 11pm differential backups and every 1h.r transaction log backups.

The Database was failed at 11.30 pm on Friday. Then what are the database recovery steps?

  • Take Tail log backup to get 11-11.30pm transactions on Friday.
  • Restore last Sunday full backup with NO_RECOVERY.
  • Restore Friday 11pm differential backup with NO_RECOVERY.
  • Restore tail log backup with RECOVERY.

What is File or File Group backup?

Using the file or filegroup backup you can backup an entire filegroup or files within the filegroup. These backups are essential when the database size is so large that backups must be done in parts because it takes too long to backup the entire database.

T-SQL Script to take File Group backup.

What is Mirrored backup?

Mirrored database backups can be used to create multiple copies of the database backups on different locations.

T-SQL Script to take Mirrored backup.

What is a log chain?

A continuous sequence of log backups is called a log chain. A log chain starts with a full backup of the database. Usually, a new log chain is only started when the database is backed up for the first time or after the recovery model is switched from simple recovery to full or bulk-logged recovery.

Is it possible in any situation when differential backup grows more than the Full backup?

Yes, it is possible in case when you do not take Full backup of the database for months and change in the databases grow more than the size of the Full backup.

Is it mandatory to take a Full backup if we switch the recovery model of a database?

Yes, It is mandatory to take a Full backup of the database after switching the recovery model of the database to initiate the log chain. Otherwise Diff. or Transaction logs will fail.

What is the below error?

Msg 3023, Level 16, State 2, Line 1

Backup and file manipulation operations (such as ALTER DATABASE ADD FILE) on a database must be serialized. Reissue the statement after the current backup or file manipulation operation is completed.

This error occurs when we try to run a backup, shrink, or alter database command in SQL Server in parallel.

Can you take differential backup of Master Database?

No, differential backup of master database is not allowed.

Can you take backup of a database which is in emergency mode?

We can’t take the database backup if the database is in emergency mode.

SQL Server has native backup. Why pay money for a tool to do it?

SQL Server backups work extremely well, but you’re going to have to do some work to get them set up and even more to get them automated.

A good third party product will make this automation process very simple.

For example, some tools more efficient at compressing backups, saving even more disk space and time during your backup processes.

Anvesh Patel

Leave a Reply

Be the First to Comment!

Notify of