- Veeam Support Knowledge Base
- How to apply a SQL script to Veeam Backup & Replication/Veeam Backup Enterprise Manager Database
How to apply a SQL script to Veeam Backup & Replication/Veeam Backup Enterprise Manager Database
Cheers for trusting us with the spot in your mailbox!
Now you’re less likely to miss what’s been brewing in our knowledge base with this weekly digest
Purpose
This article documents the procedure for applying a SQL script to a Microsoft SQL database or PostgreSQL Database. Specifically, this article is targeted at the scenario where a support engineer has provided a .sql script to modify the Veeam Backup & Replication or Veeam Backup Enterprise Manager configuration database.
Starting with Veeam Backup & Replication 12, the product now supports the PostgreSQL database engine for storing the configuration database. Please note that existing Veeam Backup & Replication deployments upgraded to version 12 will continue using the Microsoft SQL database engine until migrated to PostgreSQL.
Note: For information about applying SQL hotfixes to Veeam ONE, review: KB4414.
Solution
Microsoft SQL
Method 1: Run the Script From the Command Line
To run a SQL script from a command line, perform the following steps:
- Save the script as a *.sql file (e.g., script.sql) to the local disk.
- Make sure that no jobs are running.
- Run the following command:
Reference KB1471 for information about locating Veeam configuration databases.
sqlcmd -S <SqlServerName>\<SqlInstanceName> -d <SqlDatabaseName> -i PATHTOSCRIPT\script.sql -o c:\resetresult.txt -IMethod 2: Run the Script From SQL Server Management Studio
You may also run a SQL script from SQL Server Management Studio by right-clicking on the database, then selecting the “New query” option. Then, insert the text from the script file, and click Execute.
PostgreSQL
Method 1: Run the Script From the Command Line
To run a SQL script from a command line, perform the following steps:
- Save the script as a *.sql file (e.g., script.sql) to the local disk.
- Make sure that no jobs are running.
- Run the following command:
Reference KB1471 for information about locating Veeam configuration databases
psql -d <database_name> -U postgres -f path\to\script.sqlMethod 2: Run the Script From pgAdmin
You may also run a SQL script from pgAdmin by right-clicking on the database, then selecting the “Query Tool” option. Then, insert the text from the script file, and click ▶ to Execute.
Troubleshooting "SSPI authentication failed" PostgreSQL Error
The SSPI Authentication error most often occurs when the user account that executed the psql command is not mapped.
psql: error: connection to server at "localhost" (::1), port 5432 failed: FATAL: SSPI authentication failed for user "postgres"
Resolution:
- Navigate to the PostgreSQL log folder.
Default PostgreSQL 15 path: C:\Program Files\PostgreSQL\15\data\log\ - Sort by last modified, and open the latest logfile postgresql-yyyy-mm-dd_xxxxxx.log.
- Scroll to the end of the log file and begin scrolling up. Look for entries like this:
[user=postgres,db=VeeamBackup,app=[unknown]] LOG: no match in usermap "veeam" for user "postgres" authenticated as "pgadmin@VBR12" [user=postgres,db=VeeamBackup,app=[unknown]] FATAL: SSPI authentication failed for user "postgres"
- Take note of the account indicated in the error. In example above the account, pgadmin@VBR12, is listed at the very end of line 1.
- Open the mappings file C:\Program Files\PostgreSQL\15\data\pg_ident.conf
- Add a new line at the bottom of the file in the following format:
Replacing pgadmin@VBR12 with the account identified in the logs in step 4.
veeam pgadmin@VBR12 postgres
- Save the pg_ident.conf file, and rerun the psql command that failed.
To report a typo on this page, highlight the typo with your mouse and press CTRL + Enter.
Spelling error in text
Thank you!
Your feedback has been received and will be reviewed.
Oops! Something went wrong.
Please try again later.
You have selected too large block!
Please try select less.
KB Feedback/Suggestion
This form is only for KB Feedback/Suggestions, if you need help with the software open a support case
Thank you!
Your feedback has been received and will be reviewed.