Most database administrators (DBAs) know what it's like to be called away from home or dinner or even awakened in the middle of the night to answer a page or a cell-phone call concerning some type of database
disaster. These are the times you're glad that you have a systematic backup plan. Of course, conversely, if you
don't have a good backup plan, these are the times you regret it.
In this article, we will discuss various types of
backup strategies and demonstrate how to implement each of these types of backups using Enterprise Manager, the graphical user interface (GUI), and Transact SQL (T-SQL) in Microsoft SQL Server 2000.
Put together a backup plan
Before developing your backup strategy, you need to develop a plan of action. Evaluate your company and ask, "What is the maximum amount of downtime we can live with?" Once this is established, you can decide on the appropriate course of action by matching your company's needs to the appropriate level of database backup.
Full backups
A full backup is the easiest type of backup to implement in SQL Server. It takes a complete picture of your database that includes backing up users and permissions. In addition, this backup can occur while transactions are still occurring in your system. When the backup begins, the date is recorded, the data pages are backed up, and all transactions that occur while the backup runs are appended to the backup.
Author's note
A full backup backs up your users but not your logins. If you want to back up your logins, remember to back up the Master database. Furthermore, if you ever restore your database to a different server, you must synchronize your logins by using the sp_change_users_login. For a more detailed explanation of this stored procedure, see
SQL Books Online.
Read full article at
Selecting the best backup strategy for Microsoft SQL Server