Post reply

backup filegroups

  • July 30, 2004 at 1:10 pm

    #108763

    I am trying to backup several databases using Enterprise Manager, but the option to backup file and filegroup is grayed out.  Can anyone help?

  • July 30, 2004 at 1:24 pm

    #517097

    Master, Msdb & tempdb do not allow it.

    Per INF: Restore File and Filegroup Backups in SQL Server

    SQL Server 7.0: This requires that the
TruncateLogOnCheckpoint option is not set and that the
transaction log backups are created in addition to the
database or file and filegroup backups.SQL Server 2000: To
create transaction log backups, you must use either the Full
Recovery or Bulk-Logged Recovery models. For more
information on recovery models, see SQL Server 2000 Books
Online "Selecting a Recovery Model" topic.

    Also check article SQL Server Maintenance Wizard Does Not Allow Scheduled Filegroup or Differential Backups

    Good luck



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 30, 2004 at 1:37 pm

    #517099

    Your reply helped a little bit, but now we have an error after choosing the filegroup option.  it reads:

    MICROSOFT SQL-DMO(ODBC SQLSTATE: 42000):  Cannot open backup device...device error or device offline.  See the SQL Server log for more details. Restore HEADERONLY is terminating abnormally.

    The log file essentially says the same thing.

  • July 30, 2004 at 2:48 pm

    #517102

    What is the backup device? Tape? Disk?

    Is the device showing up in the EM under %Server% -> Management -> Backup?



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 30, 2004 at 2:58 pm

    #517104

    We are using a disk for our backup.  The drive does not show up in Management -> Backup. 

    The fishy thing about this whole problem is that we created a blank DB from scratch and tried to back it up to this same drive.  It backed up with no problems, so I am thinking my error has something to do with the permissions set on the DB.  Any thoughts?

  • July 30, 2004 at 4:14 pm

    #517109

    Just do a complete backup. Or is this DB that huge?

    It will ask you where you want to backup and what filename.



    ----------------
    Jim P.

    A little bit of this and a little byte of that can cause bloatware.

  • July 31, 2004 at 2:22 pm

    #517131

    Make sure your database is not a simple recovery model. You can not use FG backup for a simple recovery model database

     

    Shas3

  • August 4, 2004 at 11:08 pm

    #517750

    you cant take backup of filegroup or files in EM. you will have to do that in query analyzer. like this

     

    backup database <DBName> FILE = 'your logi filename',

       FILEGROUP = 'your filegroypname ' to disk='Path'

     

    but remember after taking the file or filegroup you have to take the log backup. and you cant take these backyps if your database is in SIMPLE recovery nmodel.

     

    regards,

    ganapathy

  • August 5, 2004 at 8:11 am

    #517843

    You sure can take the file backup in the EM. You just have to specify which FG or File you want to take the backup. Don’t forget to take the T-Log back up or a File incremental back up after a File backup

    Shas3

Viewing 9 posts - 1 through 8 (of 8 total)

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