June 30, 2002 at 5:40 pm
I'm a Project Manager. Last week I asked the Lead Developer for a web application about the Backup Schedule for the associated database. I was told that no SQL Backups are scheduled because the hard drive on the server gets backed up nightly. This seems odd to me. Is this a normal and safe backup strategy?
July 1, 2002 at 8:08 am
Your gut feeling is, in my opinion, correct. While it may be possible to recover a database using that strategy, I would not recommend it. There are a ton of considerations to consider when you plan a backup, and more importantly a recovery strategy to weigh out and you should spend as much time in doing this as you do planning the application. Here is a listing of some that are listed in the SQL Server Books On Line (Search "Analyzing Availability and Recovery Requirements");
In order to develop a successful backup and restore plan, you must understand when your data needs to be accessible and the potential impact of data loss on your business. Answering the following questions can help you determine your availability requirements and sensitivity to data loss. Then you can choose the correct Microsoft® SQL Server™ 2000 recovery models for your databases and make the necessary technical and financial tradeoffs.
Here are some basic questions to help you analyze your availability and recovery requirements:
What are your availability requirements? What portion of each day must the database be online?
What is the financial cost of downtime to your business?
If you experience media failure, such as a failing disk drive, what is the acceptable downtime?
In case of a disaster, such as the loss of a server in a fire, what is the acceptable downtime?
How important is it to never lose a change?
How easy would it be to re-create lost data?
Does your organization employ system or database administrators?
Who will be responsible for performing backup and recovery operations, and how will they be trained?
Here are some questions to help you choose the tools, techniques, and hardware appropriate for your site:
How large is each database?
How often does the data in each database change?
Are some tables modified more often than others?
What are your critical database production periods?
When does the database experience heavy use, resulting in frequent inserts and updates?
Is transaction log space consumption likely to be a problem due to heavy update activity?
Is your database subject to periodic bulk data loading?
Is your database subject to risky updates or application errors that may not be detected immediately?
Is your database server part of a SQL Server 2000 failover cluster for high availability?
Is your database in a multi-server environment with centralized administration?
The scope of this topic is huge and the information above should get you thinking in the right direction.
Hope this helps.
David
David
@SQLTentmaker“He is no fool who gives what he cannot keep to gain that which he cannot lose” - Jim Elliot
July 1, 2002 at 9:16 am
In past versions, I used to do this, but only if the SQL Server is STOPPED. IF the SQL Server continues to run, then you will probably not be able to recover the database.
Read David's post and implement some SQL Backups to disk at least.
Steve Jones
July 1, 2002 at 9:42 am
Thank you both for your responses. (SQL Server is not down during the disk backup.) Among other things, I'll ask for both a backup and a recovery plan.
July 2, 2002 at 9:01 am
Most backup software, unless equipped with an agent specifically for SQL Server is unable to back up database files because they are typically held open by SQL Server.
Most of the major backup software products have agents which are capable of backing up SQL Server databases, but the agents cost extra and there have been several discussions on these forums as to their reliability.
What we do where I work is exclude database files from the backup jobs (since they are open and will just return an error). We have regular database backups that get written to disk. These are closed files and are backed up. We also copy the backup files off to a dedicated file server which is also backed up.
In our particular case we use a regular file copy and the file server isn't running FTP services. I know Steve Jones has instituted backup jobs which FTP files to a second server... FTP tends to be a more efficient transfer method than a straight file copy.
K. Brian Kelley
http://www.sqlservercentral.com/columnists/bkelley/
K. Brian Kelley
@kbriankelley
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply