Category Archives: SQL

Fix: The backup set holds a backup of a database other than the existing

When restoring a database from an SQL backup .bak, one usually creates a database and then selects the restore function. As soon as you try to restore the database you get the error saying “The backup set holds a backup of a database other than the existing“. This is because it fails to read the files from the restore and matching them to the newly created files.

The error says:

Restore failed for Server ‘SQLSRV01’. (Microsoft.SqlServer.SmoExtended)
Additional Information
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
The backup set hold a backup of a database other an the existing ‘test_restore’ database.
RESTORE DATABASE is terminating abnormally. (Microsoft SQL Server, Error: 3154)

To solve this one should do the following:

– Don’t create an empty database and restore the .bak file on to it.
– Use ‘Restore Database’ option accessible by right clicking the “Databases” branch of the SQL Server Management Studio and provide the database name while providing the source to restore.

This should allow you to restore the database with no error and fix the error 3154.

(665)

Fix: The In-row data page count for object is incorrect when running integrity check on SQL

When running an integrity check on your databases, you may get the following error on some databases.

Description: Executing the query "DBCC CHECKDB(N'mydb')  WITH NO_INFOMSGS  " failed with the following error: "The In-row data myrow page count for object "my_table", index ID 0, partition ID 52794353778688, alloc unit ID 52794353778688 (type In-row data) is incorrect.

This is due to the fact that the database in question was imported from another or older version of your current SQL server. This can be fixed by running the below command on the databases in question.

DBCC UPDATEUSAGE (dbname)

dbname = The name of your database

When the database is indeed one which is failing the integrity check you will see the below sample message.

DBCC UPDATEUSAGE: Usage counts updated for table 'mytable' (index 'mytable', partition 1):
DATA pages (In-row Data): changed from (2) to (1) pages.
USED pages (In-row Data): changed from (25) to (24) pages.
RSVD pages (In-row Data): changed from (41) to (40) pages

If the database doesn’t need to update it’s usage you will simply get the message below.

DBCC execution completed.

Once you have executed the update-usage on all databases which are failing, run the integrity check again and swish, it works.

 

 

(4636)

Fix: SQL Server Database Services feature state failed during upgrade

During an SQL version upgrade I have encountered that during the checkup, you will get the error stating that Database Services feature state failed. This can be fixed by finding the registry entry below and changing as follows.

HKEY_LOCAL_MACHINE\SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.\ConfigurationState.

Change all values from 2 to 1 for all Four items in the right pane.

Re-Run the rule on the installation. Now it will be solved and through.

(2508)

Fix: Cannot use the special principal ‘sa’. Microsoft SQL Server, Error: 15405

When importing a database in your SQL instance you would find yourself with Cannot use the special principal 'sa'. Microsoft SQL Server, Error: 15405 popping out when setting the sa user as the DBO of the database. To fix this,
Open SQL Management Studio and Click New Query. Type:

USE mydatabase
exec sp_changedbowner 'sa', 'true'

Close the new query and after viewing the security of the sa, you will find that that sa is the DBO of the database.

(28545)

How to: Get all the information regarding your SQL server installed

When running the following script, will get you a verbose of the SQL server installed. This will get you the Service Pack number, version and other information which includes Product Level and other interesting stuff.

Note: if you would like to get a simplified information on the SQL server simply run the SELECT @@VERSION on your SQL while using the Master database, but if you want to know more in detail, use the script below.

SELECT @@VERSION
GO

SELECT SERVERPROPERTY('ProductVersion') 'Version', SERVERPROPERTY ('ProductLevel') 'Service Pack', SERVERPROPERTY ('Edition') 'Edition'
GO

-- Stored procedure, documented in Books Online, that returns numerous key / value pairs including version information.
-- There doesn't seem to be a correlation between the key names here and SERVERPROPERTY values;
-- for example, 'ProductVersion' is returned by xp_msver, but 'ProductLevel' is not.
xp_msver
GO

-- Stored procedure, documented in Books Online, that returns numerous key / value pairs including version information.
-- For SQL Server 2008, DBMS_VER returns "Microsoft SQL Server Yukon - 10.0.1600".
sp_server_info
GO

-- Stored procedure, not documented in Books Online, that returns the version string.
sp_MSgetversion
GO

-- @@MicrosoftVersion returns a decimal value which, when converted to varbinary, yields (for example) 0x09000FC3.
-- Enter "convert 0x0FC3 to decimal" in Google, and the result is 4035. Thus the overall MicrosoftVersion is 09.00.4035.
SELECT CONVERT(VARBINARY, @@MicrosoftVersion)
GO

-- get version info from the registry (SQL 2005 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL.1\Setup\', @value_name='SP'
GO

-- get version info from the registry (SQL 2008 key names - keys may vary)
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Version'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='Edition'
GO
xp_regread @rootkey='HKEY_LOCAL_MACHINE', @key='SOFTWARE\Microsoft\Microsoft SQL Server\MSSQL10.MSSQLSERVER\Setup\', @value_name='SP'
GO

(575)

How to: Find users in SQL having DB Owner role

Find members of db_owners in all databasesThis script is aim to provide all members of the db_owner database role in all databases in an instance. It
The following code returns the role name and the member name filtered for the db_owner database

This script is aim to provide all members of the db_owner database role in all databases in an instance. It uses sp_MSForEachDb with a custom script using sys.database_principals DMV. The following code returns the role name and the member name filtered for the db_owner database role.

exec sp_msForEachDb ' use [?]
select db_name() as [database_name], r.[name] as [role], p.[name] as [member] from
sys.database_role_members m
join
sys.database_principals r on m.role_principal_id = r.principal_id
join
sys.database_principals p on m.member_principal_id = p.principal_id
where
r.name = ''db_owner'''

(635)

How to: Execute SQL stored procedures using command line

Often when having SQL Express Editions installation I would like to schedule and execute stored procedures. Using Express editions you cannot make use of the SQL Agent, but you can run them manually using a batch file and using the SQLCMD.EXE file as below

SQLCMD /Q "USE mydb; EXEC mystoredprocedure"

This will use the mydb that you specify and run the mystoredprocedure. You can also use the > filename.txt to make the job log.

(618)

How to: Execute SQL Stored procedure from command line

When coming accross SQL Express editions, you will not be able to set a job in the Agent to execute a stored procedure. I usually do custom stored procedures to run them on Express editions.

There is a way to execute them by using the SQLCMD command

sqlcmd -Q "exec myprocedure" -S mysqlserver -d fromthisdatabase -E -o C:\log.txt

This will execute your stored procedure and you can put it in a batch file and schedule it in the Task Scheduler

(4030)

Fix: Cannot run or delete a job in SQL after changing sa password

After changing the password of the SA or the user which is in charge of running jobs on the SQL server, the jobs will not work and you will not be able to delete the jobs with the error ‘Login failed for user SA‘. This can temporary solved by changing the SA or the user used password back to it’s original state and delete the job, but still you will not be able to run the job.

To solve this permanently you have to open the ‘Management Studio’, right-click on the job and select ‘Modify’. Click on the ‘Manage Connections’ and replace the username and password on each connection if you have more than the default one using the new password that you have recently changed.

This modification you have to do it for every-time you change the password of the ‘sa’ or user you use to run the jobs in the Manage Connections.

(600)

How To: Backup databases for SQL 2000 MSDE with SQLMaint.exe

To Backup a database from command-line and using SQL 2000 MSDE version, you must use the SQLMAINT.EXE file situated in the ‘C:\Program Files\Microsoft SQL Server\MSSQL\Binn’

The backup cannot be done for all databases at once and it must be done individually. This can be done by the line below and then just duplicate it for multiple databases.

Create a batch file and run the following:

CD "C:\Program Files\Microsoft SQL Server\MSSQL\Binn"
sqlmaint -D "MyDB" -BkUpDB "X:\Backups\Data" -Rpt "X:\Backups\Reports\MyDB.txt" -BkUpMedia DISK

(1226)