0

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.

Improve this question
5
  • You can't stop SQL Server writing to the transaction log, if that's what you're asking, but if you're doing large amount of DDL operations it can be advisable to change the database to 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 like BULK INSERT, such operations are minimally logged.
    – Larnu
    Nov 5, 2020 at 13:46
  • Moving a database from a recovery mode like FULL to SIMPLE for large migration work, and then back to FULL (and reinitialising the backup sequence) is a common practice, @kdi, yes.
    – Larnu
    Nov 5, 2020 at 13:52
  • ID column should be varchar? Well, it's your database. But obviously your approach requires doing the transformation somehow that is external to the export/import process. So why not just restore a backup to the new server instance and run your transformations there directly from old restored database to new database? If this is an ongoing process where the old database is still in use and undergoing changes, then you have much more significant issues to consider before attempting this.
    – SMor
    Nov 5, 2020 at 15:39
  • Yeah its a terrible schema and yeah, well its not really mine, I was forced to fix this issue caused over different consultants over 20 years time. Look, I have one database server. On this server, we have restored the old db and created a new db. We now need to take data from the old db, make transformations and then put them in the new db. I have sucessfully used this tool to move about half the db in 60 minutes or so so that is acceptable downtime for them apparently. The old database will be scraped and not used anymore.If there are better ways to do it, I am happy to test them out. :)
    – kdi
    Nov 5, 2020 at 15:46
  • Maybe we could just drop old invalid columns, rename them and change the datatypes on the old db?
    – kdi
    Nov 5, 2020 at 15:54

3 Answers 3

Reset to default
1

IMO, this type of work should be testable and repetable. I.e., scripted. I would go for backup/restore and then have a script that does your DDL changes (ALTER TABLE, ...). You script everything and test, re-test etc to know everything is fine.

Backup/restore time can be minimized by utilizing log backups.

As for the DDL changes, you will find out how long time they take. Depending on what you need to do, simple recovery model might be useful - but again, that depends on your DDL/DML in the end.

Improve this answer
0

In the database properties, I think you can just set the "Recovery model" to "Simple". After the migration, return the setting to "Full".

https://docs.microsoft.com/en-us/sql/relational-databases/backup-restore/recovery-models-sql-server?view=sql-server-ver15

Improve this answer
0

I think you should use those log instead of getting rid of them.

Have a log shipping set so that you can restore the database to your target server without any impact on the primary.

Then, once you catch up on the secondary, you take a tail of the log backup on your old database (should be quick) and you restore it to the target server and you'll have the DB migration done with very minimal impact and downtime.

If you do not have a lot of activity on the database, I don't think that shrinking the log will give you a significative gain on the time it will required.

Improve this answer
1
  • While this approach is a great way to migrate data to a new version or new server, this likely won't work in this instance because they are doing a transformation on the data between the old db and the new db.
    – Hannah Vernon ♦
    Nov 5, 2020 at 18:46

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service, privacy policy and cookie policy

Not the answer you're looking for? Browse other questions tagged or ask your own question.