DB backup single user mode

Question

DB backup single user mode

sqlfriend

SSC Guru

Points: 52781
More actions
November 8, 2012 at 1:25 pm

#393568

We would like to migrate databases from one server to another production server.
Before I do a backup on the old server, we would like no one connect to the server and do any transactions other than me the dba. So I decide to use set the databases to single-user mode only, then do backup of all the databases. Then use the backup files to restore to another server.
My question, do I need to pay attention to any of this process to insure the backup and restore is correct?
Thanks,

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Don't do it that way. When you want to take the last backup, run backup database with norecovery (or backup log with norecovery), that'll switch DB into restoring, allow no further transactions.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

George M Parker SSCertifiable Points: 5412 More actions · Answer 2

sqlfriends (11/8/2012)
We would like to migrate databases from one server to another production server.
Before I do a backup on the old server, we would like no one connect to the server and do any transactions other than me the dba. So I decide to use set the databases to single-user mode only, then do backup of all the databases. Then use the backup files to restore to another server.
My question, do I need to pay attention to any of this process to insure the backup and restore is correct?
Thanks,

What transactions do you want to be able to run after you set the database to single user mode? Normally, I would set the database to read only, take a backup of it, restore the database on the 2nd server, and then take that database out of read only mode. That guarantees no writes occur to the database, but still allows you to read from it.

If you want to make sure that no one connects to the SQL Server instance, you can open the connections that you need in SSMS, pause the SQL Server Service and then terminate any other open connections to the database. Of course, you will need to make sure that no one else re-starts the paused service or other connections will be allowed again.

sqlfriend SSC Guru Points: 52781 More actions · Answer 3

Thanks,

The reason we do that is we have a front end application they can choose serveranme and database name to connect tothis server, we don't want them to connect to the databases esp. write to the databases before and during I start the backup. And after we finish the backup we still don't allow them to connect to the old server other than the dba. That is why I thought I made it to single user mode only and then do the backup.

I don't even want them to have read connection to the database.

Also Gail mentioned not to use this way, any reason not to do this?

Gail recommended to use backup with norecovery, but after that, DBA still want to access the database.

Also is there a backup with norecovery in SSMS other than code?

The backup here I mean full backup, for this case no log backup needed.

Thanks

david.bianco Ten Centuries Points: 1148 More actions · Answer 4

GilaMonster (11/8/2012)
Don't do it that way. When you want to take the last backup, run backup database with norecovery (or backup log with norecovery), that'll switch DB into restoring, allow no further transactions.

Hi Gail,

I recently upgraded our main database server to SQL 2012, and I used the method that the OP described of bringing the server into single user mode, etc.

Your advice seems like a much better way to accomplish this. I am testing it on a local test server, and I can't seem to get NORECOVERY to work with the BACKUP DATABASE command:

BACKUP DATABASE test_db

TO DISK = 'c:\backups\test_db.bak'

WITH NORECOVERY

Msg 3032, Level 16, State 2, Line 1

One or more of the options (norecovery) are not supported for this statement. Review the documentation for supported options.

If I changed the database into FULL recovery, take a backup, and then take a log backup with BACKUP LOG ... WITH NORECOVERY, your advice works perfectly.

Did I miss something somewhere? I am on SQL 2012 RTM.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 5

Thought it was valid on backup database. Ok, so you'll need to take the last of the log backups with norecovery then

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 6

I think Gail answered David's post.

Can anyone help to answer my previous post too?

Thanks,

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 7

Restricted mode

Shut the app down

Disable logins

several ways.

The reason why not single user is that the single user doesn;t have to be you. If the app gets it, there's trouble.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

sqlfriend SSC Guru Points: 52781 More actions · Answer 8

Thanks Gail, those are excellent ideas.

I will use some combination of it.