This article is half-done without your Comment! *** Please share your thoughts via Comment ***
What are the different types of Restore options?
- Restore an entire database.
- Restore part of a database.
- Restore specific files or filegroups.
- Restore specific pages to a database.
- Restore a specific transaction log.
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:
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 do you know about the online backup?
In addition to doing backups to your local disks there are several companies that offer online backups via the internet. Instead of you have to manage your backup files locally these services allow you to backup your databases via the internet and therefore you have an external copy stored outside of your company’s network.
How you consider the recovery model between Full recovery model and Simple recovery model?
If you are dealing with huge amount of data and failure chances are also less, you should use the simple recovery model.
If data failure risk is very high, you should use the full recovery model.
What are the three basic phases for database recovery?
- Analysis
- Redo – rolls forward committed transactions
- Undo – rolls back any incomplete transactions
What is a point in time recovery?
A point in time recovery is restoring a database to a specified date and time. When you have completed a point in time recovery, your database will be in the state it was at the specific date and time you identified when restoring your database. A point in time recovery is a method to recover your database to any point in time since the last database backup.
What do you require for point in time recovery?
In order to perform a point in time recovery you will need to have an entire series of backups (complete, differential, and transaction log backups) up to and/or beyond the point in time in which you want to recover.
I’ve just deleted a table! I know it is in the log. How do I get it back?
If the tables were dropped, ApexSQL Recover can recover them even from databases in the simple recovery model. ApexSQL Recover can recover both table structure and table records.
Another approach is, You should immediately take a log backup of your database and need to perform a process called a point in time recovery.
What is a Tail-Log Backups?
The tail-log backup captures records on the transaction log that were written since the last transaction log backup. If you’re going to restore a database to the point of failure, then you need to take a tail-log backup before you start the restore operation.
In which situation Tail-Log Backups is not required?
You do not need a tail-log backup if the recovery point is contained in an earlier log backup.
A tail-log backup is unnecessary if you are moving or replacing (overwriting) a database and do not need to restore it to a point of time after its most recent backup.
Why Tail-Log Backups have an incomplete Backup Metadata?
Tail log backups capture the tail of the log even if the database is offline, damaged, or missing data files. This might cause incomplete metadata from the restore information commands and msdb. However, only the metadata is incomplete; the captured log is complete and usable.
Can you take Tail-Log Backup in simple recovery model?
No, you can take Tail-Log backup only with full or bulk-logged recovery models.
In which scenarios, you require a Tail-Log Backup?
If the database is online and you plan to perform a restore operation on the database, begin by backing up the tail of the log.
If a database is offline and fails to start and you need to restore the database, first back up the tail of the log.
If a database is damaged, try to take a tail-log backup by using the WITH CONTINUE_AFTER_ERROR option of the BACKUP statement.
What are the issues you faced in backup and restore process?
- Error 3201 – Cannot open backup device.
- Error 3205 – Too many backup devices specified for backup or restore.
- Error 4305 – an earlier transaction log backup is required.
Can you access the database during a backup operation?
SQL Server backups are an online process; the data stored in SQL Server is highly available during this time. Operations such as INSERT, UPDATE, and DELETE are allowed, as are SELECT statements.
However, operations that would modify the underlying table or file space architecture, such as ALTER DATABASE, ADD FILE, or SHRINKFILE, can’t be done while the backup is running.
What are your steps to prevent the increasing size of Transaction log file?
- Backup the log files and make disk spaces free.
- Moving the log file to another disk.
- Adding a new log file on a different disk.
- Shrinking the log file.
What are the restore options available?
- 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 have to be applied to this database instance. The User cannot connect to the database because it is in Restoring Status.
- 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.
