Solved

An Automatically Executing Database Backup Script

Posted on 2009-05-05
2
201 Views
Last Modified: 2012-05-06
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
Comment
Question by:Ken Carpenter
2 Comments
 
LVL 60

Accepted Solution

by:
chapmandew earned 125 total points
ID: 24310793
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
 
LVL 3

Assisted Solution

by:GregTSmith
GregTSmith earned 125 total points
ID: 24333566
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

PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

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…
Use this article to create a batch file to backup a Microsoft SQL Server database to a Windows folder.  The folder can be on the local hard drive or on a network share.  This batch file will query the SQL server to get the current date & time and wi…
This Micro Tutorial hows how you can integrate  Mac OSX to a Windows Active Directory Domain. Apple has made it easy to allow users to bind their macs to a windows domain with relative ease. The following video show how to bind OSX Mavericks to …
This video shows how to quickly and easily add an email signature for all users on Exchange 2016. The resulting signature is applied on a server level by Exchange Online. The email signature template has been downloaded from: www.mail-signatures…

816 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

Need Help in Real-Time?

Connect with top rated Experts

11 Experts available now in Live!

Get 1:1 Help Now