Solved

How to back-up a database automatically?

Posted on 2008-10-13
10
497 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
What Is Transaction Monitoring and who needs it?

Synthetic Transaction Monitoring that you need for the day to day, which ensures your business website keeps running optimally, and that there is no downtime to impact your customer experience.

 
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

Forrester Webinar: xMatters Delivers 261% ROI

Guest speaker Dean Davison, Forrester Principal Consultant, explains how a Fortune 500 communication company using xMatters found these results: Achieved a 261% ROI, Experienced $753,280 in net present value benefits over 3 years and Reduced MTTR by 91% for tier 1 incidents.

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…
When writing XML code a very difficult part is when we like to remove all the elements or attributes from the XML that have no data. I would like to share a set of recursive MSSQL stored procedures that I have made to remove those elements from …
Michael from AdRem Software explains how to view the most utilized and worst performing nodes in your network, by accessing the Top Charts view in NetCrunch network monitor (https://www.adremsoft.com/). Top Charts is a view in which you can set seve…
Monitoring a network: why having a policy is the best policy? Michael Kulchisky, MCSE, MCSA, MCP, VTSP, VSP, CCSP outlines the enormous benefits of having a policy-based approach when monitoring medium and large networks. Software utilized in this v…

729 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