The SQL Server documentation you wish you had last year
Scripting out of important SQL Server objects contained both within and outside a Database should be done for these use cases:
- Disaster Recovery
- Prod/Test/Dev/UAT Tier standardization
Most Backup software only gets the Database Schema and Data.
Many SQL Server objects live above the Database, at the Server level and are not captured.
What if you just wanted to move a TDE database or backup the PKI?
By scripting out these objects in a text format useful to DBAs, you can quickly restore/move/replicate these objects around the computing environment.
1) Documentation of your SQL servers for internal Wiki/Knowledge-bases or DBA/DEV training.
2) Migrations of SQL Servers to new hardware, Windows, SQL or 32->64 bit upgrades.
3) Disaster Recovery to quickly restore critical line-of-business SQL Servers, even before the data is located.
4) Source Control code and projects management and integration into a Source Control System
5) Environmental Consistency for creation of objects on other servers
6) Change Management until SSDT Database Projects can do docs and run Diffs on them
7) Standardized Testing Tiers so that Test, Dev, UAT servers are like Production
- Powershell 3.0 or Powershell 4.0 (Windows Management Framework)
- SQL Server 2012 Feature Pack or SQL Server 2014 Feature Pack
- “Shared Management Objects” and “System CLR Types” downloads
- 16GB RAM, SSMS, WMI, BCP, SMO