Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
keithc1
Yak Posting Veteran
88 Posts |
 Posted - 2004-06-23 : 14:05:12
|
| Am i correct in thinking that Single user mode is mainly used for backup or restore operations?Also, the way I got into single user mode was by going into management folder and killing all process's, I was able to also do a restore of a transaction log but I also had to kill all process for the DB i was trying to restore to. Is this the usual way to do these operations or does one restart the sql services etc?ThanksKeithc MCSE MCSA |
|
|
MuadDBA
628 Posts |
Posted - 2004-06-23 : 14:28:24
|
| single user mode is for a couple of thigns:RESTORE operations, in which only the process which runs the restore can have access to the database.DBCC operations such as CHECKDB with some of the REPAIR options. You can find stored procedures out there that will kill all active sessions on a DB so you can perform the restore. Look in the script library. |
 |
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 14:34:03
|
| Just use ALTER DATABASE to kill the users. Use SET ROLLBACK IMMEDIATE.Tara |
|
|
|
keithc1
Yak Posting Veteran
88 Posts |
Posted - 2004-06-23 : 15:50:27
|
| Tried that Tara and it does put the DB in single-user mode but I still have to kill all process's to the DB to do a restore. The user that the process's pertain to is just one user and thats the account I would be using for alter database command so maybe thats why. Anyways, I was hoping that the Alter DB command would kill these process's as well. Whats interesting to is after I do the restore using a transaction log single user mode is no longer in effect, i thought that setting would be restored with a DB restore not a transaction log restore. Sorry if I being a pain I just trying to grasp how all this worksKeithc MCSE MCSA |
|
|
|
X002548
Not Just a Number
15586 Posts |
Posted - 2004-06-23 : 16:02:45
|
| [code]ALTER DATABASE TaxReconDB_Prod SET SINGLE_USER WITH ROLLBACK IMMEDIATERESTORE DATABASE TaxReconDB_Prod FROM DISK = 'D:\Tax\BackUp\TaxReconDB.dmp' WITH MOVE 'TaxReconDB_Data' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Data.MDF' , MOVE 'TaxReconDB_Log' TO 'D:\Database\NJROS1D151DEV\MSSQL$NJROS1D151DEV\Data\TaxReconDB_Prod_Log.LDF' , REPLACEALTER DATABASE TaxReconDB_Prod SET READ_WRITE[/code]Brett8-) |
|
|
|
tkizer
Almighty SQL Goddess
38200 Posts |
Posted - 2004-06-23 : 16:09:30
|
| ALTER DATABASE does kill the users if you use the WITH ROLLBACK IMMEDIATE option. Check out Brett's code for an entire example.Tara |
|
|
|
keithc1
Yak Posting Veteran
88 Posts |
Posted - 2004-06-23 : 16:21:28
|
| unbeleiveable how much you people know on sql, learned a ton from this site so far. Thanks againKeithc MCSE MCSA |
|
|
|
|
|
|
Restore Operations