views,tables,stored procedures are rolled back to previous versions

Question

views,tables,stored procedures are rolled back to previous versions

adisql

SSCrazy

Points: 2893
More actions
December 21, 2017 at 2:18 pm

#409887

Hi,
I have observed that some of the views,tables,stored procedures are rolled back to previous versions .
Need to find how those objects modified.
any suggestions please.
Thanks.

Viewing 15 posts - 1 through 15 (of 16 total)

You must be logged in to reply to this topic. Login to reply

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 1

Either the database was restored to an earlier version, or someone ran ALTER scripts (or DROP & CREATE) on all of the affected objects.

Check restore history, check the default trace, ask your colleagues who changed things.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adisql SSCrazy Points: 2893 More actions · Answer 2

verified restore history that it is blank as it is production server.

and i have verified In SSMS, right click on Server Name, choose Reports / Standard Reports / Schema Changes History. there i could see some objets modifed with some login name,but those users are not modified those objects manually.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 3

Default trace doesn't go back far. It's not a full history.

Still, SQL doesn't revert changes. If the objects are an older version, either someone reverted them, or you're looking at the wrong server.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adisql SSCrazy Points: 2893 More actions · Answer 4

adisql

SSCrazy

Points: 2893

December 21, 2017 at 2:37 pm

#1973197

default trace is enabled.

Lynn Pettis SSC Guru Points: 442467 More actions · Answer 5

adisql - Thursday, December 21, 2017 2:29 PM
verified restore history that it is blank as it is production server.
and i have verified In SSMS, right click on Server Name, choose Reports / Standard Reports / Schema Changes History. there i could see some objets modifed with some login name,but those users are not modified those objects manually.

Lost me at the end.

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 6

adisql - Thursday, December 21, 2017 2:37 PM
default trace is enabled.

Well, obviously it is, or the report you mentioned above would have returned no data, since it pulls from the default trace.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adisql SSCrazy Points: 2893 More actions · Answer 7

its returned data. and we are trying to find how those objects modified .

Sue_H SSC Guru Points: 90860 More actions · Answer 8

adisql - Thursday, December 21, 2017 2:37 PM
default trace is enabled.

It doesn't go back that far even if it is enabled.
sys.objects has create and modified dates and times. Check the dates and times for the objects in question to help narrow things down.

Sue

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 9

adisql - Thursday, December 21, 2017 2:42 PM
its returned data. and we are trying to find how those objects modified .

Yes, it will, but it doesn't go back far. It's not a full history.

If the changes aren't in the default trace, and you don't have custom monitoring or audits, then you will not be able to tell who made these changes.
It's possible to narrow down the list of potential culprits. SQL doesn't revert changes by itself, so if the objects are an older version, either someone reverted them, or you're looking at the wrong server, and if someone did so, then they'd need permissions to modify the objects, which should eliminate most of your users. Ask the other admins and devs.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

adisql SSCrazy Points: 2893 More actions · Answer 10

adisql

SSCrazy

Points: 2893

December 21, 2017 at 2:52 pm

#1973207

thank you !!

adisql SSCrazy Points: 2893 More actions · Answer 11

is there any way to find what jobs modifying these objects .
because these objects are modifying frequently.

Database Name

Object Name

Type

DDL Operation

Time

Login Name

ABC

sp1

Stored-Procedure

ALTER

12/17/2017 4:45:01 PM

XXX

ABC

View

ALTER

12/17/2017 3:45:01 PM

XXX

ABC

View

ALTER

12/17/2017 3:45:01 PM

XXX

ABC

table1

User Defined Table

ALTER

12/17/2017 2:45:02 PM

XXX

ABC

Database

ALTER

12/17/2017 2:45:02 PM

XXX

ABC

Database

ALTER

12/17/2017 1:45:02 PM

XXX

Gail Shaw SSC Guru Points: 1004504 More actions · Answer 12

I'd start by looking for jobs that run every hour, starting at 00:45. Shouldn't be a lot of them.

Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability

We walk in the dark places no others will enter
We stand on the bridge and no one may pass

Jeff Moden SSC Guru Points: 1003851 More actions · Answer 13

adisql - Thursday, December 21, 2017 2:57 PM
is there any way to find what jobs modifying these objects .
because these objects are modifying frequently.
Database Name Object Name Type DDL Operation Time Login Name
ABC sp1 Stored-Procedure ALTER 12/17/2017 4:45:01 PM XXX
ABC view1 View ALTER 12/17/2017 3:45:01 PM XXX
ABC view2 View ALTER 12/17/2017 3:45:01 PM XXX
ABC table1 User Defined Table ALTER 12/17/2017 2:45:02 PM XXX
ABC Database ALTER 12/17/2017 2:45:02 PM XXX
ABC Database ALTER 12/17/2017 1:45:02 PM XXX

I'd almost be willing to bet that those items are involved in an ETL process.

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

adisql SSCrazy Points: 2893 More actions · Answer 14

adisql

SSCrazy

Points: 2893

December 21, 2017 at 11:02 pm

#1973241

Thank you !!

we have ETL process, will verify.