5

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.

Improve this question
6
  • You can script the tasks to backup and restore the DB to minimize the time taken, but this is pretty much a process that will require a window of downtime, during which you'll also need to repoint your application(s) to connect to the new server - not to mention remembering to create the necessary logins and correctly apply the SIDs from the old server. If you need to keep connections active on the old DB you'll probably have to look at capturing changed and new rows
    – sTTu
    Commented Jul 17, 2023 at 20:52
  • You can also use Log Shipping or an Availability Group to minimize downtime. A Read-Scale AG does not require a cluster. learn.microsoft.com/en-us/sql/database-engine/…
    – David Browne - Microsoft
    Commented Jul 17, 2023 at 20:55
  • Alright, so it is sounding like this isn't possible. So I would essentially need to have a downtime window, take the production database offline, create a backup, and the restore it on the staging server which get's promoted to production?
    – walyzfan1
    Commented Jul 17, 2023 at 21:00
  • If it is mission critical you could turn cdc on the production tables to capture transactions while the backup is happening. The LSN for each transaction is stored so you could obtain the lsn of the backup using the fn_dblog() function, and then find all the data in the cdc tables written after that point in time. This is rather tricky and you would have to learn how to use those tables. It may be more practical to simply declare a maintenance window allowing you to transition to the new database instance with no new writes in the old system.
    – Ross Bush
    Commented Jul 17, 2023 at 21:03
  • A downtime window is the more typical method but you have some options as mentioned; you could put the database into read-only mode while you transition, even SO goes read-only from time to time for maintenance, the apps are written to handle it gracefully though.
    – sTTu
    Commented Jul 17, 2023 at 21:04

1 Answer 1

Reset to default
9

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.

Improve this answer
4
  • "Finally, take the source DB offline, and do a tail-log backup." Should be read as: run tail-log backup, it will put source database to restoring (not available for read or write) mode. Do not set source database offline.
    – SergeyA
    Commented Jul 18, 2023 at 19:24
  • I actually meant taking the server offline (firewalling it so noone can access it), alternatively putting the DB into single-user.
    – Charlieface
    Commented Jul 18, 2023 at 20:09
  • "pay big bucks"? If you only need to do this for a single database, Always On Basic Availability Groups can do this and are included in Standard edition.
    – 9072997
    Commented Jul 18, 2023 at 20:52
  • True, although it's still almost $1000 compared to the free Express Edition.
    – Charlieface
    Commented Jul 18, 2023 at 22:12

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge you have read our privacy policy.