Generate T-SQL for restoring multiple databases

Let’s say we are migrating the database server to a new host. After SQL Server has been successfully installed on the new host, we need to migrate the databases. Let’s say we need to restore well over 100 databases. A better way to do this is to generate the T-SQL restore scripts from the backup files, including the transaction logs.

The following assumptions are made when creating the scripts:

  • Full backup of all user databases daily
  • Transaction log backup every 12 hours at a specified hour
  • A subfolder is created for each database to store the backup files (.bak and .trn)
  • File extensions are:
    • .bak – full backup
    • .trn – transaction log

Here’s the script that will generate the required T-SQL script to restore all the user databases from the full backup and transaction log files. Plesae note that this script is based on someone else’s creation. I can’t remember who it was. But full credit to him. I have adapted his script for my purposes.

USE Master;
GO
-- turn off (x row(s) affected) messages
SET NOCOUNT ON;

DECLARE @BasePath varchar(1000);
DECLARE @Path varchar(1000);
DECLARE @FullPath varchar(2000);
DECLARE @Id int;
DECLARE @todayDate varchar(20);
DECLARE @yesterdayDate varchar(20);
DECLARE @fName varchar (500);
DECLARE @fExt varchar(10);
DECLARE @dbName varchar(200);
DECLARE @cmd varchar(500);

-- set backup file's date in the format yyyy_mm_dd. we restore yesterday's backups
SET @yesterdayDate = replace(convert(NVARCHAR, dateadd(day,-1, getdate()), 111), '/', '_');

--This is the backup parent folder. Can use drive-folder or UNC notation
SET @BasePath = '<enter-your-full-db-backup-path-here>';

--Create a temp table to hold the results. remove the table if exists
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;

CREATE TABLE #DirectoryTree (
   id int IDENTITY(1,1)
   ,fullpath varchar(2000)
   ,fileName nvarchar(512)
   ,fileExt varchar(10)
   ,dbName varchar (200)
   ,depth int
   ,isfile bit
);

--Create a clustered index to keep everything in order.
ALTER TABLE #DirectoryTree
   ADD CONSTRAINT PK_DirectoryTree PRIMARY KEY CLUSTERED (id);

--Populate the table using the initial base path.
INSERT #DirectoryTree (fileName,depth,isfile)
EXEC master.sys.xp_dirtree @BasePath,1,1;

UPDATE #DirectoryTree SET fullpath = @BasePath;

--Loop through the table as long as there are still folders to process.
WHILE EXISTS (SELECT id FROM #DirectoryTree WHERE isfile = 0)
BEGIN
   --Select the first row that is a folder.
   SELECT TOP (1)
      @Id = id
      ,@FullPath = fullpath
      ,@Path = @BasePath + '\' + fileName FROM #DirectoryTree WHERE isfile = 0;

   IF @FullPath = @Path
   BEGIN
      --Do this section if the we are still in the same folder.
      INSERT #DirectoryTree (fileName,depth,isfile)
      EXEC master.sys.xp_dirtree @Path,1,1;

      UPDATE #DirectoryTree
         SET fullpath = @Path,
         fileExt = RIGHT(fileName,3)
      WHERE fullpath IS NULL;

      --Delete the processed folder.
      DELETE FROM #DirectoryTree WHERE id = @Id;
   END
   ELSE
   BEGIN
      --Do this section if we need to jump down into another subfolder.
      SET @BasePath = @FullPath;

      --Select the first row that is a folder.
      SELECT TOP (1)
         @Id = id
         ,@FullPath = fullpath
         ,@Path = @BasePath + '\' + fileName
         ,@dbName = fileName
      FROM #DirectoryTree WHERE isfile = 0;

      INSERT #DirectoryTree (fileName,depth,isfile)
      EXEC master.sys.xp_dirtree @Path,1,1;

      UPDATE #DirectoryTree
         SET fullpath = @Path,
         fileExt = RIGHT(fileName,3),
         dbName = @dbName
      WHERE fullpath IS NULL;

      --Delete the processed folder.
      DELETE FROM #DirectoryTree WHERE id = @Id;
   END
END

-- create cursor to loop through the backup files
DECLARE backupFiles CURSOR FOR
SELECT fileName,fileExt,fullpath,dbName
FROM #DirectoryTree
WHERE fileName LIKE '%' + @yesterdayDate + '%'

OPEN backupFiles

-- Loop through all the database backup files (.bak and .trn) from the basepath
FETCH NEXT FROM backupFiles INTO @fName, @fExt, @fullpath, @dbName

WHILE @@FETCH_STATUS = 0
BEGIN
   SET @cmd = '';
   IF (@fExt = 'bak')
   BEGIN
      IF (@dbName != 'model' and @dbName != 'master' and @dbName != 'msdb')
      BEGIN
         SET @cmd = 'RESTORE DATABASE [' + @dbName + ']' + CHAR(13)+CHAR(10) + ' FROM DISK = ''' + @fullpath + '\' + @fName + '''' + CHAR(13)+CHAR(10) + ' WITH NORECOVERY;';
      END
   END
   ELSE IF (@fExt = 'trn')
   BEGIN
      IF (@dbName != 'model')
      BEGIN
         SET @cmd = 'RESTORE LOG [' + @dbName + ']' + CHAR(13)+CHAR(10) + ' FROM DISK = ''' + @fullpath + '\' + @fName + '''' + CHAR(13)+CHAR(10) + ' WITH RECOVERY;';
      END
   END
   PRINT @cmd
   FETCH NEXT FROM backupFiles INTO @fName, @fExt, @fullpath, @dbName
END

CLOSE backupFiles
DEALLOCATE backupFiles

--Clean-up temporary table.
IF OBJECT_ID('tempdb..#DirectoryTree') IS NOT NULL
   DROP TABLE #DirectoryTree;
GO

Note:

If databases already exist on the new host (i.e. restoring databases on subsequent run, then use the WITH REPLACE option in the restore script for the .bak files.

Click on the Messages tab to see the generated script. Copy and paste the script into a new screen to execute and restore the databases on the new host.

Here’s a sample of what the generated scripts looks like:

How to restore the msdb database

To ensure a successful restore of the msdb database, follow these two simple major steps:

  • Make preparations for the restore
  • Restoring the msdb database

Here are some information about the msdb database before we start:

The msdb database is used by SQL Server Agent for scheduling alerts and jobs and by other features such as SQL Server Management Studio, Service Broker and Database Mail.

By default, msdb uses the simple recovery model. If you use the backup and restore history tables, we recommend that you use the full recovery model for msdb. For more information, see Recovery Models (SQL Server). Notice that when SQL Server is installed or upgraded and whenever Setup.exe is used to rebuild the system databases, the recovery model of msdb is automatically set to simple.

1 Make preparations for the restore

To prepare for a smooth restore we need carry out these tasks:

  • Get the version of the destination server
  • Get the version of source server on which the backup was created
  • Match the versions for the source and destination servers
  • Ensure exclusive access to the database

1.1 Get the version of the destination server

The versions of SQL Server need to be the same for the source and destination when restoring the msdb or model database. If the versions for the source and destination servers do not match then the restore will fail.

Use one of these methods:

SSMS Object Explorer

Connect to a SQL Server instance through SSMS and find the SQL Server version in object explorer as shown below.

SERVERPROPERTY system function

The SERVERPROPERTY system function can be used for retrieving SQL Server version information to get edition and service pack information.

Using @@Version

The SQL Server version can also be retrieved by using @@Version. It can be used in a simple select statement as shown below

1.2 Get the version of source server on which the backup was created

The best way to do this is to get the information from the backup file itself as shown below using the RESTORE HEADERONLY command.


RESTORE HEADERONLY
FROM DISK = '<backup-location>\<msdb-backup-name>.bak'
GO

1.3 Match the versions for the source and destination servers

In our case we have version 10.50.1617 which matches both the source and destination servers. If this is not the case then apply or remove service packs on the destination server to match the source server. Once both versions are the same then we are ready to go further with the process.

1.4 Ensure exclusive access to the database

Exclusive access for the database is required just like restoring any database, but for the msdb it is slightly more demanding. In the case of msdb we also have to consider the SQL Agent service. If the SQL Server agent service is running then exclusive access can not be achieved.

So for msdb we need to stop the SQL Agent service to make sure we can get exclusive access. This can be done by right clicking on SQL Server Agent and selecting Stop.

2 Restoring the msdb database

Here are the steps for restoring the msdb database:

  • Stop the SQL Server Agent
  • Restore the msdb database
  • Start the SQL Server Agent

2.1 Stop the SQL Server Agent

You can either stop the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Stop

2.2 Restore the msdb database

To restore the msdb database do the following:

  • Launch SQL Server Management Studio (SSMS) or activate SQLCMD from a Command Prompt
  • Issue the following:
    RESTORE DATABASE [msdb]
    FROM
    DISK = '<backup-location>\<msdb-backup-file-name>.bak'
    WITH REPLACE;
    GO
    
    -- for example
    RESTORE DATABASE [msdb]
    FROM
    DISK = 'C:\Programs\SQL Server\MSSQL\Backup\msdb_backup.bak'
    WITH REPLACE;
    GO
    
  • Click Execute (if using SSMS) or hit Enter (if using SQLCMD)

2.3 Start the SQL Server Agent

You can either start the SQL Server Agent from SQL Server Configuration Manager or Control Panel > Services.

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server Agent (<instance-name>) and select Start

Congratulations! You should’ve already successfully restored the msdb database.

How to restore the master database

Here are the steps for restoring the SQL Server master database:

  • Set MS SQL Server to Single User Mode
  • Restore master database via SQLCMD
  • Reset MS SQL Server to Multi User Mode

Before we start, here’s some important information about the master database:

The master database records all the system-level information for a SQL Server system. This includes instance-wide metadata such as logon accounts, endpoints, linked servers, and system configuration settings. In SQL Server, system objects are no longer stored in the master database; instead, they are stored in the Resource database.

Also, master is the database that records the existence of all other databases and the location of those database files and records the initialization information for SQL Server. Therefore, SQL Server cannot start if the master database is unavailable.

Set MS SQL Server to Single User Mode

To set MS SQL Server to single user mode, do the following:

  • Launch SQL Server Configuration Manager from the Programs menu
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Append -m; to the above entry. That is you should now have something like this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

Restore master database via SQLCMD

To restore the master database, do the following:

  • Open a Command Prompt as an administrator from the Programs menu
  • At the prompt, type SQLCMD and press Enter
  • Then type
    RESTORE DATABASE master FROM DISK = ‘backup-path\master.bak’ WITH REPLACE;
  • Prese enter and type: GO

Reset MS SQL Server to Multi User Mode

After you have successfully restore the master database, use these steps to set MS SQL Server back to multi user mode:

  • Return to the SQL Server Configuration Manager dialogue screen that you had open previously
  • Click SQL Server Services
  • Right click SQL Server (<instance-name>) and select Properties
  • Click the Advanced tab and scroll to see Startup Parameters. You will see something similar to this:
    -m;-dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Remove -m; from the above entry. That is, you should now have something like this now: -dC:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\master.mdf
  • Restart SQL Server (<instance-name>)

That’s it! You’ve just successfully restore the master database!

Database msdb cannot be opened and marked as suspect

If you see the following message when trying to open a database in SQL Server Management Studio:

Database ‘msdb’ cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error:926)

Then you need to replace these two files from a backup:

  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBData.mdf
  • C:\<sql-server-install-root>\MSSQL10_50.MSSQLSERVER\MSSQ\DATA\MSDBLog.ldf

To do so, follow these steps:

  • Stop the MSSQLSERVER service
    • Either open Services
      • Find SQL Server (MSSQLSERVER)
      • Right click and click stop
    • Or use SQL Server Management Studio (SSMS)
      • Right the database icon and select Stop
  • Once MSSQLSERVER service has stopped, copy and replace the following files in the MSSQL\DATA\ folder:
    • MSDBData.mdf
    • MSDBLog.ldf

If the above files are backups of the current machine’s installation, then Error:926 is now fixed, and no further actions are needed.

However if no backup for the current installation was found, and a backup from another computer was used which has a different install path, then the following error will be shown:

Daily Backup – User Databases.Subplan_1′ cannot be run because the SSIS subsystem failed to load.  The job has been suspended.

Running this query:

SELECT * FROM
msdb.dbo.syssubsystems

will show that the path to the subsystem DLL are incorrect (if the current SQL Server installation is on the D drive but current records points to a different drive or location).

To fix the Job has been suspended error (due to incorrect DLL path), do the following:

  • Delete the existing entries:
    DELETE FROM msdb.dbo.syssubsystems
  • Update the table with new rows pointing to the correct location of the DLLs by:
    EXEC msdb.dbo.sp_verify_subsystems 1

How to Retrieve SQL Server Product Keys

This quick tip will come in handy when you need to find out the product keys for an installed SQL Server if that information was not recorded when it was initially installed.

Please note that this PowerShell module and its corresponding Get-SqlServerKeys command only works for MS SQL Server 2005 – 2014. It is currently not compatible with MS SQL Server 2016.

Here are the steps  you need to carry out from a desktop computer running Windows 7 or above to retrieve the SQL Server product keys.

1 Confirm PowerShell version

2 Download and install PowerShell 3.0 if required

If you have an older PowerShell version (e.g. version 1 or 2), then upgrade it to version 3. To do so, you will need to download and install Windows Management Framework 3.0 which also contains PowerShell 3.0 from this website:

https://www.microsoft.com/en-au/download/details.aspx?id=34595

3 Download the PowerShell module for retrieving the product keys

Once you’ve successfully upgraded to PowerShell version 3, please head over to this website to download the PowerShell module for retrieving SQL Server product keys:

https://gallery.technet.microsoft.com/scriptcenter/Get-SQL-Server-Product-4b5bf4f8/view/Discussions#content

4 Check Execution Policy

Before you can import and execute the PowerShell command to retrieve the SQL Server product keys, you will need to check the desktop computer’s execution policy.

If the execution policy is set to Restricted (which is the default), then temporary set it to Unrestricted. Import the PowerShell module you download in step 3, execute the command to retrieve the SQL Server product keys and then set it back to the default Restricted execution policy, that is:

  PS > Get-ExecutionPolicy
  PS > Set-ExecutionPolicy -ExecutionPolicy Unrestricted
    [Yes] Y

5 Import the PowerShell module for retrieving the product keys

Once the execution policy restriction has been lifted (i.e. temporary set to unrestricted), import the PowerShell module you download in step 3 so that the command Get-SqlServerKeys is available in the current session.

  PS > Import-Module .\Get-SqlServerKeys.psm1
    Run Once [R]

6 Run the Get-SqlServerKeys command to retrieve the product keys

Execute the Get-SqlServerKeys command to retrieve the SQL Server product keys. To retrieve SQL Server product remotely (i.e. those on a remote server), use the Server parameter, i.e.

  PS > Get-SqlServerKeys -Servers <list-of-remote-server-fqdn-or-ip-addresses>

The Servers parameter takes a list of server’s full qualify domain names (or IP addresses). If you’re on the actual server (i.e. not issuing the command from your client desktop computer), then the Servers parameter can be omitted, i.e.

  PS > Get-SqlServerKeys

Here are the commands:

And the returned information:

It will list all the SQL Server instances if there are multiple instances on the server. Please note the above assume you’re running the command from the actual server, and not remotely. If running from a client desktop computer, you will need to add the -Servers parameter and its list of servers names or IP addresses.

7 Set the Execution Policy to Restricted

Once you’re done, please don’t forget to set the execution policy back to the default Restricted.

  PS > Set-ExecutionPolicy -ExecutionPolicy Restricted