Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

DB backup single user mode Expand / Collapse
Author
Message
Posted Thursday, November 08, 2012 1:25 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
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,
Post #1382693
Posted Thursday, November 08, 2012 1:40 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #1382697
Posted Thursday, November 08, 2012 1:50 PM
SSChasing Mays

SSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing MaysSSChasing Mays

Group: General Forum Members
Last Login: Tuesday, November 19, 2013 8:15 AM
Points: 653, Visits: 1,428
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.
Post #1382701
Posted Thursday, November 08, 2012 2:19 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
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
Post #1382714
Posted Thursday, November 08, 2012 2:20 PM
SSC-Addicted

SSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-AddictedSSC-Addicted

Group: General Forum Members
Last Login: Wednesday, April 16, 2014 6:58 AM
Points: 413, Visits: 1,284
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.
Post #1382715
Posted Thursday, November 08, 2012 3:01 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #1382731
Posted Thursday, November 08, 2012 3:06 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
I think Gail answered David's post.
Can anyone help to answer my previous post too?
Thanks,
Post #1382736
Posted Thursday, November 08, 2012 3:10 PM


SSC-Forever

SSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-ForeverSSC-Forever

Group: General Forum Members
Last Login: Yesterday @ 12:20 PM
Points: 41,529, Visits: 34,445
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 2008, MVP
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

Post #1382738
Posted Thursday, November 08, 2012 3:29 PM
SSCommitted

SSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommittedSSCommitted

Group: General Forum Members
Last Login: Tuesday, April 15, 2014 9:54 AM
Points: 1,720, Visits: 3,063
Thanks Gail, those are excellent ideas.

I will use some combination of it.

Post #1382748
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse