Uploading database to local SQL Server creates big transaction log files

Mar 4, 2013 at 4:10 PM
Hi,

I'm using SQL Database Migration Wizard to migrate big (> 100 GB) databases from cloud to local SQL Server. I've noticed that the newly created databased are set to "Full" recovery model. This results into taking the same (or even bigger) amount of disk space for transaction log file as for the data file itself.

By creating the database and switching it to "Bulk-Logged" recovery mode while running SQLAMW it should be able to save all that space for transaction log, because bulk copy operations won't be logged (http://msdn.microsoft.com/en-us/library/aa173531(v=sql.80).aspx). Then after finishing all the bulk copy operations it can be switched to "Full" recovery mode for save usage in production.

Is there some switch option in application which allows to do me something like this or is there plan to add this feature to application?

Thanks, Marian
Coordinator
Mar 4, 2013 at 7:53 PM
Hi Marian,

Very good point and no, I don’t have anything in the program to do what you are talking about. But I like the idea so I will see about putting something in. That said, you can do what you want by modifying the script. You know when you first export your database, a script will be generated. The script is on the “SQL Script” tab on the “Results Summary” page. What you can do is modify this script. At the top of the script, add the following two lines:

ALTER DATABASE [TargetDB] SET RECOVERY SIMPLE WITH NO_WAIT
GO

Where TargetDB is the name of your target database. Then hit next, create your database and run against your database. Once all of your data is uploaded, you can do one of two things:

1) Hit the back button until you get to the “Results Summary” page and delete all of the TSQL from the “SQL Script” tab. Then add the following two lines:

ALTER DATABASE [TargetDB] SET RECOVERY FULL WITH NO_WAIT
GO

Hit next, select your database and run the script against your database.

2) Your second option is to just use SQL Server Management Studio after SQLAzureMW has completed the upload of the data and switch your target database back to recover model = FULL or run the ALTER database from the query window.

I like your idea. I will have to think on the best way to add it.

I hope this helps for now.

Regards,
George