.NET Tutorials, Forums, Interview Questions And Answers
Welcome :Guest
Sign In
Win Surprise Gifts!!!

Top 5 Contributors of the Month
Gaurav Pal
Post New Web Links

Restore MDF file only from bak file

Posted By:      Posted Date: August 31, 2010    Points: 0   Category :Sql Server
Hello Friends i want to know that whether there is any provision in SQL Server 2008 to restore MDF file only from the bak file excluding the log file. i just want to know because, the bak file which i have is close to 4-5 GB, but after specifying the details while restoring the database, then it is consuming close to 80 GB as 30GB for the MDF file or 50 GB for log file. But say for once, the total hard disk is 80 GB. all the given figures are approximate except for Hard Disk size. in that case, i only want to extract the MDF file while excluding the LOG file for the database to attach. Looking for favorable replies.

View Complete Post

More Related Resource Links

attempting to restore to a new DB and it keeps trying to save the mdf file as the original mdf file

This is using SQL Server 2005. I've had several problems and this is where I've ended up. 1. Despite the claim that this will create a new DB it doesn't.  I was getting errors about the new DB not having the specified logical file name (despite the new DB not existing).  At the time I was specifying the logical file name as it appeared in the backup. 2. if I manually create the DB I still get the error as above unless I manually change the logical file name via sql management studio.  This is meant to be an automated solution so that is not accceptable. 3. I am now giving it the logical file name of the DB that I want to restore onto (if it doesn't exist I create it), but now it's trying to save the file with the old physical file name, which is absolutely *not* what I want. My instincts tell me I need to change the logical file name in the database I'm wanting to restore onto, but I don't know how to do this via SMO.  All of the explanations I've seen are through the SQL Management Studio and that is not acceptable as this needs to be an automated solution. I have copy/pasted the code from the msdn examples verbatim changing only the dbName and the .bak file location and it has consistently failed. Here is the code in it's current incarnation. var serverName = @"myServer"; var dbName = "MIQDesignTest3"; var backupFileN

Error in RESTORE in LOG FILE. Anyway to exclude it?

I'm trying to install a database (on a SQL Server 2008 machine) with test data sent by a partner company in the form of a .BAK file. (Version of SQL Server they are using unknown). I'm using this script: Use Master RESTORE DATABASE XEMS FROM DISK = 'C:\temp\xemstest6-29.bak' WITH REPLACE, MOVE 'XEMS' TO 'E:\XEMS DATABASE\XEMS.MDF', MOVE 'XEMS_Log' TO 'E:\XEMS DATABASE\XEMS_Log.LDF' The Restore process churns for a while, then I get: Processed 2576 pages for database 'XEMS', file 'XEMS' on file 1. Processed 5 pages for database 'XEMS', file 'XEMS_log' on file 1. Msg 3283, Level 16, State 1, Line 2 The file "XEMS_log" failed to initialize correctly. Examine the error logs for more details. Msg 3013, Level 16, State 1, Line 2 RESTORE DATABASE is terminating abnormally. At this point the database is unusable - trying to do anything with it produces an error indicating the database is still in a Restore state. There is some problem with the LOG portion of the backup, which I really don't need anyway. I'd like to just NOT restore the log. Came across this article which seemed to refer to the exact problem I'm having...  http://support.microsoft.com/kb/915385/en-us They offered this suggestion: "Use the WITH NO_LOG clause during the restore process." However - in checking the RESTORE syntax.... http://msdn.microsoft.com/en-us/libr

Can't restore database backup file in my database ? using sql server 2005. please help

VERY IMPORTANT i am trying to restore database.bak in sql server 2005 (i know the database.bak was also generated in sql 2005 server) i am trying to restore back up database .bak into the new database i just created in sql server 2005 i have saved my database .bak into c drive and when i select database .bak "From Device", it doesn't get populated in the list below and i see nothing and it keeps on prompting a message "You must select a restore source" Here's the screen shot:   PLEASE HELP..it's really important (i tried restoring database in sql server 2008 and it was sucessful but i am facing this problem in sql server 2005 only)  

How to restore database from .bak file /

I have a .bak database file and i want to restore it to a new database so that i can get the data inside the .bak fileI am using SSMS 2008 Expresswhat i did :I created a database with the same name as of my .bak file (in my case "RM")now after creating a new database > righclick  on database > tasks > restoreSelected FROM : RM ( empty database that i just created)Selected TO : RM.bak (my backup file)when i press "Ok"i get this error:

TDE disabled and service restarted but still can't restore a new backup file

Hello, I disabled TDE on my dev database because we determined no personally identifiable info will be stored.  Disabling was done like so:Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using SSMS) and I get  the following error: ALTER DATABASE MyDB SET ENCRYPTION OFF GO use MyDB go drop database encryption key go use master go DROP certificate DevCert GO DROP MASTER KEY GO Afterwards, I ran the following query to ensure there are no encrypted databases, and it returns zero rows: SELECT DB_NAME(database_id) AS dbname, encryption_state, case encryption_state   WHEN 0 THEN 'Unencrypted (no database encryption key present)'   WHEN 1 THEN 'Unencrypted'   WHEN 2 THEN 'Encryption in Progress'   WHEN 3 THEN 'Encrypted'   WHEN 4 THEN 'Key Change in Progress'   WHEN 5 THEN 'Decryption in Progress'   ELSE CAST(encryption_state AS varchar(20)) END AS encryption_state, key_algorithm, key_length FROM sys.dm_database_encryption_keys Then I created a backup of MyDB database and tried to restore on a different server instance on my same machine (using the SSMS) and I get the following fatal error: The media family on device 'c:\Program Files\...\Backup\TDE_removed_from_db.bak' is incorrectly formed.  SQL Server cannot process this media family. 

Need to restore MDF or LDF file from BAK without creating database.

I need to restore the actual MDF or LDF files within a BAK file without creating/restoring to another database. I just need the physical MDF or LDF files only. Is there a way to do this?
What is not explicitly allowed should be implicitly denied

File not found error after restore the sitecollection



I backed up a MOSS sitecollection using "stsadm -o backup" and then restored the same file in the other machine/server. No problems while creating backup and doing restores . But after restore is successfull and try to open the site ..it is giving me an error "File not found".Any idea?

Restore database from file and reduce datafile while restoring


SQL 2005 Standard.

I want to restore a database from a .bak file. This bak file is referred to a database with fixed size datafile but they are bigger than I need for the new database.

Is there a way to reduce datafile size, during restore process ? I ask this because I will have to do this operation programmatically.


Multiple databases backup/restore in a transaction, if possible, into only one file


Hi dear,

I have three databases named XDW, XOLTP, XOLTPSchema. All of these are for only one application. In the application, user should do backup/restore using GUI.


1. How I should do backup/restore in one transaction? i.e. all of three actions should be success elsewhere all of them should fail.

2. Is it possible to backup them into only one file e.g. X.bak?

3. Or, What is the standard solution in this condition?

Thanks in advance.

How To: Joining multiple DataTable using “LINQ to DataSet”

Document Library version restore not behaving as expected on the "Name" column (file name)


After a rollback to a previous version, the value of the column "Name" in a document library is not restored. All other columns are restored properly. It seems that the column "Name" (file name) is excluded from the process of versioning.

I didn't find information on this behaviour on the internet. Any suggestions ?


Possible to restore deployed Reports from SSRS/Reportserver DB to a .rdl file?

I mistakenly deleted some files from a Business Intelligence Development Studio project, however they are deployed on the server. Is it possible to restore these .rdl files in some way from the deployed copies? I seem to remember doing this on a previous version but no longer see this option.

Thanks in advance.

Restore SharePoint_config from .bak file


I can restore all the data base to new SQL except SharePoint_config.

Currently I am getting this error on restoring SharePoint_Config:

Msg 3159, Level 16, State 1, Line 2

The tail of the log for the database "SharePoint_Config" has not been backed up. Use BACKUP LOG WITH NORECOVERY to backup the log if it contains work you do not want to lose. Use the WITH REPLACE or WITH STOPAT clause of the RESTORE statement to just overwrite the contents of the log.

Msg 3013, Level 16, State 1, Line 2

RESTORE DATABASE is terminating abnormally.



I cannot delete the data base from that SharePoint_config and I get:

Restore database from system dbs and log file when .mdf file size set to 0KB after Windows Update ?


This can't be right. Friday I tried to install some Windows updates including ‘Security Update for …Windows Server 2008 for x64-based Systems (KB2416473)’ on both my host Server 2008 box and on a VM instance of Server 2008 running on the same physical box. An important SQL Server 2008 instance lives on that VM.


The above mentioned update failed on both the host and the VM, but a system shutdown and reboot were required for both OS’s anyway. When SQL Server started up again on the VM, the *.MDF file size for 2 (out of maybe 15 total) databases had been reduced to 0KB. Additionally, 1 of accompanying *.LDF files was now also 0KB in size, while the other *.LDF file was probably its normal size of 2.9GB. All the other databases and log files, including Master.mdf, seem to be okay.


I’m new to SQL Server and have a couple of questions of cours

Backup/Restore 404 File not found error ;Site site created besed on Custom Site Template




I am using SharePoint (Version: 12.0.6318.5000). The scenario is like that;

  • I have created a Site Collection and under that I have 2 sub sites and under each sub sites again I have a sub site (Site Collection [level 1]>>>SubSite+Lists [level 2]>>>SubSite+ Lists [level 3]>>>Lists, Doc. Library and Picture Library

Restore file by old name

A person had renamed a file and set up a new folder to store the file, and I wanted to see if there is a way to restore the original file that appears to no longer exist.  It was done just last week (Thursday or Friday), so recent if that helps.  I assume person deleted and did not move as I look at Version history it does not have the history.  they downlaoded, change the file, changed the name, and then uploaded in new folder.  Can I restore an old folder?  Or file that does not have same name anymore to existing files out there? 

SQL server2008, CRM4.0, unable to read sql backup file when trying to restore



For a client of ours we have two CRM4.0 environments, both on a seperate domain. Domain A and Domain B.
Domain A is used for testing and developing pursoses, Domain B is the production environment.

The client has requested that we match the Domain A environment with the Domain B environment so that the testing and production evironments are the same before they continue with testing/development.

For this purpose I wanted to just copy the Domain A SQL database to the Domain B environment.

Unfortunately I am getting the following error when trying to restore the CRM4.0 SQL database of Domain A on the CRM4.0 SQL database server of Domain B.

System.Data.SqlClient.SqlError: Read on "C:\Users\Cen-Admin\Desktop\VWPCRM_MSCRM.bak" failed: 38(failed to retrieve text for this error. Reason: 15105) (Microsoft.SqlServer.Smo)

I have tried to follow these instructions but have been unable to complete them because of the fact that i cannot initiate the restore action:


The link that the error provides in the detailed desciption field provides no qlue to a solution:

File Restore after Site Collection Recycle bin




Is there any way to restore the file after get deleted from Site Collection Recycle bin like restoring from Data base. I have details of file like file name, deleted by, path of the file.





ASP.NetWindows Application  .NET Framework  C#  VB.Net  ADO.Net  
Sql Server  SharePoint  Silverlight  Others  All   

Hall of Fame    Twitter   Terms of Service    Privacy Policy    Contact Us    Archives   Tell A Friend