I have a database backup file with .bak extension and I want to restore/ import this file to my SQL Server instance using Jetbrains IDE, i.e. DataGrip, Rider, IntelliJ etc.
I have installed a plugin from JetBrains: SQL Server Backup And Restore but haven't been able to make it work. Please help.
Link: https://plugins.jetbrains.com/plugin/13913-sql-server-backup-and-restore.
You don't need any plugin to do this. Just follow the Microsoft Learn docs. I'll summarize the instructions here.
Get the backup file ready. For example, I downloaded AdventureWorks2022.bak file from here.
Put it inside backup folder. For eg: C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\ folder in Windows or /var/opt/mssql/backup/ in Linux.
Run below T-SQL command to restore the database:
For Windows
USE [master];
GO
RESTORE DATABASE [AdventureWorks2022]
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL16.MSSQLSERVER\MSSQL\Backup\AdventureWorks2022.bak'
WITH
FILE = 1,
NOUNLOAD,
STATS = 5;
GO
For Linux
USE [master];
GO
RESTORE DATABASE [AdventureWorks2022]
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2022.bak'
WITH
MOVE 'AdventureWorks2022' TO '/var/opt/mssql/data/AdventureWorks2022_Data.mdf',
MOVE 'AdventureWorks2022_log' TO '/var/opt/mssql/data/AdventureWorks2022_log.ldf',
FILE = 1,
NOUNLOAD,
STATS = 5;
GO
Done!
Run SQL server with -v flag. Note that I created /Users/ashk/Database/sqlserver2022 folder.
docker run -e "ACCEPT_EULA=Y" -e "MSSQL_SA_PASSWORD=VeryStr0ngP@ssw0rd" \
-p 1433:1433 --name sql2022 --hostname sql2022 \
-v /Users/ashk/Database/sqlserver2022:/var/opt/mssql \
-d \
mcr.microsoft.com/mssql/server:2022-latest
Create backup folder inside /Users/ashk/Database/sqlserver2022 folder.
Get the .bak file ready. For example, I downloaded AdventureWorks2022.bak file from here.
Put the .bak file inside /Users/ashk/Database/sqlserver2022/backup folder.
Go to Database window in your JetBrains IDE and connect to the database using connection string. For eg:
Server=localhost,1433;User Id=sa;Password=VeryStr0ngP@ssw0rd
Open Query Console
Run below T-SQL command to restore the database:
USE [master];
GO
RESTORE DATABASE [AdventureWorks2022]
FROM DISK = '/var/opt/mssql/backup/AdventureWorks2022.bak'
WITH
MOVE 'AdventureWorks2022' TO '/var/opt/mssql/data/AdventureWorks2022_Data.mdf',
MOVE 'AdventureWorks2022_log' TO '/var/opt/mssql/data/AdventureWorks2022_log.ldf',
FILE = 1,
NOUNLOAD,
STATS = 5;
GO
Click the tag (for eg: 1 of 5) next to the Db server name (for eg: @localhost) and select the Database you just added (for eg: AdventureWorks2022), expand it and select the schemas you want to load.
Done!
To restore from .bak file you need:
Select the data source where you intend to restore the backup
Choose .bak file and click OK
Specify database name and click OK
