SQL Server Performance

LiteSpeed Backup and restore using filegoups

Discussion in 'Third Party Tools' started by Moonwalker2000, Oct 19, 2006.

  1. Moonwalker2000 New Member

    Is anybody here using LiteSpeed able to successfully backup and restore filegroups from a SQL database using LiteSpeed?

    If so, how is it working for you?

    I have the latest LS version on my DEV server and can't get even the GUI tool to work doing a restore of a database from a bunch of filegroup backup files.
    I have a small test database and backup each filegroup including primary and log.

    On restoring it (GUI) keeps saying “Please specify at least one full backup (differentials and transactional logs are optional, but a full backup is required).

    As far as using the extended SP's I'm even in worse shape.

    Thanks
  2. joechang New Member

    in BOL, look under examples

    EXEC master.dbo.xp_backup_database @database='MyDB'
    , @filename= 'C:MSSQLBackupMyDB_Backup.BAK'
    , @filegroup = 'PRIMARY'
    , @init= 1

  3. Luis Martin Moderator

    I've moved to relevant forum.

    Luis Martin
    Moderator
    SQL-Server-Performance.com

    All in Love is Fair
    Stevie Wonder


    All postings are provided “AS IS” with no warranties for accuracy.



  4. Moonwalker2000 New Member

    I'm using the commands also from LS BOL but with no success: all the backup commands work but I need to be able to restore the full database or a partial one to one to a new name.

    EXEC master.dbo.xp_backup_database @database='DB1'
    , @filename= 'C:BackupDB1_Primary_LS.BAK'
    , @filegroup = 'PRIMARY'
    , @init= 1
    GO
    EXEC master.dbo.xp_backup_database @database='DB1'
    , @filename= 'C:BackupDB1_FG1_LS.BAK'
    , @filegroup = 'FG1'
    , @init= 1
    GO
    EXEC master.dbo.xp_backup_database @database='DB1'
    , @filename= 'C:BackupDB1_FG2_LS.BAK'
    , @filegroup = 'FG2'
    , @init= 1
    GO

    exec master.dbo.xp_backup_log @database = 'DB1'
    , @filename = 'c:DB1_Log_LS.BAK'
    , @init = 1

    -- restore after dropping DB1
    EXEC master.dbo.xp_restore_database @database='DB1' -- this works.
    , @filename= 'C:BackupDB1_Primary_LS.BAK'
    , @filegroup = 'Primary'
    , @with = 'NORECOVERY'
    , @with = 'PARTIAL'


    EXEC master.dbo.xp_restore_database @database='DB1' -- this fails with error message below.
    , @filename= 'C:BackupDB1_FG1_LS.BAK'
    , @filegroup = 'FG1'
    , @with = 'NORECOVERY'
    , @with = 'PARTIAL'

    "Msg 62309, Level 19, State 1, Line 1
    SQL Server has returned a failure message to LiteSpeed 2005 which has prevented the operation from succeeding.
    The following message is not a LiteSpeed 2005 message. Please refer to SQL Server books online or Microsoft technical support for a solution:

    RESTORE DATABASE is terminating abnormally.
    A partial restore sequence cannot be initiated by this command. To initiate a partial restore sequence, use the WITH PARTIAL clause of the RESTORE statement and provide a backup set which includes a full copy of at least the primary data file. The WITH PARTIAL clause of the RESTORE statement may not be used for any other purpose."



    Since I keep getting these type of errors I tried using the GUI but with no success.

    Thanks
  5. joechang New Member

    verify that you can do a normal SQL filegroup backup and restore
  6. Moonwalker2000 New Member

    Here is the SQL native code to do a full restore of the database from filegroup backups. I dropped it before running. Unfortunately I have a database that#%92s approaching 4TB with data separated out into filegroups by year going back to 2002. I need to reduce the time and tape requirements by doing filegroup backups.

    Thanks


    -- SQL PRIMARY FILEGROUP backup
    BACKUP DATABASE DB1
    FILEGROUP = 'PRIMARY'
    to disk = 'C:DB1_FG_PRIMARY_SQL.BAK' with init
    GO

    -- SQL FG1 FILEGROUP backup
    BACKUP DATABASE DB1
    FILEGROUP = 'FG1'
    to disk = 'C:DB1_FG_FG1_SQL.BAK' with init
    GO
    -- SQL FG2 FILEGROUP backup
    BACKUP DATABASE DB1
    FILEGROUP = 'FG2'
    to disk = 'C:DB1_FG_FG2_SQL.BAK' with init
    go
    -- SQL log backup
    BACKUP log DB1
    to disk = 'C:DB1_LOG_SQL.BAK' with init -- , norecovery, no_truncate
    GO

    --------------- restore DB1 -----------------
    RESTORE DATABASE DB1
    FILEGROUP = 'PRIMARY'
    from disk = 'C:DB1_FG_PRIMARY_SQL.BAK'
    WITH Partial, NORECOVERY, replace
    go
    RESTORE DATABASE DB1
    FILEGROUP = 'FG1'
    from disk = 'C:DB1_FG_FG1_SQL.BAK'
    WITH NORECOVERY, replace
    go
    RESTORE DATABASE DB1
    FILEGROUP = 'FG2'
    from disk = 'C:DB1_FG_FG2_SQL.BAK'
    WITH NORECOVERY, replace
    go

    RESTORE log DB1
    from disk = 'C:DB1_LOG_SQL.BAK'
    WITH RECOVERY


  7. joechang New Member

    why don't you do a test with a small database, even a blank db, that has several filegroups
    just to be certain your procedures are valid for native SQL fg backup and recovery
  8. Moonwalker2000 New Member

    That's what I did in my previous post. It's a small db with primary and 2 filegroups about 5MB in tolal. I create a table on FG2 and populate it with 2000 rows. I then back it up using the above example, dropped and restored back to origional state with 2000 rows in place. SQL Native backup works fine.

    My feeling is that Litespeed STILL don't have this working but I'd like to be proved wrong.

    Thanks
  9. joechang New Member

    are you restoring to a new location or over an existing db
  10. Moonwalker2000 New Member

    In this example I'm dropping the db and restoring to origional location. Nothing complicated for this test right now.
  11. joechang New Member

    in my test, neither native nor light speed liked the WITH PARTIAL
    both worked w/o partial
    i think it is for something else
  12. Moonwalker2000 New Member

    Ok, if you got it to work with LiteSpeed then post the script and I'll test it.
    I'm following the only examples available which is BOL.
  13. joechang New Member

    EXEC master.dbo.xp_backup_database @database='EDGE' , @filename= 'C:MSSQLBackupEDGE_Primary_LS.sls' , @filegroup = 'PRIMARY' , @init= 1
    GO
    EXEC master.dbo.xp_backup_database @database='EDGE' , @filename= 'C:MSSQLBackupEDGE_FG1.sls' , @filegroup = 'EdgeFG1' , @init= 1
    GO
    EXEC master.dbo.xp_backup_log @database='EDGE' , @filename= 'C:MSSQLBackupEDGE_log.sls' , @init= 1



    EXEC master.dbo.xp_restore_database @database='EDGE' -- this works.
    , @filename= 'C:MSSQLBackupEDGE_Primary_LS.sls' , @filegroup = 'PRIMARY' , @with = 'NORECOVERY'

    EXEC master.dbo.xp_restore_database @database='EDGE' --
    , @filename= 'C:MSSQLBackupEDGE_FG1.sls' , @filegroup = 'EdgeFG1' , @with = 'NORECOVERY'

    EXEC master.dbo.xp_restore_log @database='EDGE' --
    , @filename= 'C:MSSQLBackupEDGE_log.sls' , @with = 'RECOVERY'
  14. joechang New Member

    as soon as you get your to work
    go bug quest to better document this matter with examples

    FG b/u & restores are not common, but need to be clearly demonstrated for people who do need it
    i bugged the pm on the matter of GUI graying out the FG options
  15. Moonwalker2000 New Member

    Thanks Joe. That works.

    Actually I did bug Quest (repeatedly) and spoke to PM ("RB") to get their testing examples but he didn't get back to me.
  16. joechang New Member

    send him my examples,
    tell RB to stop wasting time talking to the marketing people,
    and start talking more to customers

    also see if you can figure what the PARTIAL is for,
    is for when the db is already in place, and you want to recover a fg?
    perhaps you could just drop 1 fg, pretending that the disks went bad, then recover just that fg with PARTIAL or other flags
  17. Moonwalker2000 New Member

    I think you are right about Partial and I will know soon as part of my testing. I need to do three types of restores:

    A full restore to another new database name with Move option either to same server or different sever with diff disk layout.
    A partial restore to another new database to restore only one FG.
    and a restore of one or more FG(s) over the original database.


    I'll get all my scripts done first and then copy to Quest.

Share This Page