Backing up SQL database while preventing user access

Question

I need to backup a SQL 2016 DB so I can restore the backup to another server for a migration. I planned on placing the DB in single user mode, run the backup then place it offline. My question is single user mode allows one connection at a time. Does my existing connection to the SSMS and DB count as one of these connections? Also from my understanding you cannot backup an offline DB correct? I just need to make sure data is not added to the DB while it is getting backed up.

Answer 1

One connection is one connection, and where the connection is coming from does not matter. Not that I see why this would be a problem. You would do

ALTER DATABASE db SET SINGLE_USER ON WITH ROLLBACK IMMEDIATE
go
BACKUP DATABASE db TO  ....
go
ALTER DATABASE db SET MULTI_USER

Then again, I am not sure that I see the point with setting the database to single user while you are taking the backup. The backup will include a portion of the transaction log, so that when you restore the backup, you will get what was committed when the backup completed.