Restore multiple SQL Server .bak Files

Question

I have a folder with multiple .bak files of sql server.

I want to restore them to sql server. How can i do a script so that all backuped files from that folder can be restored at once.

Answer 1

1. I guess databases name is not written into .bak file. No idea how to retreive by script. You may use file name or any other temp name for restored databases and after restore rename it under SSMS.
2. Be aware that .bak files may contain DIFF backups. In case of DIFF backup, it won't work just like that - one by one.
3. If folder contains FULL BACKUP and TRANSACTION LOG backups it's always the best idea to start restoring database using backup history in SSMS.

Let's talk about restoring multiple databases from multiple BAK files. I think GAWK may help. It's not the MS way, but may be the fastest way.

Do you know GAWK?

Let you build input file with file names:

dir *.bak > input.txt

Write GAWK program into file program.gawk.txt:

BEGIN {
printf "-- My auto-generated script \n"
}
{
printf "RESTORE DATABASE [substr($1,1,length($1)-4)] FROM DISK = N'$1 \n'"
printf "GO \n\n"
}
END {
printf "-- End of script"
}

Experiment with substr to generate temp name for restored database based on BAK file name. Not sure if substr expression is all right - please check yourself.

• $1 will be changed to file name from input.txt

• substr($1,1,length($1)-4) is for cutting ".BAK" from filename

Build output script by:

gawk --file=program.gawk.txt input.txt > my_script.sql

..and review it.

Maybe it's possible to do this powershell-way. Don't know.

Download GAWK from: http://gnuwin32.sourceforge.net/packages/gawk.htm

Answer 2

RESTORE DATABASE [db1] FROM  DISK = N'C:\folder\db1.bak' WITH  FILE = 1,  MOVE N'DB_Data' TO N'C:\folder\db2.mdf',  MOVE N'DB_log' TO N'C:\folder\db1.LDF',  NOUNLOAD,  STATS = 10
GO
RESTORE DATABASE [db2] FROM  DISK = N'C:\folder\db2.bak' WITH  FILE = 1,  MOVE N'DB_Data' TO N'C:\folder\db2.mdf',  MOVE N'DB_Log' TO N'C:\folder\db2.LDF',  NOUNLOAD,  STATS = 10
GO

Try something like this.