Backing up SQL Server Databases
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.
These tips are quite useful, but I think there are at least a few more that are worth picking up by any DBA when doing maintenance on his databases.
ReplyDeleteThere is an article that enumerates some of the best practices you posted in your article too, but also others that are not very common, but which will help you much more and make your DBA job easier: http://sqlbak.com/blog/backup-best-practices-shortcut/
Also, regarding the backup operation, you could do this even better, through a maintenance plan. Having a maintenance plan will give you the option of adding more tasks to one event. For example, like you said in your article, having a cleanup plan is a good practice to keep your disk clean of backups which you don't need.
There is also an article on this, which you can find here: http://sqlbak.com/blog/scheduling-backups/
Hi mgheorghiu,
DeleteThanks for these valuable comment...
Steps are very useful and I also has got help from this blog www.sqlrecoverysoftware.net/blog/backup-and-restore-in-sql-server.html in which step by step procedure is shown for restore and backup in sql server.
ReplyDelete