Backing up multiple Databases in SQL 2005

hi I've been looking around to help out our developer (who is alos the back up guy) on how ot back up SQL 2005 the recomenddations are to back up ot a file thne go to tape which is fine but I need a way to back up approx mately 20 Db's to 20 seperate file then back up those files.

all I've found so far is right click on the DB and choose bakc up for the managemnt console

we want to do a full back up everynight for al 20 db's and have this automated.

I've heard conflicting information about using open file agents.. some say do it soemk say it will not work.

apparently MS recomendcs gonig ot a fiel first then to the tape.... so this wouldn't require any backpu agents from Veritas or Acrserve.

let me know of any way to back up all 20 db's to a file.... I can handle the tape part.

off the top[ of my head I'm assumnig you will need a script but Im' unsure of what commands to issue

Find more posts tagged with

Save $250 on 2025 certification boot camps from Infosec!

Book now with code EOY2025

Button

Comments

TechJunky

If all the .db are stored in the same folder, simply create a batch file to copy the folder to another folder and you will be done.

If each .db is stored in a different location it could get tricky.

Are all the db's running off the same server? What drive and location are they being stored in currently? And why dont you put all the db's in the same folder if they are not already?

Thanks.

Smallguy

we have a Programmer who has the DB's all organized and they're in the same folder but will copying jsut the .db files restore everything do oyu need more??

also SQL is an open file so I'm not sure if you need ot follow a specila process because of this

sprkymrk

Check the \Program Files\MSSQL\ directory. There should be a folder in there with the transaction logs and backups. I believe that's what you want to copy. Search for *.trn files if they have changed the default location.

Trailerisf

Sql server management studio has the option of backing up the live database to another location. You can then backup the .bak file with whatever software you are using.

I would still reconmend Brightstore with a sql addon.