How can I import a 46GB database into MSSQL without ending up with a 46GB transaction log?

May 29, 2008 12:13 PM

I need to import a database into MS SQL but I do not want to end up with a transaction log as large as the database. What's the best way to do the import?

We're working with a software vendor to migrate their database into MS SQL. They set up an ODBC connection to their database and then use Enterprise Manager Data Transformation Services (DTS) packages to copy the tables into an MS SQL database.

However, at the end of their test pass we had a 46GB database file and a 46GB transaction log.

What's the best way to do this without creating such a giant log?

posted by odinsdream to computers & internet (4 comments total)

You could set the recovery mode to Simple while you do the DTS.
posted by Lyn Never at 1:00 PM on May 29

Seconding Lyn. This is what I do.
posted by SPrintF at 1:02 PM on May 29

Have you tried using the simple recovery model for the database you are importing into? I'm not 100% sure this will work, but in general the simple recovery mode does not increase the transaction log size. You can set the recovery model back to 'Full' after your import has completed.

To change the recovery model, right-click the database in SQL Enterprise Manager, and choose properties. It's on the 'options' page.

Of course, IANADBA, but it's worth a shot.

On preview... yeah.
posted by pipco at 1:07 PM on May 29

Thanks everyone!
posted by odinsdream at 1:32 PM on May 29