We'd use a not dissimilar approach for creating test data and removing sensitive data when copying from production to development. Each of the tables had a corresponding table in a...

Yeah, I found a lot more since I first posted. For example, from the master server:

selectserver_name, *

frommsdb.dbo.systargetservers

or this, to help identify target servers that are failing:

-- http://mikehillwig.com/tag/multi-server-administration/

create table #tmp_sp_help_targetserver(

server_id int...

You're right. I should have expanded on this. The archive data would be exported to a database on another server. If that is not possible, then an export to a...

You may also free up some space with a more aggressive archiving approach to your data, or install archiving at all.

I've seen a number of legacy systems have up 10...

I've had much better luck historically by creating a [font="Courier New"]#TempTable[/font] with a [font="Courier New"]Done = cast(0 as bit)[/font] extra column.

That way I can review the results at the end...

I didn't have any success with Adam Haines' approach. When trying to test this I got the error:

[font="Courier New"]The specified schema name "dbo" either does not exist or you do...

I very much appreciated the connection and feedback about Rob's article. I have felt that way at times, too, but never really had the means to discuss my REALLY strong...

This was some great code, Jack. Much of it is working well. However, the cdc_DBName_capture job is taking a heck of a long time. It needs about 18 hours of...

The best answer I've had for keeping the LDF and MDFs for system databases in the same directory is that they're that way by default on install and there's no...

Not so much. It seems that all the Change Data Capture stuff is a "push" to the destination.

I was hoping that it would be more like replication where the Subscriber...

We've had good success lumping similarly used tables and procedures into schemas and then creating a role for the schema and assigning "User" permissions to the role, specifically SELECT, INSERT,...

I remember doing this as well. Created a bunch of views in a separate schema that the users had access to. The views had friendly column names and made it...

In many ways, that would be true. But they didn't wan't to write a new package, or add to an additional one, every time they have new SQL. Doesn't really...

Agreed about scanning existing permissions. I had considered adding this to the article but it was already getting lengthy. Plus there are a ton of postings on how to do...

We also used AD groups in this implementation. The system is set up in such a way that any approach is possible. It can grow or shrink as needs arise....