Tek-Tips is the largest IT community on the Internet today!

Members share and learn making Tek-Tips Forums the best source of peer-reviewed technical information on the Internet!

  • Congratulations gkittelson on being selected by the Tek-Tips community for having the most helpful posts in the forums last week. Way to Go!

Maintenance plan settings for my disk backup? 2

Status
Not open for further replies.

davidchardonnet

Programmer
Mar 21, 2001
167
FR
Hello,

I want to make a disk backup of the databases which are on my SQL Server 7 server. I plan to use backup exec to put this disk backup on a tape.

I have several databases on this server and I don't know exactly how to configure the maintenance plan. I don't know if I only choose the databases other than 'msdb', 'master' and 'model', or if I must take them too.

All the other options of the maintenance plan, I don't know if I must use them... My only goal is to ensure I can restore the entire database if it crashes.

Thank you for your help

David
 
First off, I don't like using Maintenance Plans.

Here's how I do backups to disk (see reference at the end of post).

First I created 'dump devices' - these are where you are 'sending' the backups to...

EXEC sp_addumpdevice 'disk', 'masterfull', 'D:\backup\masterfull.bak'

That creates a dump device called masterfull. It will 'dump' the backup to disk to the file D:\backup\masterfull.bak

Then I run:
BACKUP DATABASE master TO masterfull WITH INIT

That backs up the Master database and overwrites the old backup file.

You would create a dump device for every database you want to backup and for each type of backup you are doing (full, differential, transaction log).

A translog might look like this:

EXEC sp_addumpdevice 'disk', 'mydbtlog', 'D:\backup\mydbtlog.bak'

BACKUP LOG mydb TO mydbtlog WITH INIT

Once you create dump devices, you can see them using Enterprise Manager, expand Management and then click on Backup.

-SQLBill

Reference: Books OnLine - use the index tab and enter sp_addumpdevice
then enter BACKUP DATABASE

Books OnLine = BOL = MS SQL Server Help
Installed as part of the Client Tools
Found at Start>Programs>Microsoft SQL Server>Books OnLine
 
>> All the other options of the maintenance plan, I don't know if I must use them

Don't use them at all.
Here is an SP that will backup all databases on the server


But better if you code it yourself.

imltho maintenance plans are just there for people who don't want to learn about the server and should never be used.


======================================
Cursors are useful if you don't know sql.
DTS can be used in a similar way.
Beer is not cold and it isn't fizzy.
 
Thank you SQLBill and NigelRivett for your help. I am using the script of nigelrivett, it's very simple, and goes straight to the goal. (and it's requires very few work for me..) ;)

Thanks again
 
Status
Not open for further replies.

Part and Inventory Search

Sponsor

Back
Top