SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


DB backup single user mode


DB backup single user mode

Author
Message
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10163 Visits: 4195
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,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219895 Visits: 46279
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
George M Parker
SSCommitted
SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)SSCommitted (1.6K reputation)

Group: General Forum Members
Points: 1564 Visits: 1472
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.
sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10163 Visits: 4195
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
david.bianco
Mr or Mrs. 500
Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)Mr or Mrs. 500 (556 reputation)

Group: General Forum Members
Points: 556 Visits: 1666
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.
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219895 Visits: 46279
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


sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10163 Visits: 4195
I think Gail answered David's post.
Can anyone help to answer my previous post too?
Thanks,
GilaMonster
GilaMonster
SSC Guru
SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)SSC Guru (219K reputation)

Group: General Forum Members
Points: 219895 Visits: 46279
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


sqlfriends
sqlfriends
SSChampion
SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)SSChampion (10K reputation)

Group: General Forum Members
Points: 10163 Visits: 4195
Thanks Gail, those are excellent ideas.

I will use some combination of it.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search