An Automatically Executing Database Backup Script

Greetings All:

I am new to SQL Server 2005 standard and am trying to catch up and wondering if I can ask assistance from someone in the group.  I would like to establish a script that would automatically execute on our database server at various times during the day and  perform a full backup of our new database.  The naming structure of the  backup would be comprised from the database name and a date and time stamp.  For example a backup of database "ABC" taken today at 10:00AM would be named "abc0505091000.bak".

My initial aim is that by backing up the database at various times during the day I minimize the amount of loss should we experience a catastrophic failure of the database.  Additionally I can copy the above backups to removable media and store them offsite for contingency protection as well.

By the time the system grows to substantial size I can explore alternative methods of protecting the data as well as have a budget to support the plan.

Your suggestions would be greatly appreciated.
Ken CarpenterAsked:
Who is Participating?
 
chapmandewConnect With a Mentor Commented:
YOu can look at the script I use in this article to get an idea as to how to write the script w/the db name in it.  The very first script will do it.

http://sqlservernation.com/blogs/tipweek/archive/2009/02/23/automated-sql-server-express-backups.aspx
0
 
GregTSmithConnect With a Mentor Commented:
SQL Server can generate the script for you as well.

Right-click the database > Tasks > Back Up > Set all the appropriate settings, and click the script button.  

You could then setup a job that runs the generated script.  
0
Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.

All Courses

From novice to tech pro — start learning today.