- Accidental DBA
- Posts
- SQL Server Databases are Full of Code
SQL Server databases are code.
More accurately they are full of code. The database schema itself, tables, indexes, stored procedures, functions and more. Each of these objects is created and has its own script, even when created in the SSMS GUI. For decades, there has been an “ignoring” aspect to protecting this code.
Let’s explore some ways to store, save, and back up your SQL Server code.
1. SQL Server Native Backups
Even though SQL Server backups primarily target data, they also capture schema and code as part of the process.
Pros: Simple, fast, and already built into SQL Server.
Cons: Restoring a whole database just to retrieve code can be overkill and VERY time consuming
Pro Tip: Don’t rely on this. Restoring a 2TB database to get one procedure back can take way too long, especially if the main application/website is also down.
2. Scripting Code to Files
Manually or automatically script out object definitions to flat files.
Pros: Granular control over each object.
Cons: Easy to forget without automation.
Pro Tip: Make sure you loop through ALL user objects…picking and choosing almost guarantees you’ll miss something critical.
3. Version Control Systems (VCS)
Version control systems aren’t just for developers. They’re perfect for SQL code.
Pros: Track changes, collaborate, and roll back to previous versions.
Cons: Steeper learning curve if you're not familiar with VCS.
Pro Tip: Limit access to production to those that also control the VCS/Git repo.
4. DACPACs & BACPACs
Deployable packages like DACPACs (schema-only) and BACPACs (schema + data) provide an all-in-one solution.
Pros: Easy deployment and migration.
Cons: Not as flexible for selective object retrieval.
Pro Tip: Use SQL Server Data Tools (SSDT) to create DACPACs for versioning your schema.
5. Third-Party Tools
Tools like Redgate SQL Compare or ApexSQL Source Control can simplify code storage and backup.
Pros: Automates the process and integrates well with VCS.
Cons: Licensing costs.
Pro Tip: Leverage free trials to find the tool that best fits your needs.
6. DBCC CLONEDATABASE
If you need a quick, schema-only copy of your database, DBCC CLONEDATABASE is a game changer. It creates a small cloned copy of your database, perfect for storing in a version control system.
Pros: Built into SQL Server, Captures everything except the data, including schema, indexes, statistics, SPs, etc.
Cons: Still requires manual effort for versioning and backups. SQL 2014 and later, assuming proper SP/CU level.
Pro Tip: Use WITH BACKUP_CLONEDB to create a file you can drop into your VCS, then remove the MyDatabase_Clone copy.
The Bottom Line
Your SQL Server code is just as valuable as the data it manages. Take proactive steps to store, save, and back it up. Whether you use native methods, VCS, or third-party tools, what matters is having a plan in place before something goes wrong.
Got a favorite method for safeguarding your SQL code? Let me know in the comments.
If you need help, please reach out:
Get a FREE SQL Server Health Check from Dallas DBAs!
New Pocket DBA® clients get the first month FREE!
Join the Accidental SQL DBA group on LinkedIn – no fee, no spam
My Recent LinkedIn Posts
Interesting Stuff I Read This Week
Walmart to roll back DEI programs | LinkedIn – Sad. That’s all I’ve got.
Zoom drops the ‘video’ from its company name - The Verge – rebranding as an AI-first company. I do love the Fathom add-on/plugin that generates a nice AI summary of calls.
SQL tidBITs:
DBCC CloneDatabase is a nifty little ‘cheat’ to create a backup copy of your entire database except the data. For when that one developer alters a procedure in prod.
Sample:
DBCC CLONEDATABASE (StackOverflow, StackOverflow_Clone) WITH Backup_CloneDB;
I then drop StackOverflow_Clone. Maybe even toss the resulting .bak file into a Git repo.
Reply