Learn how to a build a cloud-first strategyRegister Now

x
  • Status: Solved
  • Priority: Medium
  • Security: Public
  • Views: 488
  • Last Modified:

How to automate my sql backup

Dear SQL experts,

I have a database that is being backup manually. Backup to a local file and the file will eventually be put onto tape. (For some reason my SQL Server is not recognising my tape drive but its ok I can live with that because I backup not only my SQL database on it)

What I am asking is a way to automate this backup process.  At the moment I backup my database by going into Management Studio, right-click on the database, select Backup and choose the destination file (overwrite the existing one); then a separate backup process will backup this .bak file to tape. I would like to know what I can do to automate the backup.

Has anyone got a step by step guide for this? I know a bit of SQL so I am not too scared with SQL statements. I heard about creating a permanent device to store the backup but am not sure what is that; also I am not sure about truncating transaction logs. My hope is if there is a standard backup statement that I can schedule it to run at a chosen time. I want to perform FULL backup everyday.

THanks in advance.
0
ormerodrutter
Asked:
ormerodrutter
1 Solution
 
Bretty1Commented:
Hello mate.
Presuming you are using SQL 2005, and you have installed all components you can automate this process with the maintenance wizard.  
As follows in SQL Server Management Studio:
1) Root DB Folder > Management > Maintance Plans.
2) Right click new maintenance plan, when prompted give the maintenance plan a name.
Follow this wizard.
This is the most simple method.  First check and see if this suits your needs.
If you want to get more complicated and have more options, then there is plenty of TSQL options for scripting the backup.
0
 
DarkHillCommented:
I use the following SP to backup all the DBs on my server every day: http://www.sqldbatips.com/showarticle.asp?ID=27

I then use a JScript to zip the backups and FTP them to our backup server, but I think you have this covered.
0
 
ormerodrutterAuthor Commented:
Thanks Bretty1 that works a treat. I supposed backing up the database that way I don't need to worry about transaction logs?

Sorry Darkhill Bretty's solution is easier to follow and he went in first.
0

Featured Post

Free Tool: Path Explorer

An intuitive utility to help find the CSS path to UI elements on a webpage. These paths are used frequently in a variety of front-end development and QA automation tasks.

One of a set of tools we're offering as a way of saying thank you for being a part of the community.

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