We have an older SQL Server Database that is about 1TB.
We now have a new database that has a slightly different schema. We are going to migrate the 1TB of data to the new db using the export tool in SSMS.
However to make it go faster, I am thinking maybe there is a way to disable unnecessary logging and backup during the migration?
We won't need to recover to a specific time state during the migration anyway and we have the original database.
Could I set the log size to something like 100MB initially, do the migration and then change it back to unlimited again? Would it be viable to use SIMPLE recovery during the migration and then afterwards change it to FULL? I'm not sure what this tool uses to migrate data internally, probably bulk inserts.
Just for clarity, in SSMS, we will right click the database > Tasks > Export Data. This allows you to map columns from the source database to the target database. So, this is not a "restore" in that sense. We need to copy from one db to another. The problem is that the new db has other column names and in some cases other datatypes. However this tool converts and truncates correctly.
SIMPLE
recovery mode, so that the log is automatically truncated once the operation(s) are completed. It'll still grow as much as it needs up to that point though (so batching is also important if you need to keep the log small). Otherwise, if you are using things likeBULK INSERT
, such operations are minimally logged. – Larnu Nov 5 '20 at 13:46FULL
toSIMPLE
for large migration work, and then back toFULL
(and reinitialising the backup sequence) is a common practice, @kdi, yes. – Larnu Nov 5 '20 at 13:52