How can we help you today? How can we help you today?

Restoring only the data files of a backup

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
matt.stanford
0

Comments

1 comment

Sort by
  • 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
    Eddie D
    0

Add comment

Please sign in to leave a comment.