Brand new deployment: 3 part question...

• SQL_ME_RICH

  SSChampion

  Points: 11200

  More actions

  March 17, 2011 at 2:15 pm

  #1300211

  P.S. I just realized that my earlier post eludes to a Full backup scheme, and I am not doing that any longer. It's now a Filegroup backup scheme. Here is the BACK and RESTORE code...

  Backup code example of PRIMARY and FG1

  BACKUP DATABASE Testing FILEGROUP = 'PRIMARY'

  TO DISK = 'C:\SQL\Backups\Testing\Testing.FLG'

  WITH NOFORMAT,

  INIT,

  NAME = N'Testing-Primary',

  SKIP,

  NOREWIND,

  NOUNLOAD,

  STATS = 10

  GO

  BACKUP DATABASE Testing FILEGROUP = 'FG1'

  TO DISK = 'C:\SQL\Backups\Testing\FG1F.FLG'

  WITH NOFORMAT,

  INIT,

  NAME = N'Testing-FG1',

  SKIP,

  NOREWIND,

  NOUNLOAD,

  STATS = 10

  GO

  Restore example of PRIMARY and FG1

  RESTORE DATABASE [Testing]

  FILE = N'Testing'

  FROM DISK = N'C:\SQL\Backups\Testing\Testing.FLG'

  WITH FILE = 1, NOUNLOAD, REPLACE, RECOVERY, STATS = 10

  GO

  RESTORE DATABASE [Testing]

  FILE = N'FG1F'

  FROM DISK = N'C:\SQL\Backups\Testing\FG1F.FLG'

  WITH FILE = 1, NOUNLOAD, REPLACE, RECOVERY, STATS = 10

  GO

  Initially I was told that the RECOVERY option in my WITH was all that was needed to bring it back online, but here is the process I've actually had to do to bring it back online (or at least looking and operating as if online, but not allowing me to back it up as I originally did before restoring it).

  1. Restore the PRIMARY Filegroup (FILE = N'Testing') first, and not with the others listed below it.

  2. Refresh my db list in the Object Explorer to see the db, and then take it offline.

  3. Restore the other Filegroups (FG1, FG2, ROFG1, NC_INDEX, etc...). Can be done 1 at a time or all at once.

  4. Detach the db. It will give you an error, but this still works. Click OK to clear the error, and then close SSMS.

  5. Stop and restart the MSSQLSERVER Service.

  6. Go back into SSMS, Right-Click on Databases and select Attach (from the location that your .mdf was restored to), and your database is in your db list with your other db's, gold and looking "online".

  And as I said - it works as if it is (I can query from it and open and expand it), but if I try to run the backup again (which would happen in a real world scenario after a crash), it just won't see the Filegroups as being on-line.