Recovery :: Rollback At Database Level
Aug 20, 2015
Testing team is performing some activities on MSSQL - 2008 R2 database ..and after completion of the test, again team wants to restore the original database ( in the same state as before starting of the testing). Currently team is taking the back-up before starting testing and restoring the backup after the testing .. Since the database size is huge, this restoration is taking more time Is there any better way of taking the database to the previous state in lesser time.
View 3 Replies
ADVERTISEMENT
Nov 8, 2006
Crash recovey is going on over 24 hours!
I want to stop it!
One of the databases in our server is in recovery mode after a huge
delete job (started as a job) did not complete ( I suspect due to
lack of transaction log sapce.) the log file size is 120 GB.
Whenever the server restarted the recovery for this database starts.
After 20 hours, analysis done 100% and recovery done up to
76%. Then after €œthe percentage of recovery done€? went down to
16% and it is still running.
How can I get rid of this database without damaging the master database and other database in the same instance?
I don€™t mind to lose this database.
There are 4 user databases in this instance and msdb and the recovery mode database are not operational.(I cannot connect)
View 1 Replies
View Related
Sep 8, 2015
I use following trigger to stop user "smith" if he try to connect through SSMS to My Server:
create TRIGGER [trg_connection_MyServer]
ON ALL SERVER WITH EXECUTE AS 'Smith'
FOR LOGON
AS
BEGIN
IF ORIGINAL_LOGIN()= 'Smith'
begin
if exists (SELECT 1 FROM sys.dm_exec_sessions
WHERE (program_name like 'Microsoft SQL Server%' and original_login_name = 'Smith') )
ROLLBACK;
end
I want to log this information or send emal incase, this user try to connect through SSMS, so that I can catch it. How can I do this, if I use insert command it rollsback everything and I can't do any activity.
View 8 Replies
View Related
Sep 17, 2015
Pages on a full recovery model database corrupted, need to ensure data loss is minimal for restore operation am thinking about restoring the latest full backup.
View 4 Replies
View Related
Jan 16, 2008
I want to perform column level and database level encryption/decryption....
Does any body have that code written in C# or VB.NET for AES-128, AES-192, AES-256 algorithms...
I have got code for single string... but i want to encrypt/decrypt columns and sometimes the whole database...
Can anybody help me out...
If you have Store procedure in SQL for the same then also it ll do...
Thanks in advance
View 1 Replies
View Related
Nov 1, 2015
We have an issue with a 3 node SQL 2012 Always on availability group. Normal operation is node 1 (primary replica) with node 2 and node 3 as secondary replicas.After some patching, SQL wasn't running on node 1 hence the AG flipped over to node 2. This went unnoticed for some time and the transaction log for one of the AG databases became full on node 2 and node 3. (I think this is because it couldn't commit the transactions on node 1 so couldn't truncate it's t-log?) The DB is using synchronous replication btw.So I started SQL on node 1 and flipped the AG back to node 1 (with a data loss warning but I accepted this).Now the issue is that on node 2 and 3, the DB in question is stuck in a "Reverting / In Recovery" State. I've tried various commands such as ALTER DATABASE SET ONLINE, RESTORE DATABASE WITH RECOVERY etc but these fail stating unable to obtain a lock on the DB.
The weird thing is that on node 1 the state of the DB is "synchronised".how to resolve this issue on node 2 and 3? I've left them overnight (in case they were rolling back transactions, the DB is fairly large) but nothing seems to have happened. remove the DB from the AG in node 2 and 3 and add it back in again, ie recreate the replication?
View 2 Replies
View Related
Aug 31, 2007
The problem that I am having is that with Visual Web Developer I am creating a webpage and having it directly put online, so for example when I start a new ASP.NET page, I select the location to be HTTP, with the location http://MYWEBSERVER/Website and for the language and Visual BasicI notice a couple of things, first that there is no longer a a link under the Main toolbar "Website" selection called the ASP.NET configuration. So how can I configure what I want to have users be able to do? It seems that this choice is only available if I am building the ASP.NET page on my "localhost". So that is the first problem. So I am able to get the pages to work, atleast the things such as the textboxes to show up etc, (Even things as advanced as the "Login" box). How ever when I try to get someone to try to login I am taken to a page that has an server error. The error is:
"The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file. "
The Stack Trace Errors are at the bottom. I think this is happening because the automatically generated databases are not getting built online as they are on my computer. On my computer I have MSSQL express. So either the databases are not getting built for some reason, (and I think that is the case as I don't see any in the folder). So I think that somehow I have to create a database on my server, and then somehow configure the ASP.NET file, perhaps in the Web.Config file to look for that new database. Is this the correct methodology? Is there some simpler way that I can just somehow upload things as they are and have them work correctly on my server? The error says that either the Server did not find the database or that the trust level was insufficient. I don't think that is it as I just looked again and I don't see any .MDF files. So how would I go about getting this to work right? Is there a way to do this with MySQL also? So that I don't have to use MSSQL? My server only allows 1 DataBase for that.
Thanks and I hope my question makes sense. It is basically how can I get it to be able to create and check users etc. online?
Brian
[ProviderException: The SSE Provider did not find the database file specified in the connection string. At the configured trust level (below High trust level), the SSE provider can not automatically create the database file.] System.Web.DataAccess.SqlConnectionHelper.EnsureSqlExpressDBFile(String connectionString) +2555237 System.Web.DataAccess.SqlConnectionHelper.GetConnection(String connectionString, Boolean revertImpersonation) +87 System.Web.Security.SqlMembershipProvider.GetPasswordWithFormat(String username, Boolean updateLastLoginActivityDate, Int32& status, String& password, Int32& passwordFormat, String& passwordSalt, Int32& failedPasswordAttemptCount, Int32& failedPasswordAnswerAttemptCount, Boolean& isApproved, DateTime& lastLoginDate, DateTime& lastActivityDate) +1121 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved, String& salt, Int32& passwordFormat) +105 System.Web.Security.SqlMembershipProvider.CheckPassword(String username, String password, Boolean updateLastLoginActivityDate, Boolean failIfNotApproved) +42 System.Web.Security.SqlMembershipProvider.ValidateUser(String username, String password) +83 System.Web.UI.WebControls.Login.OnAuthenticate(AuthenticateEventArgs e) +160 System.Web.UI.WebControls.Login.AttemptLogin() +105 System.Web.UI.WebControls.Login.OnBubbleEvent(Object source, EventArgs e) +99 System.Web.UI.Control.RaiseBubbleEvent(Object source, EventArgs args) +35 System.Web.UI.WebControls.Button.OnCommand(CommandEventArgs e) +115 System.Web.UI.WebControls.Button.RaisePostBackEvent(String eventArgument) +163 System.Web.UI.WebControls.Button.System.Web.UI.IPostBackEventHandler.RaisePostBackEvent(String eventArgument) +7 System.Web.UI.Page.RaisePostBackEvent(IPostBackEventHandler sourceControl, String eventArgument) +11 System.Web.UI.Page.RaisePostBackEvent(NameValueCollection postData) +33 System.Web.UI.Page.ProcessRequestMain(Boolean includeStagesBeforeAsyncPoint, Boolean includeStagesAfterAsyncPoint) +5102
View 1 Replies
View Related
Dec 1, 2005
If I execute the command ALTER DATASE SET MULTI_USER WITH ROLLBACK IMMEDIATE and there are any connections to the database, the command fails with a "Lock request time out period exceeded." message. If I use SET RESTRICTED_USER, the command succeeds with the following message: "Nonqualified transactions are being rolled back. Estimated rollback completion: 100%." This seems to be a bug.
View 5 Replies
View Related
Dec 8, 2000
Hi,
We have an order posting stored procedure that's been executing for 14 hours now. This procedure has simple update statements but all the tables updated have triggers which update other tables with triggers and so on.
This procedure hasn't commited yet. I have a full database backup from last night and transaction log backup from this morning while it was running. I want to kill this job and restore database from last backup. Only thing I am afraid of is that it will take too long to roll back which I have no estimate of time. Is there any faster way to get rid of all the uncommited transactions? Has anybody rolled back such a massive transaction? Any idea on how long it can take? Can it affect the overall server performance as this is the main production ERP server.
I can see in profiler that this procedure is still running and not hung.
View 1 Replies
View Related
Oct 16, 2014
I have a wrapper stored procedure ProcMain_wrapper that executes in one main dataabase and it calls child stored procedure in multiple databases. Does the rollback occur in all the databases if the stored procedure fails in any one of the multiple databases?
create procedure dbo.db_Main.ProcMain_wrapper (
declare @curClientCode as cursor ,@db varchar(10),@dbName varchar(20)
BEGIN TRANSACTION TRAN1
BEGIN TRY
SET @curClientCode = CURSOR FOR
SELECT [name] from sys.databases where name like 'dbclient_%'
[code]....
View 2 Replies
View Related
Feb 26, 2008
Recently I created a DDL Server-scope trigger using the following:
create trigger stop_alter_database on all server
for ALTER_DATABASE
as
rollback;
print 'database change stopped by stop_alter_database';
go
Then I ran the following script:
alter database [test] modify file (name=test', maxsize=2028mb);
The result was:
Msg 3609, Level 16, State 2, Line 1
The transaction ended in the trigger. The batch has been aborted.
database change stopped by stop_alter_database
The problem is that when I checked the max size of the data file it had changed. So, the statement was never rolled back. Is there something I'm missing because I can't find any documentation or articles that state the inability to rollback alter database statements. Whats going on?
View 2 Replies
View Related
Feb 20, 2007
System Configuration :
OS : Windows 2003 latest SP
SQL Server : Standard Edition, SP2
Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86)
Feb 9 2007 22:47:07
Copyright (c) 1988-2005 Microsoft Corporation
Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
DownTime : This is not a 24x7 kind of machine. It can have downtime
Reference : http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1198044&SiteID=1
http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1026884&SiteID=1
I have been discussing this Service Broker issues in this forum for quite sometimes and sorry to bother u all again€¦ To make things more clearer before implementing in production environment I have few doubts and it should be clarified..
As discussed in the first link we can clear sys.conversation_endpoints by just giving ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE. But my apprehension is that if we run this command on production server and it truncate this table€¦ what will be the impact and overall overhead on the system€¦ Is it recommended to give this statement on Production Server daily at less traffic times, to clear this table ?€¦ will it have adverse effect. I repeat I have downtime , I can even shutdown this server daily€¦
I also, just want to know why Microsoft has not looked into this aspects€¦ why the system itself is clearing the expired messages.. What is the thought behind this architecture
I have the script to run in batch€¦ but in high-level meetings it is always difficult to convince this architecture/process€¦
Thanks in advance
View 5 Replies
View Related
Nov 9, 2015
Primary platofrm: Sql12k, 7.0 Ultimate Pro OS
I'm launching the aforementioned statement from one MASTER session windows and I get this message, I am stuck, I though ROLLBACK INMEDIATE go throught any already session open.
Msg 5064, Level 16, State 1, Line 1
Changes to the state or options of database 'GFSYSTEM' cannot be made at this time. The database is in single-user mode, and a user is currently connected to it.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
View 4 Replies
View Related
Aug 4, 2015
I found this script online..
SELECT prin.[name] [User], sec.state_desc + ' ' + sec.permission_name [Permission]
FROM [sys].[database_permissions] sec
JOIN [sys].[database_principals] prin
ON sec.[grantee_principal_id] = prin.[principal_id]
WHERE sec.class = 0
ORDER BY [User], [Permission];
but the results are this: 2 columns - User and Permission
User Permission
User1 GRANT CONNECT
User2 GRANT CONNECT
IS there a way in SQL Server (2005/2008/2012) to run a script against a Database that will show all users that have permissions to that Database and the type of permissions?
View 7 Replies
View Related
Jul 9, 2014
Last week I backed up my SQL Server by using BE 2012. I named the file "SQL Server BAK" which contained copies of my SQL Server databases. A few days ago I lost some part of my data due to accidental deletion. I backed it up, so I tried to restore the database from the .bkf file. The problem comes here, when I try to to restore my .bkf file, it becomes inaccessible.what causes this?
View 7 Replies
View Related
Nov 30, 2000
Running SQL version 7 on "Terminal" Server I have a database marked in Load Receovery and we are unable to get to it. We tried MS SQL document on how to but we are still unable to recover it!
View 4 Replies
View Related
Oct 4, 2007
hai!
i have a database which crashed recently(26th sep 2007).my last backup is on 14 sep 2007.i have restored database after two days.Now i have a database (old) with data upto 14sep 2007 and from 26sep to till date.(data from 14sep to 26sep missing).I restored database on 28th with a new database name which has data upto 26th sep 2007.how to attach these two databases to a single database to have data from beginning to tilldate.
please help in this regard.
View 1 Replies
View Related
Jan 22, 2008
Hi,
I made an UPDATE query without specifying any criteria so I had more than 1500 records have been changed and I cannot undo the UPDATE.
Is there any way I can recover the data UPDATE from the Transactions Log.
I did not back up the database lately so what is the appropriate solutions for this?
Any thoughts… :S
View 10 Replies
View Related
Mar 10, 2004
Hi,
Is there a way to Revover a Sql Server 2000 database in the absence of the backup file(the backup file got overwritten). Does Sql Server do any automatic Transaction logging?
Thanks for any input
View 4 Replies
View Related
May 23, 2008
Hai friends....
SQL Server 2005 Database, So I want to know about Database Backup and Pointing Recovery concepts..so can u please help me and any send me related documents...
---
Thanks,
Nageswar.V
New Horizons Cybersoft Ltd
+919848854114 : nageswar@nhclindia.com
View 2 Replies
View Related
Jul 23, 2005
Hi,I'm no where close to a SQL Server expert but we're using it as a back endto another application.I lost a PC that had MS SQL Server 2000. I was able to recover the .MDF and..LDF files but nothing else. How can I restore these files to another SQLServer installation on another PC. When I tried to attach them they are notrecognized as valid files (I get the red X instead of the green CHECK).Is this a basic security function that I'm not going to get around or shouldit be possible? Do the .mdf and .ldf files contain specific info about whatPC they were created on?Are there third party apps that might be able to at least extract thetables?Thanks,WH
View 1 Replies
View Related
Jul 15, 2007
Hi all
When I restart server my database apear as database(In recovery )
what's ???
thanks
View 3 Replies
View Related
Jul 30, 2007
Hello all,
Is there a way to check the status of a database in recovery. Like how far along it may be. If not is there a way to stop a database in recovery and just drop it.
Thanks in advance,
Mike
View 7 Replies
View Related
Mar 30, 2007
Our server ran out of disk space during a stored procedure process. We were able to free up some space, stopped all SQL services, restarted all SQL services and now SQL Management Studio shows the database to be in recovery. Is there a way to monitor this to ensure progress is being made and to estimate when it might complete?
View 12 Replies
View Related
Aug 9, 2007
What does it actually mean when the database is in recovery? My database has been in recovery overnight. What is normally going on in this situation. Should I wait or take more drastic action?
View 10 Replies
View Related
Aug 28, 2007
Hi all,
I had to change the path of .mdf and .ldf files, so I decided to:
1) Take offline the database
2) run the quey ALTER DATABASE... MODIFY to change the path
3) Bring online the database.
The last step hung up (with no errors) and left the database In Recovery.
When I tried to stop and restart sql server other databases changed their status In Recovery...
Here is a dump of Errorlog files
2007-08-26 18:13:29.28 spid24s Starting up database 'DbOrdini'.
...
...
2007-08-26 18:13:30.09 spid24s * BEGIN STACK DUMP:
2007-08-26 18:13:30.09 spid24s * 08/26/07 18:13:30 spid 24
2007-08-26 18:13:30.09 spid24s *
2007-08-26 18:13:30.09 spid24s * Location: "logmgr.cpp":5334
2007-08-26 18:13:30.09 spid24s * Expression: !(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)
2007-08-26 18:13:30.09 spid24s * SPID: 24
2007-08-26 18:13:30.09 spid24s * Process ID: 1380
.....
.....
2007-08-26 18:13:30.40 spid24s Error: 17066, Severity: 16, State: 1.
2007-08-26 18:13:30.40 spid24s SQL Server Assertion: File: <"logmgr.cpp">, line=5334 Failed Assertion = '!(minLSN.m_fSeqNo < lfcb->lfcb_fSeqNo)'. This error may be timing-related. If the error persists after rerunning the statement, use DBCC CHECKDB to check the database for structural integrity, or restart the server to ensure in-memory data structures are not corrupted.
2007-08-26 18:13:30.40 spid24s Error: 3624, Severity: 20, State: 1.
2007-08-26 18:13:30.40 spid24s A system assertion check has failed. Check the SQL Server error log for details
Could it be dangerous trying to kill this process ? If not, what is the best way do to it ? From Sql Server Activity Monitor (spid 24) or from Task Manager ?
Thanks in advance
View 9 Replies
View Related
Aug 17, 2015
We have a requirement to build SQL environment which will give us local high availability and disaster recovery to second site. We have two sites- Site A & Site B. We are planning to have two nodes at Site A and 2 nodes at Site B. All four nodes will be part of same Windows failover cluster. We will build two SQL Cluster, InstanceA will be clustered between the nodes at Site A Server and InstanceB will be clustered between the nodes at Site B, we will enable Always On Between the InstanceA and InstanceB and will be primary owner where data will be written on InstanceA and will be replicated to InstaceB. URL....Now we want we will have instanceC on the Site B and data will be writen from the application available on Site B, will be replicated to the instance on the Site A as replica.
View 6 Replies
View Related
Feb 20, 2001
Hi!
After upgrading SQL Server from 6.5 to 7.0 my production database compatibility level is "65".
I checked that by executing sp_dbcmptlevel <database_name>.
I can change it to "70" but my question is how it's going to affect the application and do I have to change it?
Thank you
Lena
View 2 Replies
View Related
Feb 7, 2008
Database level password security
View 2 Replies
View Related
Jul 20, 2005
I have many tables and in those i require to change some data. Sayfrom ARCA to ARCAEX. I am sure that the string is unique in the sensethere will be no ARCAABC. So what do i do change by not manuallyneeding to search in each table and the whole database and still canbe sure that the changes have taken place. Please helpRegards,Rajesh
View 2 Replies
View Related
Jul 26, 2007
Hi,
Is there a way to allow specific databases access to the CLR. Currently, my understanding is that when this setting is enabled, it applies to all databases within the instance.
Kind regards,
Jan.
View 1 Replies
View Related
Aug 3, 2006
Hi,
How do I lock entire database? I want an exclusive lock on the db by
a user
who is the dbo of that database only (not sa
user).
The
scenario is we have a web application and each week we need to do data
uploads (with etl). During this upload, the users accessing the website
should not be able to read data. This is why I want database lock.
Now
the catch here is, the application access the data using user say abc.
Abc is dbo for that database and the etl is also done by abc login. So
will db locking help in this case as the website can also read the data
being a abc user?
Thanks,
Tanmaya
View 3 Replies
View Related
Jan 24, 2001
Hi everyone,
I am trying to implement a plan for a SQL7 database that is used by the company I work for. Having read the SQL manuals I have come to the following conclusion:
I want to do a complete backup of the database at midnight and do a transaction log backup every 30 minutes between 7am and 7pm. I am hoping this will allow me to 'undo' any user mistakes and roll back the database changes to the last transaction log backup.
The questions I have are:
1) By backing up the transaction log every 30 minutes, will this then give me the ability to 'roll back' the database to the last time the transaction log was backed up (i.e. 30 minutes max) ?
2) How do I load the transaction log back in ? (i.e roll the database changes back). A simple Restore operation (with truncate ???)
3)Am I totally confused or on the right track ?
Thanks,
Tim
View 1 Replies
View Related
