• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 435
  • Last Modified:

Scheduled Backup of database!!!

I am attempting to schedule an automated backup of my database. I have went into the server objects and created the backup device. Can someone tell me how to set up and automated daily backup. I am assuming that you will use the Server Agent, just not sure how to set up the job. Thanks.
0
phil301
Asked:
phil301
  • 5
  • 3
  • 3
1 Solution
 
Aneesh RetnakaranDatabase AdministratorCommented:
Expand EM-> ..Management-> sql server agent -> jobs
right click and create the job
0
 
MikeWalshCommented:
What version of SQL Server are you in?

All you need to do is go to the SQL Agent and then jobs, and you can write the Transact SQl for your backup as one of the steps, and go to the schedule tab and schedule it.

Or you can just use a "Maintenance Plan" for backing it up.
0
 
phil301Author Commented:
Hate to answer my own question here, but I figured out I could use the maintenance wizard to automate this process. The problem I have now , is that when I try to finish, it gives me the following error:

 EXECUTE permission denied on object 'sp_help_operator',database 'msdb',schema 'dbo' [Microsoft SQL Server, Error: 229]

Any help would be appreciated.
0
Visualize your virtual and backup environments

Create well-organized and polished visualizations of your virtual and backup environments when planning VMware vSphere, Microsoft Hyper-V or Veeam deployments. It helps you to gain better visibility and valuable business insights.

 
Aneesh RetnakaranDatabase AdministratorCommented:
You dont have enough permission to execute the following procedures in msdb, that's why it is giving the errro

sp_add_job
sp_add_jobschedule
sp_add_jobserver
sp_add_jobstep

0
 
phil301Author Commented:
Where do you change permission for the stored procedures?
0
 
Aneesh RetnakaranDatabase AdministratorCommented:
USE MSDB
GO
GRANT EXECUTE ON sp_add_job TO urLogin
0
 
phil301Author Commented:
I am sorry, but I am  new to SQL Server, what is MSDB???
0
 
MikeWalshCommented:
MSDB is the system database that stores information about jobs, alerts and other SQL Server management/agent information.

Let's back up a little bit here, this is not something you should have to be doing..

Answer These:

1.) What version of SQL Server are you using
2.) How did you set up your backup job? Who is the job running as?
3.)Are you using an account with SA level access?
4.) Are you the DBA /person responsible for the server?
0
 
phil301Author Commented:
1) SQL Server 2005

2) I am new to this, I attempted to setup the backup job by using the maintenance wizard and selecting "Full Backup" and setting the schedule. If errors out when the wizard attempts to finish.

3) I am not using the "SA" account, but I thought my account had the same privileges. I will attempt to run the wizard with the "SA" login.

4) I am the closest thing we will ever have to a DBA at my facility.
0
 
MikeWalshCommented:
ok.. i asked the DBA question to make sure you should be the one with the proper perms.. Make sure your account is in the proper groups permission wise (or try and set it up as SA)

It sounds like your account does not have the proper perms.
0
 
phil301Author Commented:
Thanks for your help, the SA account did the trick. I really appreciate it.
0

Featured Post

Industry Leaders: 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!

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