Solved

How to back-up a database automatically?

Posted on 2008-10-13
10
490 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
  • 4
  • 4
  • 2
10 Comments
 
LVL 5

Expert Comment

by:harwantgrewal
Comment Utility
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
Comment Utility
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
Comment Utility
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
 
LVL 5

Expert Comment

by:harwantgrewal
Comment Utility
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
Comment Utility
Righto...you need the necessary permissions.  
0
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 
LVL 1

Author Comment

by:killdurst
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
I am happy that you found the solution :)

Harry
0

Featured Post

IT, Stop Being Called Into Every Meeting

Highfive is so simple that setting up every meeting room takes just minutes and every employee will be able to start or join a call from any room with ease. Never be called into a meeting just to get it started again. This is how video conferencing should work!

Join & Write a Comment

Recently, when I was asked to create a new SQL 2005 cluster, Microsoft released a new service pack for MS SQL 2005 what is Service Pack 3. When I finished the installation of MS SQL 2005 I found myself troubled why the installation of SP3 failed …
This article explains how to reset the password of the sa account on a Microsoft SQL Server.  The steps in this article work in SQL 2005, 2008, 2008 R2, 2012, 2014 and 2016.
In this tutorial you'll learn about bandwidth monitoring with flows and packet sniffing with our network monitoring solution PRTG Network Monitor (https://www.paessler.com/prtg). If you're interested in additional methods for monitoring bandwidt…
This video shows how to remove a single email address from the Outlook 2010 Auto Suggestion memory. NOTE: For Outlook 2016 and 2013 perform the exact same steps. Open a new email: Click the New email button in Outlook. Start typing the address: …

772 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

Need Help in Real-Time?

Connect with top rated Experts

10 Experts available now in Live!

Get 1:1 Help Now