Cant It be Done?? Backup mdf and ldf only

20 Jan 2006 12:04 AM

Sean John

Can you backup database that has .mdf, .ldf, and .ndf and restore only
.mdf and .ldf? Reason is if you have a large table thats part of a
filegroup and you want to restore database to another sever and not the
filegroup(.ndf)?If so how and example syntax would be great!TIA

*** Sent via Developersdex http://www.developersdex.com ***

20 Jan 2006 8:05 AM

Razvan Socol

Hi, Sean

You can restore only a filegroup using the FILEGROUP clause and the
PARTIAL clause of the RESTORE statement, like this:

RESTORE DATABASE Test FILEGROUP='PRIMARY' FROM DISK='C:\Test.backup'
WITH PARTIAL

Here is a full example:

CREATE DATABASE Test
ALTER DATABASE Test SET RECOVERY FULL
ALTER DATABASE Test ADD FILEGROUP Secondary
ALTER DATABASE Test ADD FILE (NAME=SecondaryFile, FILENAME=
'C:\Program Files\Microsoft SQL Server\MSSQL\Data\Test_Secondary.NDF'
) TO FILEGROUP Secondary
GO
USE Test
CREATE TABLE SmallTable (X int PRIMARY KEY) ON [PRIMARY]
CREATE TABLE BigTable (Y int PRIMARY KEY) ON Secondary
GO
INSERT INTO SmallTable VALUES (1)
INSERT INTO BigTable VALUES (10)
INSERT INTO BigTable VALUES (20)
INSERT INTO BigTable VALUES (30)
GO
BACKUP DATABASE Test TO DISK='C:\Test.backup'
BACKUP LOG Test TO DISK='C:\Test.tran'
GO
USE master
DROP DATABASE Test
GO
RESTORE DATABASE Test FILEGROUP='PRIMARY' FROM DISK='C:\Test.backup'
WITH PARTIAL
GO
USE Test
SELECT * FROM SmallTable
GO
--SELECT * FROM BigTable
/* The above statement won't work, you will get:
Msg 8653, Level 16, State 1, Line 1
Warning: The query processor is unable to produce a plan because the
table 'BigTable' is marked OFFLINE.
*/
GO
USE master
DROP DATABASE Test

For more informations, see:
http://msdn.microsoft.com/library/en-us/adminsql/ad_bkprst_71f7.asp
http://support.microsoft.com/default.aspx?scid=kb;EN-US;Q281122
http://msdn2.microsoft.com/ms177425.aspx

Razvan

20 Jan 2006 3:22 PM

Sean John

Razvan Socol You are the MAN!

*** Sent via Developersdex http://www.developersdex.com ***