January 13, 2011 at 8:21 pm
Comments posted to this topic are about the item Restore point in time
January 14, 2011 at 6:33 am
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"
January 14, 2011 at 3:09 pm
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;
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 15, 2011 at 12:31 pm
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. 😀
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"
January 16, 2011 at 6:03 pm
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. 😀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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 17, 2011 at 6:48 am
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"
January 17, 2011 at 7:55 am
TravisDBA (1/17/2011)
... 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.
"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho
January 17, 2011 at 8:59 am
Eric Russell 13013 (1/17/2011)
TravisDBA (1/17/2011)
... 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. 😀
"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ...:-D"
January 21, 2011 at 10:47 am
<<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.
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply