Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore point in time


Restore point in time

Author
Message
Passionate_DBA
Passionate_DBA
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 596
Comments posted to this topic are about the item Restore point in time
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 3069
Not a very robust solution, but I do realize this is your first rodeo too. Your script works if there is only one T-Log to restore to a FULL backup. However, 99.5% of the time this is never the case in a disaster recovery scenario in production. You will probably not only have to RESTORE a FULL backup with NORECOVERY, but probably your last DIFFERENTIAL backup with NORECOVERY, as well as MANY T-Log backups with NORECOVERY to get to your last T-log with RECOVERY where the STOPAT= option would come into play. It is just a lot easier to script all this out from the backup history.:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
When performing emergency or long running maintenance operations on a database, one method to kick out all active user sessions (and insure they stay out until the database is ready again) is to set status to Restricted User mode, meaning that only SA and DBO accounts can connect. The following command will start rolling back all active transactions, and when the last rollback is completed, it will set to Restricted Mode.
ALTER DATABASE [YourDatabaseName] SET RESTRICTED_USER WITH ROLLBACK IMMEDIATE;



When you're finished doing your stuff, set the mode back to Multi User and send out an email notifying that the database is back online.
ALTER DATABASE [YourDatabaseName] SET MULTI_USER;




"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 3069
When restoring a database or doing any kind of structural maintenance for that matter, the only seesion that should have access to the database is the restore or maintenance session itself. So, setting the database iinto RESTRICTED_USER is not really recommended for database restore or maintenamce operations. This is because that mode allows more than one person to connect to the database if more that one person are members in the sysadmin, db_owner, or dbcreator roles for that database on your db server.. On many boxes, there is usally more than one member in those roles. So, that is not really what you want here. What you should use for any database maintenance operations is SINGLE_USER mode. That makes absolutely sure no one else can access that sataabase while you have it down for maintenance. Much safer. :-D

ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
TravisDBA (1/15/2011)
When restoring a database or doing any kind of structural maintenance for that matter, the only seesion that should have access to the database is the restore or maintenance session itself. So, setting the database iinto RESTRICTED_USER is not really recommended for database restore or maintenamce operations. This is because that mode allows more than one person to connect to the database if more that one person are members in the sysadmin, db_owner, or dbcreator roles for that database on your db server.. On many boxes, there is usally more than one member in those roles. So, that is not really what you want here. What you should use for any database maintenance operations is SINGLE_USER mode. That makes absolutely sure no one else can access that sataabase while you have it down for maintenance. Much safer. :-D

ALTER DATABASE database SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

First, there is no good reason for an account belonging to a BI user, application, etc. to be a member of sysadmin or db_owner. In fact, the account used by the SQL Server Agent to run jobs, shouldn't be a member of sysadmin either, just a member of one of the more limited built in SQL Agent roles with whatever additional rights are needed to do it's work.

The problem with SINGLE_USER mode is that if the sysadmin logs off temporarily while still in single mode, then there is a window of opportunity for a user, application, scheduled job, etc. to login and lock everyone else out. I'd want to make sure that everyone, other than the sysadmins, are totally blocked out until all the work is completed, and that could span hours even into the next day. There may also be a need for another sysadmin to login and check on the status of an operation started by another sysadmin. The sysadmins can avoid stepping on each other's toes by coordinating ahead of time when there is some special maintenance going on. For these reasons, I'd prefer to go with RESTRICTED_USER mode.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 3069
First, there is no good reason for an account belonging to a BI user, application, etc. to be a member of sysadmin or db_owner.


Maybe not, but I see systems where this is the case all the time, and getting that changed can difficult at best. I actually interviewed at a position once where the manager stated right out front that ALL of his developer staff had sysadmin rights to ALL of the production boxes and he asked if I had a problem with that. I told him that was a recipe for disaster and his response was "Well that is the way it is here....". So, because of this I prefer SINGLE_USER mode just to be safe, and when the database is restoring no one can access it anyway. But for post restore maintenance I don't want anyone in the database, including other sysadmins. They can wait until I'm finiished and these maintenance operations are usually scheduled anyway so everyone knows ahead of time.:-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Eric M Russell
Eric M Russell
SSCarpal Tunnel
SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)SSCarpal Tunnel (4.6K reputation)

Group: General Forum Members
Points: 4612 Visits: 9545
TravisDBA (1/17/2011)
[quote]
... I actually interviewed at a position once where the manager stated right out front that ALL of his developer staff had sysadmin rights to ALL of the production boxes and he asked if I had a problem with that. I told him that was a recipe for disaster and his response was "Well that is the way it is here....".
...

It sounds to me like that hiring manager was tad bit defensive and uptight, and I have a good idea why he's always looking for a new DBA.


"The universe is complicated and for the most part beyond your control, but your life is only as complicated as you choose it to be."
TravisDBA
TravisDBA
UDP Broadcaster
UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)UDP Broadcaster (1.5K reputation)

Group: General Forum Members
Points: 1464 Visits: 3069
Eric Russell 13013 (1/17/2011)
TravisDBA (1/17/2011)
[quote]
... I actually interviewed at a position once where the manager stated right out front that ALL of his developer staff had sysadmin rights to ALL of the production boxes and he asked if I had a problem with that. I told him that was a recipe for disaster and his response was "Well that is the way it is here....".
...

It sounds to me like that hiring manager was tad bit defensive and uptight, and I have a good idea why he's always looking for a new DBA.


More territorial than uptight, and many of them are. :-D

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
Passionate_DBA
Passionate_DBA
SSChasing Mays
SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)SSChasing Mays (614 reputation)

Group: General Forum Members
Points: 614 Visits: 596
<<TravisDBA
Not a very robust solution, but I do realize this is your first rodeo too. Your script works if there is only one T-Log to restore to a FULL backup. However, 99.5% of the time this is never the case in a disaster recovery scenario in production. .....>>


Thank you for your feedback. I agree with your reply. On production server, you will have a full backup, then differential backup and bunch of transaction log backups to restore point in time. This script was used to restore DEV database. I take full backup of DEV database and recycle them every week. So,I didn't have any differential/transaction log backups. You are right that this may not be the case always. This script can be used as one of many solutions out there when someone is in same situation as mine.
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