Use the following checklist to make sure you don't forget crucial steps during your weekly database backups.
- Back up the master database weekly. Run a manual backup if you create, alter, or drop a database; add new SQL Server messages; add or drop linked servers; or add log devices.
- Back up the msdb database daily. It's usually pretty small, but it's important because it contains all SQL Server jobs, operators, and scheduled tasks.
- Back up the model database only if you've modified it.
- Use SQL Server Agent to schedule your backup jobs.
- If you have the resources available in your production environment, back up production databases to disk either locally or on a network server on the same switch.
- Then, copy the backup files/devices to tape. With many hardware failures (especially with RAID systems), the disk is usually intact. Recovery will go much faster if the backup files are on disk.
- Back up development and test databases using, at a minimum, the SIMPLE recovery model.
- In addition to regularly scheduled backups, back up user databases after nonlogged bulk operations (e.g., bulk copies), creating indexes, or changing the recovery model.
- If you're not using the SIMPLE recovery model, remember to back up your database AFTER you've truncated the transaction log.
- Document your recovery steps. At the very least, outline the steps, noting where all the important files are.
More enterprise IT news in ZDNet UK's Tech Update Channel.
For a weekly round-up of the enterprise IT news, sign up for the Tech Update newsletter. Let the editors know what you think in the Mailroom.
Talkback
Hello I am quite new to SQL server and when I try to run "SELECT dbpropertyex("database", "recovery")" against the Master Database I get the error: "Server: Msg 195, Level 15, State 10, Line 1
'dbpropertyex' is not a recognized function name."
Why is this and how can i get a listing of which recovery mode each of my databases are in?
USE the following, the above will not work.
SELECT [Name] AS DatabaseName, DatabasePropertyEx([Name],'recovery') AS RecoveryModel
from SysDatabases ORDER BY [Name]
SELECT DATABASEPROPERTYEX('tempdb','recovery')