What is BUFFERCOUNT option in Backup database command?
BUFFERCOUNT specifies the total number of I/O buffers to be used for the backup operation. The total space that will be used by the buffers is determined by: buffercount * maxtransfersize.
How you are improving your backup performance?
The easiest way to improve backup performance is to allow the backup operation to parallelize, which is known as backup striping.
By default, there’s a single data reader thread for each drive letter or mount point being read from and a single data writer thread for each backup device being written to.
In general, having more reader and writer threads should improve performance up to the point where the I/O subsystem becomes a bottleneck either for reads or writes.
A more advanced way to improve backup performance is to manually specify the number of backup I/O buffers to use (with the BUFFERCOUNT option) and the size of each buffer (with the MAXTRANSFERSIZE option).
What do you suggest to improve backup performance?
· Backup stripping / perform parallel with multiple backup device.
· Setting of maxtransfersize parameter.
· Setting of buffercount parameter.
· Setting of compression parameter.
T-SQL Script to calculate compression ratio.
SELECT backup_size/compressed_backup_size FROM msdb..backupset
What are the restrictions of compressed backup?
· Compressed and uncompressed backups cannot co-exist in a media set.
· Previous versions of SQL Server cannot read compressed backups.
· NTbackups cannot share a tape with compressed SQL Server backups.
My backup was failed what may be the possible scenarios?
· Disk was Full or Server was busy.
· May be problem with the network.
· If the domain is not running and SQL Server service is running with domain account.
· Problem with MSDB and SQL Agent.
· Error while reading the transaction log file.
· CHECKSUM errors
· Database has entered into Suspect and restoring mode.
· Transaction log file is full.
What is RESTORE WITH RESTART option?
RESTORE WITH RESTART option, which lets you restart an interrupted restore operation. Periodically, a restore operation writes a checkpoint file that describes the point to which the restore has progressed.
This option is really meant for large backups that span several tapes; it lets you restart a restore operation without having to go back to the first tape. However, it can also be useful for restoring disk-based backups.
What is a Disaster Recovery?
Disaster recovery is a process that you can use to help recover information systems and data, if a disaster occurs.
Some examples of disasters include a natural or a man-made disaster such as a fire, or a technical disaster such as a two-disk failure in a Redundant Array of Independent Disks (RAID) 5 array.
What are your strategies to recover data when the disaster occurs?
· Failover Clustering
· AlwaysOn Avaliability
· Database Mirroring
· Transaction Replication
· Log Shipping
· Strong backup strategies
· Virtual Machine Replication
What is Piecemeal Restores?
Piecemeal restore allows databases that contain multiple filegroups to be restored and recovered in stages. Piecemeal restore involves a series of restore sequences, starting with the primary filegroup and, in some cases, one or more secondary filegroups. Piecemeal restore maintains checks to ensure that the database will be consistent in the end.Please visit other related articles...
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
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