I want restore only MDF,what type backup needed

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

SQL Server 2000 Forums

SQL Server Administration (2000)

I want restore only MDF,what type backup needed

Author

Topic

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-31 : 14:54:35

Hi

I already asked couple of questions on Migration, to day my client asked me one question.

You’re taking full backup (using Ent mng), while restoring why your considering the LDF file.
Because Database having 20GB Trn file and 3GB Data file, If you restore entire file, I am losing 20GB space.
In fact i never came across the this type of situations I don't what to tell him
Some body helps me regarding this
Can I store MDF only or I have to restore both file what every in Database backup, if I want restore only MDF what type of back I have to take.
Recovery model of my database is "full"

Expecting help you guys

Thanks&Regards
SUSRI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-31 : 15:04:14

SQL Server will build all of the database files at the same size as the source database (from the backup). You can not restore only an MDF file. To avoid this issue, you could just detach the source database, copy only the MDF file, attach the source database and destination database. But this would require the source to be down.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-31 : 15:13:07

Hi

I am Taking backup of Prod server,Moving Datafiles we already adviced
but He is not agree

Can Change the recovery model of database into simple and then I can take backup of Database with trucate only,In this is it possible.

But still there is active transactionlog..what to do with that

I am going to say above way to reduce the space.
what you say,any suggestion.

Thanks&Regards
SUSRI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-31 : 15:15:46

I don't understand your last reply.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

sqldba_ind
Starting Member

22 Posts

Posted - 2007-10-31 : 15:38:26

Hi Tkizer

from you replay I understood that with out log file is possible to restore the Backup,
So shall I change the Recovery model of Database from FULL to SIMPLE and I will take Backup.
Sp now Trn file size reduce,this way I can save client Disk space.

Moving Datafile not possilbe for us,b'cause that Prodcution Server.

Thanks&Regards
SUSRI

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2007-10-31 : 15:41:05

Just restore the full backup, then you can shrink the transaction log file.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources