Understanding Backup Strategies in SQL Server: A Complete Guide
Table of Content:
The purpose of the backup is to create a copy of data that can be recovered in the event of a primary data failure. Primary data failures can be the result of hardware or software failure, data corruption, or a human-caused event, such as a malicious attack (virus or malware), or accidental deletion of data. Backup copies allow data to be restored from an earlier point in time to help the business recover from an unplanned event.
Storing the copy of the data on separate medium is critical to protect against primary data loss or corruption. This additional medium can be as simple as an external drive or USB stick, or something more substantial, such as a disk storage system, cloud storage container, or tape drive. The alternate medium can be in the same location as the primary data or at a remote location. The possibility of weather-related events may justify having copies of data at remote locations.
For best results, backup copies are made on a consistent, regular basis to minimize the amount data lost between backups. The more time passes between backup copies, the more potential for data loss when recovering from a backup. Retaining multiple copies of data provides the insurance and flexibility to restore to a point in time not affected by data corruption or malicious attacks.
In SQL Server, you can create a backup of a database very easily.
Process: Database Backup
Login to the respective server and select the Database need to take the backup.
- Right Click on a specific database
- Select tasks
- select backup
You can use below code to take backup.
<span class="pln"> BACKUP DATABASE </span><span class="pun">[</span><span class="typ">SampleDataBase</span><span class="pun">]</span><span class="pln"> TO DISK </span><span class="pun">=</span><span class="pln"> N</span><span class="str">'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\Backup\SampleDataBase.bak'</span><span class="pln"> WITH COPY_ONLY</span><span class="pun">,</span><span class="pln"> NOFORMAT</span><span class="pun">,</span><span class="pln"> NOINIT</span><span class="pun">,</span><span class="pln"> NAME </span><span class="pun">=</span><span class="pln"> N</span><span class="str">'SampleDataBase-Full Database Backup'</span><span class="pun">,</span><span class="pln"> SKIP</span><span class="pun">,</span><span class="pln"> NOREWIND</span><span class="pun">,</span><span class="pln"> NOUNLOAD</span><span class="pun">,</span><span class="pln"> COMPRESSION</span><span class="pun">,</span><span class="pln"> STATS </span><span class="pun">=</span><span class="pln"> </span><span class="lit">10</span><span class="pln"> GO </span>
- Select Backup type as Full
- Select copy only backup option if needed
- Add a destination of backup file
Backup file type is filename.bak
- In the backup options page, select the set backup compression as "compress backup".
Click on OK button to get the backup.
Go to the path you will get the backup file.
