Uncategorized

when I pressed Keyboard back slash (\) it  gives #. How to fix it?

I was working on one of the UK server and trying to access a file share. To access fileshare I used backslash \\ and it gave me ##. Then after searching in online found the fix,

As displayed in the screenshot, you need to add a language English united states under controlpanel -> clock, language and region -> language

before fixing the issues, the language was showing as UK English.

Is it possible to configure Availability group if the secondary database file paths (drives) are differs from the primary?

yes, we can configure the availability groups even the secondary database file paths (drives) are differs from the primary, but you can’t perform following actions on the database

  1. add-file operation on the primary replica might fail on the secondary databases.
  2. automatic seeding or the wizard will fail while creating Availability groups.
  3. You cannot drop a database that currently belongs to an availability group.

Recover Lost Access to a SQL Server

There are several ways to recover lost access on SQL ServerUsing PSTools

Using PSTools:

  1. Download the PSExec tool from https://docs.microsoft.com/en-in/sysinternals/downloads/psexec
  2. Run the SQL server instance in single user mode, as show below open in configuration manager add -m and restart the SQL server services.

3. Now run the PSExec from command prompt.

4. When Management Studio launches, it will prompt you to connect to a server. You will notice that the authentication mode is Windows Authentication, and the username is hard-coded as NT AUTHORITY\SYSTEM:

5. in new query window run the below query to grant sysadmin access to your login. replace your login\groupname in theT-SQL

EXEC sp_addsrvrolemember 'DomainName\DBAGroupName', 'sysadmin';
GO

Using SQL Server VSS Writer service:

https://www.mssqltips.com/sqlservertip/4672/more-on-recovering-access-to-a-sql-server-instance/

  1. copy the SQLCMD file from the path C:\Program Files\Microsoft SQL Server\90\Shared and paste in C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\140\Tools\Binn\
  2. rename the sqlwriter.exe as sqlwriter_old at C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\140\Tools\Binn\
  3. rename the SQLCMD file as sqlwriter.exe at C:\Program Files\Microsoft SQL Server\Client SDK\ODBC\140\Tools\Binn\
  4. change the registry setting by adding below extension for the sqlwriter key from HKEY_LOCAL_MACHINE\SYSTEM\CurrentControlSet\Services\SQLWriter ( -s . -i D:\adddba.sql)
  5. restart the sqlwriter service.

some times adding login will not work as shown in the link, in that case use below method to grant access to your group

create login [domain\yogi] from windows;

alter server role sysadmin add member [domain\yogi];

Get compressed backup size in SQL Server

Today I was trying to get the backup size for the compressed database backups and used backup_size from msdb.dbo.backupset to calculate the backup size, the values are not matching with the window explorer backup sizes. Then I realized there is another parameter to calculate the compressed backups that is compressed_backup_size from msdb.dbo.backupset.

SELECT bs.database_name AS DatabaseName

–, CAST(bs.backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS BackupSizeGB

–, CAST(bs.backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS BackupSizeMB

, CAST(bs.compressed_backup_size/1024.0/1024/1024 AS DECIMAL(10, 2)) AS CompressedSizeGB

–, CAST(bs.compressed_backup_size/1024.0/1024 AS DECIMAL(10, 2)) AS CompressedSizeMB

, bs.backup_start_date AS BackupStartDate

, bs.backup_finish_date AS BackupEndDate

, CAST(bs.backup_finish_date – bs.backup_start_date AS TIME) AS AmtTimeToBkup

, bmf.physical_device_name AS BackupDeviceName

FROM msdb.dbo.backupset bs JOIN msdb.dbo.backupmediafamily bmf

ON bs.media_set_id = bmf.media_set_id

WHERE

bs.backup_start_date > DATEADD(dd, -1, GETDATE()) and

bs.type = ‘D’

ORDER BY bs.database_name, bs.backup_start_date