SQL 2008: Best way to perform backups on Windows Server 2008?

Posted on 2011-03-01
Last Modified: 2012-06-27
What would be the best way to implement backups for an SQL 2008 instance with a dozen or so databases running on Window Server 2008 R2? I was thinking weekly or bi-weekly full backups with daily differentials.  How would it be best implemented?
Question by:VoodooFrog
  • 4
  • 3
  • 2

Expert Comment

ID: 35010919
There are many question to be asked before you implement a backup policy. I'm sure all the databases on a server are not of same importance. You should categorize the database based on Service Level Requirements, Size, Role (Production, Dev/Test etc) and Type (OLTP, Data Warehouse, Reporting etc.) then consider the implementation.

As a general rule of thumb, the target should be that critical databases must be backup so that there is no loss of data. That is could be a combination of Full, differential and transaction log backup.

Please see the link below to see how it could be implemented to minimize the loss.

Author Comment

ID: 35011396
Thank you, that link explains the differing backup types and their uses very well.  I the best way to schedule the backups using Task Scheduler? or is there a better way of managing the scheduling of the backups?

Expert Comment

ID: 35011824
I use the SQL server agent to schedule jobs. You can create a step to email you an alert using "sp_send_dbmail " in case of a failure with some detail information related to failure or you can use the notification tab to add a group or individual operators to receive failure alerts.

exec msdb.dbo.sp_send_dbmail
@profile_name ='sqlsvc',
@recipients = N'',
@subject = N'PUBS Data Backup Succeeded on <hostname>',
@body = ' PUBS FULL Backup Succeeded on <hostname>';

You can also log the  SQL Agent Jobs to a log file and view history.
Three Reasons Why Backup is Strategic

Backup is strategic to your business because your data is strategic to your business. Without backup, your business will fail. This white paper explains why it is vital for you to design and immediately execute a backup strategy to protect 100 percent of your data.


Expert Comment

ID: 35014399
If you have a Window Server 2008 with a dozen of databases I assume it must serve an enterprise in a production installation. So I would put a second HD and configure a RAID 1 (mirror) array. If there is a problem on one HD, data are at disposition. This would be my first level of back-up and would garantize me to avoid service interruptions. The costs are really low.

About the choice between full and differential, I thanks niaz for the very explanatory link above.

I determine the frequency of back-ups depending on the following criteria (in order of importance):
1 - The importance of the data written to the server. Am I doing accounting or data warehouse, or am I logging web traffic? In the first case I need absolutely all the operations restored, in the second I can accept some loss of data.
2 - The number of operation pro day (or pro hour, minute, second): this will determine the time I need to recover the actual situation in case of data restore (eventually the time I need to manually re-insert the operations that are not in the last back-up). The more operations I have, the often I want to back-up.
3 - The work-load for the server: is the server working near his limits? Are the back-ups slowing down the machine and db-response times? But if my data are very important the solution is to empower the hardware and do more back-ups.

I hope you can understand my bad english.

Expert Comment

ID: 35014542
If you want to go to next level of data protection after RAID implementation and are running mission critical databases and can't afford loss of data or down time for recovery, you should also look into Log Sipping, Mirroring, Replicaion and Clustering. These are some of the solution available within SQL Server for disaster recovery and high availability.

You decision to implement any strategy should be based on the three points listed above by "pieropingi" and the Service Level Requirement (SLR).

Author Comment

ID: 35017542
If you had to write a t-sql statement to backup every database, how would you do it?  Ideally I would like to write it so that if a new database is created, it will be backed up without having to alter the Job.

I will include the code I have so far:
DECLARE @BackupLocation varchar(255); 

SET @BackupLocation = 'X:\MyBackups\' + REPLACE(CONVERT(VARCHAR(19), GETDATE(), 120),':','.') + ' - Full Backup.bak';

BACKUP DATABASE [DatabaseName] TO DISK = @BackupLocation;

Open in new window


Assisted Solution

pieropingi earned 100 total points
ID: 35018367
Perhaps my intervention is trivial, but not knowing you and having seen several times bad things, I cite an obvious point:
every back-up has to be on a phisically separated storage device (not in D: drive on a second partition of the same hard disk, please!) in case of hardware-failure. That can be a second internal IDE, SATA or SCSI/SAS hard disk, an external-one (USB or FireWire) or a networked-one, depending on server-load and data-volume.
Having big amounts of strategical data, I will avoid external HDs preferring internal SATA with Native Command Queuing technology. Motherboard must also support NCQ. With this configuration SATA is even faster and really cheaper as SCSI. In case of PC substitution, It has more flexibility, too.

Author Comment

ID: 35018426
Pieropingi, thank you for the input, I have this covered.  The system drive is mirrored.  I also have a RAID 5E set up with 4 drives for data storage.  The backups are going to data raid, where they will be then uploaded to an offsite location.

Accepted Solution

niaz earned 400 total points
ID: 35019196
I would not recommend to have just one script to do all database backup. Having all eggs in one basket is not a good idea. You can either create separate jobs for each DB or group databases based on the criticality and create jobs for each group. Still I would recommend to create separates steps for each DB within each job.

But if you still want to keep the backup process on auto pilot you can use the un-documented stored procedure "sp_foreachdb"

EXEC sp_foreachdb
       @command = N'BACKUP DATABASE [?]
               TO DISK = ''C:\databasebackups\?.bak''  WITH INIT, COMPRESSION;',
       @user_only = 1,
       @recovery_model_desc = N'SIMPLE',
       @suppress_quotename = 1;

You can also use the script discussed in the following link.

Yet another simple approach is to create a maintenance plan for all you databases.

Featured Post

What is SQL Server and how does it work?

The purpose of this paper is to provide you background on SQL Server. It’s your self-study guide for learning fundamentals. It includes both the history of SQL and its technical basics. Concepts and definitions will form the solid foundation of your future DBA expertise.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Suggested Solutions

Title # Comments Views Activity
Outlook 2010 and delayed messages in out box 7 19
Outlook 2016 Huge Memory Leak 9 70
Connection reset by peer 18 35
What is this datetime? 1 19
Today, still in the boom of Apple, PC's and products, nearly 50% of the computer users use Windows as graphical operating systems. If you are among those users who love windows, but are grappling to keep the system's hard drive optimized, then you s…
When you start your Windows 10 PC and got an "Operating system not found" error or just saw  "Auto repair for startup" or a blinking cursor with black screen. A loop for Auto repair will start but fix nothing.  You will be panic as there are no back…
This tutorial will show how to configure a new Backup Exec 2012 server and move an existing database to that server with the use of the BEUtility. Install Backup Exec 2012 on the new server and apply all of the latest hotfixes and service packs. The…
This tutorial will show how to configure a single USB drive with a separate folder for each day of the week. This will allow each of the backups to be kept separate preventing the previous day’s backup from being overwritten. The USB drive must be s…

837 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question