Get expert advice for scheduling backup jobs in SQL Server via SQL Server Agent using a crafted BACKUP DATABASE command or maintenance plan. If you're using SQL Server Express Edition, you'll learn how to schedule backups with Microsoft Windows Task Scheduler. This tip also shows you how to test your backups for successful restores and the best method for securing SQL Server backups.
A database administrator can choose from several SQL Server backup and recovery models. Most of the available options you can choose from would not be considered wrong, but some models will fit your environment better than others.
There are six primary SQL Server backup methods in use today by database administrators. They are:
Of these six methods, five are recognized by SQL Server as valid backups. The one method not recognized is the file-level backup. A file-level backup is not accepted as a valid backup because it is an offline backup. It must be performed when SQL Server is offline or while the database is detached from SQL Server. With file-level backups, the database engine is not aware of the backup; therefore, it cannot ensure that the backup is valid.
So far, no third-party tool can do a file-level backup while the SQL Server engine is running and accepting transactions. When you use proper backup methods, a file-level backup isn't necessary for SQL Server recovery.
The five primary backup methods are all performed within the SQL Server engine, so they are tracked and recorded.
Scheduling SQL Server backups
When scheduling SQL Server backups, you can use the same technique in all the database editions, with the exception of SQL Server Express Edition. SQL Express does not include the SQL Server Agent, and since Microsoft did not build job scheduling into the database, an outside scheduler must be used. Fortunately, Microsoft Windows includes a scheduler, called Task Scheduler, that works just fine for
To continue reading for free, register below or login
To read more you must become a member of SearchSQLServer.com
most people using SQL Server Express Edition. While it is reporting, error handling and alerting are basically non-existent – it does the job for small SQL Express databases that only need to have a backup kicked off.
I've found that the easiest way to back up a SQL Express database is to put a stored procedure in the database with the BACKUP DATABASE command that you will be using within the stored procedure. The BACKUP DATABASE command within SQL Server Express Edition supports all of the features and switches that the higher-end editions do, so you have all the standard options.
After you have your stored procedure set up, create a batch file (.BAT) and have it use SQLCMD to log into the database and run the stored procedure. I like to use a batch file instead of putting the SQLCMD command directly into the task scheduler job, because that way I have the option to do any file moving or other commands. It also gives me the ability to run the same command that the task scheduler runs by simply running the batch file. Then, I can schedule the batch file within the task manager, making sure to select the task to run under a Windows Account that has the appropriate rights needed within SQL Server to back up the database.
Other editions of SQL Server include the SQL Server Agent and it's your best bet for scheduling backup jobs to run. There are a couple of options for setting up your jobs. You can use the maintenance plans (not available in SQL Express because Express doesn't include SSIS) or you can craft the BACKUP DATABASE command yourself. I prefer to write the BACKUP DATABASE command myself, most of the time.
If you're using the maintenance plans and have your maintenance plan set up, just click the schedule button at the top. This creates a SQL Agent job for launching the maintenance plan. Keep in mind that you must have SSIS installed on SQL Server in order to run the job.
If you are using a hand-crafted BACKUP DATABASE command, navigate to the SQL Server Agent in the Management Studio (it's under Management in the Enterprise Manager for SQL Server 7.0 and 2000), right click on Jobs and select New Job. Put your BACKUP DATABASE command in a new job step, set up a schedule and you are all set.
If you are using SQL Server 2000 MSDE, you have access to the SQL Server Agent, so you can use this method. If you plan on upgrading to SQL Server 2005 Express Edition, you will lose access to the SQL Server Agent.
Testing SQL Server backups
Once you've backed up your data, you must be ready to restore it at a moment's notice. The only way to ensure that you can restore the databases when you need to is by testing the backups. Testing them means more than simply verifying them. A proper test involves a test restore, which simply means restoring the database to a test or development server. Ideally, you should perform test restores monthly, at a minimum, and you should schedule them to run on a regular basis.
The restores should come from your regular backups, not your special backups. Doing test restores from backups taken for the purpose of testing the restores invalidates the test, as those backups would not be available in typical restore circumstances.
Database restores can be scheduled as a SQL Agent Job or via the task scheduler and can have the job run the RESTORE DATABASE command. The hardest part of scheduling a restore is identifying the database filename. It can be done in a number of ways, but the easiest way is to use the msdb database and query for the filename of filenames the database was backed up to. Here is the basic query to find the filenames:
SELECT physical_device_name
FROM msdb.dbo.backupmediafamily backupmediafamily
JOIN msdb.dbo.backupset backupset ON backupmediafamily.media_set_id = backupset.media_set_id
and backupset.backup_start_date = (SELECT max(backup_start_date)
FROM msdb.dbo.backupset child
WHERE child.database_name = backupset.database_name)
and database_name = 'Northwind'
Database backup security
After taking your backups, be sure to secure them. If you don't secure the folder where the backups reside, then anyone on the network can have access to your entire database. Someone with malicious intent would simply need to restore the database to another server or workstation running MSDE. The best method of securing a backup is to use the MEDIANAME, NAME, MEDIAPASSWORD and secure the folder via NTFS permissions and network share permissions. Don't be one of the many DBAs who forgets this important step of backup management.
Setting up database backups is fun and easy. No, wait … it's boring and tedious, which is why it's often ignored. However, being able to restore the database in the event of database corruption or hardware failure is key to being a successful database administrator.
[TABLE]
