Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 304
  • Last Modified:

Need to schedule SQL backups

I do everything at my company so I'm not that familiar with this, but I need to schedule SQL backups of all our SQL servers. I need it to do a full backup one day and then differentials the next 2 days. Then do it again. But when it starts the third full backup set, to delete the oldest full backup and differentials. I want it to use SQL compression and it needs to go to a share via UNC path (\\backupserver\sqlbackups). Lastly I need it to email me if it fails, succeeds or there are any errors.

can anyone point me to a blog that shows exactly how to do this or cares to set up a script. I'm not too familar with SSMS, so if you suggest I do something there, please be specific where/how.

THis is on SQL Server 2008 R2 in Windows Server 2008 R2

Thanks so much!
0
MrVault
Asked:
MrVault
4 Solutions
 
Om PrakashCommented:
0
 
MrVaultAuthor Commented:
is there a way to see the sql script behind this? I'm debating using SSMS and putting the script into our own scheduling tool.
0
 
sachinpatil10dCommented:
right click on database which you want to back up

select Tasks>>BackUp
set the backup options which you need (full/incremential) after you complete.
instead of clicking ok.
Script>>Script Action to Job


BackUptoJobSchedule.png
0
Technology Partners: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
TempDBACommented:
You can create a script that backups the database. The following link will help you with the backup:-
http://msdn.microsoft.com/en-us/library/ms186865.aspx

Then you can create separate jobs fro each database to call the t-sql script or you can include all the script in different steps inside a single job (in case parallel backup is not required). There are various other options of scheduling and maintaining the parallelism with backup like using SSIS packgae, increasing number of jobs with the same schedule. It depends on how you want it and how your resources allow it.

0
 
MrVaultAuthor Commented:
Thanks. It's a single DB per server. Is there something in the script or in SSMS that can automate the deletion of the older backups? I don't want it to delete the most recent one, but one that 2 or 3 days old. And I don't want it to delete it until it confirms the latest backup.

I did just realize that since the system databases are so small I do want to back those up at the same time.
0
 
MrVaultAuthor Commented:
@om_prakash_p: I followed that article. However it seems a bit different than using a job. The job among other things has the ability to react to success or failures. The use of a maintenance plan has me fill in static text for the subject line and body. I need it to change the subject line and/or body based on success or failure of each step. I added a notify operator task at the end, but that's the part that's static.
0
 
MrVaultAuthor Commented:
Can anyone explain how to get a report in the email using the maintenance task method? I want the subject line to say "success" or "failed" and then to put in the body of the email what was backed up, to where, with what options, when it started and completed, the size of the file, and whether it was successful or not. Can't figure this part out.

Thanks!
0
 
MrVaultAuthor Commented:
Lastly, how do I make the next step not execute if the previous failed? I don't want the maintenance plan to move on to the step of deleting old backups if the current backup failed.
0
 
TempDBACommented:
While creating the steps in a job, you have option in advanced tab to set what to do if the step failed or succeeded. You can chose your option there. You can directly jump to any of the steps.
0
 
MrVaultAuthor Commented:
thanks, but that's not what I'm asking .currently there is a bug in sql server 2008 r2 that deletes the steps in a job randomly. I went to see the steps this morning in a job and sure enough the steps were gone. been happening seemingly randomly since I started doing this. so what I'm saying is, how to I duplicate that dependency and reporting by using the maintenance plan piece? I would have have two jobs each with one step instead of a single job with 2 steps.
0
 
MrVaultAuthor Commented:
have you seen this bug before? I opened a ticket, but no other response yet. I can't rely on the steps thing it seems.
0

Featured Post

Free Tool: IP Lookup

Get more info about an IP address or domain name, such as organization, abuse contacts and geolocation.

One of a set of tools we are providing to everyone as a way of saying thank you for being a part of the community.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now