Data is the key to your organization’s future, but if it’s outdated, irrelevant, or hidden then it’s no good. Maintenance and administration of databases takes a lot of work. As database administrators, we often tend to automate most of these repetitive tasks. A database refresh is one of the most common tasks performed by most of the administrators as part of their daily routine.
Today, database refreshes are quite frequent because of Continuous Integration (CI) and Continuous Deployment (CD). In most of the cases, testing requires a separate but current production dataset to ensure the validity of the desired result.
In today’s world, we rely more on third party tools to perform a Backup and Restore of databases. With many advanced tools and techniques, this is a pretty straight forward approach. Think of the real-world scenarios where customers rely on the native SQL Tools and techniques. Creating an automated database refresh tasks regularly will have a huge impact on the quality of the release management cycles and would save a lot of time for the database administrators.
There are many ways to automate this, some of which are:
- SQLCMD
- PowerShell
- SqlPackage
In this article, we about the following:
- Details of Sqlcmd
- The use of the cross-platform tool, Sqlpackage
- Automation using Windows batch scripting
- And more…
Using sqlcmd provides flexible ways to execute T-SQLs and SQL script files. As its available on Linux, Windows and Mac, this command line utility plays a vital role in managing the database restore operations in a DevOps pipeline.
PowerShell script to automatically create a bacpac file and restore the database using the created bacpac, using SqlPackage.exe
This section deals with the preparation of a PowerShell script to automate database restoration using the SqlPackage tool which is part of the SQL Server Data Tools suite.
The first step is to prepare and set the environment variables. The SqlPackage tool is installed under C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin. The script uses sqlcmd and SqlPackage tool; make sure that the path variable is updated accordingly.
- The input parameter section lists the source, target SQL databases instance and folder for extracting the bacpac file
- Create the bacpac file using export action type
- Select the latest bacpac file for further database restoration action
- Drop the destination database using the sqlcmd command
- Restore he database using import action type.
First, let’s work on the getting and setting the environment variables. Pull the details of the environment variables into a local variable. The output shows if the environment variable contains the path or not (if not, the path is added to the PATH variable).
|
1 2 3 4 5 6 7 8 9 |
$PathVariables=$env:Path $PathVariables IF (-not $PathVariables.Contains( "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin")) { write-host "SQLPackage.exe path is not found, Update the environment variable" $env:Path = $env:Path + ";C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;" } |
Defining the required input parameters
- Backup directory
- Source database name
- Source server name
- Target server name (in this case it’s a Linux machine)
|
1 2 3 4 5 6 |
$BackupDirectory="c:\SQLShackDemo\" $DatabaseName="ApexSQLBackup" #Source SQL Server name $SourceServerName="HQDBT01" #target instance is SQL Server on Linux Machine $TargetserverName="10.2.6.51" |
Prepare the target filename
In most of the case, defining a backup file is a standard practice as one backup file may not be enough to safeguard the entire system. So, a backup file name should be more meaningful with timestamps attached to it, and it should speak a little about the data. It includes the following;
- Filename
- Extension
- Timestamp yyyyMMddhhmmss format
|
1 2 3 4 5 6 7 8 9 |
$dirName = [io.path]::GetDirectoryName($BackupDirectory) #set the filename, the database should be a part of the filename $filename = "ApexSQLBackup" #extension must be bacpac $ext = "bacpac" #target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds $TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext" #print the full path of the target file path $TargetFilePath |
Run the SqlPackage tool
Now, run SqlPackage.exe to export the ApexSQLBackup database. This step will create the BACPAC file for an existing database. The following sqlpackage.exe commands are used to export the database. These commands are compatible with all the databases on a local instance or remote instance of SQL Server, or even an Azure SQL Databases.
sqlpackage.exe /a:Export /ssn:<SourceServerName> /sdn:<SourceDatabaseName> /su:<sourceUsername> /sp:<SourcePassword> /tf:<targetFileName>
/Action – /a
specify the action to be performed. In this case it’s Export, used to export the database into a bacpac file
/SourceServerName -/ssn
defines the name of the server hosting the database. It can be an Azure database URI or instance name or named instance
/SourceDatabaseName (sdn)
defines the name of the existing source database
/SourceUser (su)
defines the SQL Server login username
/SourcePassword (sp)
defines the password; In this case, we’re exporting from a trusted SQL instance on Windows. For trusted connections, ignore the /su and /sp parameters
In the following example, the short form is used
|
1 |
SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$DatabaseName /tf:$TargetFilePath |
Now, we’ll see how to get the latest bacpac file. In this case, selecting an item is done using Get-Childitem cmdlet. The specific files are listed using pattern matching. The output is then piped to get a sorted list in a descending order and then selected the first object of the list into the resultset.
|
1 2 3 |
$NewestBacPacFile = Get-ChildItem -Path $dirName\$filename*.$ext | Sort-Object LastAccessTime -Descending | Select-Object -First 1 $file="$NewestBacPacFile" $file |
There are some caveats to keep in mind while importing the data. The database must either not exist or be completely empty. The following SQL is used to check for the database existence on the target SQL instance. On its existence, the database is dropped using sqlcmd.
|
1 2 3 4 5 |
$DropSQL= @" IF EXISTS (SELECT name FROM sys.databases WHERE name = '$DatabaseName') DROP DATABASE $DatabaseName "@ SQLCMD -S $TargetserverName -U SA -P thanVitha@2015 -Q $DropSQL |
Import the ApexSQLBackup database
To import the database, you can run the following command. These commands work for databases on a local instance or remote instance of SQL Server or even an Azure SQL Database instance:
sqlpackage.exe /a:Import /sf:<SourcefileName> /tsn:<TargetServerName> /tdn:<targetdatabaseName> /su:<sourceUsername> /sp:<SourcePassword>
In this case, we are connecting to a Linux instance.
Listed below are the arguments we’re using for importing the bacpac file. In my example above, I’m using the short form, you can use either long or short forms that suit your requirements or understanding
/Action – /a
the action type, in this case its import, Importing database into Linux SQL instance
/SourceFileName -/sf
the path of the latest bacpac file of the database.
/TargetDatabaseName -/sdn
the name of the target database
/SourceUser (su)
login user name
/SourcePassword (sp)
the password; In this case its uses SQL authentication to connect to SQL on Linux. The /su and /sp parameters are in used in this place.
|
1 |
SqlPackage.exe /a:Import /sf:$file /tsn:$TargetserverName /tdn:$DatabaseName /tu:SA /tp:thanVitha@2015 |
Output
Copy and save the Appendix A content into sqlpackage.ps1 file
Automated Windows Batch script to create bacpac file and restoring the database using the created bacpac using SqlPackage.exe
A batch file is a collection of commands or executables, run within the command shell (CMD). It’s similar to a bash script in Unix/Linux.
The walkthrough of the batch file is pretty straight forward and it is no different from the PowerShell script that we just saw. I will discuss only the required pieces of the batch file in the next section.
Let’s save the below following script code as a batch file. The filename has an extension of .bat or .cmd.
I would like to take you through the for loop of the code. For example,
FOR /F [“options”] %%parameter IN (‘command to process’) DO command. Here are the steps we’d follow:
- Take a dataset
- Make a FOR Parameter %%G equal to some part of that data
- Execute a command (optionally using the parameters as part of the command).
- Repeat for each data item
- If you are using the FOR command at the command line rather than in a batch program, use a single ‘%’ sign: %F
Save the batch script from Appendix B as a .bat file:
That’s it!
Wrapping up
In this article, we looked at how a PowerShell or a Batch script can be used to automate data restoration using a bacpac file using sqlpackage.exe. This tool is one of the easiest tools that can be used to refresh the database. The code can be further simplified and automated for various other database refresh needs.
Table of contents
References
Appendix (A)
PowerShell script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 57 58 59 60 61 62 63 64 65 66 67 68 |
# Get the path variables details into the variable $PathVariables=$env:Path #Print the path variable $PathVariables #Check the path existence of the SqlPackage.exe and print its status IF (-not $PathVariables.Contains( "C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin")) { write-host "SQLPackage.exe path is not found, Update the environment variable" $env:Path = $env:Path + ";C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin;" } #Get the list and ensure the update is made on the environment variable #$PathVariables=$env:Path #print the variable data #$PathVariables #the input parameters $BackupDirectory="c:\SQLShackDemo\" $DatabaseName="ApexSQLBackup" #Source server name is SQL Server $SourceServerName="HQDBT01" #target instance is SQL Server on Linux Machine $TargetserverName="10.2.6.51" #Prepare the targe filename $dirName = [io.path]::GetDirectoryName($BackupDirectory) #set the filename, the database should be a part of the filename $filename = "ApexSQLBackup" #extension must be bacpac $ext = "bacpac" #target filepath is a combination of the directory and filename appended with year month and day hour minutes and seconds $TargetFilePath = "$dirName\$filename-$(get-date -f yyyyMMddhhmmss).$ext" #print the full path of the target file path $TargetFilePath #Run the SqlPackage tool to export the database SqlPackage.exe /a:Export /ssn:$SourceServerName /sdn:$DatabaseName /tf:$TargetFilePath #Get the latest file in a given directory $NewestBacPacFile = Get-ChildItem -Path $dirName\$filename*.$ext | Sort-Object LastAccessTime -Descending | Select-Object -First 1 #print the latest bacfile name depending the name of the database $file="$NewestBacPacFile" $FILE #check or drop for the database existence on the target SQL instance $DROPDATABASESQL= @" IF EXISTS (SELECT * FROM [sys].[databases] WHERE [name] = '$DatabaseName') DROP DATABASE $DatabaseName "@ #Using sqlcmd, the dropdatabase sql is executed on the target instance. SQLCMD -S $TargetserverName -U SA -P thanVitha@2015 -Q $DROPDATABASESQL #Import the ApexSQLBackup database using sqlpackage.exe SqlPackage.exe /a:Import /sf:$file /tsn:$TargetserverName /tdn:$DatabaseName /tu:SA /tp:thanVitha@2015 |
Appendix (B)
Batch script
|
1 2 3 4 5 6 7 8 9 10 11 12 13 14 15 16 17 18 19 20 21 22 23 24 25 26 27 28 29 30 31 32 33 34 35 36 37 38 39 40 41 42 43 44 45 46 47 48 49 50 51 52 53 54 55 56 |
@echo OFF SETLOCAL REM The installation directory where SSDT tools SET SQLDIR=C:\Program Files (x86)\Microsoft SQL Server\140\DAC\bin\ SET SQLPACKAGE="%SQLDIR%SqlPackage.exe" REM Specify the path of the bacpac files SET DATABASEDIR=c:\SQLShackDemo\ REM The database for export and import SET DATABASENAME=ApexSQLBackup REM The SQL Server Source instance SET SOURCESERVERNAME=HQDBT01 REM The SQL Server target instance SET TARGETSERVERNAME=10.2.6.51 REM Get the datetime in a format that can go in a filename. For /f "tokens=2-4 delims=/ " %%a in ("%date%") do (set mydate=%%c-%%a-%%b) For /f "tokens=1-2 delims=/:" %%a in ("%TIME%") do (set mytime=%%a%%b) echo %mydate%_%mytime% REM Export the database %SQLPACKAGE% /a:Export /ssn:%SOURCESERVERNAME% /sdn:%DATABASENAME% /tf:%DATABASEDIR%%DATABASENAME%_%mydate%_%mytime%.bacpac REM Drop the target database in order to make import command to work succesfully SET DropSQL=IF EXISTS (SELECT name FROM sys.databases WHERE name = '%DATABASENAME%') DROP DATABASE [%DATABASENAME%]; REM Find the latest BACPAC file using the pattern matching technique FOR /F "tokens=*" %%d IN ('DIR %DATABASEDIR%%DATABASENAME%*.bacpac /B /OD /A-D') DO SET BACPACNAME=%%d IF "%BACPACNAME%"=="" GOTO :bacpacfilenotfound SET DATABASEFILE=%DATABASEDIR%%BACPACNAME% SQLCMD -S %TARGETSERVERNAME% -Q "%DropSQL%" -b -U SA -P thanVitha@2015 IF %ERRORLEVEL% NEQ 0 GOTO :error %SQLPACKAGE% /a:Import /sf:%DATABASEFILE% /tdn:%DATABASENAME% /tsn:%TARGETSERVERNAME% /tu:sa /tp:thanVitha@2015 IF %ERRORLEVEL% NEQ 0 GOTO :ERRORBLOCK GOTO :complete :bacpacnotfound ECHO bacpac file doesn't exists EXIT /B 1 :ERRORBLOCK ECHO import failure EXIT /B 1 :complete ENDLOCAL |
My specialty lies in designing & implementing High availability solutions and cross-platform DB Migration. The technologies currently working on are SQL Server, PowerShell, Oracle and MongoDB.
View all posts by Prashanth Jayaram
- Stairway to SQL essentials - April 7, 2021
- A quick overview of database audit in SQL - January 28, 2021
- How to set up Azure Data Sync between Azure SQL databases and on-premises SQL Server - January 20, 2021