Backup Part-6

One scenario, As a DBA you have already created one database maintenances plan to take database full backup, differential backup and transaction log backup. This plan is running in the background as per defined schedule.

Now your client requires one ad-hoc, full database backup so you have taken full database backup for some X reason.

In other side, your defined backup plan is also running and accidently your system crashed.

Now you have to restore your database and you are restoring your database by using full backup which is taken by database maintenance plan. Next step is to start restoring of differential backup. But I got an error like, your database LSN are mismatch.

 What do you know about this?

Yes, LSN was broken because we have taken one ad-hoc full database backup so there are two solutions.

First, we have to use ad-hoc full database backup as a base full backup.

Second, we have to take this kind of ad-hoc full database using COPY-ONLY option.

 What is Copy-Only Backup?

The Copy-Only Backup is similar to Full database backup, it takes full database backup without modifying the DatabaseBackupLSN.

It does not break the chain of differential backups.

 T-SQL Script to take Copy-Only Backup.

BACKUP DATABASE Database_Name TO BackupDevice WITH COPY_ONLY

 What is your backup retention policy?

Never store databases and backups on the same disks because when a disk failure occurs, then you lose both backups and databases.

Store database backups on both local server and remote server. If your database size is very big the, you can use a local database backups for instant standby restore.

If you are following Agile Software Development Methodology, you should take Sprint wise database backups which help us to track and manage different functionality changes.

What are some common errors related to backup failures?

ID	Category	Error	Severity	Description
1	Backup Success	18264	10	Database backed up: Database: %1, creation date(time): %2(%3), pages dumped: %4!d!, first LSN: %5, last LSN: %6, number of dump devices: %9!d!, device information: (%10).
2	Backup Failure	18204	16	%1: Backup device ‘%2’ failed to %3. Operating system error = %4.
3	Backup Failure	18210	16	%1: %2 failure on backup device ‘%3’. Operating system error %4.
4	Backup Failure	3009	16	Could not insert a backup or restore history/detail record in the msdb database. This may indicate a problem with the msdb database. The backup/restore operation was still successful.
5	Backup Failure	3017	16	Could not resume interrupted backup or restore operation. See the SQL Server error log for more information.
6	Backup Failure	3033	16	BACKUP DATABASE cannot be used on a database opened in emergency mode.
7	Backup Failure	3201	16	Cannot open backup device ‘%ls’. Device error or device off-line. See the SQL Server error log for more details.

Please visit other related articles...

https://sqldbaiq.blogspot.com/p/backup-iq.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-2.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-3.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-4.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-5.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-6.html
https://sqldbaiq.blogspot.com/p/backuprestore-part-7.html

https://sqldbaiq.blogspot.com/p/backuprestore-part-8.html

References: Thanks to the all the SQL Server bloggers who wrote and shared the valuable information on their blogs which helped me a lot to prepare this series of Questions. Also big thanks to Microsoft Documentation which contains each and everything about their product.