multi-db backup

  • Hi,

    On my development computer I have sqlserver installed. I have about 15-20 databases that I want to backup, is it possible for me to do this all-at-once as oppose to individually?

  • No and Yes.

    You can't run one BACKUP DATABASE command and have it backup ALL databases.

    You CAN create one JOB (Enterprise Manager, Management, SQL Server Agent, Jobs) and in the job create a Step for each database backup job. Then all you need to do is schedule the job and it will backup all the databases for you.

    -SQLBill

  • maintain plan

  • Do you have some generic code that I can use for my STEPS? I can change the c: path's etc myself.

    Thanks this will be just perfect!

  • You should look at the BOL (Books Online) topics for Backup Database and sqlmaint.exe.

    Backup Database is a T-Sql command that you would execute from a script.

    Sqlmaint.exe is a command line utility that you can run in a cmdline window or from a job.

    I personally like Sqlmaint. It lets me take care of a lot of housekeeping chores in one go.

    Richard L. Dawson

    Sql Server Support Engineer

    ICQ# 867490

    Richard L. Dawson
    Microsoft Sql Server DBA/Data Architect

    I can like a person. People are arrogant, ignorant, idiotic, irritating and mostly just plain annoying.

  • I wrote a little script that I have stored as an sp called 'sp_backup' that uses a cursor through sysdatabase to execute a backup on each database in the system, one at a time. I use a previously configured backup device, or you could hard-code the disk device into your script.

    The advantage of using the existing backup device is then your sp is generic and you can roll it out to all servers.

    I have a policy to create a new backup device with each new database create. That way I know it's out there when the sp kicks off each evening.

    Laura

Viewing 6 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply