Backup, Restore, and Verify you SQL Server Backup
Backup, restore, and verify: the three main features of any good backup strategy, made easier with Datvat SQL Server Backup.
Datvat SQL Backup enables you to schedule backup jobs, taking faster, smaller, and more secure SQL Server database backups. It can also test backup files when they are created.
Reminders alert you to set up regular backups using the Schedule wizard.
Backups
Microsoft defines, SQL Server backup as:
A copy of data that is used to restore and recover data after a system failure
SQL Backups can be created a number of ways and can incorporate all or some of the data, as well as some part of the transaction log. While this article is focused on 2005 syntax, most of the concepts are applicable to 2000. This is a huge topic. At best, I’m going to scratch the surface and give you enough information so you won’t start crying again. After reading this, you should be able to set up a reasonable set of backups for your system.
Recovery Models
In order to begin working on your sql server backups, the business needs define a database recovery model. In essence, a
recovery model defines what you’re going to do with the transaction log data. There are three recovery models:
Full,
Simple and
Bulk Logged. These are pretty easy to define:
- Simple – in simple recovery mode, the transaction log is not backed up so you can only recover to the most recent full or differential backup.
- Full – in full recovery mode you backup the database and the transaction log so that you can recover the database to any point in time.
- Bulk Logged – in bulk logged mode, most transactions are stored in the transaction log, but some bulk operations such as bulk loads or index creation are not logged.
The two most commonly used modes are
Simple and
Full. Don’t necessarily assume that, of course, you always need to use
Full recovery sql server backup to protect your data. It is a business decision. The business is going to tell you if you need to recover to a point in time or if you simply need the last full backup. It’s going to define if your data is recoverable by other means, such as manual entry, or if you have to protect as much as possible as it comes across the wire. You use Simple recovery if you can afford to lose the data stored since the last full or differential backup and/or you just don’t need recovery to a point in time. In Simple mode, you must restore
all secondary read/write file groups when you restore the primary. You use Simple mostly on secondary databases that are not an absolute vital part of the enterprise or reporting systems, with read only access so there isn’t a transaction log to worry about anyway. You use Full if every bit of the data is vital, you need to recover to a point in time or, usually in the case of very large databases (VLDB), you need to restore individual files and file groups independently of other files and file groups.
With both Simple and full recovery models,
you can now run an sql server backup which allows you to copy the database to a backup file, but doesn’t affect the log, differential backup schedules or impact recovery to a point in time.
Profile for datvat
