Database Simplified Headline Animator

Database Simplified

Thursday 20 August 2015

Rebuild System Databases in SQL Server Failover Cluster Environment

 

This blog describes process of rebuilding system databases in clustered environment.

Sometimes an unexpected shutdown of SQL Server database server has resulted in crashing of the SQL Server or system databases. In this case when you will try to start SQL Server service, it will not start and if you will check the SQL Server error logs to check error message, you could see the following entries “Restore the database from a full backup, or repair the database”, “Cannot recover the master database”. means that corruption of master database.

So system databases must be rebuilt to fix corruption problems in the master, model, msdb, or resource system databases.

Limitation and Restrictions in Rebuilding System Databases

When master, model, and msdb system databases are rebuilt, the databases are dropped and re-created in their original location. Any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

Now to get system databases back to its original position means all SQL logins and jobs, we need to restore last or previous full backup files of master, model and msdb databases. So it is very important to configure and schedule system databases backup also like other user databases in SQL Server.

Process for Rebuilding System Databases in Clustered Environment

In this blog, we are using SQL Server 2008 R2 failover cluster. It is a two node window failover cluster having four SQL Server instances running as shown below - 

Window Failover Cluster Configuration information -

               Node 1                                                                                  Node 2

Server Name – TESTSERVER_A                                            Server Name – TESTSERVER_B

Server IP – 10.123.23.1                                                          Server IP – 10.123.23.2

OS Version – Win server 2008 R2 (SP1)                                  OS Version – Win Server 2008 R2 (SP1)

SQL Server Version – SQL Server 2008 R2                              SQL Server Version – SQL Server 2008 R2

SQL Instances Running – SQLA, SQLB                                   SQL Instances Running – SQLC, SQLD

 

Here we will rebuild master/system database on SQL Instance named as SQLA running on Node TESTSERVER_A.

Steps to rebuild master/system databases are as follows-

Step 1.

First of all take RDP of cluster node named TESTSERVER_A using cluster administrator account which is current owner of SQL Server instance named SQLA for which you are rebuilding the master/system databases.

Step 2.

After connecting to node TESTSERVER_A, open failover cluster manager and then take cluster resources offline for SQL server instance named as SQLA for which you have to rebuild master/system database using setup.exe.

1

Step 3.

After taking cluster resource offline then SQL server instance service will stop but all disks associated with SQL Instance named SQLA will be available for this instance. In below screen shot, you can see that cluster Disk 7 and 9 are online.

2

Step 4.

Now we will rebuild master/system databases using setup.exe. We can find setup.exe file at following locations –

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2

Step 5.

Open command prompt using Run as Administrator then type below command to rebuild system databases.

C:\Program Files\Microsoft SQL Server\100\Setup Bootstrap\SQLServer2008R2>setup.exe /QUITE /ACTION=REBUILDDATABASE /INSTANCENAME=MSSQL$SQLA /SQLSYSADMINACCOUNTS="GGN-AM-19\User" /SAPWD=Password@123

Note – There are some parameter specified in above code-

a. SQLSYSADMINACCOUNTS - Local system administrator account which is GGN-AM-19\User.

b. INSTANCENAME - SQL Instance name which is default means MSSQL$SQLA.

c. SAPWD – Password of SA account.

Step 6.

After completion of command run in above steps, all system databases will be rebuilt.

Step 7.

Now you can check new created system databases files with current date and time at default location of SQL Server.

Rebuild_3

Step 8.

Now Start SQL Server services from configuration manager.

Step 9.

Now after starting SQL Services, connect to SQL Server using SQL Server Management Studio. Here all system databases are new and does not contain any details of all other user databases like SQL logins and jobs information.

Step 10.

Rebuilding the master, recreated the tempdb in the default location. If you are keeping tempdb in a different location perhaps on another set of disks then you need to move it.

Use below code to move tempdb as per your requirement.

USE master
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = tempdev, FILENAME = 'F:\tempdb.mdf')
GO
ALTER DATABASE tempdb
MODIFY FILE (NAME = templog, FILENAME = 'F:\tempdblog.ldf')

Note - Now master/system databases has been rebuilt. In next step we will see how to restore most recent full backup of system databases to get backup system database in previous state.

 

Process for Restoring System Databases in Clustered Environment

Once system databases are rebuilt using setup.exe as mentioned in above steps then all system databases (master, model and msdb) are dropped and re-created and any user modifications to these databases are lost. For example, you may have user-defined objects in the master database, scheduled jobs in msdb, or changes to the default database settings in the model database.

So we need to restore most recent full backups of all system databases to get system databases back in previous state as these were before damage or crash happened to system databases.

We will restore master database on SQL Instance named as SQLA running on Node TESTSERVER_A.

Step 1.

After connecting to node TESTSERVER_A, open failover cluster manager and then take cluster resources offline for SQL server instance named as SQLA in which you have to restore master database.

1

Step 2.

After taking cluster resource offline then SQL server instance service will stop but all disks associated with SQL Instance named SQLA will be available for this instance. In below screen shot, you can see that cluster Disk 7 and 9 are online.

2

Step 3.

Now start SQL instance service in single user mode from command prompt. Open command prompt via run as administrator as shown below.

Use below code to start SQL Server in single user mode-

net start MSSQL$SQLA /m

Start-SQL_Single

Step 4.

Now after starting SQL server service in single user mode using CMD, we will connect to instance of SQL Server using SQLCMD from command prompt and restore master database as shown below.

Use below code to start SQL Server in single user mode-

C:\Windows\system32>SQLCMD -S SQLA\SQLA -E

Note – Here SQL Virtual Network name is SQLA and Instance is also SQLA.

After connecting to SQL server, restore master database from backup file using below code -

Restore Database master from disk='D:\SQL_DB_Backup\master.bak'
GO

Restore_Master

Step 5.

Now after restoring master database using CMD, SQL Server service will stop. Now restart SQL server service from SQL Server Configuration manager in normal mode.

Step 6.

Now we will restore msdb database. To restore MSDB database, no need to start SQL Server in single user mode. But make sure that SQL Server Agent service is stopped because by default it make one connection to MSDB database and prevent user to restore MSDB database via showing error that database is in use. Now restore backup of MSDB database from either command prompt or management studio.

If you found any issue in restoring MSDB database backup like database is in use then check using running command sp_who2 and find spids and kill those spids which are using MSDB database.

Use below code to restore MSDB database from command prompt.

Use master
GO
Restore database msdb from Disk='D:\SQL_Master_DBFiles\msdb.bak' with replace
GO

Step 7.

To restore model database, no need to start SQL Server in single user mode. Like user database, simply restore backup of model database from either command prompt or management studio.

Use below code to restore model database from command prompt.

Use master
GO
Restore database model from Disk='D:\SQL_Master_DBFiles\model.bak' with replace
GO

 

Troubleshooting in Rebuilding System Databases for SQL Server 2008/R2/2012

Sometimes, there may be some issue in rebuilding system database due to problem with files present in Binn\Templates folder. Once you check summary.txt file, let's say it is saying one message as follows:

Configuration error description: The file C:\Program Files\Microsoft SQL Server \MSSQL10_50.MSSQLSERVER \MSSQL\Binn\Templates\master.mdf is missing

In the above scenario, we have to copy file (master.mdf) from SQL Server dump or DVD into Binn\Templates folder and retry.

Note - Key Points to Rebuilding System Databases for SQL Server 2000, 2005, 2008, 2008 R2 and 2012

We can summarize the requirement to rebuilding system databases:

a. SQL Server 2000

· We can use RebuildM.exe

· Need setup media for system database files

b. SQL Server 2005

· We can use setup.exe

· Need setup media for setup.exe and system database files

c. SQL Server 2008/2008R2/2012

· We can use setup.exe

· Setup media not required and system database files are there in Binn\Templates folder

Thursday 6 August 2015

Database Backup Encryption in SQL Server 2014

 

This blog describes process of database backup with encryption. Database Backup Encryption is new feature that is available in SQL Server 2014. You can create an encrypted backup file by specifying the encryption algorithm and the encryptor (either a Certificate or Asymmetric Key).

This blog will show you how we can performed encrypted database backup by creating master key and certificate. Once backup is encrypted then you can not restore it on other SQL Server Instance. To restore encrypted backup on other instance of SQL Server, first of all you need to create master key and create certificate on instance of SQL Server where you want to restore encrypted backup.

 

Restriction for database backup with encryption

The following are restrictions that apply to the encryption options:

a. Backup encryption feature is available in all editions of SQL Server 2014 except Express and Web editions.

b. Restoring of encrypted backup is supported in all editions of SQL Server 2014.

c. Previous version of SQL Server cannot read encrypted backup.

d. Appending to an existing set option is not supported for encrypted backup.

 

Steps to perform database backup with encryption on source instance of SQL Server named “GGN-AM-19\S2K14” are as follows-

Step 1

First of all, connect to source instance of SQL Server instance named as “GGN-AM-19\S2K14” as shown below.

Stesp_1

Step 2

Next we will create master key on master database at source instance of SQL Server using below query.

Use Master
GO
Create master key encryption by password='Password@123'

Stesp_2

Once master key is created on master database then we can check this key by using below query –

select * from master.sys.symmetric_keys where name='##MS_DatabaseMasterKey##' 

Stesp_2.1

Step 3

Next we will create certificate named as “TestDB_Backup_Certificate” which will be used for encrypting database backup file.

Use Master
GO
Create Certificate TestDB_Backup_Certificate
with subject='Backup Encryption in SQL 2014'

Stesp_3

Step 4

Now perform backup of certificate created in previous steps using below query.

BACKUP CERTIFICATE TestDB_Backup_Certificate
TO FILE = 'D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.cer'
WITH PRIVATE KEY(FILE='D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.pvk'
ENCRYPTION BY PASSWORD='Password@123')

Stesp_4

Step 5

Now perform database backup with encryption using algorithm AES_256 and certificate as shown below.

Backup database Test
To Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'
with encryption(ALGORITHM= AES_256,Server Certificate=TestDB_Backup_Certificate)

Stesp_5

Step 6

Now we have three files as shown below.

Stesp_6

 

Now we will restore encrypted database backup on destination instance of SQL Server.

Steps to perform restoration of encrypted database backup on destination instance of SQL Server named “GGN-AM-19\S2K14_A” are as follows-

Step 1

Now we will restore database backup on destination instance of SQL Server. Connect to instance named GGN-AM-19\S2K14_A.

Step_Restore_1

Step 2

After connecting to Instance named as “GGN-AM-19\S2K14_A”, run below query check files in backup files.

Restore filelistonly From Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'
But we will get error as mentioned below –

Msg 33111, Level 16, State 3, Line 1

Cannot find server certificate with thumbprint '0x4C1FF9AFD5DBB5C3B5B0C89D757632B0D9573B56'.

Msg 3013, Level 16, State 1, Line 1

RESTORE FILELIST is terminating abnormally.

Above error is coming because certificate is not exists on instance of SQL Server where we want to restore encrypted backup.

Step 3

Now first of all, we will create master key on master database on destination instance using below query.

 

Use Master
GO
Create master key encryption by password='Password@123'

Step_Restore_2

Step 4

Next we will restore certificate from backup performed at Source instance (GGN-AM-19\S2K14).

 

Use Master
GO
Create CERTIFICATE TestDB_Backup_Certificate
From FILE = 'D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.cer'
WITH PRIVATE KEY(FILE='D:\SQL_DB_Backup\SQL_2K14\TestDB_Backup_Certificate.pvk',
DECRYPTION BY PASSWORD='Password@123');

Step_Restore_3

Step 5

Now we will try to read files inside encrypted backup of database on destination SQL Server using below query.

 

Restore filelistonly
From Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'

Step_Restore_4

Step 6

Now we can restore encrypted backup of database on destination instance of SQL Server.

 


Restore Database Test
From Disk='D:\SQL_DB_Backup\SQL_2K14\Test.bak'
with move 'Test' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.S2K14_A\MSSQL\DATA\Test.mdf',
move 'Test_log' to 'D:\Program Files\Microsoft SQL Server\MSSQL12.S2K14_A\MSSQL\DATA\Test_log.ldf'

Step_Restore_5

Wednesday 5 August 2015

Backup Database using PowerShell : Microsoft SQL Server

Often there’s a need to perform backup of all multiple databases on an instance or maybe multiple databases on multiple instance across environment. Now, this may well become a laborious task if you don’t have any fancy tool to do the job for you and that’s exactly where PowerShell comes to rescue. PowerShell can be used to perform such task easily.
Let’s Get Started!!!

How to Use the Script:
  1. Copy below function to notepad file and save as .psm1
  2. Follow instruction in script before function Credentials
  3. Import psm1 file using command : Import-Module <Location of psm1 file>
  4. Type Get-Help Sql-BackupDatabases to get information and instructions about script
function Sql-BackupDatabases
{
<#
.SYNOPSIS
Backup User databases on specified Instances across Environment.
.DESCRIPTION
This Script uses SMO to backup databases across environment based on parameters provided.
This will create CopyOnly Backup's to avoid breaking Backup Chain.
.PARAMETER backupdirectory
Backup Location for CopyOnly Backups either on local system or on shared location.
.PARAMETER instanceList
List of instances for whih backup needs to be performed.
You may specify a Text file like d:\List.txt containing list of instances.
.EXAMPLE
Backup all user databases on specified Instance.
Sql-BackupDatabases -backupdirectory d:\backup -instanceList "My\Instance"
Sql-BackupDatabases -backupdirectory d:\backup -instanceList d:\list.txt
.NOTES
The SQL Server Service account must have access to Backup Location and You should use an account that has access to all SQL Instances across environment.
#>
param([parameter(Mandatory=$true)] [string] $backupdirectory, $instanceList)
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.Smo")
[System.Reflection.Assembly]::LoadWithPartialName("SqlServer.SMOExtended")
    if ($instanceList -like "*.txt")
    {
        $list=get-content $instanceList
    }
    else { $list=$instanceList}
if($backupdirectory -eq ""){write-host "please provide Backup Location"}
foreach ($l in $list)
    {
        $instance=Connect-Instance -instanceName $l
        $bkdir = $backupdirectory +"\"+ $l
        if (!(Test-Path $bkdir))
            {
                New-Item -Path $bkdir -ItemType Directory
             }
        $database=$instance.Databases["master"]
        $dbs = $database.ExecuteWithResults("select name from sys.databases where name not in ('master','model','tempdb','msdb') and state_desc='ONLINE'")
        $dbs = $dbs.tables[0]
    foreach ($db in $dbs)
        {
            Backup-Database -data $db.name -server $instance
        }
    }
}
function Connect-Instance
{
        param ([string] $instanceName)
        $s = new-object ("Microsoft.SqlServer.Management.Smo.Server")  $instanceName
        $s.connectioncontext.loginsecure=$false
        $credential=get-credential
        ##$s.connectioncontext.set_login("sa")
        $s.connectioncontext.set_login($credential.UserName)
        ##$credential=Credentials
        ##$s.connectioncontext.set_securepassword($credential)
        $s.connectioncontext.set_securepassword($credential.Password)    
        return $s
}
### Instruction To generate password use below command #####
in powershell command prompt type: [byte[]] $key=(1..16)
$pass="Your Password"| ConvertTo-SecureString -Force -AsPlainText | ConvertFrom-SecureString –Key $key
This will generate a secure password using Key variable. Use the contents of $pass variable for $password value in below function
##############################################
function Credentials
{
[byte[]] $key=(1..16)
    $password="use $pass value from above"
    $credential=new-object system.management.automation.pscredential "sa",($password | convertto-securestring –key $key)
    return $credential.Password
}
Function Backup-Database
{
    Param ([string] $data,$server)
$back= new-object ("Microsoft.SqlServer.Management.Smo.Backup")
            $back.database=$db.name
            $dbname = $db.name
            $back.action="Database"
            $back.CopyOnly=$true
            $back.devices.adddevice($bkdir + "\" + $db.name + ".bak","File")
            $back.backupsetname="BackupTest"
            $percent = [Microsoft.SqlServer.Management.Smo.PercentCompleteEventHandler] {
                     Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete $_.Percent -status ([System.String]::Format("Progress: {0} %", $_.Percent))
            }
                    $back.add_PercentComplete($percent)
                    $back.add_Complete($complete)
            Try
                {
                        $back.sqlbackup($server) 
                    Write-Progress -id 1 -activity "Backing up database $dbname to $bkdir " -percentcomplete 0 -status ([System.String]::Format("Progress: {0} %", 0))            
                }
            Catch
                {
                        Write-Output $_.Exception.InnerException
                }
}