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 ***

Backingng Up a Database that is Attached

Hi,

I was trying to figure out how to back up a database that has been attached to the SQL Express instance. I found on the web this tutorial and tried to use it, however it seems like it cannot find the database. The DB is attached by the web application using AttachDbFilename in the connectionstring.

http://www.sqldbatips.com/showarticle.asp?ID=27

Any ideas?

thanks

If you are doing personal development you download the eval version and install it as a named instance and register the Express so you can manage it with Management Studio. It is good for 180 days but if you are developing full application buy the developer edition it cost $60 on the web and do the same. And one more thing the database is not attached to Express it is created in Express which is a full RDBMS(relational database management system) without the GUI tools. Hope this helps.

http://www.microsoft.com/sql/downloads/trial-software.mspx

|||

Hi,

I actually do have the the developer edition, and when I used the management studio to connect to SQL Express server (on a different machine) I was still unable to see/view this database thats was attached by the web app. I know the database was attached, because the web app was functioning properly and displaying/inserting data in the database.

|||If you have the developer edition you just right click at the top of management studio to register the Express and the Express becomes local to you. Then you use the backup and restore wizard to backup your databases in the Express. I have registered 68 SQL Server 2000/7.0 in one box. If you have more questions post again. Hope this helps.|||

Hi,

The problem is I do not see this databse in the pulldown in the wizard. I see all the other databses on the server, but none of the databses that the web app atached through AttachDbFilename.

Am I missing something?

tony

|||Well I think it is because you used just half of the database which is the data file, so check the Microsoft SQL Server folder under programs and check the data sub folder to see if the LDF(log data file) is there. If not create a blank database with same name and do INSERT INTO to recreate the database and delete the existing ones of all the attached databases because you need the log file and you also need to backup your databases. And no you are not missing anything Microsoft is using RDBMS(relational database management system) as file based database. So the SQL Server wizards may not be seeing all the attached databases because they are missing the log file. Another option is to right click on the database to see if backup is enabled. Hope this helps.

Backing up to a Tape Drive

I am setting up a new SQL Server that has an external HP 920 SAS Ultrium tape drive attached to it via a p400 sas card.

I am running 2003 R2 x64 with 2005 SQL Server x64, and I am trying to set up my DB backup plan but for some reason I can't get SQL to recognize my tape drive, alhough it is listed in device manager.

Anyone have any thoughts? I'm kind of stumped.

Thanks in advance,

Mark.

What do you mean SQL Server doesn't recognise it?

It's not listed in the dropdown when you try to create a new backup device in SQL Server Management Studio.|||

Sorry ... Yes when trying to add it as a new backup device in Management Studio.

Also I have SP2 installed.

|||

Not sure - maybe there's a problem with the compatibility of the drive. I don't know if there might be a problem if the drive wasn't attached and switched on when SQL Server started up.

It is worth trying to run the SQL

EXEC sp_addumpdevice 'tape', 'DumpToTape', '\\.\tape0'

Assuming that it is the first tape device in the machine.

Sorry you are running off the limit of my knowledge.

|||

I did that before I started the thread, but i must have done it incorrectly because when i ran it just now it created the device under backup devices and I am now all set.

Thanks for the help.

|||

Sorry , I have already added to DumpDevice. but The Error is represent.

would u tell me what to do ? it is SQL Server 2005 X64 Bug ?

thanks

Backing up to a Tape Drive

I am setting up a new SQL Server that has an external HP 920 SAS Ultrium tape drive attached to it via a p400 sas card.

I am running 2003 R2 x64 with 2005 SQL Server x64, and I am trying to set up my DB backup plan but for some reason I can't get SQL to recognize my tape drive, alhough it is listed in device manager.

Anyone have any thoughts? I'm kind of stumped.

Thanks in advance,

Mark.

What do you mean SQL Server doesn't recognise it?

It's not listed in the dropdown when you try to create a new backup device in SQL Server Management Studio.|||

Sorry ... Yes when trying to add it as a new backup device in Management Studio.

Also I have SP2 installed.

|||

Not sure - maybe there's a problem with the compatibility of the drive. I don't know if there might be a problem if the drive wasn't attached and switched on when SQL Server started up.

It is worth trying to run the SQL

EXEC sp_addumpdevice 'tape', 'DumpToTape', '\\.\tape0'

Assuming that it is the first tape device in the machine.

Sorry you are running off the limit of my knowledge.

|||

I did that before I started the thread, but i must have done it incorrectly because when i ran it just now it created the device under backup devices and I am now all set.

Thanks for the help.

|||

Sorry , I have already added to DumpDevice. but The Error is represent.

would u tell me what to do ? it is SQL Server 2005 X64 Bug ?

thanks