Restoring only the data files of a backup

matt.stanford

I'm trying to find out if it is possible to restore only the data files from a full database backup. My issue is that on our production servers I have the transaction logs blown out to a fairly large size (60 GB in some cases), and I feed our reporting systems off of full backups of production. I don't care at all about having a 60 GB log file on our reporting system, but it is very inconvenient to have to maintain an extra 60 GB for a transaction log that I don't care about.

Ideally, I'd like to just restore the data files and just tell SQL server to rebuild the log (like you can do with an attach statement). I know this may cause inconsistencies but this isn't used for DR, just for feeding our ancillary systems.

Thanks,
-Matt

Eddie D

Thank you for your post into forum.

Unfortunately you cannot restore just the data files when restoring from a full database backup.

If you wish wish to reduce the size of the transaction log after you restore the full backup. I recommend that you truncate the transaction log followed by shrink file command.

The following SQL Server Books On Line articles contain further information on truncating the transaction log and running the Shrink File commands:

Truncating the Transaction Log

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/8105239c-b657-48c1-9991-17159081b280.htm

Shrink File Command

ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/udb9/html/ce5c8798-c039-4ab2-81e7-90a8d688b893.htm
ms-help://MS.SQLCC.v9/MS.SQLSVR.v9.en/tsqlref9/html/e02b2318-bee9-4d84-a61f-2fddcf268c9f.htm

I hope the above helps.

Many Thanks
Eddie