Results 1 to 5 of 5
  1. #1
    Join Date
    Oct 2003
    Posts
    7

    Unanswered: Backing Mult DB's to a Device

    Im using SS2k, and I created a backup device via Enterprise Manager that points to a BAK file.

    I'd like to backup multiple databases to it, but when I do, the .BAK file keeps getting larger and larger (i.e. it's storing each copy of the backup). I'd like to store only one copy of the backup, but if I specify the INIT property when doing the backup, all databases written to that .BAK file get overwritten.

    Any ideas here?

  2. #2
    Join Date
    Sep 2003
    Posts
    522
    you need to do with init on the first database in your cycle.

    i also don't think it's the best approach to backup all dbs to the same device. do you have too many of them? even then it shouldn't be a factor. can you clarify your reasoning?

  3. #3
    Join Date
    Oct 2003
    Posts
    7
    I wanted to use a device in order to provide a layer of abstraction between my backups and the actual file.

    I have several Years of Sales data, each in a seperate db (i.e. Sales1999, Sales2000, Sales2001, etc). I was hoping to back them up to a device (I called it SalesBackups) that pointed to a file. This way, in case I need to move the physical file location to a diffent drive, I wouldn't have to touch my backup jobs, as far a they're concerned, they're still backing up to SalesBackups.

    I can do this, but they keep appending on, causing my BAK to get quite large, quite quickly.

    I understand what your saying about INIT the first DB, and I suppose that's what I'll do.

    Any comments or criticisms about my strategy? Any advice would be appreciated.

  4. #4
    Join Date
    Sep 2003
    Posts
    522
    how about scripting the device, and then the backup jobs? this way you can just dedicate a device per db, script them, script the jobs (i'd even have just one job unless multiple trx dumps are done per each db dump cycle), and you're home free, even if you have to move to a different box

  5. #5
    Join Date
    Oct 2003
    Posts
    7
    Originally posted by ms_sql_dba
    how about scripting the device, and then the backup jobs? this way you can just dedicate a device per db, script them, script the jobs (i'd even have just one job unless multiple trx dumps are done per each db dump cycle), and you're home free, even if you have to move to a different box
    Yeah - I hear ya. What I've done is added the ability to store where the BAK file should be created in. I'll then set each of my sales files to be created in the "SalesFile" directory, and then I'll have a Sales1999.BAK, Sales2000.bak, blah blah blah. It'll work fine, it's just not as sexy as i'd like.

    Another Question, since your so nice to respond to my post. How do I clean up my "Restore History"? If I run BACKUP DATABASE with the INIT option 10 times, it shows each date/time when I go to restore it, even though only the last one is really valid. Any thought on how to clean that up?

Posting Permissions

  • You may not post new threads
  • You may not post replies
  • You may not post attachments
  • You may not edit your posts
  •