SQL-Express 2019: Restore Multiple .BAK Files as individual Databases

Question

I have to restore a SQL backup from someone external and have the following issue: Instead of making a nice single big file for the entire instance of SQL, I have a folder with 100+ individual Database Backups as .bak files.

First of all - since I usually only press one button to "wor"k with SQL (restore a DB) and then never touch it again - let me clarify what I mean by "databases" just so there is no confusion:

When I connect via SSMS to the SQL Instance there is a database folder. Inside this database folder I need every single .Bak file as a - what I presume is called - databse. So basically top level thing. Not sure if this is called database or Table, but I assume its database.

I already tried with ChatGPT and Deepseek (14B Modell) but the scripts dont work or dont make sense to me so Im weary of running them.

Main goal: I have Backup folder with: File1.bak file2.bak file3.bak (about 80 of these)

And I need to import&restore them as same Named Databases into my current SQL instance. So I need the Databases: File1 File2 File3 File4 etc.

And I'd prefer to not do this manually.

Here is what I got for now:

DECLARE @SQLServer AS VARCHAR(100) = 'SQLSERVER\SQLINSTACE';
DECLARE @DatabaseName VARCHAR(100);
DECLARE @FileName VARCHAR(255);

DECLARE backup_files CURSOR FOR
SELECT FileName 
FROM sys.system_file 
WHERE FileName LIKE '%.bak';

OPEN backup_files;

FETCH NEXT FROM backup_files INTO @FileName;

WHILE @@FETCH_STATUS = 0
BEGIN
    SET @DatabaseName = LEFT(@FileName, LEN(@FileName) - 4);

    EXEC ('RESTORE DATABASE [' + @DatabaseName + '] FROM DISK = ''' + @BackupPath + '\' + @FileName + ''' 
            WITH FILE = 1, 
                 MOVE N''LogicalDataFileName'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @DatabaseName + '.mdf'',
                 MOVE N''LogicalLogFileName'' TO ''C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\' + @DatabaseName + '.ldf'',
                 REPLACE');

    FETCH NEXT FROM backup_files INTO @FileName;
END;

CLOSE backup_files;
DEALLOCATE backup_files;

I have no idea about SQL, but it seems that this is considered black magic given how complicated it is, I ask for your help my wise wizards.

Thank you!

Charlieface · Accepted Answer · 2025-03-25 11:04:26Z

1

Do not use that ChatGPT script, there are many issues with it.

The best tool for this is probably DbaTools' free Powershell scripts.

You can use Restore-DbaDatabase for this, to restore an entire folder. It will work out the database names, file locations, and handle multiple restores on the same database.

$files = Get-ChildItem -Path 'c:\backups';
$files | Restore-DbaDatabase `
  -SqlInstance 'YourServer\YourInstance' `
  -WithReplace `
  -DestinationDataDirectory 'C:\Program Files\Microsoft SQL Server\MSSQL15.MSSQLSERVER\MSSQL\DATA\';

edited Mar 25 at 11:04

answered Mar 24 at 12:49

Charlieface

78.5k8 gold badges35 silver badges77 bronze badges

Sign up to request clarification or add additional context in comments.

1 Comment

asdfmoin Mar 25 at 7:16

Just for clarification: You use "files" (with "s") in the first line and "file" (without "s") in the second. Is that right? Im unfamiliar with DBATools.

Collectives™ on Stack Overflow

SQL-Express 2019: Restore Multiple .BAK Files as individual Databases

1 Answer 1

1 Comment

Your Answer

Hot Network Questions

Collectives™ on Stack Overflow

1 Answer 1

1 Comment

Your Answer

Sign up or log in

Post as a guest

Related