How to restore transactions after full backup

I am moving my SQL Server database to a new windows machine, however, I do not want to lose any transactions during this process. I have been unable to find anything around this anywhere which this seems like it would be common, so any help would be appreciated.

I have a production server and a staging server. My production server I would like to retire, so I have created a full database backup and restored it on my staging server. I would like to point the production endpoint to the staging server to it becomes production. This works great, however, any transaction between me creating the full database backup up until the switching of the production endpoint would be lost. How can I get those transactions over to the new production server without having any downtime?

My thought was to import the full database backup on the staging server, and then point production to that server, and then use transaction logs to update the new server, however, after restoring the full backup, the restore transaction logs is grayed out.

I've seen posts suggesting using NO RECOVERY when restoring the full backup, however, this puts the database in an inaccessible state which would cause downtime.

Yes you are going to need at least some downtime if you are using Backup/Restore to migrate.

One option to minimize downtime is as follows:

• Take a FULL backup of the database. Keep it online.

• Restore WITH NORECOVERY onto the destination server.

• Assuming that took a long time then you might want to do an intermediate step:

• Take a LOG backup of the source DB.

• Restore that also WITH NORECOVERY.

• Finally, take the server out of public access and/or put the source DB into single-user mode, and do a tail-log backup. This will place it into RESTORING mode which means no queries can be run on it. The backup should be very fast, especially if you did the intermediate step.

• Restore this onto the destination server using WITH RECOVERY to bring it online.

• Redirect clients to the new server and you are back in business.

Even on a relatively busy OLTP database, the size of the final tail-log is likely very small, therefore quick to create, quick to transfer and quick to restore.

Do not forget server-level data, such as logins, DDL triggers, certificates, and the SQL Server Agent database msdb. You can move this over separately though, before migrating your main database.

If you absolutely must not have any downtime at all, then be prepared to pay big bucks to get Availability Groups set up correctly with synchronous commit. Then you can just fail it over. But it's rare that this is actually required by the business.

StackExchange Database Administrators Q#329380, answer score: 9