While cleaning up a project, I needed a list of all the views in the database. SQL Server Management Studio provides a menu for this, but I needed a way to get this data in text form. In SQL Server, you can get this information from the INFORMATION_SCHEMA.TABLES view.
With SQL Server 2016 SP1 came a small but helpful new statement to create views: OR ALTER. If you add this to the create script for a view, SQL Server will figure out if it should run the CREATE or the ALTER statement:
We are currently migrating our larger databases to SQL Server 2019. While the migration went smoothly for the small databases, there was an unexpected problem with the last server: the backup was incredibly slow.
The first larger database we moved to the new server took 10 times longer to back up than on the old server (50 minutes instead of 5). If the backup time would proportionally increase with the database size, our biggest database would use the whole maintenance window just for the backup task. We had to fix this problem immediately.
Dev containers are not only for old software. They are also a great help when you plan a migration and need a new technology stack. Let's look how SQL Server 2019 works nicely with our dev containers.
For our light-weight data synchronisation tool we need to disable all triggers and constrains on our tables until the data is consistent again. In SQL Server we can use these commands to disable everything, do our work and then reactivate the checks and triggers:
SQLite is a great little database for all kinds of personal data storage. Not only does it have drivers for all current programming languages, but it also works without the hassle of creating a server to host it. Today I give you a quick overview on 3 no-code tools to explore your SQLite databases.
Exporting data from SQL Server as CSV is well understood and works (most of the time). But most often when I create a CSV file, I only use it as an intermediate format that I then convert to JSON.
The Object Explorer window in SQL Server Management Studio (SSMS) is the place in which you find the databases, the tables, and all other objects inside a SQL Server. While I worked with this view for ages, I only recently found the Object Explorer Details view.
As part of our build and deploy job in the user group project we create a backup of our Azure database before we run any data migrations. If something goes wrong, we do not lose any data and can restore our cloud database to a known good state.
It would be a great opportunity to take this backup file and restore our database to the local SQL Server on our developer machines. Unfortunately, the backup tool creates a *.bacpac file and not a *.bak. Restoring a *.bacpac file needs a few additional steps and, most importantly, a different entry point in the SQL Server Management Studio to start the restore process.
The installer of SQL Server has a little annoyance: By default, the available memory for your database is set to 2147483647 (or Int32.MaxValue) :
