Solved

How to back-up a database automatically?

Posted on 2008-10-13
10
494 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
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
Independent Software Vendors: 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!

 
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

U.S. Department of Agriculture and Acronis Access

With the new era of mobile computing, smartphones and tablets, wireless communications and cloud services, the USDA sought to take advantage of a mobilized workforce and the blurring lines between personal and corporate computing resources.

Question has a verified solution.

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

There are some very powerful Data Management Views (DMV's) introduced with SQL 2005. The two in particular that we are going to discuss are sys.dm_db_index_usage_stats and sys.dm_db_index_operational_stats.   Recently, I was involved in a discu…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
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…
Are you ready to implement Active Directory best practices without reading 300+ pages? You're in luck. In this webinar hosted by Skyport Systems, you gain insight into Microsoft's latest comprehensive guide, with tips on the best and easiest way…

749 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