October 8, 2003 at 6:43 am
Hello,
What is the best method for restoring a backup and transacation logs on a SAN? The SQL Server will not let me pause it because that would send it into failover. If I stop it and restart the service with -m, the server won't let me restore because it one user is allowed in at a time.
Thanks in advance.
J
October 8, 2003 at 7:01 am
When I had to restore to my SAN, all I needed to do was put the database in SINGLE_USER mode and then do the RESTORE. I didn't have to stop or pause anything.
You can use Enterprise Manager (drill down to the database, right click on it, select Properties, go the to Options tab, click on Restrict Access and then on Single User), or Query Analyzer (ALTER TABLE tablename ALTER COLUMN column_name SINGLE_USER WITH ROLLBACK IMMEDIATE).
-SQLBill
October 8, 2003 at 7:17 am
Thanks SQLBill,
How long did you wait after you put it in single user mode? If users are still hitting the DB will single user kick in? Does it stop letting new users in like Pausing does?
Jeff
October 8, 2003 at 8:28 am
In terms of a SQL restore, a SAN is no different than any other drive system. If you have SQL2K there is a switch you can add that will kick out any active users when you set single user mode.
Andy
October 8, 2003 at 10:47 am
SINGLE USER mode stops anyone else from connecting. Depending on the WITH option, you can wait until transactions are done, or you can kick everyone out and rollback any transactions. That's the WITH ROLLBACK and WITH ROLLBACK IMMEDIATE options respectively.
I put it in single user mode and immediately began my restore.
-SQLBill
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply