Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 

PowerSQL By Prashanth Jayaram

Technology enthusiast with 7+ years of experience in Database Technology. I am Microsoft Certified Professional with certificates of OCA, MCP, MCTS, MCITP developer, MCITP administration and backed with a degree in Master of Computer Application. My expertise lies in T-SQL programming, Replication, PowerShell and Performance Tuning. Hobbies are Drawing, playing soccer and listening to Melodies songs.

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… Read more

0 comments, 249 reads

Posted in PowerSQL By Prashanth Jayaram on 6 June 2016

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… Read more

0 comments, 146 reads

Posted in PowerSQL By Prashanth Jayaram on 1 June 2016

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… Read more

0 comments, 2,566 reads

Posted in PowerSQL By Prashanth Jayaram on 28 May 2016

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…

Read more

0 comments, 208 reads

Posted in PowerSQL By Prashanth Jayaram on 25 May 2016

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″,… Read more

0 comments, 159 reads

Posted in PowerSQL By Prashanth Jayaram on 24 May 2016

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… Read more

3 comments, 186 reads

Posted in PowerSQL By Prashanth Jayaram on 23 May 2016

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.… Read more

0 comments, 187 reads

Posted in PowerSQL By Prashanth Jayaram on 17 May 2016

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… Read more

2 comments, 826 reads

Posted in PowerSQL By Prashanth Jayaram on 12 May 2016

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 –… Read more

2 comments, 186 reads

Posted in PowerSQL By Prashanth Jayaram on 11 May 2016

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… Read more

3 comments, 196 reads

Posted in PowerSQL By Prashanth Jayaram on 9 May 2016

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… Read more

0 comments, 191 reads

Posted in PowerSQL By Prashanth Jayaram on 2 May 2016

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… Read more

2 comments, 216 reads

Posted in PowerSQL By Prashanth Jayaram on 27 April 2016

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

Read more

0 comments, 1,020 reads

Posted in PowerSQL By Prashanth Jayaram on 26 April 2016

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

Read more

0 comments, 175 reads

Posted in PowerSQL By Prashanth Jayaram on 20 April 2016

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”)

 


Read more

0 comments, 278 reads

Posted in PowerSQL By Prashanth Jayaram on 14 April 2016

MongoDB – List All the databases

The function getDBs() function returns all the database list

db.getMongo().getDBs()

The runCommand is a special type of query called a database command and its is used database administration. The listDatabases parameter provides a list of all existing databases.

You can see all commands by running the db.listCommands() command.

db.runCommand({ “listDatabases”:… Read more

0 comments, 271 reads

Posted in PowerSQL By Prashanth Jayaram on 13 April 2016

SQL DB Details Of Multiple Servers Using PowerShell

 

One of my blog reader requested me to get the DB details(Server,DBName,Size,SpaceAvailable,LogSize,PercentUsed Log Space,compatiblilty mode, recovery model etc:-) using PowerShell hence this post is written.

The power of PowerShell is to capture an output of DBCC commands and store it an variable for later processing of the desired result Read more

4 comments, 1,114 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2016

MongoDB -Get Total datasize of all DB’s

var sum = 0; db.getMongo().getDBs()[“databases”].forEach(function(x) { sum += db.getMongo().getDB(x.name).stats().dataSize }); print(sum );

OR
db = db.getSiblingDB(“admin”);
dbs = db.runCommand({ “listDatabases”: 1 }).databases;
var sum1=0;dbs.forEach(function(database) { sum1+=db.getMongo().getDB(database.name).stats().dataSize }); print(sum1);


Read more

0 comments, 120 reads

Posted in PowerSQL By Prashanth Jayaram on 11 April 2016

MongoDB – Drop database

The dropDatabase command drops the current database, deleting the associated data files.

Different methods to drop database

Method 1:

1. select the database which you want to delete
>use < database name >
2. Then issue the below command
>db.dropDatabase()

Method 2:

1. Define the name of the database
>… Read more

0 comments, 168 reads

Posted in PowerSQL By Prashanth Jayaram on 8 April 2016

MongoDB -Get Size, RowCount, CollectionSize

There are two different ways of getting the desired result

The first method is iterating through each collection using for loop and displaying the result

cols = db.getCollectionNames();
for (index = 0; index < cols.length; index++) {
var coll = db.getCollection(cols[index]);
var stats = coll.stats();
print(stats.ns, stats.count, stats.size, stats.totalIndexSize); } Read more

0 comments, 123 reads

Posted in PowerSQL By Prashanth Jayaram on 7 April 2016

Older posts