Restoration Part-4

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.

 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 happening in production.

Can I backup databases from multiple instances of MS SQL Server?

Yes. You can backup databases from multiple instances of MS SQL Server running on your local computer. However, you may not be able to backup the databases from multiple instances of the MS SQL Server hosted at multiple locations in your network.

Can you perform Log shipping and Mirroring using Simple Recovery Model?

No, You cannot perform Log shipping and Mirroring with Simple Recovery Model.

 Can we perform a SQL Server compressed backup?

Starting SQL Server 2008, we can perform a compressed backup. All types of backups can be performed with compressed option.

 When a checkpoint file is updated?

·         Database file creation and zeroing is completed.

·         After each backup set is processed.

·         The redo part of a recovery is finished.

 If your database size is bigger, why database backup performance is important?

It reduces the time that the extra I/O workload (and potentially CPU workload for compressed backups) is present on the system.

It reduces the potential for the transaction log file to have to grow to accommodate the transaction log generated while a backup operation is running, especially for long-running full database backups.

Please visit other related articles...

https://sqldbaiq.blogspot.com/p/restoration.html
https://sqldbaiq.blogspot.com/p/restoration-part-2.html
https://sqldbaiq.blogspot.com/p/restoration-part-3.html
https://sqldbaiq.blogspot.com/p/restoration-part-4.html
https://sqldbaiq.blogspot.com/p/restoration-part-5_16.html
https://sqldbaiq.blogspot.com/p/restoration-part-6_16.html

https://sqldbaiq.blogspot.com/p/restoration-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.