how do i automatically back up my sql server database every month?

Question

i have a sql server 2005 database without around 100 tables. i need to back it up every month. is there a way to automate this process? how would i do it? i am a newbie so please explain thoroughly

if it is a stored procedure please let me know what that procedure would look like and where to call it from.

Since you're a newbie, I'd suggest starting here: http://technet.microsoft.com/en-us/magazine/2009.07.sqlbackup.aspx — , Jul 14 '10 at 15:46
The version is SQL 2005, we need to know what edition it is. — joeqwerty, Jul 14 '10 at 21:25
Every month? Don't you mean every day? Or don't you value that data? — John Gardeniers, Jul 14 '10 at 21:28
Why do you only want to backup once a month? Are you prepared to lose one month's worth of data? — bearrito, Jul 14 '10 at 19:22

score 11 · Accepted Answer · answered Jul 14 '10 at 15:46

11

you could create a maintenance plan to do the backups and then you would schedule it to run once a month. See here: http://www.databasedesign-resource.com/sql-server-maintenance-plan.html

answered Jul 14 '10 at 15:46

SQLMenace

391
4
4

score 2 · Answer 2 · answered Jul 14 '10 at 15:47

2

You can create a scheduled task and call the SQL method. Think about using a VB script or so. I did this for backing up a SQL server to a zip-drive every day and it worked great.

answered Jul 14 '10 at 15:47

1

please show me how – Jul 14 '10 at 15:49

score 1 · Answer 3 · answered Jul 14 '10 at 15:47

1

You could do this with a database script and a scheduled task. Visit this link for more details.

answered Jul 14 '10 at 15:47

Scott Lawrence

123
6

score 1 · Answer 4 · edited May 23 '17 at 12:41

1

take a look here

edited May 23 '17 at 12:41

Community

1

answered Jul 14 '10 at 16:24

score 0 · Answer 5 · answered Jul 14 '10 at 19:25

0

If this is SQL Express you can't use Maintenance Plans, you'll need something like http://expressmaint.codeplex.com/

answered Jul 14 '10 at 19:25

Malnizzle

1,441
2
16
30

score 0 · Answer 6 · answered Sep 11 '18 at 20:06

You could use a VB Script I wrote exactly for this purpose: https://github.com/ezrarieben/mssql-backup-vbs/

Schedule a task in the "Task Scheduler" to execute the script as you like and it'll backup the entire DB to a BAK file and save it wherever you specify.

how do i automatically back up my sql server database every month?

6 Answers6