Reverse Log shipping Setup

If database size big and in different data center and we need to reverse log shipping without restore full database.

Follow below steps:-

STEP 1:-

1)      Go to Primary Server and disable backup job

2)      Go to secondary Server run copy job manually and after that run restore job manually after completed the job disable both the jobs.

STEP 2:-

GO TO primary server and take log backup with no recovery command:-

Backup log REVERSE_LOGSHIPPING to disk='d:\REVERSE_LOGSHIPPING.trn' with no recovery

After execution this command you will see your primary Server will show in with restoring mode.

 

 

Step:3

Go to secondary Server and restore log backup that we took in step 2 with Recovery option:-

Use [Master]

Restore log REVERSE_LOGSHIPPING from disk='d:\REVERSE_LOGSHIPPING.trn' with recovery

Now your database will we operational from restoring mode.

 



Step 4:-

Now you can configure logshipping again:-

Right click on the database [REVERSE_LOGSHIPPING] and configure the log shipping same way .

 

That’s all:-

If you want to see the data on both side then you have to take secondary database in operational if it’s on restoring state or if you configure as read_only then you can query easily.

If database in restoring mode you need to run the below command and after that you need to setup log shipping again better option go with stand_by\read only option.

As of now for testing purpose we are making secondary database operational from restoring state:-

 STEP 5 :-

You can see the same record exist on secondary server as well:-

See below screen shot:-

Row count from secondary Server (29)

Row count from Primary Server (29) rows

How to troubleshoot when all Login Removed No Admin Rights

No Admin right for User and sa is disable

1) sa is disable

2) Windows user [Domainname\ashish.malwal] don’t have access to create any table or database it has public role.

How to get backup sys admin right and how to give sys admin right to user?

Steps:-

1.       net stop "SQL Server (MSSQLSERVER)"

2.       net start "SQL Server (MSSQLSERVER)" /m  ---single user mode

1.       sp_addsrvrolemember [Domainname\ashish.malwal],sysadmin ----Give sys admin right existing user

2.       alter login sa enable ---enable sa  login if disable

Now check SSMS and try to create table:-

Get MDF and LDF File Statistics Using fn_virtualfilestats

You can get what is the size of the file as well how many times the reads and writes are done for each file. It also displays the read/write data in bytes with the following script.

SELECT DB_NAME(vfs.DbId) DatabaseName,
       mf.name,
       mf.physical_name,
       vfs.BytesRead,
       vfs.BytesWritten,
       vfs.IoStallMS,
       vfs.IoStallReadMS,
       vfs.IoStallWriteMS,
       vfs.NumberReads,
       vfs.NumberWrites,
       (cast(SIZE AS bigint)*8)/1024 Size_MB
FROM ::fn_virtualfilestats(NULL,NULL) vfs
INNER JOIN sys.master_files mf ON mf.database_id = vfs.DbId
AND mf.FILE_ID = vfs.FileId

SQL Server Satrtup Parameters .

SQL Server Parameters when starting Instance:

1) -m -> Single User Mode

2) -f -> Minimal Configuration Mode

3) -c -> Starts instance quickly by not contacting Service Control Manager.

4) -g -> To set MTL portion of SQL Server

5) -n -> Does not use the Windows application log to record SQL Server events.

6) -T -> Indicates that an instance of SQL Server should be started with a specified trace flag (trace#) in effect. Trace flags are used to start the server with nonstandard behavior.

7) -m <ClientApplicationName> -> Only connections from a specific client application are allowed.

Manage SQL Server Services from the Command Line

You can start and stop Sql Services by cmd by following commands:-

NET START MSSQLSERVER:-

 Starts SQL Server as a service. 

NET STOP MSSQLSERVER :-

Stops SQL Server when running as a service. 

NET PAUSE MSSQLSERVER:-

 Pauses SQL Server when running as a service. 

NET CONTINUE MSSQLSERVER:-

 Resumes SQL Server when running as a service.

To manage named instances of SQL Server, use the following commands: 

NET START MSSQL$instancename :-

Starts SQL Server as a service, where instancename is the actual name of the database server instance. 

NET STOP MSSQL$instancename:-

 Stops SQL Server when running as a service, where instancename is the actual name of the database server instance. 

NET PAUSE MSSQL$instancename :-

Pauses SQL Server when running as a service, where instancename is the actual name of the database server instance. 

NET CONTINUE MSSQL$instancename:-

 Resumes SQL Server when running as a service, where instancename is the actual name of the database server instance.

You can add startup options to the end of net start MSSQLSERVER or net start MSSQL$instancename commands. Use a slash (/) instead of a hyphen (–) as shown in these examples: 
net start MSSQLSERVER /f /m 
net start MSSQL$CUSTDATAWAREHOUS /f /m

Find OS information with SQL Query

EXEC master..xp_cmdshell 'systeminfo'

Track who, when create and modified Login in Sql Server Database from Default Trace.

You can find the user created and modified date from below query.

First Create a Login and user:-

USE [master] 

CREATE login ashish WITH password='Access@123' 

USE [test] 

CREATE USER ashish FROM login ashish 

SELECT createdate,  updatedate, 
       * 
FROM   sys.sysusers 
WHERE  name LIKE 'ashish%' 

Now find who create the user:-

SELECT @TRACE_ID = id 
FROM   sys.traces 
WHERE  is_default = 1 

SELECT @FILENAME = CONVERT(NVARCHAR(4000), value) 
FROM   sys.Fn_trace_getinfo(@TRACE_ID) 
WHERE  property = 2 

SELECT hostname, 
       loginname 
FROM   sys.Fn_trace_gettable(@FILENAME, DEFAULT) 
WHERE  eventclass = 109 
       AND databasename = 'TEST' ---your DB name 
       AND targetusername = 'ASHISH' ---new created login

How To Troubleshoot if you are not able to enable and install .net framework 3.5 during install SQL Server 2008 or 2012 on Windows 8.

When you try to enable .Net framework from Windows Feature you will get following error.

Its showing error like “Windows couldn't connect to the internet to download necessary files”

  

Select checkbox and click ok

  

Click On Download and Install this Feature

 

You will get this error :-

Try to connect internet if internet connection already connected and still you are getting same error then you need to do some troubleshoot with registry file of .Net

Press win + R key from your keyboard and type regedit and press ok

GO To HKEY_LOCAL_MACHINE>>SOFTWARE>>Policies>>Microsoft>>Windows>>WindowsUpdate>>AU

Click on the AU and change value data 1 to 0 and click OK

Now restart the System and try to enable .Net Framework the same way it will work  

Find all tables size in a database.

Follow below script and you can specify like operator for that tables you want. For example I want onlt that table that start with load

DECLARE @space_table TABLE (table_name varchar(500), row_count int, reserved varchar(500)

, data varchar(500), index_size varchar(500), unused varchar(500))

INSERT INTO @space_table(table_name, row_count, reserved, data, index_size, unused)

EXEC sp_MSforeachtable 'sp_spaceused ''?'''

SELECT * FROM @space_table where table_name like ‘Load%’