Re: VLDB backups and partial restore

Tech-Archive recommends: Fix windows errors by optimizing your registry

From: Michael Swart (mjswart_at_yahoo.com)
Date: 08/20/04 

Date: 20 Aug 2004 05:39:02 -0700

"Russell Fields" wrote:
> Michael,
>
> I am not sure that I understand. You seem to know about the following
> points from the BOL. As I read it, if you do filegroup backups of the
> particular filegroups that you care about, you can restore only those
> filegroups, which is different from doing a 'partial' restore. Do you read
> it differently?

You can restore separate filegroups, but you can't recover that way
without all the filegroups being synchronized to a certain state. Say
a disaster occurs and I restore the primary filegroup and only one
other filegroup. Trying to recover at that point will show the
following error message:

The database cannot be recovered because the files have been restored
to inconsistent points in time.

See the following script:

-- Can we restore partial databases

-- Drop any existing databases
USE master
GO
DROP DATABASE mytest
GO
DROP DATABASE mytest_recovered
GO

-- Create database
CREATE DATABASE mytest
GO
ALTER DATABASE [mytest] SET RECOVERY FULL
GO

-- Create full backup
BACKUP DATABASE mytest
        TO DISK = 'c:\mytest.dmp'
        WITH INIT
GO

-- Add new file group LotsOfData1 and add table t1 to it.
ALTER DATABASE mytest ADD FILEGROUP LotsOfData1
GO

ALTER DATABASE mytest ADD FILE
   (NAME='mytest_data_1',
   FILENAME='c:\mt.dat1')
   TO FILEGROUP LotsOfData1
GO

CREATE TABLE mytest..t1 (id int) ON LotsOfData1
GO

INSERT into mytest..t1 values (1)
GO

-- Add new file group LotsOfData2 and add table t2 to it.
ALTER DATABASE mytest ADD FILEGROUP LotsOfData2
GO

ALTER DATABASE mytest ADD FILE
   (NAME='mytest_data_2',
   FILENAME='c:\mt.dat2')
   TO FILEGROUP LotsOfData2
GO

CREATE TABLE mytest..t2 (id int) ON LotsOfData2
GO

INSERT into mytest..t2 values (2)
GO

-- Perform backup of filegroup2
BACKUP DATABASE mytest
        FILEGROUP = N'LotsOfData2'
        TO DISK = 'c:\mytest.dmp'
        WITH NOINIT
GO

-- Disaster occurs here. We'll restore tables t2 but not t1.

RESTORE FILELISTONLY FROM DISK='c:\mytest.dmp'
GO
RESTORE HEADERONLY FROM DISK='c:\mytest.dmp'
GO

-- Restore db and primary filegroup
RESTORE DATABASE mytest_recovered
        FROM DISK='c:\mytest.dmp'
        WITH FILE=1,NORECOVERY,
        MOVE 'mytest' TO 'c:\mt2.pri',
        MOVE 'mytest_log' TO 'c:\mt2.log'
GO

-- Restore t2 from backup 3
RESTORE DATABASE mytest_recovered
   FILEGROUP = 'LotsOfData2'
   FROM DISK='c:\mytest.dmp'
   WITH FILE=2,NORECOVERY,
   MOVE 'mytest_data_2' TO 'c:\mt2.dat2'
GO

-- Recover
RESTORE DATABASE mytest_recovered
        WITH RECOVERY

-- clean up dbs and files etc...
GO
DROP DATABASE mytest
GO
DROP DATABASE mytest_recovered
GO

Relevant Pages

  • Re: PARTIAL SQL Server RESTOREs
    ... DROP DATABASE mywind_part ... ALTER DATABASE mywind ADD FILEGROUP new_customers ... -- A full database backup is performed. ... RESTORE FILELISTONLY lists the database ...
    (microsoft.public.sqlserver.server)
  • Re: Filegroup restore problems
    ... Click Leave database operational. ... but able to restore additional ... transaction logs if another transaction log backup is to be applied ... It seems that the log file backup was not taken before the filegroup backup. ...
    (comp.databases.ms-sqlserver)
  • PARTIAL SQL Server RESTOREs
    ... Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you ... have to have a Full Database Backup (containing all the Filegroups including ... Even if you have a performed a filegroup Backup which contains the PRIMARY ...
    (microsoft.public.sqlserver.server)
  • Re: Question about restore database and create new filegroups
    ... RESTORE won't help you here, since a restore database will be an image of the source database. ... For a table which doesn't have a clustered index, you just create a clustered on the desired filegroup and then drop that clustered index. ... I want to create separated filegroups and data files to re-organize ...
    (microsoft.public.sqlserver.server)
  • Re: VLDB backups and partial restore
    ... if you do filegroup backups of the ... >> particular filegroups that you care about, you can restore only those ... > CREATE DATABASE mytest ... > WITH RECOVERY ...
    (microsoft.public.sqlserver.server)