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:

Setting Up an MS Access 32 bit Data Source in ColdFusion

If you see this error when verifying an MS Access data source in ColdFusion Administrator:

Connection verification failed for data source: <data-source-name> java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

The root cause was that: java.sql.SQLException: [Macromedia][SequeLink JDBC Driver][SequeLink Server]The specified data source is not defined.

Then you need to follow these steps to properly set up an MS Access 32 bit Data Source on a 64 bit ColdFusion 10 installation running on Windows 2008 SP2 (because the add new data source function in ColdFusion 10 Admin doesn’t do correctly):

  1. Preparations
  2. Set up the 32 bit System DSN
  3. Create the Data Source in ColdFusion Administrator
  4. Verify Connection to Data Source

1 Preparations

Before we get started, here are some tips to ensure a successful setup:

  • If the Microsoft Access driver is not available, then please download and install it:
    • Copy this file to the server where ColdFusion is installed: AccessDatabaseEngine_x64.exe
    • Launch to install the driver using default location and settings
  • If the MS Access data source exists in ColdFusion Administrator, please remove it before creating it via the 32 bit ODBC Data Source Administrator
  • You will see following errors when the db subfolder in the ColdFusion installation is missing:
    • If data source deletion in ColdFusion Administrator results in a 500 Internal Server Error
    • java.io.FileNotFoundExcetption and file operation write error:
      An error occurred when performing a file operation WRITE on file C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp
      The cause of this exception was: java.io.FileNotFoundExcetption: C:\ColdFusion10\<instance-name>\db\slserver54\admin\<data-source-name>.inp (The system cannot find the path specified).
    • If the db folder is missing, then copy the db folder into C:\ColdFusion10\<instance-name>. At the very least, you will need this subfolder and its contents: C:\ColdFusion10\<instance-name>\db\slserver54
    • If you have multiple ColdFusion instances, then a good place to look for a copy of the db folder is under the default cfusion instance, i.e. C:\ColdFusion10\cfusion\

2 Set up the 32 bit System DSN

To setup the 32 bit MS Access System DSN, follow these steps:

  • Log on to the server where ColdFusion is installed
  • Browse to C:\Windows\SysWOW64 and locate the 32 bit ODBC Data Source Administrator, i.e. odbcad32.exe
  • Right click this file and launch it as an administrator
  • In the ODBC Data Source Administrator dialogue, make the following selections:
    • Select the System DSN tab and click Add…
    • Select Microsoft Access Driver (*.mdb) from the drivers list and click Finish
    • In the Data Source Name input box, enter the exact data source name to be used in the ColdFusion Administrator (the name here must match)
    • Under Database, click Select… and browse to select the Microsoft Access database file, i.e. database-name.mdb
    • Click OK to close the dialogue
    • You should now see this new data source in the System DSN list
  • Confirm an entry has been added to the registry in the 32 bit node as follows:
    • Click Start and type regedit in the search box and then press Enter
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > Wow6432Node > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here

3 Create the Data Source in ColdFusion Administrator

Follow these steps to create the Microsoft Access data source in the ColdFusion Administrator:

  • Log on to the ColdFusion Administrator
  • Click Data Sources under the Data & Services section on the left pane
  • Check to see if this data source already exists. If it does, and there isn’t a registry entry for it, then delete it first before continuing.
  • In the Add New Data Source section at the top, enter and select the following:
    • Data Source Name (this must be the same name used when creating the 32 bit System DSN)
    • Driver: select Microsoft Access
    • Click Add
  • For Database File, enter the full path and file name of the Microsoft Access database (or browse to select it from the file system)
  • Leave Use Default User Name checked
  • Click Show Advanced Settings and make the following changes:
    • For Allowed SQL, de-select the following:
      • CREATE
      • DROP
      • ALTER
      • GRANT
      • REVOKE
    • Tick Enable connection validation
  • Click Submit to create this new data source
  • You will see the following error messages:
    Unable to update the NT registry.
    Variable DRIVERPATH is undefined.
  • Ignore these error messages. An entry in the registry would have been created.
  • To confirm an entry has been added to the registry in the 64 bit node, do this:
    • Click Start and type regedit in the search box and press Enter (if not already opened)
    • Browse to HKEY_LOCAL_MACHINE > SOFTWARE > ODBC
    • Expand ODBC.INI node and you should see the new data source listed here
  • Return to the ColdFusion Administrator and click Data Sources under the Data & Services section on the left pane
  • Find the new data source that you have just created, and click the tick icon to the left of the data source name to verify it.
  • You should see OK in the status column (right most) if everything is good.

4 Verify Connection to Data Source

As a final check to ensure that we can access this new data source, let’s do the following:

  • Create a ColdFusion file (.cfm) and enter the following:
    <!doctype html>
    <html>
    <head>
       <meta charset="utf-8">
       <title>MS Access Datasource Check</title>
    </head>
    <body>
       <h1>MS Access Datasource Check</h1>
       <cfquery name="qCheckDS" datasource="data-source-name">
          SELECT now() AS dt
       </cfquery>
       <cfdump var="#qCheckDS#" label="qCheckDS">
    </body>
    </html>
    
  • Save it somewhere on your website and browse to it.
  • If data source connection is successful, you will see this dump, that is, something similar to this:

Congratulations! You’ve just successfully created an MS Access 32-bit data source on a 64-bit ColdFusion 10 installation.

Setting up an Oracle Thin Client Data Source in ColdFusion

To set up an Oracle thin client data source in ColdFusion 10 on a Windows environment, do the following:

  • Browse to http://www.oracle.com/technetwork/topics/winx64soft-089540.html
    (Note: You will need to create an Oracle account to download if you don’t have one)
  • Download the Oracle Instant Client, i.e. instantclient-basic-win-x86-64-10.2.0.2.0-20060503 from the Oracle website
  • Unzip and copy ojdbc14.jar to C:\ColdFusion10\<instance-name>\wwwroot\WEB-INF\lib, e.g. C:\ColdFusion10\cfusion\wwwroot\WEB-INF\lib
  • Add an entry to the Path system variables
  • Access the System Properties, i.e. Computer > right click and select Properties
  • Click Advanced system settings
  • Click Advanced tab
  • Click Environment variables…
  • Select path under System variables
  • Click Edit…
  • Append C:\ColdFusion10 to Path using the semicolon as a separator, i.e. add this:
    ;C:\ColdFusion10
  • Restart the server (needed when the Path system variables is updated so applications will pick it up)

In the image above, the path to append to the Path System Variable should be:
C:\ColdFusion10

Once the driver is installed and the server rebooted, do the following to create the Oracle thin client data source in ColdFusion 10:

  • Launch the ColdFusion admin via a web browser
  • Click Data Sources under Data & Services section
  • In the Add New Data Source section, enter the data source name and choose Other for the driver selection
  • Click Add
  • Then enter the following details for this new Oracle thin client data source:
    • JDBC URL
    • Driver Class: oracle.jdbc.OracleDriver
    • Driver Name (can be same as driver class)
    • Username (if required)
    • Password (if required)
    • Description (if required)
  • Click Show Advanced Settings
  • Make changes as required, e.g. select the Allowed SQL, set Login Timeout, etc.
  • Click Submit to create the data source
  • If there are no errors, a mesage is displayed to indicate that the data source was successfully created

data sources

Specify the JDBC URL using this format: jdbc:oracle:thin:@host-name:port:sid

If you do not install the Oracle thin client driver first and try to create a new Oracle thin client data source in ColdFusion, then verifying this new data source will result in this error:

Connection verification failed for data source: <data source name>
java.sql.SQLException: No suitable driver available for <data source name>, please check the driver setting in the resources file, error: oracle.jdbc.OracleDriver.

The root cause was that: java.sql.SQLException: No suitable driver available for <data source name>, please check the driver setting in the resources file, error: oracle.jdbc.OracleDriver.

 

 

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 create a website system maintenance page and redirect

Let’s say we are performing a system maintenance task on the database server but not the web server will not be affected. Given that our websites are database-driven and use this database server, we want to redirect all website visits to a maintenance page during the system maintenance period.

Here are the steps:

  1. Create the site maintenance page
  2. Create a redirect rule using the IIS URL Rewrite
  3. Activate the rule at system maintenance time

Create the site maintenance page

1 Create an HTML page with the following information

  • Page title
  • Brief description of maintenance window
  • Time service resumes

Include the style inline on the page, and save this page as maintenance.html on the website root. Here’s a sample:

2 Creating the IIS URL Rewrite Redirect Rule

Let’s use the IIS Rewrite Module to create a rule to redirect all requests to the website to a maintenance page that we created in the step above:

  • Logon to the IIS Manager
  • Select the site and then URL Rewrite
  • Add Rule(s)…
  • Blank Rule under Inbound Rules
  • Enter the following for this new rule:
    • Name: Site Maintenance
    • Expand the Match URL section and enter or select the following:
      • Requested URL: Matches the pattern
      • Using: Regular Expressions
      • Pattern: (.*)
        We want to match all requests coming to the site, hence (.*)
      • Ensure Ignore case is checked
    • Expand the Conditions section and enter or select the following:
      • Logical Group: Matches All
      • Click Add and add these conditions:
        • Exclude maintenance (we want to show the maintenance.html page)
          • Condition input: {REQUEST_URI}
          • Check if input string matches: Does not match the pattern
          • Pattern: maintenance
        • Exclude images (we want to show the site banner images)
          • Condition input: {REQUEST_URI}
          • Check if input string matches: Does not match the pattern
          • Pattern: images
    • Expand the Actions section and enter or select the following:
      • Action type: Redirect
      • Redirect URL: http://<domain-name>/maintenance.html
        This is the html page named maintenance.html that you created in the step above.
      • Uncheck Append query string
      • Redirect type: Temporary (307)
  • Ensure this rule is the first one for the website (as we do not want to process any other rules if they exist). If not, move it to the top.
  • Test this new rule (preferably on a non-production server first)

3 Activate the rule at system maintenance time

On the day of the system maintenance, activate the rule using these steps:

  • Logon to the IIS Manager
  • Select the site and then URL Rewrite
  • Select the Site Maintenance rule
  • Click Enable Rule under Inbound Rules in the Actions right pane

Once maintenance is done, don’t forget to log back to IIS Manager and disable the Site Maintenance IIS Rewrite rule.