Restoring only the data files of a backup

matt.stanfordmatt.stanford Posts: 11
edited January 19, 2009 9:21AM in SQL Backup Previous Versions
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

Comments

  • Eddie DEddie D Posts: 1,805 Rose Gold 5
    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 Davis
    Senior Product Support Engineer
    Redgate Software Ltd
    Email: support@red-gate.com
Sign In or Register to comment.