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.

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, 9 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

0 comments, 108 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, 154 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

0 comments, 790 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, 165 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, 172 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, 169 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

0 comments, 161 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, 994 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, 136 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, 254 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, 256 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,067 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, 106 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, 145 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, 103 reads

Posted in PowerSQL By Prashanth Jayaram on 7 April 2016

MongoDB – Local and Remote Connections in Mongo Shell

To Install and Getting started with MongoDB refer the below links

How to access Mongo instance

  • Open a terminal session or command window.
  • For a local connection to mongod, at the operating system prompt, enter the following command to…

Read more

0 comments, 214 reads

Posted in PowerSQL By Prashanth Jayaram on 6 April 2016

MongoDB -Calculating Collection Size

Calculating Collection Size

We can get the size of a collections by using the shell’s functions

  • db.collection.dataSize() : Returns data size in bytes for the collection.
  • db.collection.storageSize() : Returns allocation size in bytes, including unused space.
  • db.collection.totalSize() : Returns the data size plus the index size in bytes.

Calculating Size…

Read more

0 comments, 130 reads

Posted in PowerSQL By Prashanth Jayaram on 5 April 2016

MongoDB – Calculating Document Size

  • We can get the size of a document is by using the shell’s Object.bsonsize() function.
  • We pass the object_id of the document to get the size

For example in the foo collection, we can find the size of the document using any document id.


Read more

0 comments, 286 reads

Posted in PowerSQL By Prashanth Jayaram on 4 April 2016

Simple Methods to Recover master.mdf File Password

Overview

MS SQL Server is the relational database management system used to store information in form of tables, views, indexes, triggers, constraints and stored procedures. Master Database File (MDF) is the main database file with the file extension .mdf used in SQL Server. These MDF files are protected with password… Read more

1 comments, 1,381 reads

Posted in PowerSQL By Prashanth Jayaram on 15 March 2016

Older posts