Solved

How to back-up a database automatically?

Posted on 2008-10-13
10
496 Views
Last Modified: 2012-06-27
I would like to automatically back-up my database daily at 12:00AM. I would like the back-up files to be in "C:\backups" and the file name should be "databaseName_YYYYMMDD.bak" where "YYYYMMDD" is the year, month and day. For example, the following is what we should see in "backups":

databaseName_20081012.bak
databaseName_20081013.bak
and so on...

I don't mind writing the script in a batch file and then use the task scheduler to run it at 12:00AM daily.
0
Comment
Question by:killdurst
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
  • 2
10 Comments
 
LVL 5

Expert Comment

by:harwantgrewal
ID: 22708622
Hi you dont have access to Managment Studio. As you can create Job in there and sechdule them.

Harry
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22708630
The standard way to do that is to create a Management Plan.  A wizard will walk you thru the steps under the Management node of Object Explorer
0
 
LVL 1

Author Comment

by:killdurst
ID: 22708760
Ok I see the Management node under Object Explorer, but when I right click on it I only see "Refresh". How do I access the wizard?
0
MS Dynamics Made Instantly Simpler

Make Your Microsoft Dynamics Investment Count  & Drastically Decrease Training Time by Providing Intuitive Step-By-Step WalkThru Tutorials.

 
LVL 5

Expert Comment

by:harwantgrewal
ID: 22708829
Probably you user is not in SysAdmins group thats why you couldn't see anything there. Attached is the snap shot of how it looks.

Harry
14-10-2008-4-42-37-PM.png
0
 
LVL 42

Expert Comment

by:dqmq
ID: 22711974
Righto...you need the necessary permissions.  
0
 
LVL 1

Author Comment

by:killdurst
ID: 22717358
I'm logged in as "sa" and under "Security" -> "Logins" and when I right click on "sa" and check the server roles, "sysadmin" is selected already. Am I missing anything else here?
0
 
LVL 1

Author Comment

by:killdurst
ID: 22717377
Aww shucks... I'm using Management Studio Express... I think the maintenance plans are not included in this version...
0
 
LVL 5

Expert Comment

by:harwantgrewal
ID: 22717404
Yeah you are right you need to have
Microsoft SQL Server Management Studio                                    9.00.1399.00

Thanks
Harry
0
 
LVL 1

Accepted Solution

by:
killdurst earned 0 total points
ID: 22717405
Managed to find a solution...

- Right clicked on my database -> Tasks -> Back Up... and then scripted it to a file...
- Then I called the sql file from a batch file.
- The batch file creates the backup by executing the following code:

C:\Program Files\Microsoft SQL Server\90\Tools\Binn>SQLCMD.EXE -S <server name or ip> -U <username> -P <password> -i C:\temp\20081015\backup.sql
0
 
LVL 5

Expert Comment

by:harwantgrewal
ID: 22717412
I am happy that you found the solution :)

Harry
0

Featured Post

SharePoint Admin?

Enable Your Employees To Focus On The Core With Intuitive Onscreen Guidance That is With You At The Moment of Need.

Question has a verified solution.

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

Suggested Solutions

If you having speed problem in loading SQL Server Management Studio, try to uncheck these options in your internet browser (IE -> Internet Options / Advanced / Security):    . Check for publisher's certificate revocation    . Check for server ce…
Data architecture is an important aspect in Software as a Service (SaaS) delivery model. This article is a study on the database of a single-tenant application that could be extended to support multiple tenants. The application is web-based develope…
A short tutorial showing how to set up an email signature in Outlook on the Web (previously known as OWA). For free email signatures designs, visit https://www.mail-signatures.com/articles/signature-templates/?sts=6651 If you want to manage em…
Exchange organizations may use the Journaling Agent of the Transport Service to archive messages going through Exchange. However, if the Transport Service is integrated with some email content management application (such as an antispam), the admini…

739 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