Today I faced an issue where one of secondary server box is now not available due to some circumstances, now I have to delete this secondary server Name and Database entry from primary server’s database. If we go through log shipping wizard from Database property page and try to remove secondary server it will ask to connect secondary server but in my case secondary server is now not available with us. To resolve this, here is a script to delete secondary server entry from primary server’s database is: ( in this case there is no need to connect secondary server)

EXEC Master.dbo.sp_delete_log_shipping_primary_secondary

@primary_database N’VirendraTest’,
@secondary_server =  N’VIRENDRA_PC’,
@primary_database =N’LSVirendraTest’;

GO

Please don’t forget to comment on this and your experinces about it.


Sometime SQL Server error msg 8101 occurred, when anyone is trying to insert a new record into a table that contains an identity column without specifying the columns in the INSERT statement and trying to assigning a value to the identity column instead of letting SQL Server assign the value. Error displays as

Server: Msg 8101, Level 16, State 1, Line 2
An explicit value for the identity column in table “Table_Name” can only be specified when a column list is used and IDENTITY_INSERT is ON.

 The solution for above error is , we should include SET IDENTITY_INSERT ON,

Example :

SET IDENTITY_INSERT Table_name ON
Go
Insert into Table_Name (Col1,Col2,Col3,Col4)
Select Col1,Col2,Col3,Col4 from Any_Table_Name
Go

SET IDENTITY_INSERT Table_name OFF
Go


SQL Server’s Replication requires many components to replicate data from one location to another. The below image is a high-level overview of the pieces involved in a replication setup.

The components used for replication setup include a Publisher and its publication database. The publication database contains a publication that may include a number of articles. The setup also includes a Distributor and its distribution database as well as a Subscriber and its subscription database, which contains the subscription. And using replication agents data replicates as per defined architecture.

The replication components details are as below.

Articles
For each SQL Server object that should be replicated, an article needs to be defined. Each article corresponds to a single SQL Server object like tables, views, stored procedures and functions (For a complete list of objects that can be replicated, check out the topic, Publishing Data and Database Objects in SQL Server Books Online.) An article’s properties determine whether that article contains the entire object or a filtered subset of its parts. For example, an article can be configured to contain only some of the columns of a table. With some restrictions, multiple articles can be created on a single object.

Publications

A publication is a collection of articles grouped together as one unit. Every article is defined to be part of exactly one publication. But in few cases we can also define different articles on the same object in separate publications. A publication supports several configurable options that apply to all its articles. Perhaps the most important option is the one that lets you define which type of replication to use.

Publication Database

A database that contains objects designated as articles is called a publication database, when we set up a publication on a database, SQL Server modifies the inner workings of that database and creates several replication-related objects. A publication database is also protected against being dropped. A publication can contain articles from a single publication database only.

Publisher

The Publisher is a database instance that makes data available to other locations through replication. The Publisher can have one or more publications, each defining a logically related set of objects and data to replicate.

Distributor

Each Publisher is linked to a single Distributor. The Distributor is a SQL Server instance that identifies changes to the articles on each of its Publishers. Depending on the replication setup, the Distributor might also be responsible for notifying the Subscribers that have subscribed to a publication that an article has changed. The information about these changes is stored in the distribution database until all Subscribers have been notified or the retention period has expired. The Distributor can be configured on a SQL Server instance separate from the Publisher, but often the same instance takes the role of the Publisher and the Distributor.

Distribution Databases

Each Distributor has at least one distribution database. The distribution database contains a number of objects that store replication metadata as well as replicated data. A Distributor can hold more than one distribution database , However, all publications defined on a single Publisher must use the same distribution database.

Subscriber

Each SQL Server instance that subscribes to a publication is called a Subscriber. The Subscriber receives changes to a published article through that publication. A Subscriber does not necessarily play an active role in the replication process. Depending on the settings selected during replication setup, it might receive the data passively.

Subscriptions

A subscription is the counterpart of the publication. Each subscription creates a link, or contract, between one publication and one Subscriber. There are two types of subscriptions: push subscriptions and pull subscriptions. In a push subscription, the Distributor directly updates the data in the Subscriber database. In a pull subscription, the Subscriber asks the Distributor regularly if any new changes are available, and then updates the data in the subscription database itself.

Subscription databases

A database that is the target of a replication subscription is called a subscription database. As in the case of the publication database, SQL Server modifies the subscription database during the first initialization. The most obvious change is the addition of a few replication-related objects. However, unlike publication databases, SQL Server doesn’t prevent a subscription database from being dropped.

Replication agents

The replication processes are executed by a set of replication agents. Each agent is an independent Windows executable responsible for one piece of the process of moving the data. In a default installation of replication, each agent is executed by its own SQL Server Agent job. Most of those agents usually run on the Distributor, although some can run on the Subscriber. The Publisher houses replication agents only when the Publisher and Distributor are the same instance. Instead of relying on the SQL Server Agent, you can execute any replication agent manually or by some other scheduling means. However, in most cases, these approaches provide little advantage and often make troubleshooting more complex.

The details of each replication agent types as

Snapshot Agent

In all replication topologies, the Snapshot Agent provides the data required to perform the initial synchronization of the publication database with the subscription database. Transactional replication and merge replication use other agents to keep the data in sync afterwards. For both topologies, replication will use the Snapshot Agent again (after the initial synchronization) only when you request a fresh resynchronization. Snapshot replication, on the other hand, uses the Snapshot Agent exclusively to replicate data. It works by copying all the data every time from the publication database to the subscription database.

Log Reader Agent

The Log Reader Agent reads the transaction log of the publication database. If it finds changes to the published objects, it records those changes to the distribution database. Only transactional replication uses the Log Reader Agent.

Distribution Agent

The Distribution Agent applies the changes recorded in the distribution database to the subscription database. As with the Log Reader Agent, only transactional replication uses the Distribution Agent.

Merge Agent

The Merge Agent synchronizes changes between the publication database and the subscription database. It is able to handle changes in both the publication database and the subscription database and can sync those changes bi-directionally. A set of triggers in both databases support this process. Only merge replication uses the Merge Agent.

Queue Reader Agent

The Queue Reader Agent is used for bi-directional transactional replication.

 

Happy readying …

Sources: Fundamentals of SQL Server 2012 Replication and SQL Server Book Online

The below script will backup all user databases. Every database backup file will be generate as Database name with timestamp.

USE MASTER
GO

– Declaring a cursor named as DBName for all User database having DBID>4

DECLARE DBName Cursor FOR
Select Name as DatabaseName from sys.sysdatabases 
where dbid >4
OPEN DBName
DECLARE @dbName varchar(100);
DECLARE @backupFolder varchar(100);
DECLARE @backupcommand varchar(500);
Set @backupFolder ‘F:\DBBackup\Full\’
FETCH NEXT FROM DBName INTO @dbName
While (@@FETCH_STATUS <>-1)
BEGIN
Set @backupcommand =‘Backup Database ‘+ @dbName +‘ to Disk = ”’+ @backupFolder + @dbName +‘_['+REPLACE(Convert(varchar,Getdate(), 109),':', '-')+ '].Bak”’
–Print @backupcommand
EXEC (@backupcommand)
Fetch NEXT FROM DBName INTO @dbName
END
CLOSE DBName
DEALLOCATE DBName
GO

 

Sometimes with SQL server a DBA or Developer might be faced an error saying “The transaction log for database is full” OR “Could not allocate space for object because the filegroup is full” OR may be as “Primary filegroup is full” There may be many reason for same but most probable error is related to Data/log file’s AUTOGROWTH option definition at the time of database creation. To resolve this issue commonly DBA may perform a shrink log operation and do changes with the DATA/LOG files AUTOGROWTH/MAXSIZE option. Here is a script to find out all databases’s files AUTOGROWT values as

SELECT DB_NAME(MF.DATABASE_IDAS DBNAME,MF.NAME AS FILENAME ,SIZE/128.0 AS CURRENTSIZE_MB,DB.RECOVERY_MODEL_DESC RECOVERYMODEL, MF.TYPE_DESC AS ‘FILE TYPE (DATA/LOG FILE)’,
CASE WHEN IS_PERCENT_GROWTH = 0 THEN LTRIM(STR(MF.GROWTH * 8.0 / 1024,10,1))+’ MB, ‘ELSE ‘BY ‘+ CAST(MF.GROWTH AS VARCHAR) +’ PERCENT, ‘END + CASE WHEN MAX_SIZE =-1 THEN ‘UNRESTRICTED GROWTH’ ELSE ‘RESTRICTED GROWTH TO ‘ +LTRIM(STR(MAX_SIZE * 8.0 / 1024,10,1)) + ‘ MB’ END AS AUTOGROW, MF.PHYSICAL_NAME

FROM SYS.MASTER_FILES MF INNER JOIN SYS.DATABASES DB ON MF.DATABASE_ID =DB.DATABASE_ID
ORDER BY MF.SIZE/128.0 DESC

Click here to see PDF file  : Back to Basics

Save a Command’s Output to a File

The use of redirection operators, specifically > and >> operators are used to redirect the output of a command to a file, giving us a saved version of whatever data the command produced in the Command Prompt window.

Redirection Operator

Explanation

Example

>

The greater-than sign is used to send to a file, or even a printer or other device, whatever information from the command would have been displayed in the Command Prompt window had you not used the operator. assoc > types.txt

>>

The double greater-than sign works just like the single greater-than sign but the information is appended to the end of the file instead of overwriting it.

ipconfig >> netdata.txt

<

The less-than sign is used to read the input for a command from a file instead of from the keyboard. sort < data.txt

|

The vertical pipe is used to read the output from one command and use if for the input of another. dir | sort

Copy command output to Windows clipboard

The clip command copies all output from a command to the Windows Clipboard. You can then paste that output into to any Windows application, such as into Windows Notepad or Microsoft Word.

command | clip

Copies the output from the specified command to the Windows clipboard.

Example :

dir | clip Copies the directory listing that is output from the dir command to the Windows Clipboard.

clip < filename

Copies the contents of the filename file to the Windows Clipboard.

Example :

CLIP < README.TXT Places a copy of the text from readme.txt on to the Windows clipboard.

Customize the Command Prompt Title Bar Text

The TITLE command Sets the windows title for the command prompt window

TITLE string

The specified set as command prompt windows.

Example :

Title Virendra Yaduvanshi.

Copy From the Command Prompt

Right-click anywhere in the Command Prompt window and choose Mark. Now, highlight with left mouse button whatever like to copy. Once selection is made, press Enter.

Now we can paste that information into any program.

Open the Command Prompt From Any Location

There’s a super easy Command Prompt trick that will let open a Command Prompt window from whatever folder we’re viewing in Windows.

All it is depend on navigate, in Windows, to the folder where we want to start working from in the Command Prompt. Once there, hold down Shift key while right-click anywhere in the folder. Once the menu pops up, notice there is an entry that’s not usually there: Open command window here.

Drag and Drop For Easy Path Name Entry

There’s a Command Prompt trick that makes this much easier: drag and drop.

Just navigate to the folder you want the path for in Windows Explorer. Once there, drag the folder or file to the Command Prompt window and let go. Like magic, the full path is inserted, saving you a considerable amount of typing depending on the length and complexity of the path name.

Robust File Copy

Robocopy, or “Robust File Copy”, is a command-line directory and/or file replication command. Robocopy functionally replaces Xcopy, with more options. It has been available as part of the Windows Resource Kit starting with Windows NT 4.0, and was first introduced as a standard feature in Windows Vista and Windows Server 2008. The command is robocopy.

View Your Computer’s Important Network Information

Everything we want to know about our network connection is available somewhere in the Control Panel in Windows, but it’s much easier to find, and much better organized, in the results from the ipconfig command.

Map a Local Folder Just Like a Network Drive

The subst command is used to map a local folder just like a Network drive. Just execute the subst command, followed by the path of the folder which need to appear as a drive. For example, let’s say you want your C:\Windows\Fonts folder to appear as the Q: drive. Just execute subst q: c:\windows\fonts and you’re set!

Automatically Complete Commands with Tab Completion

Tab completion is another Command Prompt trick that can save you lots of time, especially if your command has a file or folder name in it that you’re not completely sure of.

Run Commands Simultaneously

We can put && between two commands and execute them one after another. The command on the left will execute first followed by the command on the right of the double ampersand.

Change Windows Explorer’s Starting Location

Right-click on the Windows Explorer icon in taskbar. Right click on it and choose Properties.

Under “Target,” change the path to the folder you want Windows Explorer to display by default

Backspace Key to Go Up

Windows Explorer in Windows XP allowed you to move up a folder using the Backspace key. If you got used to that feature, you’re probably frustrated that it was removed in Windows 7. The Backspace key in Windows 7 moves you Back in the folder browsing history, not to the parent folder of the current folder.

 

Add Copy To / Move To to the Windows Explorer Right Click Menu

A hidden functionality in Windows allows you to right click on a file, select Copy To Folder or Move To Folder, and the move to box will pop up and let you choose a location to either copy or move the file or folder to. The quick registry hack to get this working:-

1) Go to HKEY_CLASSES_ROOT\AllFilesystemObjects\shellex\ContextMenuHandlers

Now you will double-click on the (Default) value and enter the following:

{C2FBB630-2971-11D1-A18C-00 C04FD 75D13}

For Move to – Now when you right click on a file or folder, you should see the following options:

{C2FBB631-2971-11D1-A18C-00C04FD75D13}      Page 16

Use check boxes to select multiple files
In order to select multiple files for an operation such as copying, moving or deleting in Windows Explorer, you generally use the keyboard and the mouse, Ctrl-clicking every file you want to select.
Steps are as below :
1. In Windows Explorer, click Organize, and then select “Folder and search options.”
2 .Click the View tab
3. In Advanced Settings, scroll down and check the box next to “Use check boxes to select items.” Click OK.

Launch taskbar apps without a mouse

Likewise, you can launch any program on the taskbar without the mouse. Press the Windows key and the number that corresponds to the position of the application on the taskbar — for example, Windows key-1 to launch the left-most application on the taskbar, Windows key2 to launch the second left-most application and so on.

Filtering Folder Content Views

Moving your mouse over a Heading will display a drop-down arrow. Clicking on the down arrow will display a set of filtering options (check boxes) that can be set for viewing items in the column.

Ranking Functions – ROW_NUMBER,RANK,DENSE_RANK,NTILE

SQL Server 2005 introduced four new ranking functions: ROW_NUMBER, RANK, DENSE_RANK, and NTILE. These functions allow you to analyze data and provide ranking values to result rows of a query. For example, you might use these ranking functions for assigning sequential integer row IDs to result rows or for presentation, paging, or scoring purposes.

All four ranking functions follow a similar syntax pattern:

function_name() OVER([PARTITION BY partition_by_list] ORDER BY order_by_list)

The basic syntax follows.


ROW_NUMBER() OVER ([<partition_by_clause>] <order_by_clause>)
RANK() OVER ([<partition_by_clause>] <order_by_clause>)
DENSE_RANK() OVER([<partition_by_clause>]<order_by_clause>)
NTILE(integer_expression) OVER ([<partition_by_clause>] <order_by_clause>)


Ranking functions are a subset of the built in functions in SQL Server. They are used to provide a rank of one kind or another to a set of rows in a partition. The partition can be the full result set, if there is no partition. Otherwise, the partitioning of the result set is defined using the partition clause of the OVER clause. The order of the ranking within the partition is defined by the order clause of OVER. Order is required even though partition is optional.

 

ROW_NUMBER: ROW-NUMBER function returns a sequential value for every row in the results. It will assign value 1 for the first row and increase the number of the subsequent rows.

 

Syntax:

SELECT ROW_NUMBER() OVER (ORDER BY column-name), columns FROM table-name

 

OVER – Specify the order of the rows

ORDER BY – Provide sort order for the records


RANK: The RANK function returns the rank based on the sort order. When two rows have the same order value, it provide same rank for the two rows and also the ranking gets incremented after the same order by clause.

Syntax:
SELECT
RANK() OVER ([< partition_by_clause >] < order_by_clause >)

SELECT RANK() OVER ([< partition_by_clause >] < order_by_clause >)

Partition_by_clause – Set of results grouped into partition in which RANK function applied.
Order_by_clause – Set of results order the within the partition


In the above example, based on the sort order Employee Name, the Rank is given.
The first two rows in the list has same Employee Name, those rows are given same Rank, followed by the rank of next for another set of rows because there are two rows that are ranked higher. Therefore, the RANK function does not always return consecutive integers.


DENSE_RANK: The DENSE_RANK function is very similar to RANK and return rank without any gaps. This function sequentially ranks for each unique order by clause.

Syntax:
SELECT DENSE_RANK() OVER ([< partition_by_clause >] <order_by_clause>)</order_by_clause>

SELECT DENSE_RANK() OVER ([< partition_by_clause >] )

Partition_by_clause – Set of reults grouped into partition in which DENSE RANK function applied.
Order_by_clause – Set of results Order the within the partition

NTILE: NTILE () splits the set of rows into the specified number of groups. It equally splits the rows for each group when the number of rows is divisible by number of group. The number is incremented for every additional group. If the number of rows in the table is not divisible by total groups count (integer_expression), then the top groups will have one row greater than the later groups. For example if the total number of rows is 6 and the number of groups is 4, the first two groups will have 2 rows and the two remaining groups will have 1 row each

Syntax:
SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] <order_by_clause>) </order_by_clause>

 

SELECT NTILE (integer_expression) OVER ([< partition_by_clause >] )

(integer_expression) – The number of groups into which each partition must be divided.