Checking if a particular database exists on the server is one of those routine tasks that are typically a tiny yet important part of a more complex job. In this article, we examine a common use case of comparing and synchronizing databases that involves checking whether a specific database exists in SQL Server.
Contents
Prerequisites
To proceed with our demonstration, we need the following:
- SQL Server of any edition, locally installed. We use the SQL Server Express edition which is free of charge, and its functionality is enough for our task.
- Microsoft ODBC Driver for Windows. If you work with a different operating system, note that Microsoft also provides this ODBC driver for macOS and Linux. Also note that the ODBC Driver is only compatible with a locally installed SQL Server, and you need it on the same computer where your SQL Server is installed.
- The sqlcmd utility.
- dbForge Studio for SQL Server. It is a multi-featured IDE for SQL Server that is more powerful than the standard SQL Server Management Studio (SSMS). We are going to utilize the integrated Schema Compare feature.
Now, let us see how to check if a specified database exists and run a schema comparison process afterward.
How to check if a database exists in SQL Server
Comparing and synchronizing database schemas is a fundamental task of database management. As a rule, it is necessary when deploying changes from the development stage to testing or production. dbForge Studio for SQL Server allows performing this comparison quickly and easily, as well as automating it with the help of the command line.
However, when configuring the process, especially in large organizations with many projects, it can be vital to ensure that a certain required database exists in SQL Server (and not moved to the cloud, for instance) before launching the comparison. Otherwise, the entire process may fail because the target database will be unavailable. Thus, the first step in configuring the database comparison is to check if the database exists in our SQL Server.
Run a check using T-SQL
To define if the database in question exists in SQL Server, we can use the standard T-SQL means, querying the DB_ID() function or the sys.databases catalog view.
Querying the DB_ID() function is the simplest approach. Use the below command to check if the database AdventureWorks2022 exists on the server:
IF DB_ID('AdventureWorks2022') IS NOT NULL
BEGIN
PRINT 'Database Exists'
ENDQuerying the sys.databases catalog view is another popular method. The following command will bring the answer if the database exists on your server:
IF EXISTS(SELECT * FROM master.sys.databases
WHERE name='AdventureWorks2022')
BEGIN
PRINT 'Database Exists'
ENDWhich one to choose depends on your preferences.
Compare database schemas in dbForge Studio for SQL Server
Let us explore the database schema comparison in dbForge Studio for SQL Server. In our demonstration, we use two test databases, OlympicGames_Dev and OlympicGames_Prod.
1. Launch the schema comparison wizard by selecting New Schema Comparison from the Comparison menu.
2. Now follow the wizard to configure the database comparison task. On the Source and Target page, specify the databases to be compared.
3. Specify the comparison options on the Options page.
4. Check and adjust the mapping where necessary.
First, on the Schema Mapping page:
Then on the Table Mapping page:
5. Once ready, click Compare to start the schema comparison process. The Studio will present the results visually, so you can view all the discrepancies between the two databases.
As you can see in the screenshot, there is a difference between the OlympicGames_Dev and the OlympicGames_Prod databases. Now we can synchronize these databases. Also, we can automate the sync process with the help of the command line. This process will also include checking if the database exists on the server.
Configure database synchronization
After examining the database comparison results, we can configure the database synchronization process.
1. Click the green arrow button to launch the Schema Synchronization Wizard.
2. On the Output page of the Schema Synchronization Wizard, specify the preferences for the database syncing process. dbForge Studio for SQL Server generates the deployment script; further, it can present the script to the user at once, save it to a file, or execute it directly against the target database.
3. View the synchronization options provided on the Options page. You can leave them as the Devart Defaults or adjust them according to your preferences and requirements and save configurations for the future. Then click Next.
4. On the Additional Scripts page, you can set the Studio to execute certain scripts before or/and after the synchronization procedure. Note that you may enter the scripts directly into the Wizard or point to the SQL scripts stored locally.
5. Finally, view the information on the Summary page and choose the synchronization mode.
To start the database synchronization process immediately, click Synchronize. If you want to automate the sync process, click Save Command Line in the bottom left corner of the Schema Synchronization Wizard.
Automate synchronization from the command line
After clicking the Save Command Line button, we can save them as a BAT file. In our demonstration, we save it under the SyncOlympicGames.bat name in the root directory of the C drive.
Now, do the following to automate the schema comparison task and include the algorithm of checking if the database exists on the server in this process.
1. Save the below script as a separate DBExists.sql file in the C root directory too.
IF NOT EXISTS
(
SELECT name FROM master.dbo.sysdatabases
WHERE name = $(dbname)
)
BEGIN
SELECT 'Database does not exists!!' AS Message;
RAISERROR (50002, 10, 127);
END
ELSE
BEGIN
SELECT 'Synhroniaztion started!!' AS Message;
END
2. We need to include the verification of database existence as the first step of the database schema synchronization process.
Proceed to the SyncOlympicGames.bat file, right-click it, and click Edit. Enter the following lines on the top of the SyncOplympicGames.bat contents:
@echo off
echo Running DBExists.sql
sqlcmd –S localhost\SQLEXPRESS -v dbname= 'OlympicGames_Prod' -i DBExists.sql -b -o out.log if not errorlevel 1
goto next1 echo == An error occurred
:next1
Notice the following:
-S requires the local SQL Server name
–v defines the target database name
–i defines the path to the DBExists.sql file; it is a script that will check if the OlympicGames_Prod database exists on the server
–b –o are necessary to write the information about possible errors into the log file; by default, the log file will be saved in the same directory where sqlcmd is installed
The modified SyncOlympicGames.bat file looks as follows in our case. It might look different in your environment, so make sure that all paths are specified correctly.
3. Save the changes in the SyncOlympicGames.bat file and execute it.
As you can see, the synchronization of databases has been completed successfully, with updates from OlympicGames_Dev now deployed onto OlympicGames_Prod.
However, if any failure takes place during the task performance, you will be notified immediately about that. The warning will be present in the .bat file and in dbForge Studio for SQL Server itself. Detailed information about the causes of the error will also be provided.
Resolve the trouble and re-run the task.
Conclusion
Verifying the existence of a database and taking actions based on the outcome is a fundamental operation critical for ensuring the safety and efficiency of your work. With dbForge Studio for SQL Server, configuring this process with all necessary steps becomes a simple task that can be automated for seamless performance.
To explore this functionality and other features of the Studio, you can take advantage of the 30-day fully functional trial. This trial provides complete access to the extensive capabilities of the most robust edition of dbForge Studio for SQL Server, allowing you to evaluate its performance under a full workload.
