Re: PARTIAL SQL Server RESTORE's

From: Tibor Karaszi (tibor_please.no.email_karaszi_at_hotmail.nomail.com)
Date: 06/09/04 

Date: Wed, 9 Jun 2004 12:32:14 +0200

Paul,

I agree with your points, and this is how I understand that things work as well. Also, I just ran a test,
which also confirms it (see script at below, adapted from example in BOL). Did you perform the Books Online
feedback on this (the envelope top left in the right pane)?

USE master
GO
DROP DATABASE mywind_part
GO
DROP DATABASE mywind
GO
CREATE DATABASE mywind
GO

ALTER DATABASE mywind ADD FILEGROUP new_customers
ALTER DATABASE mywind ADD FILEGROUP sales
GO

ALTER DATABASE mywind ADD FILE
   (NAME='mywind_data_1',
   FILENAME='c:\mw.dat1')
   TO FILEGROUP new_customers
ALTER DATABASE mywind
   ADD FILE
   (NAME='mywind_data_2',
   FILENAME='c:\mw.dat2')
   TO FILEGROUP sales
GO

CREATE TABLE mywind..t2 (id int) ON sales

-- A full database backup is performed.
-- Then the t1 table is created on new_customers.
-- The transaction log is backed up:

BACKUP DATABASE mywind
filegroup = 'sales', filegroup='primary', filegroup = 'new_customers'
   TO DISK ='c:\mywind.dmp'
   WITH INIT
GO

USE mywind
GO

CREATE TABLE t1 (id int) ON new_customers
GO

BACKUP LOG mywind TO DISK='c:\mywind.dmp'
WITH NOINIT
GO

-- At some point, it becomes necessary to restore the t2 table
-- on the sales filegroup. RESTORE FILELISTONLY lists the database
-- files and the filegroups in which they reside.
-- RESTORE HEADERONLY lists the contents of the backup medium:

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

-- The RESTORE DATABASE statement restores the database under a different name
-- and the sales filegroup using the WITH PARTIAL and NORECOVERY options.
-- In addition, the primary file and filegroup (mywind), the log (mywind_log),
-- and all files in the restored filegroup (in this example, mywind_data_2 is the
-- only file in sales) are moved to a new location. The log is then recovered:

RESTORE DATABASE mywind_part
   FILEGROUP = 'sales'
   FROM DISK='c:\mywind.dmp'
   WITH FILE=1,RECOVERY,PARTIAL,
-- WITH FILE=1,NORECOVERY,PARTIAL,
   MOVE 'mywind' TO 'c:\mw2.pri',
   MOVE 'mywind_log' TO 'c:\mw2.log',
   MOVE 'mywind_data_2' TO 'c:\mw2.dat2'
GO

-- RESTORE LOG mywind_part
-- FROM DISK = 'c:\mywind.dmp'
-- WITH FILE = 2,RECOVERY
-- GO

--Notice that t2 is accessible after the partial restore operation.
SELECT COUNT(*) FROM mywind_part..t2

--Notice that t1 is not accessible after the partial log restore operation.
SELECT COUNT(*) FROM mywind_part..t1 

-- 
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Paul McMillan" <paul.mcmillan@email4u.com> wrote in message news:%23xZPXcXTEHA.2716@tk2msftngp13.phx.gbl...
> Please..
>
> Can someone confirm that with a PARTIAL FILEGROUP SQL Server RESTORE you
> have to have a Full Database Backup (containing all the Filegroups including
> the PRIMARY) before you can do a PARTIAL filegroup RESTORE.
>
> Even if you have a performed a filegroup Backup which contains the PRIMARY
> filegroup and all the other filegroups then it will not work and you get the
> error message:
>
> Server: Msg 3135, Level 16, State 2, Line 1
> "was created by BACKUP DATABASE...FILE=<name> and cannot be used for this
> restore operation."
>
>
> All the examples I have seen on the net use a FULL Database backup as a
> starting point and I have seen the following posting which kind of confirms
> my suspisions from someone with a microsoft.com email address
>
>
http://groups.google.com/groups?q=partial+sql+server+restore+full+backup&start=20&hl=en&lr=&ie=UTF-8&selm=eJxAxlwzCHA.1576%40TK2MSFTNGP12&rnum=25
>
> Furtheremore, all the documentation in BOL is very very murky on this
> particular subject area:
>
> Partial Database Restore Operations
> "Partial restore operations work with database filegroups. The primary
> filegroup is always restored, along with the files that you specify and
> their corresponding filegroups. The result is a subset of the database.
> Filegroups that are not restored are marked as offline and are not
> accessible.
>
> Partial restore operations are accomplished with the PARTIAL clause of the
> RESTORE statement. You can also use the PARTIAL option when restoring a full
> database backup. Partial database restore of file backups is not supported."
>
> What it says is "Partial database restore of file backups is not supported"
> I think it should ALSO say "Partial database restore of filegroup and file
> backups is not supported"
>
> Many Thanks
>
> Paul McMillan
>
>

Relevant Pages

  • Backup and Restore Files
    ... What is the best way to backup and restore a runtime database file? ... pszDisplayName As String ...
    (microsoft.public.access.gettingstarted)
  • RE: How do I restore from mdf and ldf files?
    ... Tasks -> Backup Database ... Tasks -> Restore Database ... Windows 2003 Server with Latest Service Pack ... Pre-requisites for Sharepoint Backup and Restore: ...
    (microsoft.public.sharepoint.portalserver)
  • Re: POINT IN TIME RESTORE
    ... This is what you should have done in order to do the restore as you wish: ... > 3- erase data ... > 5- restore database with norecovery ... The BACKUP DATABAE can of course be at an earlier point in time, ...
    (microsoft.public.sqlserver.server)
  • RE: How to restore edb & stm files from bkf backup file on another exc
    ... copied to a different storage group on the same server or to a different ... On production server successfully backup information ... Restore Status ... Failed to find a database to restore to from the Microsoft ...
    (microsoft.public.exchange.admin)
  • Re: Transaction Log ??
    ... we used to simplify our database restore process: ... every database got their respective backup folder. ... -- get logFileName, FirstLSN, LastLSN list ...
    (microsoft.public.sqlserver.security)