Results 1 to 4 of 4
  1. #1
    Join Date
    Jul 2002
    Posts
    3

    Unhappy Unanswered: Backup and Restore Filegroups

    Hi,
    I want to take the backup of a particular filegroup in SQL server 2000 and then restore the same filegroup backup.
    Currently I am using the following steps :
    1. Creating the database with 2 filegroups. The syntax that I used for it is as follows :
    create database test
    on primary
    (name = test1,
    filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test1.mdf'),
    (name = test2,
    filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test2.ndf'),
    filegroup group2
    (name = test3,
    filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test3.ndf'),
    (name = test4,
    filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test4.ndf')
    log on
    (name = test_log,
    filename = 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test_log.ldf')

    2. Now I am creating two tables table1 and table2 using Enterprise Manager. Both the Tables, table1 and table2 are on the "Group2" Filegroup of test database.

    3. After creating the tables, I inserted three records in each table.

    4. Then I performed the backup using the following commands:
    backup log test
    to disk = 'e:\test.lg1' with init

    backup database test
    filegroup = 'group2'
    to disk = 'e:\test_group2.bak'

    5. After the backup has been taken, I deleted the two records from both the tables. Now both my tables have one record each.

    6. Now I tried restoring the database using following command :

    backup log test
    to disk = 'e:\test.lg3' with init

    restore database test
    filegroup = 'group2'
    from disk = 'e:\test_group2.bak'
    with norecovery

    restore log test
    from disk = 'e:\test.lg3' with norecovery

    restore database test
    with recovery

    7. After all these commands are executed, when i checked my database, it contains again the one record in each table. Ideally, according to me the tables should contain 3 records as i had taken the backup when each table had 3 records.

    How can I bring the database back to the state in which I had taken the backup.

    Pls. help me as soon as possible.

    And also let me know, if I need to do any SQL level or Database level settings for the above task.

    Waiting for your reply. and thanking you in advance.

    Regards,
    a_k93

  2. #2
    Join Date
    Nov 2002
    Location
    Switzerland
    Posts
    524
    A backup log beffore a backup database has no effect.

    You must do a backup database, then backups log... and be sure that your option "trunc log on checkpoint " is set to false for this particular db.

  3. #3
    Join Date
    Jul 2002
    Posts
    3
    Hi,
    Tried setting the option to false and followed the same backup and restore procedure after setting the option to false, but it is not working.

    Can u post an example of the same, starting from the creation of the database and filegroups?

    Regards,
    a_k93

  4. #4
    Join Date
    Feb 2004
    Location
    San Antonio, TX
    Posts
    565
    do you need to recover this database as soon as possible?

    unfortunately it appears that in your descirption that you have restored your full on top of your latest log backup so you should

    restore the last full db backup with no recovery
    restore any filegroup backups since the last full with no recovery
    restore the log backups with recovery
    this will force recovery to roll forward any commited xacts and roll back any incomplete xacts.
    they you should be at the moment that the log backup was created.
    unfortunately (if your description is correct) you will not be able to restore to the moment of corruption due to the lack of a log backup with no_truncate

    good luck.

    ps
    download lumigent log explorer and use it to read your live log and your backed up logs and find the missing xacts and you can restore them or remove them.

Posting Permissions

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