Database Snapshots is an Enterprise feature introduced in SQL Server 2005, and represent a database and its data at a fixed point in time. Among other things, they can be useful for reporting scenarios and as a restore point (for instance after a major update).
In SQL Server Management Studio, it is possible to execute a multi server query against several servers at the same time. The description from TechNet says most of it:
This topic describes how to query multiple servers at the same time in SQL Server 2012, by creating a local server group, or a Central Management Server and one or more server groups, and one or more registered servers within the groups, and then querying the complete group. The results that are returned by the query can be combined into a single results pane, or can be returned in separate results panes. The results set can include additional columns for the server name and the login that is used by the query on each server. Central Management Servers and subordinate servers can be registered by using only Windows Authentication. Servers in local server groups can be registered by using Windows Authentication or SQL Server Authentication.
Here I’ll show how to create a multi server query from a group under registered servers.
Ever needed to generate insert statements for the data in a table? This feature is actually included in SQL Server Management Studio (from version 2008), but is somewhat hidden.
This guide will show how to script schema and data to a file.
When you run scripts in SSMS and get an error, the error references a line number in the t-sql statement that caused the error. If you double click the error, SSMS will take you to the error. This works even if you have multiple statements in your query window. So it can be a really helpful feature. But if the error is in code that isn’t in the query window (for instance in a referenced stored procedure), nothing happens when you double click. In that case it can be good to know how SSMS calculates the line number in error messages.
Aaron Bertrand has written a great introduction to server/database DDL triggers in his article SQL Server DDL Triggers to Track All Database Changes.
I wanted to implement similar DDL triggers on more or less all servers. Some of the databases on those servers were from a 3:rd party, so changes in those databases wasn’t possible. Therefore I wanted to make sure these DDL triggers were “zero footprint”. That is, not causing existing database code to fail. I simply added TRY and an empty CATCH to the trigger, believing I’d be safe. Unfortunately, an error like this…
…happened to a SQL Agent job running during the evening.