Error 3154: The backup set holds a backup of a database other than the existing database.

What is this below error

Error 3154: The backup set holds a backup of a database other than the existing database.

Connect to the SSMS >take the DB backup of AdventureWorksDW-

So I have taken to the C:Drive as shown below-

So when you are start restoring of this database Backup file to the active database I,e TestDB you will get the below error(see below).

So to resolve this issue you may required to use the below query-

RESTORE DATABASE [TestDB] FROM  DISK = N'C:\ADWK.BAK' WITH  FILE = 1,  MOVE N'AdventureWorksDW_Data' TO N'C:\t1.mdf',  MOVE N'AdventureWorksDW_Log' TO N'C:\t2.ldf',  NOUNLOAD,  REPLACE,  STATS = 10

GO

Output will be

10 percent processed.

20 percent processed.

30 percent processed.

40 percent processed.

50 percent processed.

60 percent processed.

70 percent processed.

80 percent processed.

90 percent processed.

100 percent processed.

Processed 8760 pages for database 'TestDB', file 'AdventureWorksDW_Data' on file 1.

Processed 2 pages for database 'TestDB', file 'AdventureWorksDW_Log' on file 1.

RESTORE DATABASE successfully processed 8762 pages in 2.047 seconds (35.065 MB/sec).

If didn’t works then you may required to drop and re-create with backup file.

Do you Know Improper Autogrowth can cause the timeout error for users (when they are firing DML) and for SQL DB engine itself.

Do you know in SP4 of SQL server 2000 ->Larger autogrowth or wrong autogrowth settings for DB files also affect for the time out error for users when they are firing DML queries or even SQL server will get cancels the expanding of the DB files and usually the message like this :

Autogrow of file ABC_Log' in database 'ABC cancelled or timed out after 29547 ms.
Use ALTER DATABASE to set a smaller FILEGROWTH or to set a new size.

Go through the Below KB's ->very Interesting..one

http://support.microsoft.com/kb/822641
http://support.microsoft.com/kb/315512
http://support.microsoft.com/kb/305635 

So do the better autogrowth settings for the files so that you can avoid above issues.

(Even Performance, recovery of the databases, restore activities etc..).

Unable to connect to SQL Server’’. The step failed

One of the Job was failing with the below error-

Message:
Unable to connect to SQL Server’<XXXXXXXXXXXXX>’. The step failed.

So to resolve this issue:

1.I have went through the SQL server agent jobs->opened the job and verified that the job was not pointing to any database however the command was doing execution of stored procedure.

2. Verified that recently the database migrated to new server and database was dropped from existing server.(I have verified when the job was last successful ran).

Hence the above error has been written in the log.

So ensure when ever if you are going to migrate to new server or dropping database(s) -identify the Jobs  and take the necessary actions.

Database Suspect Mode.

Never -ever rebuild the transaction log file without knowing proper knowledge because as we know

 Rebuilding an log is the last option when there is no good backup in your hand (see how the backup

Plays an very important role here-hence good backup strategy can save time & can save real time business.)( will explain on other post about how to rebuild the log).

I have seen many cases in my working environment and worked for resolving the issues.

Let discuss possible ways the databases goes in to suspect mode.

1)      Improper shutdown of SQL server services ->for ex : when users are on server and performing some operations against user DB’s(like DML,DDL,DCL ) or Jobs are running, suddenly the SQL server services get’s restarts it may be either Human error or SQL server itself(like SQL server shutdown unexpectedly it has done 1 times –for this we have multiple solutions to check how ever this out of topic and I will explain this on other post).

2)      When users are performing bulk operations and the transaction log file grown rapidly how ever

The transaction log file do not have space on the drive to grow further ->the reason why Iam telling is that I have seen one year back on one of the servers where users are fired Large queries against the database and the transaction log file has grown very huge size almost it reached to 50GB ,all the VLF’s are active status and since drive was running out of space user’s start getting the transaction log file was full backup the tran…… how ever when I see during that time the transactions (I,e spids which users executed queries) were in rolled back status.

But you should know that the rollback is an single thread processes and also to get rollback completely the transaction log file should have space in it ,but in our case no space in the log file & in the disk, so transaction log file marked as corruption –so finally the Database reflected to suspect mode.

3)Mount points or Drives are missing where the databases are resides ->if you have created the database on mount points & those mount points are missing when you have fired the query

Select * from sysaltfiles (SQL 2000) or sys.sysaltfiles(2005).

4)Yes Drive space also plays an very important role. That’s the reason you should have proper capcity planning( you should have proper report against your Database growth based on daily ,weekly or monthly.).

5)I have seen another case where the database resides on one node(I,e active-active cluster)

I,e on one node one instance and another node has another instances- when you are doing failover activity during SQL patch or when you are supporting to OS team when they are performing OS hotfix/service packs.

When failover performing there is no problem when you are doing failback you may see database go suspect mode(here I said only as an example which I have seen and worked-there may be other reason).

>>I will explain in the other post what cause the issue and resolving the issue.

6) During SQL server services start up if any of the database files locked by another processes like

OS incremental backup or any scan(so that’s the reason we always exclude DB files from incremental backup or from Scan(but proper plan can help  here.))

7)No proper backup strategy for t-log backups(full/bulk logged recovery model)  for the databases.

Here it is very important you should have enough information about user’s transaction activity during peak/non –peak hours.

8) Hardware failures.

9) I/O subsystem errors.

10) OS corruption.

11) Do not continue running the uncommitted transactions forcefully.

12) You have added one additional file to the one file group and forcefully trying to remove

From GUI.

13) Renaming the Physical files.

14) Consistency errors in the Databases and not taken care during initial stage.

15)One interesting information in SQL server Enterprise Manager( sql server 2000).

We have one of the servers, usually during non business hours it does the Cold backup(using scripts I,e using XP_CMD)

I,e first it shutdowns the SQL server services and copies the physical files from one drive to another once done it will start the SQL server services.

So in our case every day one of the database was going to suspect mode after SQL server services starts->worked with OS team,Backup team to see any DB files will lock during the SQL server services starts or in between during cold backup –how ever we didn’t see any issues but

Database was going to suspect mode –we didn’t find any errors in SQL server logs but in the event logs was showing one of my colleague Id that sql server services started by his/her id.

But my colleague was not aware of that cold backup much, hence durning the cold backup it was in progress…  he used to connect to SQL server via SQL server enterprise manager.

Below is the interesting part in SQL server 2000.

We know how to map other server using SQL server enterprise manager I,e

Connect to the SQL server enterprise Manager ->right click select new SQL server registration->type the server name ->click on OK.

But in our case when he was using to connect to SQL server Enterprise manager it has one option in SQL server  registration as mentioned below

See above encircled one “when ever the user trying to connect SQL server using this  option

Eventhough SQL server services are stopped it will start –see below how it is

1. Currently my sql server services stopped state-

2.now I will connect to SQL server  using SQL server enterprise manager-

3.Click OK

4. see the interesting result as below

This is how the database was going to suspect mode.

So be careful when you are doing other sql server registration part from other SQL server  enterprise manger ->so better uncheck this option-

Error: 18456, Severity: 14, State: 5

In my organization usually the application team gives us the change(it is fully processes based) to DBA to perform the work on Production for any activity.

One fine day the APP’s DBA team raised an change for creating new login(s) and granting data reader/writer permission for xyz database using t-sql queries.

For example ->Below is the similar query which he provided us(in this I have used my test server).

USE [master]

GO

CREATE LOGIN [ MU-Uday] WITH PASSWORD=N'uu', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

USE [AdventureWorksDW]

GO

CREATE USER [MU-Uday] FOR LOGIN [MU-Uday]

GO

USE [AdventureWorksDW]

GO

EXEC sp_addrolemember N'db_datareader', N'MU-Uday'

GO

USE [AdventureWorksDW]

GO

EXEC sp_addrolemember N'db_datawriter', N'MU-Uday'

GO

So one of my friends he executed the query successfully and it completed successfully.

So he sent an mail to the app user to verify, seems he is busy or off he didn’t reply to that mail

Saying that he is able to login. but after 15 to 20 days he come back and said he is not able to login

Getting below error-

So we have checked many ways to resolve the issue but it didn’t work how ever same issue I have resolved in my local system .

To resolve :

Scan the errorlog :Identify what is the state of login failed in my case the state of the error login as mentioned below

   Date                   25/10/2011 23:23:52

Log                         SQL Server (Current - 25/10/2011 23:31:00)

Source                  Logon

Message

Login failed for user 'MU-Uday'. [CLIENT: <local machine>]

Date                      25/10/2011 23:31:17

Log                         SQL Server (Current - 25/10/2011 23:31:00)

Source                  Logon

Message

Error: 18456, Severity: 14, State: 5.

So keeping state level in mind (before this go through the information about state) I resolved the issue.

State    

1           

  Only state returned in SQL 2000. Seems to be catch all where no other states apply; for example Account Disabled shows up as State 1(2005).

2

                Invalid Userid

5

                SQL Account provided does not exist on SQL Server. (SQL2005)

6

                Attempt to use a windows login name with SQL Authentication

7

                Login disabled and password mismatch

8

                Password mismatch

9

                Invalid Password

For other state go through the Link :

http://schelstraete.dyndns.org/index.php/sql-server/590-sql-server-list-login-state-failures 

so understood that SQL server not able to recognize the login because in our script if you have observed

there was an small space provided while creating login as mentioned below-

USE [master]

GO

CREATE LOGIN [ MU-Uday] WITH PASSWORD=N'uu', DEFAULT_DATABASE=[master], CHECK_EXPIRATION=OFF, CHECK_POLICY=OFF

GO

So I have decided to let  try renaming the login from [ MU-Uday] to [MU-Uday] then try for

Login –you can use t-sql or GUI to rename :alter login like as mentioned below-

Use master

ALTER LOGIN [ MU-Uday]  WITH NAME = [MU-Uday];

So I plan to connect one more time using SSMS-Wow Iam able to connect without any issues.

See below

Happy ending…..user able to login after resolving the issue.