PowerShell – Install PowerShell

Recently got a question regarding PowerShell installation hence this post took an existence.

How to Install PowerShell various versions

http://technet.microsoft.com/en-us/library/hh847837.aspx

http://social.technet.microsoft.com/wiki/contents/articles/21016.how-to-install-windows-powershell-4-0.aspx

Can we have more than one version of Powershell?

Yes, You can have multiple versions PowerShell.

How to switch between Powershell version?

ps:\>Powershell -version 4

ps:\>Powershell -version 3

ps:\>Powershell -version 2

Hope this simple post solve various doubts.

Happy Learning:-)

 

 

 

Posted in PowerShell | Leave a comment

PowerShell : Script Method Error -Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.

I was working on generating scripts for logins, users and server roles and I could able to to generate scripts for users and logins but where as for server roles, the script() method is not working.

The script is throwing following error

Method invocation failed because [Microsoft.SqlServer.Management.Smo.ServerRole] does not contain a method named ‘Script’.
At line:3 char:23
+ $srv.Roles | foreach {$_.Script()+ “GO”}
+ ~~~~~~~~~~~~~~~~~
+ CategoryInfo : InvalidOperation: (:) [], RuntimeException
+ FullyQualifiedErrorId : MethodNotFound

In any such cases the easiest way to identify the problem by issuing the Get-Member cmdlet. It tells you the property and method for the current object.

Using Get-Member, It din’t list script() method that gives me an answer to look for a workaround.

$directoryname= 'E:\Output\'
$sqlserver='AQDBSP18'

Function get-roles {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Roles|Get-Member| Where-Object {$_.name -like 'script*'} 
#foreach {$_.Script()+ "GO"} | Out-File $($directoryname + $serverfilename + "_roles.sql") 
}
#get-roles

Function get-userlogins {
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.Smo") | Out-Null
$srv = New-Object "Microsoft.SqlServer.Management.Smo.Server" $sqlserver
$srv.Logins |Get-Member |Where-Object {$_.name -like 'script*'}
#| foreach {$_.Script()+ "GO"}

#| Out-File $($directoryname + $serverfilename + "_logins.sql") 
}

scriptMethod1

Workaround for generating script for server roles

$directoryname= 'E:\Output\'
$sqlserver='AQDBSP18'
[System.Reflection.Assembly]::LoadWithPartialName("Microsoft.SqlServer.SMO") | out-null
$srv =  New-Object ("Microsoft.SqlServer.Management.SMO.Server") ($sqlserver)
$srv.Logins|Where-Object {$_.IsSystemObject -eq $false -and $_.loginType -like  "windows*"} | foreach { $_.Script() +"GO" | Where {$_ -notlike 'ALTER LOGIN*DISABLE'}
#|Out-File $($directoryname + $sqlserver + "_logins.sql") 
}

 #| Out-File $($directoryname + $sqlserver + "_logins.sql") 
foreach ($Role in $srv.Roles){$Role.EnumServerRoleMembers() | Where-Object {$_ -ne "sa"} |% {"EXEC master..sp_addsrvrolemember @loginame = N'{0}', @rolename = N'{1}'{2}" -f ($_,$Role.Name,"`r`nGO");}
#|Out-File $($directoryname + $sqlserver + "_roles.sql") 
};

 

scriptMethod2

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

Restore a SQL Server Database with Different Methods

Database backups are generated as part of the business continuity planning procedure. Disasters with server or its database are a common scenario that takes place every now and then lead to the loss of an enormous amount of data. In order to sustain the same, administrative ensure backup plans as part of their administrative roles apart from handling the server. There are different ways to restore a SQL Server database, which have been discussed in the segment below for a better understanding of the concept and fulfilment of different user needs.

Discussing Different Ways to Restore A SQL Server Database

Servers deal with not just one but multiple databases which requires their proper maintenance in an automated manner. Automated backup procedure puts least amount of responsibilities on an administrators shoulder.

Following is a diagram represents the methods of restoring SQL DB in multiple ways, which has been further explained individually for a better understanding.

Point in Time Recovery

The diagram represents 3 types of SQL backups:

  • Full DB backup – F’x’
  • Differential backup – D’x’
  • Transaction log backup – T’x’

In this diagram, X defines an incrementing number that is associated to a point-in-time when a particular backup was taken. Meanwhile, the prefixed characters (D, F, and T) represent the type of backup procedures whereas; the point-in-time (p) represents the point in time when a backup took place.

For Instance First Full Backup F1 has taken at P1 point-in-time whereas, the second one, i.e. F2 took place at a later point-in-time which is P9. Similarly, rest of the backup procedures has been specified in the same way.

NOTE: In the figure shown above, P13 is a point-in-time that represents a committed-transaction taking place. However, the backup of transaction log hasn’t taken place until P14. Therefore, recovering to point-in-time P13 becomes a little complicated in order to simplify which method 3 of database restoration has been discussed in the segment below.

Simplifying the Restoration of SQL Server Database

Discussed below are top three different methods of database restoration that can be implemented in respective circumstances to fulfill certain restoration needs.

Method 1: Restoring to the Point in Time – P8

Differential backups increase by successive addition, i.e. the last differential backup after a full database backup consists of all the changes taken place in the database. Therefore, only one can be restored once the full database backup restoration.

Method 2: Restore to Point in Time – P10

Just in case the F2 full backup goes missing, it is still possible for an SQL Administrator to perform a restore with the help of F1. Only difference is that a combination of differential and transaction backup will have to be used to reach P10.

A full database backup does not act as a break to the chain of a transaction log backup. Therefore, restoration of transaction logs can be performed for previously taken place full database backups.

The most important factor for having a full database backup is to have the best of Recovery Time Objective (RTO). The representation here only shows a few transact log backups but a lot more are involved in reality. Thus, in such a case, the restoration of an enormous amount of transaction log backup is excessively time consuming and can result in largely affecting the RTO.

Method 3: Restore to Point in Time – P13

In here, we are discussing situations pointing out the restoration of exact point in time. For instance, transactions need to be restored to a point in time P13 however; the log backup is only available at P14.

DBA are not given exact point in times in real-time situations, which is what complicates the entire situation a lot more. In case a bug happens to take place in a code updating the entire table without a WHERE clause, the DBA is told just to simply restore the database to a point right before the update took place without any specifications made about the point in time.

The only way to tackle a situation like this is by using a transaction log backup with a STOPAT clause specified.

NOTE: Performing a point in time recovery is only permitted to transaction log backups.

Therefore, the restore technique to be used here is by utilizing an UNDO file so that you can restore a Server database in STANDBY mode and mention the STOPAT clause.

TIP: The trick is to know that you are allowed to carry out a RESTORE from the very same transaction log DB repeatedly until the desired results are achieved.

Conclusion:

Some of the possible different ways to restore a SQL Server database in predefined conditions have been discussed. These set of methods can be used as tips to handle similar situations for restoring a database in disaster conditions to a previous state without loss. In addition, you can also restore database with NORECOVERY option.

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

The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)

“The RPC server is unavailable. (Exception from HRESULT: 0x800706BA)” error may occurs when executing PowerShell scripts using WMI query.

There can be a few reasons for this error:

  1. The remote computer is blocked by the firewall.
    Solution: Open the Group Policy Object Editor snap-in (gpedit.msc) to edit the Group Policy object (GPO) that is used to manage Windows Firewall settings in your organization. OpenComputer Configuration, open Administrative Templates, open Network, open Network Connections, open Windows Firewall, and then open either Domain Profile or Standard Profile, depending on which profile you want to configure. Enable the following exception: “Allow Remote Administration Exception” and “Allow File and Printer Sharing Exception“.
  2. Host name or IP address is wrong or the remote computer is shutdown.
    Solution: Verify correct host name or IP address.
  3. The “TCP/IP NetBIOS Helper” service isn’t running.
    Solution: Verity that “TCP/IP NetBIOS Helper” is running and set to auto start after restart.
  4. The “Remote Procedure Call (RPC)” service is not running on the remote computer.
    Solution: Verity that “Remote Procedure Call (RPC)” is running and set to auto start after restart.
  5. The “Windows Management Instrumentation” service is not running on the remote computer.
    Solution: Verity that “Windows Management Instrumentation” is running and set to auto start after restart
Posted in PowerShell | Tagged , , | Leave a comment

XML-> JSON document Example

Convert the XML document to JSON document example

<person>
  <name>John</name>
  <age>25</age>
  <address>
    <city>New York</city>
    <postalCode>10021</postalCode>
  </address>
  <phones>
    <phone type="home">212-555-1234</phone>
    <phone type="mobile">646-555-1234</phone>
  </phones>
</person>

 

{
“name” : “John”,
“age” : 25,
“address” : { “city” : “New York”, “postalCode” : “10021” },
“phones” :
[
{“phone”:”212-555-1234″, “type” : “home”},
{“phone”:”646-555-1234″, “type” : “mobile”}
]
}


You can also use a converter utility to get the required output.

http://codebeautify.org/xmltojson

 

Posted in JSON | Tagged , | Leave a comment

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 "
     }

 }


}   
   

Please refer the below link which gives a full list of required details

SQL Server Instance Security: Scripting Permissions

 

Posted in PowerShell, SQL | Tagged , , | 3 Comments

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 , , | 4 Comments

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 , , , | 2 Comments

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