• Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 209
  • Last Modified:

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.
0
Ken Carpenter
Asked:
Ken Carpenter
2 Solutions
 
chapmandewCommented:
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
 
GregTSmithCommented:
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

Featured Post

What does it mean to be "Always On"?

Is your cloud always on? With an Always On cloud you won't have to worry about downtime for maintenance or software application code updates, ensuring that your bottom line isn't affected.

Tackle projects and never again get stuck behind a technical roadblock.
Join Now