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

Restore point in time Expand / Collapse
Author
Message
Posted Thursday, January 13, 2011 8:21 PM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 11:36 AM
Points: 596, Visits: 515
Comments posted to this topic are about the item Restore point in time
Post #1047716
Posted Friday, January 14, 2011 6:33 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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.

"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1047854
Posted Friday, January 14, 2011 3:09 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 1,477, Visits: 4,281
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;




"Winter Is Coming" - April 6, 2014
Post #1048239
Posted Saturday, January 15, 2011 12:31 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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. ..."
Post #1048363
Posted Sunday, January 16, 2011 6:03 PM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 1,477, Visits: 4,281
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.



"Winter Is Coming" - April 6, 2014
Post #1048512
Posted Monday, January 17, 2011 6:48 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1048733
Posted Monday, January 17, 2011 7:55 AM


UDP Broadcaster

UDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP BroadcasterUDP Broadcaster

Group: General Forum Members
Last Login: 2 days ago @ 12:40 PM
Points: 1,477, Visits: 4,281
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.



"Winter Is Coming" - April 6, 2014
Post #1048767
Posted Monday, January 17, 2011 8:59 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Thursday, March 06, 2014 1:05 PM
Points: 1,334, Visits: 3,068
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.


"Technology is a weird thing. It brings you great gifts with one hand, and it stabs you in the back with the other. ..."
Post #1048798
Posted Friday, January 21, 2011 10:47 AM
Mr or Mrs. 500

Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500Mr or Mrs. 500

Group: General Forum Members
Last Login: Tuesday, February 11, 2014 11:36 AM
Points: 596, Visits: 515
<<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.
Post #1051701
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse