How a differential database backup work?
If data changes on any one of the pages in an
extent, a flag is set at the extent level to indicate that the extent must be
backed up.
If you have 10 differential database backups, how you restore these all differential database backups?
Not require all 10 differential database
backups. First restore last database full backup and restore only last
taken differential database.
What are your basic steps to perform database recovery using database backups?
·
Restore the most recent full backup with the
NORECOVERY clause.
·
Restore the most recent differential backup
with the NORECOVERY clause.
·
Restore all of the subsequent transaction log
backups with the NORECOVERY clause, except the last transaction log backup.
·
Restore the last transaction log backup with
the RECOVERY clause.
Basic steps to verify backups regularly.
·
Check SQL Server error log for backup related
entries.
·
Check msdb.dbo.backupset table for backup
related entries.
·
Check the backup existence in the file system.
How to check if your database backups are restorable?
You can use RESTORE VERIFYONLY command to
validate the backup.
Syntax:
RESTORE VERIFYONLY FROM DISK = C:\dbrnd.BAK
How you can automate the backup process?
Backups can be automated by using T-SQL script
and you can execute and configure this T-SQL script using SQL Job.
You can also configure automated Database Maintenance Plan in which you can configure all different types of backups.
What all options we have in a Database maintenance
plans which helps a DBA to keep the database healthy?
We have below options in the Maintenance Wizard.
1.
Check
Database Integrity – checks the logical and physical integrity of all objects
in the database
2.
Shrink
Database – shrinks the files and the logs in the specified database on a
file-by-file basis. It pools log files. Physically shrinking makes the file
smaller and truncates a file in the backup.
3.
Reorganize
Index – uses minimal system resources and defragments indexes of tables and
views by reordering the pages to match the logical order
4.
Rebuild
Index – drops and recreates the index reclaiming space and reorders the index
rows in contiguous pages
5.
Update
Statistics – updating statistics ensures queries run with up-to-date statistics,
but they also require queries to be recompile, can result in a performance
trade-off
6.
Clean Up
History – deletes entries in the history tables
7.
Execute
SQL Server agent job – Windows service that runs scheduled jobs
8.
Backup
Database (Full) – Backs up the entire database, but as databases grow, the
backup takes longer and requires more storage. The best solution is to use in
conjunction with differential backups.
9.
Backup
Database (Differential) – used to back up only the data that has changed since
the last full backup
10.
Backup
Database (Transaction Log) – used to back up individual transaction logs, which
can be individually restored since the last full backup
Please visit other related articles...
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
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.
No comments:
Post a Comment