Backup and Restore

How does the database recovery model impact database backups?

First the database recovery model is responsible for the retention of the transaction log entries. So based on the
setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes,
hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.

Simple – Committed transactions are removed from the log when the check point process occurs.

Bulk Logged – Committed transactions are only removed when the transaction log backup process occurs.

Full – Committed transactions are only removed when the transaction log backup process occurs.

Is the native SQL Server 2005 backups are in clear text or in encrypted?
With SQL Server 2008 is the introduction of natively encrypted database backups. Prior to SQL Server 2008 a third
party product was necessary to encrypt the database backups.

How can I verify that backups are occurring on a daily basis?

1. Check all backup jobs history

2. Review the SQL Server error log for backup related entries.
3. Query the msdb.dbo.backupset table for the backup related entries.
4. Review the file system where the backups are issued to validate they exist.

How do you know if your database backups are restorable?

1. Issue the RESTORE VERIFYONLY command to validate the backup. For validating LiteSpeed backups use
XP_restore_verifyonly.
2. Randomly retrieve tapes from off site and work through the restore process with your team to validate the
database is restored in a successful manner.

What are some common reasons why database restores fail?

1. Sufficient space not available on drive
2. User may not have sufficient permissions to perform the restore
3. Unable to gain exclusive use of the database.
4. LSN’s are out of sequence so the backups cannot be restored.
5. Syntax error such as with the WITH MOVE command.
6. Version problem
7. Might be wrong backup location specified
8. Service account may not have permissions on backup folder

What are the permissions required to perform backup and Restore?

The user must be a member of either of the roles

Backup:
1. sysadmin – fixed server role
2. db_owner – fixed database role
3. db_backupoperator – fixed database role

Restore:
1. Sysadmin – fixed server role
2. Dbcreator – fixed server role
3. db_owner – fixed database role

How can you be notified if a native SQL Server database backup or restore fails via the native tools?
Setup SQL Server Alerts to be sent to Operators on a failure condition. Include RAISERROR or TRY\CATCH logic in
your backup or restore code to alert on the failure.
 Do all successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single
trace flag?
Yes – Just enable the trace flag 3226.

What are some common post restore processes?

1. Sync the logins and users
2. Validate the data is accurate by running dbcc commands
3. Notify the team\user community
4. Cleanse the data to remove sensitive data i.e. SSN’s, credit card information, customer names, personal
information, etc.
5. Change database properties i.e. recovery model, read-only, etc.

Explain how you could automate the backup and restore process?
1. Backups can be automated by using a cursor to loop through each of the databases and backup each one.
2. Restores can also be automated by looping over the files, reading from the system tables (backup or log
shipping) or reading from a table as a portion of a custom solution

What is the database that has the backup and restores system tables? What are the backup and restore system
tables? What do each of the tables do?
The MSDB database is the database with the backup and restores system tables. Here are the backup and restore
system tables and their purpose:

1. backupfile – contains one row for each data file or log file backed up
2. backupmediafamily – contains one row for each media family
3. backupmediaset – contains one row for each backup media set
4. backupset – contains one row for each backup set
5. restorefile – contains one row for each restored file
6. restorefilegroup – contains one row for each restored filegroup
7. restorehistory – contains one row for each restore operation

How can full backups be issued without interrupting the LSN’s?

Issue the BACKUP command with the COPY_ONLY option

How is a point in time recovery performed?

1. It depends on which backup types are issued. In this example let’s assume that full, differential and
transaction log backups are issued.
2. Restore the most recent full backup with the NORECOVERY clause
3. Restore the most recent differential backup with the NORECOVERY clause
4. Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last
transaction log backup
5. Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire
transaction log does not need to be applied

What are your recommendations to design a backup and recovery solution? Simply what is Backup Check list?
1. Recovery Model
2. Select Backup Types
3. Backup Schedule
4. Backup Process
5. Document
6. Backup to Disk
7. Archive to Tape
8. Backup to Different Drives
9. Secure Backup Files
10. Encrypt or Password Protect Backup Files
11. Compress Backup Files
 12. How Much to Keep on Disk
13. Online Backups
14. Run Restore Verify only
15. Offsite Storage

Consider a scenario where you issue a full backup. Then issue some transaction log backups, next a differential
backup, followed by more transaction log backups, then another differential and finally some transaction log
backups. If the SQL Server crashes and if all the differential backups are bad, when is the latest point in time you
can successfully restore the database? Can you recover the database to the current point in time without using
any of the differential backups?

You can recover to the current point in time, as long as you have all the transaction log backups available and they
are all valid. Differential backups do not affect the transaction log backup chain.

What are the three basic phases for database recovery and in what order do they occur?
1. Analysis
2. Redo – rolls forward committed transactions
3. Undo – rolls back any incomplete transactions

What options/arguments can be specified in a BACKUP LOG statement to keep inactive log records from being
truncated?
SQL Server 2000: NO_TRUNCATE
SQL Server 2005/2008: NO_TRUNCATE, COPY_ONLY

What are all of the backup \Restore options and their associated value?

Backup Options:
1. Full – Online operation to backup all objects and data in a single database
2. Differential – Backup all extents with data changes since the last full backup
3. Transaction log – Backup all transaction in the database transaction log since the last transaction log backup
4. File – Backup of a single file to be included with the backup when a full backup is not possible due to the
overall database size
5. File group – Backup of a single file group to be included with the backup when a full backup is not possible
due to the overall database size
6. Cold backup – Offline file system backup of the databases
7. Partial Backup – When we want to perform read-write filegroups and want to exclude read-only filegroups
from backup. It will be useful for huge databases (Data warehousing)
8. Third party tools – A variety of third party tools are available to perform the operations above in addition to
enterprise management, advanced features, etc.

Restore Options:
1. Restore an entire database from a full database backup (a complete restore).
2. Restore part of a database (a partial restore).
3. Restore specific files or filegroups to a database (a file restore).
4. Restore specific pages to a database (a page restore).
5. Restore a transaction log onto a database (a transaction log restore).
6. Revert a database to the point in time

What are the issues you faced in backup and restore process?
Common Errors in Backup:
Error 3201 – when performing a backup to a network share

Solution: Where SQL Server disk access is concerned, everything depends on the rights of the SQL Server service
startup account. If you are unable to back up to a network share, check that the service startup account has write
rights to that share.
Error: Cannot open the backup device:
Sol: Either the specified location is missing or the service account under which the SQL Agent is running does not
have the permissions on that folder.

Common Errors in Restore:

Error 3205 – Too many backup devices specified for backup or restore;
Sol:The most common cause for this error is because you are trying to restore a SQL Server 2005 backup on a SQL
Server 2000 instance

Error 4305 – an earlier transaction log backup is required

Sol:There are one or more transaction log backups that need to be restored before the current transaction log
backup. Using LSN number we can identify the prior log backups.

How to perform the tail log backup?

As normal log backup we can perform the tail log backup. We have two options to consider
1. WITH NORECOVERY: When database online and you are planning to perform a restore after the tail log
backup. It takes the database in restoring mode to make sure that no transactions performed after the tail
log.
2. WITH CONTINUE_AFTER_ERROR: When database offline and does not starts. Remember we can only
perform the log backup on damaged database when the log files are not damaged

What is the difference between NO_LOG and TRUNCATE_ONLY?

Both removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all
but the active log. This option frees space. NO_LOG and TRUNCATE_ONLY are synonyms.
After truncating the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not
recoverable. For recovery purposes, immediately execute BACKUP DATABASE to take a full or full differential backup.
Always try to avoid running the truncating as it breaks the log chain, Until the next full or full differential backup, the
database is not protected from media failure.

Consider a situation where I have to take a backup of one database of 60 GB. My hard drive lacked sufficient
space at that moment. I don’t find 64GB free on any drive. Fortunately, I have 3 different drives where I can hold
20 GB on each drive. How can you perform the backup to three different drives? How can you restore those files?
Is this really possible?
Yes it is possible. We can split the backup files into different places and the same can be restored.

BACKUP DATABASE AdventureWorks

TO DISK = ‘D:\Backup\MultiFile\AdventureWorks1.bak’,
DISK = ‘E:\Backup\MultiFile\AdventureWorks2.bak’,
DISK = ‘F:\Backup\MultiFile\AdventureWorks3.bak’

RESTORE DATABASE [AdventureWorks]

FROM DISK = N’D:\Backup\MultiFile\AdventureWorks1.bak’,
DISK = N’E:\Backup\MultiFile\AdventureWorks2.bak’,
DISK = N’F:\Backup\MultiFile\AdventureWorks3.bak’

What is piecemeal Restore?

Consider we have a database of 3 TB where as on primary file group is a read write filegroup of size 500 GB and we
have other files groups which are read-only of size 2.5 TB. We actually need not perform backup for read-only file
groups, here we can perform partial backups.
Piecemeal restore process allows us to restore the primary filegroup first and the database can be online and the
remaining filegroups can be restored while the recovery the transactions are running on primary
File group. Mostly suitable for data warehousing databases.
Can you restore master database? If yes how?
All server level information stored in master database that includes logins information etc. Schedule a regular
backup for master database and below is the process to restore a master db.

1. Start the sql server in single user mode (-m)

2. Issue the restore command with replace from SQLCMD prompt
3. RESTORE DATABASE master FROM <backup_device> WITH REPLACE
4. Restart the sql server in normal mode

All databases as available at the time of master db backup must be attached as everything is tracked in master
database.
If any databases are missing we can manually attach the mdf-ldfs.

How can we rebuild the system databases?

We usually rebuild the system databases when they are corrupted.

Rebuild deletes the databases and recreates it hence all the existing information is vanished.

Before rebuild:
1. Locate all recent backup of system databases
2. Make a note on mdf and ldf file locations, server configuration, Build /hotfix /sp applied

Rebuild:
1. Locate the Sql Server installation bits and run the command setup.exe fro command prompt by passing the
argument as “/ACTION=REBUILDDATABASE”
2. Review the summary.txt once the rebuild completes

Post Rebuild:
1. Restore all the system databases from existing backups
2. Move the system databases mdf/ldf files to the actual locations

How can we rebuild Resource system database?

In Sql Server 2008 from installation wizard from left navigation pane select “Maintenance” and Click on Repair. It
rebuilds the resource database.

As a database is recovering, after which phase will the database be available/online?

SQL Server 2000: After the Undo phase.
SQL Server 2005/2008: In all editions but Enterprise, after the Undo phase (if running FULL recovery model). With
Enterprise edition, after the Redo phase. Fast recovery is possible because transactions that were uncommitted
when a crash occurred reacquire whatever locks they held before the crash. While these transactions are being
rolled back, their locks protect them from interference by users.

On the Enterprise Edition of SQL Server 2005/2008, users are allowed access after REDO. So the point is REDO phase
is done first.

How do you respond to the increasing transaction log file?

Alternatives for responding to a full transaction log include:

1. Backing up the log.

2. Freeing disk space so that the log can automatically grow.
3. Adding a log file on a different disk.
4. Completing or killing a long-running transaction.
5. Moving the log file to a disk drive with sufficient space.
6. Increasing the size of a log file.
7. Shrinking the log file.
How to rebuild the system databases in SQL 2008?

First we need to confirm that the master database is corrupted. We cannot restart SQL Server without the MASTER
database. By checking the error logs we can confirm that master database is corrupted.

To rebuild the master database we have to use setup.exe from command prompt. There is no much difference
between 2005 and 2008 except few command line switches.
Find the setup.exe file (C:\……………………….\100\Setup BootStrap\Release\setup.exe)
Run the below command from dos prompt

c:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\Release>setup.exe

/QUIET
/ACTION=REBUILDDATABASE
/INSTANCENAME=<Default / Named>
/SQLSYSADMINACCOUNTS= <Service Account>
[/SAPWD=<Mandatory when using Mixedmode>]
[/SQLCOLLATION=<Give new collation if you want to change default>]

When setup has completed rebuilding the system databases, it will return to the command prompt with no
messages (It always first prints out the version). Examine the “Summary” log file (100\setup bootstrap\logs) to verify
it was completely successful.

What should we do if cannot find these database files at ….Templates\ location?

There are two options available.

Use the Repair feature of Setup (Available from the Maintenance option of the SQL Server Installation Center
installed your machine)
OR
Manually copy the necessary file(s) yourself. On your media source find the directory of your platform (x86, x64, or
ia64). Then go to the following directory:

setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn\Template
Once you have copied the file into the templates directory or repairing, re-run setup with the syntax I’ve described
above.

Can we rebuild resource database?

Yes! To rebuild these database files you would need to run Repair from the Installation Center.

Can we rebuild msdb?

Yes! We can directly restore it from a valid backup. If there is no valid backup available, restore all system databases
as described above.

What if I have applied Updates / HotFixes after installation?

As with SQL Server 2005, if for any reason you rebuild system databases or repair the resource database, you should
apply your latest update even if you restore backups of system databases.

How to restore Master or Msdb database from a valid backup?

1. Stop and Start the SQL Server Database Engine in Singe User Mode (Using parameter –m)
2. Restore the Master Database from SQLCMD prompt
3. From dos prompt using SQLCMD connect to the sql server and run the restore script
4. RESTORE DATABASE MASTER FROM DISK=’D:\MASTER_FULL.BAK’ WITH REPLACE
5. Stop and start the SQL Server Database Engine in normal mode
6. Restore MSDB Database
7. Connect to management studio and run the restore script for msdb
8. RESTORE DATABASE MSDB FROM DISK=’D:\MSDB_FULL.BAK’ WITH REPLACE
What is the difference between Hot and Cold Backup?
Performing backup while the database is online is called Hot backup. Stopping SQL server service and copying MDF
and LDF files is called cold backup which is not really happens in production.

What are the restore options available?

When you restore a backup, you can choose from 3 restore option.
With Recovery – Database is ready to use, and user can connect to database, user can change data inside database.
No Recovery – Database is not ready, there are few more backups that has to be applied to this database instance.
User cannot connect to database because it is in Restoring Status. (Exception: Not considering Database Snapshots )

Standby / Read Only – Database is ready to use but database is in Read Only mode, user can connect to database but
they cannot change data inside database. A running database con not be changed to standby mode. Only a data in
no-recovery state can be moved to standby mode. This is an option that is specified while restoring a database or
transaction log.