Want to win a PS4? Go Premium and enter to win our High-Tech Treats giveaway. Enter to Win

x
?
Solved

How to back-up a database automatically?

Posted on 2008-10-13
10
Medium Priority
?
501 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
Technology Partners: 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

NFR key for Veeam Backup for Microsoft Office 365

Veeam is happy to provide a free NFR license (for 1 year, up to 10 users). This license allows for the non‑production use of Veeam Backup for Microsoft Office 365 in your home lab without any feature limitations.

Question has a verified solution.

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

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 …
In this article I will describe the Backup & Restore method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this video, Percona Solution Engineer Rick Golba discuss how (and why) you implement high availability in a database environment. To discuss how Percona Consulting can help with your design and architecture needs for your database and infrastr…
How to fix incompatible JVM issue while installing Eclipse While installing Eclipse in windows, got one error like above and unable to proceed with the installation. This video describes how to successfully install Eclipse. How to solve incompa…

636 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