5

MongoDB has something called an oplog which you can tail to read/replay all operations (insert, update, delete, etc) that happen to a database. I am looking to do something similar in SQL Server but have been unable to find anything equivalent. Does anything similar to this exist in SQL Server, and more specifically, SQL Azure?

kspearrin
  • 10,238
  • 9
  • 53
  • 82

3 Answers3

6

Depending on what version of SQL Server your running, I believe Change Data Capture will cover your need. There are built in functions that will allow you to query all the changes that took place on CDC enabled tables. I've included a link from the Microsoft TechNet library and another from a blog that provides an introduction to CDC.

Hope this helps!

Mike Zalansky
  • 796
  • 7
  • 14
  • 1
    Awesome. Looks like CDC is not available in Azure (yet, at least), but there is another alternative which is available with v12 databases: Change Tracking: https://msdn.microsoft.com/en-us/library/bb933875.aspx – kspearrin Nov 20 '15 at 02:00
3

SQL Server has the Transaction Log facility that does just that - record all transactions in order to be able to rollback up to a certain point.

As stated here you can use DBCC LOG(databasename, typeofoutput) to access that information

Community
  • 1
  • 1
Jan
  • 13,738
  • 3
  • 30
  • 55
  • Parsing the transaction log seems very cryptic. Are there any tools or libraries to make this easier (in code)? – kspearrin Nov 16 '15 at 19:31
  • You could run a trace - that will show statements nicer. But at a quite heavy cost. – Jan Nov 16 '15 at 19:56
1

Easy way to accomplish it is SQL Server profiler. Other ways are given here. You can save SQL Server Profiler output to file or table then use other means to read it.

Atilla Ozgur
  • 14,339
  • 3
  • 49
  • 69