I have to restore around 60 SQL databases of different sizes. I googled to find a script to restore all databases after each other , just picking it 1 by 1 from my folder. I was not that successful, probably because I am quite new to SQL etc.
It can be either done in powershell or sql command line, what I found for now was something like this:
RESTORE DATABASE [db1] FROM DISK = N'C:\folder\db1.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
RESTORE DATABASE [db2] FROM DISK = N'C:\folder\db2.bak' WITH FILE = 1, NOUNLOAD, STATS = 10
GO
So what I would do is get the filenames from a certain folder and replace "db1.bak" etc. with the names I just picked from my folder and then run the script.
I know that there must be a smarter way, or is it maybe possible to "load" many .bak files into SSMS so the program will run them after each other?
Script of Collet with some adaptations worked for me.
First enable xp_cmdshell by running the following SQL commands:
-- To allow advanced options to be changed.
EXEC sp_configure 'show advanced options', 1;
GO
-- To update the currently configured value for advanced options.
RECONFIGURE;
GO
-- To enable the feature.
EXEC sp_configure 'xp_cmdshell', 1;
GO
-- To update the currently configured value for this feature.
RECONFIGURE;
GO
Next, update the script below to restore the databases from the backup files. Replace C:\Backup\ with your local directory containing your .BAK files and replace C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\ with your SQL Server data directory. This script also assumes the .BAK file names match the database names, otherwise the script will likely throw errors about logical file names not matching.
DECLARE @FilesCmdshell TABLE (
outputCmd NVARCHAR (255)
)
DECLARE @FilesCmdshellCursor CURSOR
DECLARE @FilesCmdshellOutputCmd AS NVARCHAR(255)
INSERT INTO @FilesCmdshell (outputCmd) EXEC master.sys.xp_cmdshell 'dir /B C:\Backup\*.bak'
SET @FilesCmdshellCursor = CURSOR FOR SELECT outputCmd FROM @FilesCmdshell
OPEN @FilesCmdshellCursor
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
WHILE @@FETCH_STATUS = 0
BEGIN
DECLARE @sqlRestore NVARCHAR(MAX) = 'RESTORE DATABASE [' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '] FROM DISK = N''C:\Backup\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.bak'' WITH FILE = 1, MOVE N''' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + ''' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '.mdf'', MOVE N''' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '_log'' TO N''C:\Microsoft SQL Server\SQLINSTANCE\MSSQL\DATA\' + SUBSTRING(@FilesCmdshellOutputCmd, 0, CHARINDEX('.', @FilesCmdshellOutputCmd)) + '_log.ldf'', NOUNLOAD, STATS = 10'
EXEC(@sqlRestore)
FETCH NEXT FROM @FilesCmdshellCursor INTO @FilesCmdshellOutputCmd
END