Securing SQL Server Backups – Procedures And Tips

Database loss is one of the worst nightmares that a computer user can experience. But having a backup of your database will defend you in such cases. Database backups protect your data from getting lost due to factors like; hardware or software failure or human errors.

The same applies to SQL Server as well.

How To Create SQL Server Backup?

1. Select a proper SQL server backup type:
2. Create SQL server backup either by using T-SQL or SQL Server Management Studio.

T-SQL

• Full backup

BACKUP DATABASE <DATABASE NAME>TO DISK = ‘C:\AdventureWorks.BAK’

GO

• Log backup
• Differential backup

BACKUP LOG <DATABASE NAME> TO DISK = ‘C:\AdventureWorks.TRN’   GO   BACKUP DATABASE <DATABASENAME> TO DISK = ‘C:\AdventureWorks.DIF’ WITH DIFFERENTIAL   GO

SQL SERVER MANAGEMENT STUDIO

• Right click on the database name
• Select tasks>backup
• Select any one from “Full/Log/Differential” as the preferred backup type
• Select “disk” as the destination
• Click on “add” to add a backup file and click “ok”
• Again, click “OK” to create backup

Note: There is a possibility of change to occur in the steps of backup when performed for a particular backup type. Kindly follow the instructions on screen accordingly.

SECURING SQL BACKUP SERVERS

Consider that you have created the backup for your SQL servers. But what about the security of these backups? We need to ensure the security of these backups as well to make sure that no unauthorized access is being made to it.

We can employ following ways to secure our backups: –

1. MANAGE LOG BACKUPS

• If you have used bulk recovery model and you lose the log backup, there might be a chance that you will not be able to restore the database which was present before creating the backup.
• To avoid such a situation you can create multiple copies of the log backup by copying the log backup to an external hardware device.

2. PHYSICAL PROTECTION

• To ensure the security of your backup tapes, it is advisable to store them in an off-site location.
• For the security of backup disk files, you should backup your files in disks that are protected by restrictive access control lists (ACLs).
• For further protection of your disk backups, you can also use the NTFS encrypting file system.
• Using Windows Backup for creating a backup of your SQL database is also a reliable option.

3. PASSWORD PROTECTION

• BACKUP SET PASSWORD: Use the “password” option in your “backup” or “restore” statement. A different password is used for different sets of backup. But this provides a weak protection for your backup database.
• MEDIA SET PASSWORD: This password is saved when the media header is written & cannot be changed afterwards. The same password should be provided during the creation of backup which was used during the time of formatting the media set. Also the same password is required for media set restoration.

4. RESTORATION FROM TRUSTED SOURCES

• Do not restore databases from untrusted sources.
• These sources cause an error by modifying physical database of your SQL server.
• Before using database from such sources run DBCC CHECKDB on the database.
• Examine user defined and stored procedure code in the database.

IN CASE OF ANY MISHAP!

If any mishap occurs at the time of securing your SQL Server Backups you don’t have to panic. You can always make use of SQL Backup Recovery software that is capable of recovering the corrupted backup of your database. With its help, you can also export your restored database into SQL Server Database or SQL Server Compatible Script accordingly.

CONCLUSION

Many users rely on manual methods for securing their SQL Backups. The users are advised to carefully implement the methods used for creating SQL Database Backups. If anything turns out to be wrong, it is recommended to recover the corrupted backup with the help of the named backup recovery software.