Filegroup backup/copy/restore? 1

[JohnDTampaBay]

I need advise on refining our backup/restore process.

We have a big honkin' production database that needs to be backed up, BAK copied to 2nd server, then restored on 2nd server for Endmonth processing. Currently the backup/restore time is quite lengthy.

It is my understanding that I can create a round-robin backup/restore process of filegroups to speed up the backup/restore time. More than 2 filegroups are possible, but simply put:

      Backup Filegroup 1
        |            |
        |            |
  Copy BAK to     Backup Filegroup 2
  2nd server         |
        |            |
  Restore FG1        |
  on 2nd server      |
       |             |
       '--.       .--'
          |       |
         Restore FG2
         on 2nd server

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mrdenny]

Yep, except that you have to backup and restore primary first then the other file groups that you want to copy accross.

After each restore make sure to put the database into NORECOVERY or STANDYBY mode until the last one has been restored.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com

 

[JohnDTampaBay]

So I can't bring the database online until all filegroups have been restored? It was my understanding that tables would be available as soon as each filegroup was restored. Our endmonth process only requires about 1/3 of the tables in the database. I would love to get it churning without waiting on the other 2/3 of the database. Suggestions?

--John
-----------------------------------
Behold! As a wild ass in the desert
go forth I to do my work.
--Gurnie Hallock (Dune)

 

[mrdenny]

With SQL 2005 as soon as each filegroup is restored it's data is available. With SQL 2000, I'm not sure as I haven't tried it.

Denny
MCSA (2003) / MCDBA (SQL 2000) / MCTS (SQL 2005)

--Anything is possible. All it takes is a little research. (Me)

http://www.mrdenny.com