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.

Advertisements

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

SQLException – Too many parameters were provided in this RPC request

Say you’re updating a table using the SQL IN clause. Your SQL statement might be something like this and the number of values in listUsrPolicyID exceeds 2100:

<cfquery name="qUpdate" datasource="#request.dsn#">
   UPDATE usrPolicy
    SET reminderSent = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateNow#">
    WHERE usrPolicyID IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#listUsrPolicyID#">)
 </cfquery>

The above query will fail and trigger the following error:

[Macromedia][SQLServer JDBC Driver][SQLServer]The incoming tabular data stream (TDS) remote procedure call (RPC) protocol stream is incorrect. Too many parameters were provided in this RPC request. The maximum is 2100.

The solution then is to split listUsrPolicyID into batches containing id not exceeding 2100, then loop through all the batch lists to perform the update, i.e.

<cfscript>
variables.aUsrPolicyID = ListToArray(variables.listUsrPolicyID);
variables.cnt = 0;
variables.batchList = StructNew();

//let's create batches of 1000 user policy ID, for example
for (i=1; i lt ArrayLen(aUsrPolicyID); i += 1000) {
variables.cnt = variables.cnt + 1;
variables.batchList["#cnt#"] = "";
variables.toCnt = i + 999;
//do not go over the last available user policy id
if (variables.toCnt gt ArrayLen(aUsrPolicyID)) {
variables.toCnt = ArrayLen(aUsrPolicyID);
}
//add user policy id to each batch list
for (j=i; j lt variables.toCnt; j +=1){
variables.batchList["#cnt#"] =   ListAppend(batchList["#cnt#"],"#aUsrPolicyID[j]#");
}
} //for i
</cfscript>

<!--- there is a max of 2100 for the SQL IN clause- so let's split the list into smaller batches --->
<cfloop index="k" from="1" to="#variables.cnt#">
<cfquery name="qUpdate" datasource="#request.dsn#">
UPDATE usrPolicy
SET reminderSent = <cfqueryparam cfsqltype="cf_sql_timestamp" value="#dateNow#">
WHERE usrPolicyID IN (<cfqueryparam cfsqltype="cf_sql_integer" list="yes" value="#batchList['#k#']#">)
</cfquery>
</cfloop>

Install Room Booking System on Windows and SQL Server

The Oxalto Room Booking System is a free open source room booking solution developed using ColdFusion and  CFWheels MVC framework on the backend, and FullCalendar.js, jQuery and Bootstrap on the front-end. For more information about this open source system (including submitting issues), visit: https://roombooking.readme.io/.

There is a set of instruction for installing it on an environment running ColdFusion Application Server, Railo or Lucee with MySQL as the database server. However in this guide, we will install it in the following environment:

  • Windows Server 2008 R2
  • Adobe ColdFusion 10 Enterprise
  • MS SQL Server 2008 R2

We will need to convert the sql script from MySQL to T-SQL (MS SQL Server) before we can run it to create the required tables for the system.

Here are the steps for installing the Oxalto Room Booking System:

  • Getting the source files
  • Setting up the database
  • Create the initial administrator account
  • Other tweaks
  • Final confirmation
  • Post installation errors resolution

Getting the source files

  • Download the 1.2 release
  • Unzip into the web root of the application server

Setting up the database

  • Create a blank database in MS SQL Server called roomBooking
  • Assign db_dataread, and db_datawrite database roles to the user that will access this database via ColdFusion Admin
  • Create a new datasource call roomBooking in ColdFusion Admin
  • Convert the new-installation.sql from MySQL to T-SQL (MS SQL Server). Launch the file in MS SQL Server Management Studio, and make the following changes:
    • Remove this line: SET FOREIGN_KEY_CHECKS=0;
    • Change all drop table if exists MySQL to T-SQL equivalence:
      IF OBJECT_ID(N'[table-name]’, N’U’) IS NOT NULL
      DROP TABLE [table-name]
      GO
    • Replace ` with square brackets for table names and field names, i.e. [table-name], [fieldname]
    • Change AUTO INCREMENT to IDENTITY(1,1)
    • Remove COLLATE statement from table column definitions
    • Surround column type with square brackets, i.e. [int],[nvarchar]
    • Replace this: ENGINE=InnoDB DEFAULT CHARSET=utf8 COLLATE=utf8_unicode_ci; with ON [PRIMARY]
    • Define primary key using one of these formats:
      CONSTRAINT PK_<table-name>_id PRIMARY KEY ([id]) or
      CONSTRAINT PK_<field-name> PRIMARY KEY ([field-name]) or
      CONSTRAINT PK_<primary-key-1>_<primary-key-2> PRIMARY KEY ([primary-key-1],[primary-key-2])
    • Define column default values using this format:
      CONSTRAINT df_<column-name> DEFAULT ‘<default-value>
      if definition is DEFAULT NULL, just remove the word DEFAULT, leaving only NULL, i.e.
      [description] [nvarchar] (255)  NULL
    • For the events table, change the column end to endDate
    • For the permissions table, change the column user to users (or some other non-reserved word)
    • Change table column types from MySQL to their T-SQL (SQL Server) equivalences:
      int(11) to [int]
      varchar to [nvarchar]
      longtext to [ntext]
      smallint to [smallint]
      tinyint to [tinyint]
      datetime to [datetime]
  • Run the converted new-installation.sql script in SQL Server Management Studio to create the tables

Create the initial administrator account

  • Browse to this page to do a few quick checks and create an initial administrator account:
    http://your.website.com/roomBooking/install/
  • To confirm, check:
    • the admin user you specified was created in the users table
    • a new file auth.cfm is created under /config folder
  • The install folder should be deleted or renamed after the initial administrator account has been successfully created.

Other tweaks

  • Open /config/settings.cfm and change set(reloadPassword=”roombooking”) to something unique
  • Open /config/production/settings.cfm and set an error handling email address.

Final confirmation

Post installation errors resolution

1. Replace all instances of end with endDate

This is needed as end may be a reserved MS SQL Server word, and is causing the event not to load. Change it in the following locations:

  • /controllers/eventdata.cfc
    • getevents() function – lines 46,47,51,52,56,57
    • prepeventdata() function – line 87
  • /controllers/Booking.cfc
    • day() function – lines 99,104,110,111,115,126
    • create() function – lines 251,252,258,259
    • _dayListWC() function – lines 352,357
    • _agendaListWC() function – line 383 (change from start to endDate)
    • check() function – lines 431,433
  • /controllers/Api.cfc
    • display() function – lines 36,39
    • ical() function – lines 99,100
  • /views/eventdata/_details.cfm – line 61

2. users.email is invalid

Open /controllers/Users.cfc. On line 83, remove this: group=”id”,

Reload the application and ensure that the errors are now resolved. To reload the application, append the following to the main page, i.e. :

?reload=production&password=YOURRELOADPASSWORD

3. Remove MySQL escape characters for table fields

Open controllers/Controller.cfc and remove the ` characters from any of the table columns in queries. Save the file and reload the application, then click on Settings > Users to confirm the error is fixed.

Congratulations! You have now fixed all the errors and the Room Booking System is now successfully installed.

This is the screen you see when you’re not logged in.

roomBooking-8

This is the screen you see once you’re logged in.

roomBooking-9

Install SQL Monitor 4 on Windows Server 2008 – Part 2

In Part 1 we looked at installing Red Gate’s SQL Monitor 4 on Windows Server 2008  and SQL Server 2008. Now in this second part, we will look at configuring SQL Monitor after it has been successfully installed.

Before we can do any configuration, let’s create an Administrator role password to access the SQL Monitor 4 dashboard for an initial log on.

sqlmonitor-16a

Red Gate SQL Monitor 4 has the following pre-defined roles for controlling access to the monitoring dashboard:

  1. Administrator Role – have unrestricted access to every features in SQL Monitor 4
  2. Standard User Role – can manage and configure alerts, cannot configure custom metrics, alerts or alert email settings, can view most areas except the Manage User Roles, Licensing and Base Monitor Connection pages
  3. Read-Only User Role – cannot configure any of the settings in SQL Monitor 4, can view most areas except the Manage User Roles , Licensing and Base Monitor Connection pages

Each role is activated when a password is assigned to the role. You can not create any other custom accounts with its unique username and password.

Here’s the dashboard global overview:

global-overview

SQL Monitor 4 configurations are broadly categorise as follows:

  • Monitoring
  • Data Management
  • Application Options
  • Alerts and Metrics
  • Licensing

configuration-main

Let’s look at some of the configurations:

  1. Monitored server (Monitoring)
  2. Data purging (Data Management)
  3. User roles (Application Options)
  4. Alert settings and Email settings (Alerts and Metrics)

1. Monitored server

To add an MS SQL Server machine to monitor, click Configurations on the top menu, then under the Monitoring section, click Monitored servers.

Click Add SQL Server to monitor, and then enter the machine-name\* to add all instances on that machine, or machine-name\instance-name to add a particular instance.

For the Host Machine Credentials, select Base Monitor Service Account* (this will be automatically pre-populated for you as Windows-Domain\Username) and for the SQL Server Instance, click Use Same Credential as Host Machine, or enter its authentication method, username and password.

Then finally, click Add. Repeat this process for all the MS SQL Server machines that need monitoring.

Add Monitored Servers

2. Data Purging

This configuration enables you to decide how long to keep various types of data in the SQL Monitor 4 Data Repository database. The 3 types of data store in the database that can be purged are categorised broadly as:

  • Machine data
  • SQL Server data
  • Alert data

You can decide not to purge data (if the Data Repository database, but eventually needed due to disk space availability), or purge those older than:

  • 1,2,3,5 days or
  • 1,2 weeks or
  • 1,2,3,6 months or
  • 1 year

Data Purging Configurations

3. User Roles

If you wish to enable different levels of access to the dashboard, enter the password for the other non-adminstrator roles. Please note these roles won’t appear as an option on the logon prompt unless you create a password for the role.

Configure User Roles

4. Alerts settings and Email settings

In this section, you can configure the email and alert settings, and also custom alerts. First, let’s configure the email settings by Check Send emails when alerts are raised and completing the following:

  • Send emails to box: email addresses (comma separated) of those that should received the alerts
  • Send from email address: enter the sender’s email address
  • Outgoing mail server (SMTP) – can be an IP Address or fully qualified domain name, and its port
  • Check and enter optional security related mail server settings

Alert settings is the first item under the Alerts and Metrics section. Alert settings are separated into two categories:

  • Host machine alerts
  • SQL Server alerts

For each alert, you can

  • Enable or disable the alert
  • Set the threshold at which the alert is raised
  • Choose to use the default email recipients list configured above, or add a specific email recipient to receive the alert

SQL Server alerts

For example, here’s the screen for the Long Running Query Alert settings:

long-running-query-alert-settings

There are 3 levels of alerts to enable (but you don’t have to enable all levels):

  1. Low
  2. Medium
  3. High

You can specify the threshold at which each level is activated, and also exclude queries from activating an alert if they are known to be long running and not unusual.

The Notification sections allow you select to use the default email list (added when configuring the email settings) or a enter an email address for this particular alert.

Please note that the mail server settings must be configured to receive email alerts.

This covers some of the most important configurations for SQL Monitor. There are other configurations which you may want to look into, in particular the following:

  • Trace
  • Group
  • Base Monitor Connection
  • Custom Metrics
  • Licensing

And lastly, the About link, provide a summary detail of the SQL Monitor components, i.e.

  • Web Server
  • Base Monitor
  • Client Browser