Backup Contains .mdf .ldf and .ndf dont want .ndf

Hello,
I have a database(.mdf and .ldf) with a large table that is attached to
its own filegroup(.ndf). I have created a full database backup and used
Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
I see 3 files
contact.mdf
contact.ldf
cont.ndf
I restored just the .mdf and .ldf to another database and when I look at
the properties and goto file groups it shows that the filegroup is still
there too. How is the ndf a part of this database when I never restored
the .ndf
I wan to restore only .mdf and .ldf and leave the .ndf behind is this
possible and how. Thanks for all HELP!
*** Sent via Developersdex http://www.examnotes.net ***First, if you don't supply a "WITH MOVE" clause for the .ndf file, it will
use the original file path from where the backup was created. Second, if
you do a full database restore, you must restore all the files. You cannot
omit any files. You can delete the file/filegroup after the restore is
completed.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:eoNsFaTHGHA.2064@.TK2MSFTNGP09.phx.gbl...
> Hello,
> I have a database(.mdf and .ldf) with a large table that is attached to
> its own filegroup(.ndf). I have created a full database backup and used
> Restore FilelistOnly From Disk = 'C:\MSSQL\Data\Backup\Contact.bak'
> I see 3 files
> contact.mdf
> contact.ldf
> cont.ndf
> I restored just the .mdf and .ldf to another database and when I look at
> the properties and goto file groups it shows that the filegroup is still
> there too. How is the ndf a part of this database when I never restored
> the .ndf
> I wan to restore only .mdf and .ldf and leave the .ndf behind is this
> possible and how. Thanks for all HELP!
>
>
> *** Sent via Developersdex http://www.examnotes.net ***|||When trying to delete file it says that
The file 'Contact'cannot be removed because it is not empty.
Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
and no .ndf?
*** Sent via Developersdex http://www.examnotes.net ***|||Have a look in BOL under DBCC SHRINKFILE specifically the EMPTYFILE option
and then ALTER DATABASE specifically the REMOVE FILE option.
Andrew J. Kelly SQL MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***|||You have to delete all user objects from the file.filegroup. Then folow
Andrew's instructions.
Backups and restores always require a full database to work with. You can
do a filegroup backup, but it must be restored to a full database. The
short answer is no, you cannot transfer part of a database using backup and
restore. The tools simply do not work that way.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Sean John" <sj@.aol.com> wrote in message
news:O3$1kXUHGHA.1424@.TK2MSFTNGP12.phx.gbl...
> When trying to delete file it says that
> The file 'Contact'cannot be removed because it is not empty.
> Is there a way to backup only .mdf or .ldf or restore just .mdf or .ldf
> and no .ndf?
>
> *** Sent via Developersdex http://www.examnotes.net ***

backup 300G DB error

I have a problem with backing up 300G DB. It is SQL Server 2000, the
MDF(50MBs) is in C drive, one NDF(33,000MBs) and one LDF (21G) are in F
drive. The index files are in F drive too. The backup mode is "Full".
When I tried to back it up in Enterprise Manager( right click on
database -> All tasks ->backup database), after 10 minutes of running, I
got the following error:
---
Microsoft SQL-DMO (ODBC SQL State: 42000)
Error 601: Could not continue scan with NOLOCK due to data movement.
---
Then I hit 'OK' and this one pops up
---
Microsoft SQL-DMO (ODBC SQL State:HY000)
Attempt to fetch logical page (1:5439) in database 'msdb' belongs to object
'sysdtspackages', not to object 'backupmediafamily'
---
Can anyone help me out?
Thanks a million.Hi
Look like you have corruption in MSDB. Run DBCC CHECKDB on the database.
--
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jluo" <jluost1@.yahoo.com> wrote in message
news:41C9D1B9.8060802@.yahoo.com...
> I have a problem with backing up 300G DB. It is SQL Server 2000, the
> MDF(50MBs) is in C drive, one NDF(33,000MBs) and one LDF (21G) are in F
> drive. The index files are in F drive too. The backup mode is "Full".
> When I tried to back it up in Enterprise Manager( right click on
> database -> All tasks ->backup database), after 10 minutes of running, I
> got the following error:
> ---
> Microsoft SQL-DMO (ODBC SQL State: 42000)
> Error 601: Could not continue scan with NOLOCK due to data movement.
> ---
> Then I hit 'OK' and this one pops up
> ---
> Microsoft SQL-DMO (ODBC SQL State:HY000)
> Attempt to fetch logical page (1:5439) in database 'msdb' belongs to
object
> 'sysdtspackages', not to object 'backupmediafamily'
> ---
> Can anyone help me out?
> Thanks a million.
>

backup 300G DB error

I have a problem with backing up 300G DB. It is SQL Server 2000, the
MDF(50MBs) is in C drive, one NDF(33,000MBs) and one LDF (21G) are in F
drive. The index files are in F drive too. The backup mode is "Full".
When I tried to back it up in Enterprise Manager( right click on
database -> All tasks ->backup database), after 10 minutes of running, I
got the following error:
Microsoft SQL-DMO (ODBC SQL State: 42000)
Error 601: Could not continue scan with NOLOCK due to data movement.
Then I hit 'OK' and this one pops up
Microsoft SQL-DMO (ODBC SQL State:HY000)
Attempt to fetch logical page (1:5439) in database 'msdb' belongs to object
'sysdtspackages', not to object 'backupmediafamily'
Can anyone help me out?
Thanks a million.
Hi
Look like you have corruption in MSDB. Run DBCC CHECKDB on the database.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Jluo" <jluost1@.yahoo.com> wrote in message
news:41C9D1B9.8060802@.yahoo.com...
> I have a problem with backing up 300G DB. It is SQL Server 2000, the
> MDF(50MBs) is in C drive, one NDF(33,000MBs) and one LDF (21G) are in F
> drive. The index files are in F drive too. The backup mode is "Full".
> When I tried to back it up in Enterprise Manager( right click on
> database -> All tasks ->backup database), after 10 minutes of running, I
> got the following error:
> Microsoft SQL-DMO (ODBC SQL State: 42000)
> Error 601: Could not continue scan with NOLOCK due to data movement.
> Then I hit 'OK' and this one pops up
> Microsoft SQL-DMO (ODBC SQL State:HY000)
> Attempt to fetch logical page (1:5439) in database 'msdb' belongs to
object
> 'sysdtspackages', not to object 'backupmediafamily'
> Can anyone help me out?
> Thanks a million.
>