Be cautious, and careful - like holding a new born baby. 😆
Keep reading for my top tips on how to navigate your database with confidence.
If you're anything like me, you'll panic every time you have to do something with a production database. I think it's because we always hear so many horror stories - people wiping entire tables worth of data, or overwriting records with the wrong thing. For companies which rely on a massive database to centrally store everything, you are effectively doing open-heart surgery. One wrong move... and flat line!
Of course, it's not actually life or death. But it can feel like it.
What can you do to improve your confidence in working with databases?
Get familiar with your tools on a local database. I personally use Microsoft SQL Management Studio (SSMS). This is a very powerful tool with a lot of features - but when you've never used it before it can look overwhelming.
Learn your basic SQL queries/commands. SELECT is how you view your data and is safe to use. Pretty much everything else will mutate your data so make sure you read up on it first. Which leads onto the next point...
If you can avoid using UPDATE, DELETE or DROP commands, it's the much safer option. In SSMS, you can open an Edit window which allows single line editing. Firstly craft a SELECT statement which outputs the record(s) you want to edit. You can take that WHERE clause and use in the Edit window to filter the editable records. Now you can update each cell individually. This may not be useful if you have a lot of data to change, but if you only need to update a few records it's much, much safer this way. For dropping tables, or columns, you can right-click on them and delete.
Use Entity Framework or another similar code-based API and connect to your database through that. The APIs are tested and assuming you are a developer, rather than a DBA - you will feel much more confident using EF.
If you're writing/amending scripts, don't use the * - be explicit and write the name of the variable you're extracting. It will help others - and yourself - later when reading the script.
Verify that your production database is backed up! If something does go wrong - you need to be able to restore it. Find out how they restore the database and make sure you're ready for the worst.
Don't ever do development work against a production database. Not only could you mess up live data, but if you're dealing with real customers' data, then there are harsh ramifications if you misuse it due to data protection regulation such as GDPR.
Ask if you're unsure of anything you're doing on a production database. Being asked a few questions is far less annoying than having to clean up a huge mistake.
SQL Management Studio 101
- Right-click on a database table to bring up the context menu - from here you can choose to view the Top 1000 records.
- You can also choose to Edit top 200.
- Press F5 / the green play button to run a Query.
- Highlight a section of the query and press F5 to only run that. Always check what you are highlighting before running the query!