PowerShell : Scripting Logins, Role Assignments and Server Permissions Using PowerShell

This post is a continuation of Multiple DB refresh automation using PowerShell post . The OP requested me to automate the login transfer process as well. The script should generate an outfile and execute the file on the target server.

<#
.ProblemStatement

Copying SQL server logins to file is little bit tricky though we are able to generate the script the password need to handled in a proper way to make it work.
Usually we’ll make use of scripts like sp_help_revlogin to manage copying logins from one instance to another but the requirement won’t suffice our purpose it won’t generate the script for server permissions and role assignments.
When you are automating the entire refresh process,PowerShell provides a flexibility to run SQL and generate the scripts in the said path. The SQL file can be invoked using invoke-sqlcmd to to execute it across any target server

The below code loads the snapins

SqlServerProviderSnapin100
SqlServerCmdletSnapin100

.Example1

To generate the script

PS:\>Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut

.Example2

Generate and apply script on to the target server

PS P:\> Copy-Logins -SourceServer <SourceServer> -outpath E:\OutPut -TargetServer <ServerName>

.Reference Link

Scripting Out the Logins, Server Role Assignments, and Server Permissions

#>

 

function Copy-Logins{
    [cmdletbinding()]
    Param
    (
    [parameter(Mandatory=$true)][string] $Sourceserver,
    [String] $TargetServer,
    [string] $outpath)

##Create a new outfile at query function call

[string]$FileDS = Get-Date -Format "yyyyMMdd" 

[string]$outFile = $outpath +'\'+ $Sourceserver+'_'+$FileTS + "_login.sql" 

New-Item -ItemType file $outfile -Force

##Prepare the connection string 

$SqlConnection = New-Object System.Data.SQLClient.SQLConnection("Server=$Sourceserver;Integrated Security=sspi;");
$SqlCommand = New-Object System.Data.SQLClient.SqlCommand; 
$SqlCommand.Connection = $SqlConnection;   

Try
{

##SQL Account - Executing a SQL is better than working with Hashed value

$SQL=  "
-- Scripting Out the Logins To Be Created
SELECT 'IF (SUSER_ID('+QUOTENAME(SP.name,'''')+') IS NULL) BEGIN CREATE LOGIN ' +QUOTENAME(SP.name)+
			   CASE 
					WHEN SP.type_desc = 'SQL_LOGIN' THEN ' WITH PASSWORD = ' +CONVERT(NVARCHAR(MAX),SL.password_hash,1)+ ' HASHED, CHECK_EXPIRATION = ' 
						+ CASE WHEN SL.is_expiration_checked = 1 THEN 'ON' ELSE 'OFF' END +', CHECK_POLICY = ' +CASE WHEN SL.is_policy_checked = 1 THEN 'ON,' ELSE 'OFF,' END
					ELSE ' FROM WINDOWS WITH'
				END 
	   +' DEFAULT_DATABASE=[' +SP.default_database_name+ '], DEFAULT_LANGUAGE=[' +SP.default_language_name+ '] END;' COLLATE SQL_Latin1_General_CP1_CI_AS AS [-- Logins To Be Created --]
FROM sys.server_principals AS SP LEFT JOIN sys.sql_logins AS SL
		ON SP.principal_id = SL.principal_id
WHERE SP.type IN ('S','G','U')
		AND SP.name NOT LIKE '##%##'
		AND SP.name NOT LIKE 'NT AUTHORITY%'
		AND SP.name NOT LIKE 'NT SERVICE%'
		AND SP.name <> ('sa')"

$SQL2="SELECT 
'EXEC master..sp_addsrvrolemember @loginame = N''' + SL.name + ''', @rolename = N''' + SR.name + '''
' AS [-- server Roles the Logins Need to be Added --]
FROM master.sys.server_role_members SRM
	JOIN master.sys.server_principals SR ON SR.principal_id = SRM.role_principal_id
	JOIN master.sys.server_principals SL ON SL.principal_id = SRM.member_principal_id
WHERE SL.type IN ('S','G','U')
		AND SL.name NOT LIKE '##%##'
		AND SL.name NOT LIKE 'NT AUTHORITY%'
		AND SL.name NOT LIKE 'NT SERVICE%'
		AND SL.name <> ('sa')"

$SQL3="
SELECT 
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
		THEN SrvPerm.state_desc 
		ELSE 'GRANT' 
	END
    + ' ' + SrvPerm.permission_name + ' TO [' + SP.name + ']' + 
	CASE WHEN SrvPerm.state_desc <> 'GRANT_WITH_GRANT_OPTION' 
		THEN '' 
		ELSE ' WITH GRANT OPTION' 
	END collate database_default AS [-- server Level Permissions to Be Granted --] 
FROM sys.server_permissions AS SrvPerm 
	JOIN sys.server_principals AS SP ON SrvPerm.grantee_principal_id = SP.principal_id 
WHERE   SP.type IN ( 'S', 'U', 'G' ) 
		AND SP.name NOT LIKE '##%##'
		AND SP.name NOT LIKE 'NT AUTHORITY%'
		AND SP.name NOT LIKE 'NT SERVICE%'
		AND SP.name <> ('sa')"
 
        $SqlCommand.CommandText = $SQL;   
        $SqlConnection.Open(); 
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
        $SqlConnection.Open(); 
        $SqlCommand.CommandText = $SQL2;   
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();
        $SqlConnection.Open(); 
        $SqlCommand.CommandText = $SQL3;   
        $table = $SqlCommand.ExecuteReader(); 
        While ($table.Read())
           {
               $table[0]+' '+"`r`n"+"GO"|Out-File -Append -FilePath $outfile
            } 
        $SqlConnection.Close();

}
catch
{
 $ErrorMessage = $_.Exception.Message
 Write-host "$Sourceserver does't exist or Invalid SQL instance Name $ErrorMessage"
}

if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerProviderSnapin100'})) 
{
   add-pssnapin SqlServerProviderSnapin100 
   write-host "Loading SqlServerProviderSnapin100 in session" 
} 
else 
{ 
  write-host "SqlServerProviderSnapin100 is already loaded" 
} 

if (!(Get-PSSnapin | ?{$_.name -eq 'SqlServerCmdletSnapin100'})) 
{   
    add-pssnapin SqlServerCmdletSnapin100 
   write-host "Loading SqlServerCmdletSnapin100 in session" 
} 
else 
{ 
  write-host "SqlServerCmdletSnapin100 is already loaded" 
} 


 If($TargetServer){
 try
    {
    Invoke-Sqlcmd -ServerInstance $TargetServer -InputFile $outFile -ErrorAction Stop
    }
catch
    {
     Write-host "$TargetServer not reachable "
     }

 }


}   
   

 

Posted in PowerShell, SQL | Tagged , , | Leave a comment

MongoDB : Point in Time Restore/Recovery of MongoDB

This post demonstrates the methods of PIT recovery of the database. The step by step details of the recovery process is explained below

The requirement for PIT restore/recovery is to setup a single node replica so that the oplogs can be used to “replay transactions” for point in time recovery. Oplog is a capped collection which stores all the transactions in an order. Oplog can be queried like other collections.

https://docs.mongodb.com/manual/core/replica-set-oplog/

Have used two instances one for backup and its configured on 30001 port and other is for restore/recovery on 30002 port.

The below diagram gives an idea about each steps

BackupandRestoreImage

I’m using a new instance to demonstrate the backup and restore process.

  1. Lets start the instance 30001 a source for an entire example

>mongod –port 30001 –dbpath d:\data\TestRepSet_30001\ –replSet TestRepSet_30001  –oplogSize 128 –logpath d:\data\TestRepSet_30001.log

Backup_to_location_2

  1. Connect to an mongo instance using the port 30001

>mongo.exe –port 30001

  1. Configure single node replica set

> rsconf = { _id: “TestRepSet_30001”, members: [{ _id: 0, host: “localhost:30001” }] }

> rs.initiate( rsconf )

  1. Insert the dummy data

TestRepSet_30001:PRIMARY> for(var i=0;i<=100;i++) {

db.backupCollection.insert({“id”:+i, “Date”: new Date()}) }

  1. cross verify the count for our reference just to compare before and after restore

TestRepSet_30001:PRIMARY> db.backupCollection.count()

 

Backup_to_location_3

 

  1. Backup the database to specific path. You can also use default path. I’m using c:\data\backup

Backup_to_location_1

 

  1. Perform data manipulation(Insert and Delete few documents)

TestRepSet_30001:PRIMARY> for(var i=102;i<=1000;i++) { db.backupCollection.insert({“id”:+i, “Date”: new Date()}) }

TestRepSet_30001:PRIMARY> db.backupCollection.count()

TestRepSet_30001:PRIMARY> db.backupCollection.remove({“id” : {“$gt” :800}})

TestRepSet_30001:PRIMARY> db.backupCollection.count()

Backup_to_location_4

  1. Dump and move the oplog to default folder and remove the local folder

>mongodump –port 30001 -d local -c oplog.rs

>move dump\local\oplog.rs.bson dump\oplog.bson

>rmdir /s local

Backup_to_location_5_backup_oplog

  1. Identify the time stamp for Point in time recovery

TestRepSet_30001:PRIMARY> db.oplog.rs.find({“ns”: “backup.backupCollection”,”op”:”d”}).sort({$natural:1}).limit(5)

PointInTIme

  1. I have created an instance to restore the database. Have used 30002 port to demonstrate the restore process

>mongod –port 30002 –dbpath d:\data\TestRepSet_30002\ –logpath d:\data\TestRepSet_30002.log

Restore_to_location_5_backup_oplog

  1. Restore the database from first snapshot from d:\data\backup to an instance hosted on 30002 port

>mongorestore –port 30002  d:\data\backup\

Restore_to_location_5_backup_oplog_2

12. Connect to an instance to check the count

>mongo.exe –port 30002

>use backup

>db.backupCollection.count()

13. After you have the right time replay the logs

>mongorestore –port 30002  –oplogReplay –oplogLimit “1463498418:1”

14. Check the record count at the target database

Restore_to_location_5_backup_oplog_3Job done

The learn other method you can refer the below link

http://stackoverflow.com/questions/25802786/is-there-any-way-to-recover-recently-deleted-documents-in-mongodb

 

Posted in MongoDB | Tagged , , , , , , , | Leave a comment

PowerShell – SQL Database Refresh -Restore – Multiple Databases

.SYNOPSIS

The purpose of the script is to restore database/s from a database backup. The requirement is to refresh ‘n’ databases.

.DESCRIPTION

The advantage is that the flexibility of passing restoreDbList to restore specific group of databases.

The Restore databases has three mandatory paramters

param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer – Source where you have the backup files
[Parameter(Mandatory=$true,Position=1)][String]$DestServer – Target Server where the databases to be restored
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbLis – List of databases to be restored on DestServer and datbases are separated by comma – “rtc,SafetyDB”

After the backup, the physical file name details and converting the path to UNC is done with the help of Function BackupFile. It returns the UNC path of actual backup file located on the source server.

For example, The backup of ABC databases initiated on SQLSERVER20 and dump got created on F:\PowerSQL\ABC_20160511_104239.BAK then the function will return the following value

\\SQLSERVER20\f$\PowerSQL\ABC_20160511_104239.BAK 

For the database restore have used the readily available code from the internet. The Function Invoke-DatabaseRestore uses the target server and actial backup physical location to restore the database

The successive restore will overwrite the target database

.NOTES
Author : Prashanth Jayaram
Requires: Make sure you have all the required admin access on the server

.EXAMPLE
Invoke_SQLDBRestore -SourceServer HQDBSP18 -DestServer HQSPDBSP02 -RestoreDbList “SafetyDB,rtc,rtcab1”

. Reference LINK

Powershell Function to Get Last SQL Server Backup File

#>

 

 

<#  
.SYNOPSIS  
 
The purpose of the script is to restore databse/s from a database backup. The requirement is to refresh 'n' databases. 
  
.DESCRIPTION  
 
The advantage is that the flexibility of passing dblist to restore specific group of databases. 
 
The Restore databases has three mandatory paramters 
 
param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer - Source where you have the backup files  
[Parameter(Mandatory=$true,Position=1)][String]$DestServer - Target Server where the databases to be restored 
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbLis - List of databases to be restored on DestServer and datbases are separated by comma - "rtc,SafetyDB" 
 
   
.NOTES   
Author  : Prashanth Jayaram 
Requires: Make sure you have all the required admin access on the server 
  
.EXAMPLE  
Invoke_SQLDBRestore -SourceServer HQDBSP18 -DestServer HQSPDBSP02 -RestoreDbList "SafetyDB,rtc,rtcab1"  
 
. Reference LINK 
http://www.webofwood.com/2012/05/11/powershell-function-to-get-last-sql-server-backup-file/   
 
  
#> 
 
Function Invoke_SQLDBRestore 
{ 
 
param ([Parameter(Mandatory=$true,Position=0)][String]$SourceServer,  
[Parameter(Mandatory=$true,Position=1)][String]$DestServer, 
[Parameter(Mandatory=$true,Position=2)][String]$RestoreDbList) 
 
#Load the required assemlies SMO and SmoExtended. 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
#SourceServer 
$Backupserver=$SourceServer 
$targetServer = $DestServer 
##Restore DB List 
#$RestoreDbList = "SafetyDB,rtc,rtcab1" 
 
 

Function BackupFile 
{ 
Param( 
    [Parameter(Mandatory=$true,Position=0)] 
    [string]$server, 
    [Parameter(Mandatory=$true,Position=1)] 
    [string]$database 
) 
Function New-SMOconnection { 
    Param ( 
        [Parameter(Mandatory=$true)] 
        [string]$server, 
        [int]$StatementTimeout=0 
    ) 
    If(!(Test-Connection -ComputerName ($server.Split('\')[0]) -Quiet -Count 1)) { 
        Throw "Could not connect to SQL Server $server." 
    } 
    $conn = New-Object Microsoft.SqlServer.Management.Common.ServerConnection($server) 
    $conn.applicationName = "PowerShell SMO" 
    $conn.StatementTimeout = $StatementTimeout 
    Try {$conn.Connect()} 
    Catch {Throw $Error} 
    if ($conn.IsOpen -eq $false) { 
        Throw "Could not connect to SQL Instance $server." 
    } 
    $smo = New-Object Microsoft.SqlServer.Management.Smo.Server($conn) 
    $smo 
} 
Function Get-LastBackupFile { 
    Param( 
        [string]$server, 
        [string]$database 
    ) 
    <#   Use a hereto to construct the T-SQL 
        You will notice the query eliminates any snapshots. This is because we 
        sometimes have VEEAM backups on some servers. 
    #> 
    $qry = @" 
DECLARE @dbname sysname  
SET @dbname = '$database' 
SELECT  f.physical_device_name as [backup] 
FROM    msdb.dbo.backupset AS s WITH (nolock) INNER JOIN 
            msdb.dbo.backupmediafamily AS f WITH (nolock) ON s.media_set_id = f.media_set_id 
WHERE   (s.database_name = @dbname) AND (s.type = 'D') AND (f.device_type <> 7)  
        AND (s.backup_finish_date = (SELECT MAX(backup_finish_date) 
FROM         msdb.dbo.backupset WITH (nolock) 
WHERE     (database_name = @dbname) AND (type = 'D') AND (is_snapshot = 0))) 
"@ 
  
    # Get an SMO Connection 
    $smo = New-SMOconnection -server $server 
    # most appropriate to use MSDB 
    $db = $smo.Databases["msdb"] 
    # Execute query with results 
    $rs = $db.ExecuteWithResults($qry) 
    # SMO connection is no longer needed 
    $smo.ConnectionContext.Disconnect() 
    # Return the result 
    $rs.Tables[0].Rows[0].Item('backup'} 
# Load SMO Assemblies 
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") 
[Void][System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") 
  
# Call the function and trap any error 
Try {$backup = Get-LastBackupFile -server $server -database $database} 
Catch { 
    $ex = $Error[0].Exception 
    Write-Host $ex.Message 
    While($ex.InnerException) { 
        $ex = $ex.InnerException 
        Write-Host $ex.Message 
    } 
} 
<# Verify the file   
    NOTE: most developent and run-time is performed on remote servers 
    so there may be a need to convert to UNC format 
#> 
#$backup.replace(':','$') 
#$backup 
 
$backup = Join-Path "\\$($server.split('\')[0])" $backup.replace(':','$') 
  
if(!(Test-Path $backup)) { 
    Throw "Database backup $backup not found" 
} 
return  $backup 
 
} 
 
function invoke-DatabaseRestore { 
    param ([String]$SQLServer="(local)", $BackupPath, [String]$BackupFileFilter = "") 
    #load assemblies 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
    [System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
    [Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
      
   
        $backupFile = $BackupPath 
  
        #we will query the database name from the backup header later 
        $server = New-Object ( "Microsoft.SqlServer.Management.Smo.Server" ) $SQLServer 
        $backupDevice = New-Object"Microsoft.SqlServer.Management.Smo.BackupDeviceItem" ) ($BackupPath, "File") 
        $smoRestore = new-object( "Microsoft.SqlServer.Management.Smo.Restore" ) 
        $backupDevice| FL * 
  
        #Get default log and data file locations http://sqlblog.com/blogs/allen_white/archive/2009/02/19/finding-your-default-file-locations-in-smo.aspx 
        $DataPath = if ($server.Settings.DefaultFile.Length -gt 0 ) { $server.Settings.DefaultFile } else { $server.Information.MasterDBLogPath } 
        $LogPath = if ($server.Settings.DefaultLog.Length -gt 0 ) { $server.Settings.DefaultLog } else { $server.Information.MasterDBLogPath } 
  
        #restore settings 
        $smoRestore.NoRecovery = $false; 
        $smoRestore.ReplaceDatabase = $true; 
        $smoRestore.Action = "Database" 
        $smoRestore.PercentCompleteNotification = 10; 
        $smoRestore.Devices.Add($backupDevice) 
   
        #get database name from backup file 
        $smoRestoreDetails = $smoRestore.ReadBackupHeader($server) 
   
        #display database name 
        "Database Name from Backup Header : " +$smoRestoreDetails.Rows[0]["DatabaseName"] 
   
        #give a new database name 
        $smoRestore.Database = $smoRestoreDetails.Rows[0]["DatabaseName"] 
  
        #Relocate each file in the restore to the default directory 
        $smoRestoreFiles = $smoRestore.ReadFileList($server) 
  
        foreach ($File in $smoRestoreFiles) { 
            #Create relocate file object so that we can restore the database to a different path 
            $smoRestoreFile = New-Object"Microsoft.SqlServer.Management.Smo.RelocateFile" ) 
   
            #the logical file names should be the logical filename stored in the backup media 
            $smoRestoreFile.LogicalFileName = $File.LogicalName 
  
            $smoRestoreFile.PhysicalFileName = $( if($File.Type -eq "L"{$LogPath} else {$DataPath} ) + "\" + [System.IO.Path]::GetFileName($File.PhysicalName) 
            $smoRestore.RelocateFiles.Add($smoRestoreFile) 
        } 
        #restore database 
        $smoRestore.SqlRestore($server) 
   
     
} 
 
 
foreach($db in $RestoreDbList.split(",")) 
{ 
$backupFile= BackupFile -server $Backupserver -database $db 
invoke-DatabaseRestore -SQLServer $targetSErver -BackupPath $backupFile 
} 
} 
 
Invoke_SQLDBRestore -SourceServer AQDBSP18 -DestServer AQSPDBSP02 -RestoreDbList "SafetyDB,rtc,rtcab1"

 

Flow:-

To Backup refer my previous backup post

PowerShell – Backup Specific group of SQL Databases

BackupandRestoreImage

 

 

Posted in PowerShell, SQL | Tagged , , | Leave a comment

PowerShell – Backup Specific group of SQL Databases

<#
.SYNOPSIS

The purpose of the script is to initiate backup only for desired group of databases. The requirement is to refresh databases.

.DESCRIPTION

The advantage is that the flexibility of passing a list of specific group of databases for backup.

The backup databases has three mandatory parameters

$SQLServer – Source SQL Server where we have to initiate the backup – For Example HQDBSP18
$BackupDirectory – Backup path for the BAK files – For example – F:\PowerSQL
$dbList – List of databases separated by comma – “rtc,SafetyDB”

.NOTES
Author : Prashanth Jayaram
Requires: Make sure you have all the required admin access on the server

.EXAMPLE

Invoke_SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL -dbList “SafetyDB,rtc,rtcab1

.LINK
#>

Function Invoke_SQLDBBackup 
{ 
 
param ([Parameter(Mandatory=$true,Position=0)][String]$SQLServer,  
[Parameter(Mandatory=$true,Position=1)][String]$BackupDirectory, 
[Parameter(Mandatory=$true,Position=2)][String]$dbList) 
 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoExtended") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.ConnectionInfo") | Out-Null 
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SmoEnum") | Out-Null 
 
$BackupDate = get-date -format yyyyMMdd_HHmmss 
 
$Server = new-object ("Microsoft.SqlServer.Management.Smo.Server") $SQLServer 
 
foreach ($Database in $Server.databases) 
{ 
foreach($db in $DbList.split(",")) 
{ 
if($Database.Name -eq $db) 
{ 
$DatabaseName = $Database.Name 
$DatabaseBackup = new-object ("Microsoft.SqlServer.Management.Smo.Backup") 
$DatabaseBackup.Action = "Database" 
$DatabaseBackup.Database = $DatabaseName 
$DatabaseBackup.Devices.AddDevice($BackupDirectory + "\" + $DatabaseName + "_" + $BackupDate + ".BAK", "File") 
$DatabaseBackup.SqlBackup($Server) 
} 
} 
} 
}

Invoke_SQLDBBackup -SQLServer HQDBSP18 -BackupDirectory f:\PowerSQL 
-dbList "SafetyDB,rtc,rtcab1"

 

BackupSpecificDatabases

 

 

Posted in PowerShell, SQL | Tagged , , , | Leave a comment

SQL Server : Could not load file or assembly ‘Microsoft.SqlServer.BatchParser, Version=10.0.0.0

I have recently experienced an issue with a third-party  backup application (Commvault) that was trying to backup the databases from the SQL 2014 . It was throwing a below error

“Description: Error encountered during backup. Error: [ Could not load file or assembly ‘Microsoft.SqlServer.BatchParser, Version=10.0.0.0, Culture=neutral, PublicKeyToken=89845dcd8080cc91’ or one of its dependencies. The system cannot find the file specified.]”

This Assembly Version=10.0.0.0 is something to do with SQL 2008 or R2 feature pack. I couldn’t able to list the assembly in the c:\windows\assembly folder.

To work around this problem, download and install the following files from SQL Server 2008  Feature Pack

Download the Microsoft SQL Server 2008 Feature Pack

  1. x64\SharedManagementObjects.msi
  2. x64\SQLSysClrTypes.msi

SQL Error

The SQLSysClrTypes.msi should be installed first due to its dependency on SharedManagementObjects.

Successful Backup!!!. I hope some one may get hint from this post.

Job done:-)

 

Posted in SQL | Tagged | 3 Comments

PowerSehll : How to suppress the table heading and remove the blank space from a text file

There are a multiple ways to suppress and remove the first line from text file. The first scenario would be writing a suppressed heading to an output text file Or after writing, we can remove the line using trim function Or using the trick shared in PowerShell magazine.

Just give you a background, we wanted to create a input a file based on some criteria and use the output  text file as an input for further processing.

For example,

I came across an requirement to uninstall the list of patches that were installed yesterday. Have to get a list of patches and store it in a file and used PowerShell script to uninstall the patches.

Using Looping

PS:\>$Computername='ABCD'
PS:\>$outFile='HotFix.txt'
PS:\>Get-HotFix -ComputerName $computerName|
where {$_.installedOn -gt "4/01/2013 12:00:00 AM "}|
ForEach-Object { $_.hotfixID >>$outFile}

Using Trim and set-content cmdlet to update the file by trimming the blank space

 

PS:\>$Computername='ABCD'
PS:\>$outFile='HotFix.txt'
PS:\>Get-HotFix -ComputerName $computername|where {$_.installedOn -gt "4/01/2013 12:00:00 AM "}|select hotFixID |out-file $outFile
PS:\>(gc $outFile) | ? {$_.trim() -ne "" } | set-content $OutFile

Using assignment and and redirecting the operator

PS:\>$Computername='ABCD'
PS:\>$outFile='HotFix.txt'
PS:\>$a,$b=get-content $OutFile 
PS:\>$b>$OutFile

removeBlank

Reference :-

remove-the-first-line-from-a-text-file/

 

Posted in PowerShell | Tagged , , | Leave a comment

PowerShell – SQL Databases Backup Status Report of Multiple Servers

 

This post is a requirement from one of my blog reader.

There are plenty of scripts available to get the backup status of the databases across multiple server. The  requirement is to read a server name and application names from an input file. The server name is used for building a connection and to get the required backup details but where as an app name is used for making an heading in a HTML body.

The Input CSV file consists of Servername, ApplicationName. In this case the App names are separated ‘/’.

InputFile

You need to change the below colored input values as per your environment setup

  • ServerList – Input file where DB Server and Application Names are Listed
  • OutputFile – Used for HTML Email body
  • emlist – You can send it more than one intended receipients. The receipients list are separated by comma
  • MailServer – Valid SMTP servername

CODE:

#Change value of following variables as needed
$ServerList = "f:\Powersql\ServerList.csv"
$OutputFile = "f:\Powersql\Output.htm"
$emlist="pjayaram@appvion.com,prashanth@abc.com"
$MailServer="maa.stmp.com"
 
$HTML = '<style type="text/css">
#Header{font-family:"Trebuchet MS", Arial, Helvetica, sans-serif;width:100%;border-collapse:collapse;}
#Header td, #Header th {font-size:14px;border:1px solid #98bf21;padding:3px 7px 2px 7px;}
#Header th {font-size:14px;text-align:left;padding-top:5px;padding-bottom:4px;background-color:#A7C942;color:#fff;}
#Header tr.alt td {color:#000;background-color:#EAF2D3;}
</Style>'
$HTML += "<HTML><BODY><Table border=1 cellpadding=0 cellspacing=0 width=100% id=Header>
  <TR>
   <TH><B>Database Name</B></TH>
   <TH><B>RecoveryModel</B></TD>
   <TH><B>Last Full Backup Date</B></TH>
   <TH><B>Last Differential Backup Date</B></TH>
   <TH><B>Last Log Backup Date</B></TH>
   </TR>"
  
  
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
Import-Csv $ServerList |ForEach-Object {
$ServerName=$_.ServerName
$AppName=$_.ApplicationName
$HTML += "<TR bgColor='#ccff66'><TD colspan=8 align=center>$ServerName - $AppName</TD></TR>"
$SQLServer = New-Object ('Microsoft.SqlServer.Management.Smo.Server') $ServerName 
 Foreach($Database in $SQLServer.Databases)
{
$DaysSince = ((Get-Date) - $Database.LastBackupDate).Days
$DaysSinceDiff = ((Get-Date) - $Database.LastDifferentialBackupDate).Days
$DaysSinceLog = ((Get-Date) - $Database.LastLogBackupDate).Days
IF(($Database.Name) -ne 'tempdb' -and ($Database.Name) -ne 'model')
{
if ($Database.RecoveryModel -like "simple" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>NA</TD>
     </TR>"
}
}
  if ($Database.RecoveryModel -like "full" )
{
if ($DaysSince -gt 1){
  $HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='RED'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
}
if ($DaysSince -lt 1)
{
$HTML += "<TR >
     <TD>$($Database.Name)</TD>
     <TD>$($Database.RecoveryModel)</TD>
     <TD bgcolor='GREEN'>$($Database.LastBackupDate)</TD>
     <TD>$($Database.LastDifferentialBackupDate)</TD>
     <TD>$($Database.LastLogBackupDate)</TD>
     </TR>"
}
 }
}
}
 
$HTML += "</Table></BODY></HTML>"
$HTML | Out-File $OutputFile
 
Function sendEmail  
 
{ 
param($from,$to,$subject,$smtphost,$htmlFileName)  
 
$body = Get-Content $htmlFileName 
$body = New-Object System.Net.Mail.MailMessage $from, "$to", $subject, $body 
$body.isBodyhtml = $true
$smtpServer = $MailServer
$smtp = new-object Net.Mail.SmtpClient($smtpServer)
$smtp.Send($body)
 
}  
$date = ( get-date ).ToString('yyyy/MM/dd')
$emlist
sendEmail pjayaram@appletonideas.com $emlist "Backup Report - $Date" $MailServer $OutputFile

 

Output

BackupStatusReport

Posted in Backup and Restore, PowerShell, SQL | Tagged , | Leave a comment

MongoDB : How to get Top, Bottom,Middle and Range of records

In MongoDB,the limit() method is used to limit the result set to be returned. You can also use this with various methods such as sort() and skip() for various combination of requirement.

The MS SQL equivalent is TOP function

>SELECT TOP 10 * FROM <TABLENAME>

Some examples are

Top ‘N’Record

db.categories.find().sort({$natural:1}).limit(10 )

Bottom ‘N’ Record

db.categories.find().sort({$natural:-1}).limit(10 )

Middle Record

db.categories.find().skip(db.categories.count()/2).limit(1)

Range of records from 3 and then select 4 records

>var startRange=3
> var EndRange=4
> db.categories.find().skip(startRange).limit(EndRange)

Output:

TopMiddle

 

Posted in MongoDB | Tagged , , | Leave a comment

MongoDB – Insert,Update,Upsert and Delete Examples – CRUD

In MongoDB we have to use either insert() or save() method to add the document to a collection

Insert Single document

> db.employee.insert( 
    { 
    "employee_id":1101, 
    "name":"Prashanth", 
    "sal":90000, 
    "dob"new Date(1983,2,3,5,20), 
    "department": 
        [ 
            'DB Amdin','DB Developer' 
        ], 
    "Location":"New York" 
    });

Insert Multiple document –

We have to use an array to pass multiple documents and its enclosed in a square brackets,separated by comma.

>db.employee.save( 
[ 
{ 
"employee_id":1102"name":"Jayaram""sal":95000"dob"new Date(1983,18,4,12,05), 
"department":"Web Admin""Location":"New York" 
}{ 
"employee_id":1103"name":"Pravitha""sal":195000"dob"new Date(2015,07,06,11,22), 
"department":['Health Science','Scientist'], 
"Location":"NJ" 
}{ 
"employee_id":1104"name":"Prarthana""sal":295000"dob"new Date(2015,07,06,11,23), 
"department":['Engineer','Pilot'], 
"Location":"NJ" 
}, 
{
"employee_id":1105"name":"Ambika""sal":80000"dob"new Date(1983,2,3,5,20), 
"department":['DB Amdin','DB Developer'], 
"Location":"Dallas" 
}, 
] 
);

Mongo DB’s Update

Mongo DB’s update() method used to update values of an existing document

  • Update the document Prashan and set the sal to 100K
>db.employee.update({name: 'Prashan'},{$set: {sal: 100000}})
  •   Update sal to 10000 for all. To update multiple document you need to set a parameter multi to true.

>db.employee.update({},{$set: {sal:10000}},{multi:true})

Use of Multiply operator in Update

  • multiply the sal by 10 where sal is > 10000 for all
>db.employee.update({sal:{$gt:10000}},{$mul: {sal:10}},{multi:true})

EmployeesalaryUpdate

 

The Users document

The users collection has four documents and its details are as follows

 

>db.users.insert(

[{
"name" : "Prayer",
"age" : 1,
"status" : "B"
},
{
"name" : "Pravitha",
"age" : 10,
"status" : "C"
},
{
"name" : "Prashanth",
"age" : 33,
"status" : "A"
},
{
"name" : "Ambika",
"age" : 32,
"status" : "E"
}])
 In the below example, the status is update to “N” where age>18 of the users collection
>db.users.update({age:{$gt:18}},{$set: {status:"N"}},{multi:true})

userUpdateUse Multiple criteria’s to update the document

  • In the below example, the status is updated to “New” where age<15 and Name is Pravitha of the users collection
>db.users.update( {"age": {$lt :15}, "name" : "Pravitha" }, { $set : { status: "New" } } )

Use Regular Expression

  • In the below example, the search parameter ‘Pra’ is searched and updated its status to “Yes”
>var search='Pra'
>db.users.find({name : new RegExp(search)}).forEach(function(doc) { db.users.update({_id:doc._id},{$set:{"status":"Yes"}})})

 UsersUpdate.jpg

MongoDB’s Upserts

• Upserts are a special type of inserts that allows a document to be inserted if it is not found by the update criteria
• We specify upsert: true inside the db.collection.update function

>db.employee.update({name: 'Prayer'},{$set: {sal: 100000}},{upsert:true})

MongoDB’s Remove

MongoDB’s remove() method is used to remove document from the collection

• To delete a document from the database, we can use db.collection.remove() function.
• To remove all documents, we can use – db.collection.remove({})

Remove a document

>db.employee.remove({"name":"Prashanth"})

Remove a document based on condition

>db.employee.remove({"sal":{$lt:85000}})

Remove All documents

>db.employee.remove({})
Posted in MongoDB, Uncategorized | Tagged , , , , , , | Leave a comment

MongoDB – How to Copy Database

Using copydb

Run the copydb under admin database with from,to, host parameters

>use admin
switched to db admin
> db.runCommand({ copydb: 1, fromdb: “test”, todb: “new_test”, fromhost: “localhost” })

Using copyDatabase method

The db.copyDatabase() method is used to copy a database.

>db.copyDatabase(“source_test”,”TargetTest”,”LocalHost”)

CopyDatabase

 

Posted in MongoDB | Tagged , , | Leave a comment