Backing up SQL Server Databases
Backing up your SQL Server database is essential for protecting your data. The word Backup refers to “Copies the data or log records from a SQL Server database or its transaction log to a backup device, such as a disk, to create a data backup or log backup.”
You should consider some below points also when making planning for database backups.
1. Firstly set your database backup plan, In plan do not only include backup databases but also include backups of other things like reporting services, analysis services ,integration services and other third party tools. You should back up complete SQL server environment.you can use third party tools to achieve the same.
2. After that you should include your physical backup storage location protection policy, since this storage has all data of your databases. It may be very dangerous if it is getting in wrong hands, because the can easily restore this and access your confidential data like Credit card nos. , passwords etc. Also it will be better policy if you restricts from being copied your database backups to another location. However there are some third party database backup tools that allow backup encryption.
3. Store database backup another location [some other drive] from database location.so that if in any chance to corruption of files on database drives your back will be secure.
4. Do not write multiple backups in one physical file so create one backup for one database so that if a backup file for single database has been corrupted it does not affect other backups.
5. Do not allow multiple users to access on creating backups but if you allow then monitor who is creating backups and where they are being created.
6. Also backup system databases at least master and msdb since the master database stores security information as well as metadata about the other databases. Msdb stores information about jobs, operators, and alerts etc.
7. Monitor backup failure, backup size and backup time regularly, if you scheduled backup using Job Scheduling then create an alert to your mail id if job fails.
8. Testing the entire restore process to verify backup periodically.
9. Remove older backups time to time to free disk space.
To create and automate backup using SSMS, follow below steps
1. Go to databases in object explorer and select your database to backup.
2. Right click on database =>Backups
3. In destination, you must select backup device that can be any drive or tape.
4. Go to options
5. In options you can set over all existing back up options to overwrite however ignore this rule because if backup corrupted you lost all of backups.
Choose Verify backup when finished.
You also can select Backup compression option from here.
6. Click ok.The backup file is created on your defined destination.
7. You can also schedule this task using Script
8. Set all option as your requirement in job scheduling
9. Test your job by running once. If it successfully runs, you have successfully automated your backup.
