How to test linkedserver's connectivity using SQL query

declare @Lnksrvr nvarchar(128), @returnvalue int;
set @Lnksrvr = 'LinkedServer_ConnectionName';
begin try
    exec @returnvalue = sys.sp_testlinkedserver @Lnksrvr;
end try
begin catch
    set @returnvalue = sign(@@error);
end catch;
if @returnvalue <> 0
  raiserror('Unable to connect to server. Please try later!', 16, 2 );
  

SQL Server Function for GetLastDayOfMonth

CREATE FUNCTION [dbo].[fn_GetLastDayOfMonth]     
(    
@InputDate datetime    
)    
RETURNS datetime    
BEGIN    
    RETURN DATEADD(day, -1, DATEADD(month, 1, CAST(CAST(YEAR(@InputDate) AS char(4)) + '/' + CAST(MONTH(@InputDate) AS char(2)) + '/01' AS datetime)))    
END    
  

SQL Server Function for GetFirstDayOfMonth

CREATE FUNCTION [dbo].[fn_GetFirstDayOfMonth]     
(    
@InputDate datetime    
)    
RETURNS datetime    
BEGIN    
    RETURN CAST(CAST(YEAR(@InputDate) AS char(4)) + '/' + CAST(MONTH(@InputDate) AS char(2)) + '/01' AS datetime)    
END    

How to take SharePoint Site into Offline Mode

How do you quiesce the farm?

                1. From Central Administration, Operations, select "Quiesce Farm."

                2. Enter the number of minutes in which you want the farm to be fully quiesced and click "Start Quiescing."

How do you "un-quiesce" (reset) a farm:

                1. From Central Administration, Operations, select "Quiesce Farm."

                2. Click "Close Quiescing."

Turn off SharePoint Services

This will stop the connections to some of the databases. If you try to detach any of the databases you will find that some will have connections active.

In case of the content databases you will probably find that the connections to the have already been taken care of

Either way you will need to stop some of the SharePoint services in order to get the search databases and config database totally disconnected.

You can either stop the services through the Central Administration, or you can do it at the Services Console.

To stop the connections to the existing databases stop the following services:

                1. Office SharePoint Search Service

                2. Windows SharePoint Services Administration

                3. Windows SharePoint Services Search

                4. Windows SharePoint Services Timer

                5. Windows SharePoint Services Tracing

                6. Windows SharePoint Services VSS Writer

                7. World Wide Web Publishing Service

After the services are stopped, you should be able to detach the databases that you are looking to move.

After detaching the databases navigate to where the data and log files are being stored and copy them to your destination.

Reattach your databases.

Restart your services.

Un-quiesce the farm

How to change Primary Key From Int to BigInt

ALTER TABLE [dbo].[TableName]
Drop CONSTRAINT PrimaryKeyConstraintName

--change data type
ALTER TABLE [dbo].[TableName]
ALTER COLUMN ColumnName BigInt

--add primary key
ALTER TABLE [dbo].[TableName]
ADD CONSTRAINT PrimaryKeyConstraintName PRIMARY KEY (ColumnName)