High on SQL!!!

Apr 16
The target principal name is incorrect. Cannot generate SSPI context. (.Net SqlClient Data Provider)

Hi All,

This is a fairly wide topic with many possible different causes if SQL Server service account running  under active directory domain account and the AD account not SPN registered. Make sure the sql server service account is SPN registered and  this issue will be resolved.

Here is the below reference:--

https://support.microsoft.com/en-ca/help/811889/how-to-troubleshoot-the-cannot-generate-sspi-context-error-message

 

Enjoy!

Zahid

Apr 10
### How to Offline/Online/Drop multiple databases

Taking Databases Offline and/or Dropping through SQL Query

The following SQL query is for taking databases offline, drop the offline databases, or bring them all back online. This affects every database in your SQL instance, so be very careful when using these. The following three queries will save you a considerable amount of time if repurposing an instance.

Taking databases offline and then dropping them

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET OFFLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Once all databases are offline you can drop them to clear out the entire instance with this SQL query.
Note: This is extremely destructive so please use this with care.
use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'DROP DATABASE ['+name+'];'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Use the following SQL query to take all databases online

use master;
declare @nsql nvarchar(max);
select @nsql=coalesce(@nsql+CHAR(13)+CHAR(10),'')+
'ALTER DATABASE ['+name+'] SET ONLINE WITH NO_WAIT;'
from master..sysdatabases where sid <> 0x01
exec (@nsql)

Apr 10
## How to attach multiple databases

Hello,

If you require to attach multiple databases in your SQL Server environment then first run this following Stored Procedure...

USE [master]

GO

/****** Object: StoredProcedure [dbo].[usp_MultiAttachSingleMDFFiles] Script Date: 4/10/2019 7:35:46 PM ******/

SET ANSI_NULLS ON

GO

SET QUOTED_IDENTIFIER ON

GO

 

CREATE PROCEDURE [dbo].[usp_MultiAttachSingleMDFFiles] ( @mdfTempDir nvarchar(500) )

AS

BEGIN

DECLARE @dirstmt nvarchar(1000)

DECLARE @currFile nvarchar(160)

DECLARE @db_name nvarchar(256)

DECLARE @phys_name nvarchar(520)

DECLARE @dbccstmt nvarchar(1000)

DECLARE @db2attch_ver INT

DECLARE @curr_srv_ver INT

DECLARE @mdfFileNames TABLE (mdfFile nvarchar(260))

DECLARE @mdfFileATTR TABLE (attrName sql_variant, attrValue sql_variant)

 

DECLARE cf CURSOR FOR SELECT mdfFile FROM @mdfFileNames

SET NOCOUNT ON

-- get all mdf file names only , in bare format.

SET @dirstmt = 'dir /b "' + @mdfTempDir + '"\*.mdf'

INSERT into @mdfFileNames

EXEC xp_cmdshell @dirstmt

 

DELETE from @mdfFileNames where mdfFile IS NULL or mdfFile = 'File Not Found'

-- if file is already attached skip it

DELETE FROM @mdfFileNames

WHERE mdfFile IN (SELECT mdfFile FROM @mdfFileNames a INNER JOIN sys.master_files b ON lower(@mdfTempDir + '\' + a.mdfFile) = lower(b.physical_name) )

-- if no files exist then exit process

IF not exists (SELECT TOP 1 * FROM @mdfFileNames)

BEGIN

PRINT 'No files found to process'

RETURN

END

-- get the current server database version

SELECT @curr_srv_ver = CONVERT (int,DATABASEPROPERTYEX('master', 'version'))

BEGIN TRY

OPEN cf

FETCH NEXT FROM cf INTO @currFile

WHILE @@FETCH_STATUS = 0

BEGIN

 

SET @phys_name = @mdfTempDir + '\' + @currFile

SET @dbccstmt = 'DBCC checkprimaryfile (' + '"' + @phys_name + '"' + ',2)'

INSERT INTO @mdfFileATTR

EXEC (@dbccstmt)

 

SELECT @db_name = convert (nvarchar(256), attrValue)

FROM @mdfFileATTR

WHERE attrName = 'Database name'

 

-- get the candidate to be attached db version

SELECT @db2attch_ver = convert (int, attrValue)

FROM @mdfFileATTR

WHERE attrName = 'Database version'

-- if the current server database version is less that the attached db version

-- OR

-- if the database already exists then skip the attach

-- print an appropriate message message

IF (@db2attch_ver > @curr_srv_ver)

OR

(exists (SELECT 1

FROM sys.databases d

WHERE RTRIM(LTRIM(lower(d.name))) = RTRIM(LTRIM(lower(@db_name)))))

BEGIN

PRINT ''

PRINT ' Attach for database ' + @db_name + ' was not performed! '

PRINT ' Possible reasons : '

PRINT '1. ' + @db_name + ' DB version is higher that the currnet server version.'

PRINT '2. ' + @db_name + ' DB already exists on server.'

PRINT ''

END

ELSE

BEGIN

EXEC sp_attach_single_file_db @dbname= @db_name , @physname = @phys_name

PRINT ''

PRINT 'Database "' + @db_name + '" attached to server OK using file ' + @currFile + '".'

PRINT ''

DELETE FROM @mdfFileATTR

END

FETCH NEXT FROM cf INTO @currFile

END

 

CLOSE cf

DEALLOCATE cf

END TRY

BEGIN CATCH

PRINT 'Error while attaching FILE ' + @phys_name + ',...Exiting procedure'

CLOSE cf

DEALLOCATE cf

END CATCH

SET NOCOUNT OFF

END

GO

 

Example Use

  • Create a directory on your server ( C:\Migration, for example ) and copy the unattached MDF files.  Note don't include a "\" at the end of the folder name for the parameter value.
  • From SSMS, open a new query window and run the following.
USE master
GO

EXEC dbo.usp_MultiAttachSingleMDFFiles 'C:\ATT' 
GO 
Apr 10
## How to detach multiple databases

SET NOCOUNT ON

GO

IF OBJECT_ID('StoreDatabase','U') IS NOT NULL

DROP TABLE StoreDatabase

CREATE TABLE StoreDatabase(

DatabaseNumber int IDENTITY(1,1),

DatabaseName sysname)

INSERT INTO StoreDatabase

SELECT name FROM sys.databases

WHERE name IN ('Test','Test1','Test2','Test3')

DECLARE @i int

SET @i = 0

IF ((SELECT COUNT(*) FROM StoreDatabase) = 0)

PRINT 'No user database found!'

ELSE

BEGIN

WHILE ((SELECT COUNT(*) FROM StoreDatabase) <> 0)

BEGIN

DECLARE @DN sysname

SET @DN = (SELECT TOP 1 DatabaseName FROM StoreDatabase)

PRINT 'Detaching ' + @DN + '…… '

DECLARE @cmd nvarchar(400)

SET @cmd = 'EXECUTE sp_detach_db ' + @DN

EXECUTE (@cmd)

PRINT 'Detached ' + @DN + '! '

DELETE FROM StoreDatabase

WHERE DatabaseNumber = @i + 1

SET @i = @i + 1

END

END

Mar 10
### Rebuild lost or deleted transaction log files

Every so often someone will call with a terrible problem because a drive was lost or someone mistakenly deleted transaction log files.  When this happens, the affected databases are not useable and are usually marked as SUSPECT or IN RECOVERY in Management Studio.  Usually the best option is to restore the database from a backup however, sometimes backup files may be lost or simply not set up and therefore do not exist.  If the transaction log files cannot be recovered and a database backup is not available a last ditch option is to force SQL Server to rebuild the lost transaction log files.  This option should be the last to be considered but there are cases where doing this is justified.  Since the transaction log file is built as a brand new file, any transactions that were in the old transaction log file would be lost so it would be possible that data corruption would occur.  You will obviously need to create folders for the transaction logs on a drive with enough space to hold the new log files.

Force a new transaction log to be built

Use this statement to get the names to substitute in the appropriate places in the ALTER DATABASE statements that will follow:

 

select d.name as dbname, m.name as logicalname, m.physical_name from sys.master_files m

join sys.databases d on (d.database_id = m.database_id) where m.type = 1

order by 1, 2

You may want to use the actual file name from the physical_name column in the FILENAME value below.  Use the new folder path as the path in the FILENAME value. 

Run each statement one-by-one and especially pay attention to what the CHECKDB output returns if there are errors.  If it says you need to run REPAIR_ALLOW_DATA_LOSS then uncomment the command to do so and run that.  When you get to no errors from the DBCC CHECKDB then you can put the database in MULTI_USER and can use the database then.

USE master

GO

ALTER DATABASE [dbname] SET EMERGENCY

GO

ALTER DATABASE [dbname] SET SINGLE_USER

GO

ALTER DATABASE [dbname] REBUILD LOG ON

(NAME= logicalname, FILENAME=’Drive path to new file location\Log file name.ldf’) — Like ‘E:\MSSQL\LOGS\DB_Log.ldf’

GO

DBCC CHECKDB ([dbname])

GO

— DBCC CHECKDB ([dbname], REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS, ALL_ERRORMSGS;

— GO

ALTER DATABASE [dbname] SET MULTI_USER

GO

 

 

Mar 07
### Who Knows! If you require to move System Database

​### Move System database; just change the path based on your environment

ALTER DATABASE model MODIFY FILE ( NAME = modeldev , FILENAME = 'S:\DATA\model.mdf' );

ALTER DATABASE model MODIFY FILE ( NAME = modellog , FILENAME = 'S:\LOG\modellog.ldf' );

ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBdata , FILENAME = 'S:\DATA\MSDBData.mdf' );
ALTER DATABASE msdb MODIFY FILE ( NAME = MSDBlog , FILENAME = 'S:\LOG\MSDBlog.ldf' );

Now close your Management Studio and Start your SQL Server Enterprise Manager. Stop the SQL Services of the SQL Server Instance you just altered the databases in.
Open up a windows explorer and move the model, tempdb, msdb AND master .mdf and .ldf files to the correct location. My example: all .ldf files to F:\SQLLOG, all .mdf files to G:\SQLDATA.

After the move is complete return to your SQL Server Enterprise Manager and open up the properties of the SQL Server Service.
Modify the Startup Parameters to the correct location of the master database and logfiles (see screenshot below)

-dG:\SQLDATA\master.mdf;-ed:\sql\sql_error.log;-lF:\SQLLOG\mastlog.ldf

•-d : path to data file (.mdf)
•-e : path to sql server error logfile
•-l : path to transaction log


Now fire up your SQL Server Service and it should start. If that is the case your job is done!

Dec 24
Welcome to my blog!

My Dear TechyFrndZ,

Welcome to the launch http://www.zclouddata.com/sqlhub/default.aspx and improved web based application site and my first blog post High on SQL! which is running 24/7 on my ZCLOUD Data Center!

I am so excited to share with you MORE of what orderly has to offer! The amount of ideas, how-to’s and inspiration whirling around in my head is ready to jump out for critical problem solutions like SQL Server virtualization and performance tuning and so many.

I wanted to start by telling you why I created this blog, what you will be seeing more of in my High on SQL posts, and my overall experience of cloud based and on-premises SQL Database Administration.

If I catch your interest, let me hear from you.

 

Cheers,

Zahid Ahamed

IT Specialist

Microsoft Certified IT Professional (MCITP)
Microsoft Certified SQL Server Expert (MCSE)

Security + Certified

 

 

 

About this blog
Stay tuned with High on SQL!!!. Thanks!