Restore SQL Server .
BAK File and Connect to Power BI
This document outlines the complete process to automatically restore a daily .bak file from an
SFTP source or local folder into SQL Server and connect it to Power BI on a Windows machine. It
includes the setup, tools required, and a clear workflow using PowerShell and Task Scheduler.
Prerequisites
1. Windows 10/11 or Windows Server
2. SQL Server Installed (see installation guide below)
3. Power BI Desktop Installed (see installation guide below)
4. PowerShell (Windows PowerShell 5.1 or PowerShell Core)
5. Task Scheduler
6. Access to SFTP server and credentials
7. SQL Server Management Studio (SSMS) (optional but recommended)
Step-by-Step Workflow
1. SQL Server Installation Guide
If you haven't installed SQL Server yet, follow these steps:
Download SQL Server Express or Developer edition from:
[Link]
Run the installer and select Basic or Custom installation.
Ensure the Database Engine Services feature is selected.
Install SQL Server Management Studio (SSMS) separately from [Link]
During installation, choose Mixed Mode Authentication so you can log in with SQL and
Windows accounts.
Note down the instance name (e.g., MSSQLSERVER or SQLEXPRESS).
2. Power BI Installation Guide
To install Power BI Desktop:
Visit: [Link]
Download the installer and run it.
Follow the prompts and complete the installation.
After installing, launch Power BI Desktop and sign in with your Microsoft account (if
needed).
Daily Automation Workflow Overview
3. SFTP Download and File Management
Use a PowerShell script (executed by Task Scheduler) to:
o Connect to the SFTP server or local folder in windows.
o Download the .bak file or locate the .bak file in the folder.
o Save it in a specified folder (e.g., C:\DatabaseBackups\[Link]).
o Optional: archive or delete old backups.
4. SQL Server Database Restore with PowerShell
PowerShell plays a central role in automating the daily restore process:
Checking if the Database Exists
PowerShell connects to SQL Server using Invoke-Sqlcmd or a SQL connection.
It checks if the target database (e.g., MyBusinessDB) already exists.
If Database Exists
PowerShell issues a DROP DATABASE command to remove the existing database.
It ensures all connections are terminated using ALTER DATABASE [DBName] SET SINGLE_USER
WITH ROLLBACK IMMEDIATE.
Restore Database from .BAK
PowerShell runs a RESTORE DATABASE command using the .bak file.
The logical and physical file paths are specified explicitly.
It maps the .bak content to the new database location (e.g., C:\Program Files\Microsoft SQL
Server\[Link]\MSSQL\DATA\).
Automating with Task Scheduler
A Windows Task Scheduler job runs the PowerShell script daily at a specified time (e.g.,
7:00 AM).
This job performs all actions: download, drop (if needed), and restore.
Logs are written to a file (e.g., [Link]) to track issues.
5. Connect Power BI to SQL Server
Open Power BI Desktop.
Click Get Data → SQL Server.
Enter the SQL Server instance name (e.g., localhost\SQLEXPRESS).
Enter the database name (e.g., MyBusinessDB).
Use Import Mode for performance or DirectQuery for real-time.
Load the necessary tables or views.
Build your report and save the .pbix file.
Optional: Refresh Automatically
Use Power BI Gateway and Power BI Service (Pro license required) to schedule
refreshes.
Or, open Power BI manually and refresh data after the restore completes.
6. Summary of Automation Workflow
1. PowerShell script triggered daily by Task Scheduler.
2. PowerShell connects to SFTP → downloads .bak file.
3. PowerShell checks if SQL database exists:
o If yes: drop the database.
o If no: proceed to restore.
4. PowerShell restores the database from .bak file.
5. Power BI connects to the restored database.
6. Reports are refreshed manually or via Power BI Service.
Daily Automation Workflow Diagram
