1

I'm having difficulties to setup a daily backup schedule in SSMS 2008 R2 Developer Edition.

This is exactly what I'm trying to achieve: My SSMS connect to my DB (remotely), which located at some hosting service.

I'm looking for some script (or other way) to get into the query(in the SSMS, or even better - in "MyLittleAdmin" tool) that will backup my DB in a daily basis.

I'll appreciate any further help.

Edit:

I'm sorry for the disinformation, I'll answer all of the questions that showed up:

@DaniSQL - Yes. I got access to the DB through my hosting service tool from Rackspace (MyLittleAdmin) and through SSMS.

  • In SSMS for some reason the "Maintenance Plans" folder doesn't shows up in the Object Explorer when I'm connecting (remotely) to the DB which located at my hosting service servers. But, when I download the bak file locally to my PC, and connect him to SSMS, it shows up.

(I assume that the hosting service doesn't allow me to create plans/jobs?)

@ Chris S - As I wrote above, the maintenance plan disappears when I connect to the DB remotely (By entering the IP&PORT of the server to the SSMS)

And for the last answer from @DaniSQL:

This is look good enough, though it seems that I can't run jobs in SSMS. If I'll put this script in a new query through Rackspace SQL Management tool (MyLittleAdmin), it will work?

I just want to automatically backup and restore my DB on a daily basis.

Thank you very much for your help guys!

Matan Eldan
  • 113
  • 5

2 Answers2

1

SSMS isn't a service that runs on your computer, it's just a management tool that you can use to configure the server. You can configure the server with a maintenance plan that includes backing up the server, deleting old backups, rebuilding indices and other common tasks.

If you want the backup files transferred somewhere else, you'll likely have to configure another service of some kind to transfer the file offsite. Your environment will determine how your do offsite backups.

Chris S
  • 77,945
  • 11
  • 124
  • 216
1

Check this:

  1. easiest way is to create a maintenance plan using SSMS How to: Back Up a Database (SQL Server Management Studio) or check here

  2. If what you need is only a full backup you can write a procedure in the line of this and create a job that will execute the procedure and schedule it accordingly:

    alt text

  3. If you need a more robust backup/maintenance script check this article which explains how to use Ola Hallengren' highly regarded scripts

Edit:

I had to use a picture since the symantec protection is not alowing me post the script for reasons i dont know.

Community
  • 1
DaniSQL
  • 1,107
  • 7
  • 12
  • I assumed he has access to the databases, so he should be able to connect using SSMS and create a job using a script or create one using a maint plan. – DaniSQL Aug 04 '10 at 15:34
  • Look at my new edit. I hope that it will clarify the issue in all of his aspects :) – Matan Eldan Aug 04 '10 at 16:38
  • 1
    Two things: first if you are on sql server express it doesnt support maintenance plans and you need to use scripts. secondly, You may require a a more elevated permission to do backup and restore operations(something like sysadmin Server role and a dbcreator/db_owner/db_backupoperator database role). Try connecting using sysadmin and you will know that if permission is the problem. – DaniSQL Aug 04 '10 at 17:38
  • It was permissions problem from my hosting services. They offer only limited permissions by default (and unfortunately they can't change it) Thank you guys very much for your assistant! – Matan Eldan Aug 08 '10 at 08:43