Tuesday, January 31, 2012

Create Backup File with Password protection

Sometime it is necessary to set password in backup file. If backup is password protected than we cannot restore the database or see details without knowing the password. Now to add password protection we have to add "With MediaPassword='DBPassword' " at the end of TSQL where 'DBPassword' is password we set for the backup. Now during restore we have to add this at the end also.

TSQL to backup database with password
Backup Database DemoDB To Disk ='C:\Temp\DemoDB.BAK' With MediaPassword='DBPassword'  

Now we cannot restore this database or get details of database from this backup without knowing the password.
We will get the below error:

Msg 3279, Level 16, State 6, Line 1
Access is denied due to a password failure
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.

Now to restore the database run below command:

Restore Database DemoData From Disk='C:\Temp\DemoDB.BAK' With MEDIAPASSWORD='DBPassword'  

To resore details of the backup run below command

Restore HeaderOnly From Disk='C:\Temp\DemoDB.BAK' With MEDIAPASSWORD='DBPassword'  

Friday, January 27, 2012

Change Recovery Model of all your SQL Server databases

In the below script, we use a system table to get all the database names on our server. Then we use a cursor to loop through all the records and then change the recovery model.

--Declaration of variables
DECLARE @DBName SysName, @sql VarChar(100)
-- Declare begin cursor to get the database names and get info from sys.databases catalog
DECLARE Cursor_db Cursor FOR SELECT Name From sys.Databases WHERE Name != 'TempDB'
-- Now using the cursor to loop through database names and change recovery model
OPEN Cursor_db Fetch NEXT FOM Cursor_db INTO @DBName 
--While Loop with Alter database command
WHILE @@fetch_status = 0
--print 'database is ' + @DBName 
SET @sql='ALTER Database ' + @DBName + ' SET Recovery Simple'
PRINT 'sql is ' + @sql
exec (@sql)

Fetch Next FROM Cursor_db INTO @DBName 
--clean up objects
CLOSE Cursor_db 
Deallocate Cursor_db 

Monday, January 23, 2012

Error: Cannot find the template file for the new query

Today morning I got this error in my computer when I was opening new Query Editor Window:

The SQLFile.sql is a template file for new query. This was accidentally removed from my system. This is a blank file. To solve this error I had created this file in C:\Program Files\Microsoft SQL Server\90\Tools\Binn\VSShell\Common7\IDE\SsmseeTemplates\Sql Folder.

Saturday, January 21, 2012

List all Tables in Database With / Without Primary Key

USE <DatabaseName>;
SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name
FROM sys.tables
ORDER BY Schema_Name, Table_Name;

SELECT AS Index_Name,OBJECT_NAME(ic.OBJECT_ID) AS Table_Name,COL_NAME(ic.OBJECT_ID,ic.column_id) AS Column_Name
FROM sys.indexes AS i
INNER JOIN sys.index_columns AS ic
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

Friday, January 20, 2012

Database 'msdb' cannot be opened.

Today I am getting the below error while querying Database:
Database 'msdb' cannot be opened. It has been marked SUSPECT by recovery. See the SQL Server errorlog for more information. (Microsoft SQL Server, Error: 926)

I searched the net and found the below solution: 
In SQL Server 2005 & Above
steps need to be run and taken if DB is in Suspect Mode.

EXEC sp_resetstatus 'Database_Name';
DBCC checkdb('Database_Name')

In SQL Server 2000
USE Master
-- Determine the original database status
SELECT [Name], DBID, Status FROM master.dbo.sysdatabases
-- Enable system changes
sp_configure 'allow updates',1
-- Update the database status
UPDATE master.dbo.sysdatabases
SET Status = 8 or 32768 --8 means online & 32768 means emergency
WHERE [Name] = 'Database_Name'
-- Disable system changes
sp_configure 'allow updates',0
-- Determine the final database status
SELECT [Name], DBID, Status
FROM master.dbo.sysdatabases

Thursday, January 19, 2012

SQL Server Configuration Manager – Cannot connect to WMI provider – Invalid class [0x80041010]

Today when I was trying to connect to my SQL Server 2008 using SQL Server Configuration Manager, the following error dialog pops up:
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]
Cannot connect to WMI provider. You do not have permission or the server is unreachable. Note that you can only manage SQL Server 2005 and later servers with SQL Server Configuration Manager. Invalid class [0x80041010]

I have searched the netand the following code saves me.
>> Open an elevated Command Prompt, and type the following…
mofcomp.exe "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof"
mofcomp "C:\Program Files (x86)\Microsoft SQL Server\100\Shared\sqlmgmproviderxpsp2up.mof" 

Wednesday, January 18, 2012

List the modified objects in SQL Server

For viewing the modified date of Stored Procs and UDF alone:
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
Routine_type in ('PROCEDURE', 'FUNCTION')
Order by
Last_altered desc, Routine_type, Routine_name 

For viewing the modified date of Stored Procs, UDF and Views:

We can query 'Sys.Objects' table and find out the list of Stored procs, UDFs, Views etc., which have got modified.

Code snippet:
[name] as 'Object Name',
[type] as 'Object Type',
From sys.objects
Where [type] in ('P', 'FN', 'TF', 'V')
Order by Modify_Date desc, [type], [name]

The above query will list all 'SPs', 'UDFs' and 'Views' in the current database with its 'Created date' and 'Modified date'. We can further finetune this code to match our exact need!

For triggers
Check out create_date and modify_date columns in sys.triggers.
select * from sys.triggers

Tuesday, January 17, 2012

Resetting user names for orphaned logins

Sometime when we restore the database from a backup file in SQL Server, after that some logins are not allowed access to database. error like can’t login user ‘null’ in the database occur when connecting using code.
This happens because while restoring the database backup the login was not linked with the user name it was associated in the database whose backup we are using. To login into the DB using that login we need to assign that login a username.
@Office I need to restore databases many times and use them to debug to solve the issue. So I used to get this problem every time I restore the backup. Earlier to solve this issue, I used to use brute force method to resolve this like deleting the troubled login from the Database\Security\Users and deleting the schema related to this login. Then adding the same login again to Database\Security\Users . This used to solve my problem.
Microsoft SQL Server provides a procedure to solve this issue, which is in fact the right way to it. sp_change_users_login this sp is intended to relink the orphaned login with their user name. Following are the parameters to this procedure.
These parameters indicate what action to perform. It can be one of the 3 values
  • Reports : Returns all the orphaned logins in the database
  • Auto_Fix: Fixes the given login and sets the username to the user with the same name in database, if a user with same name is not found then a new entry in sys.database_principals table and the newly created entry is linked with the provided login.
  • Update_One: updates the given login with the login name provided.
EXEC SP_Change_User_Login 'Report'

The Report argument will return any orphaned users within the current database.

EXEC SP_Change_User_Login 'Auto_Fix', 'SampleUser'

Above example displays usage of Auto_fix

EXEC SP_Change_User_Login 'Update_One', 'SampleUser', 'SampleUser'

The above command will update the sampleuser user’s login to sampleUser.

Following are some references:

Monday, January 16, 2012

The database may be offline. Wait a few minutes and try again

The database may be offline. Wait a few minutes and try again
The above error is coming while accessing the Database. But before 10 minutes it was working fine.

To resolve this problem you have to check the database properties to see if the AutoClose parameter is not set to On. 
When this parameter is On SQL closes the database in auto and then when a new connection tries to get or add data to this database SQL turn it on again.

This parameter can be found If you open the SSMS then right click on the database - click on Properties in the properties window click on the options menu you will find the Auto Close parameter in there.

Saturday, January 14, 2012

Drop All Indexes and Stats in one Script

SQL Server 2005/2008

SELECT FROM sys.indexes JOIN sys.objects ON indexes.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id schemas.schema_id WHERE indexes.index_id 0
AND indexes.index_id 255
AND objects.is_ms_shipped 0
AND NOT EXISTS (SELECT FROM sys.objects WHERE ORDER BY objects.OBJECT_ID, indexes.index_id DESC

SELECT * FROM sys.stats OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql 
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END
CLOSE dropindexes DEALLOCATE dropindexes


SELECT FROM sys.stats JOIN sys.objects ON stats.OBJECT_ID = objects.OBJECT_ID JOIN sys.schemas ON objects.schema_id schemas.schema_id WHERE stats.stats_id 0
AND stats.stats_id 255
AND objects.is_ms_shipped ORDER BY objects.OBJECT_ID, stats.stats_id DESC

OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql 
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END
CLOSE dropstats DEALLOCATE dropstats 
SQL Server 2000 Code


SELECT,, FROM sysindexes JOIN sysobjects ON = JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(,, 'IsStatistics') = 0
  AND sysobjects.TYPE = N'U'

OPEN dropindexes FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP INDEX '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@indexname)
  PRINT @sql
  EXEC sp_executesql @sql  
  FETCH NEXT FROM dropindexes INTO @indexname, @tablename, @ownername END
CLOSE dropindexes DEALLOCATE dropindexes


SELECT,, FROM sysindexes JOIN sysobjects ON = JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(,, 'IsStatistics') = 1
  AND sysobjects.TYPE = N'U';
OPEN dropstats FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername WHILE @@fetch_status = 0 BEGIN
  SET @sql = N'DROP STATISTICS '+QUOTENAME(@ownername)+'.'+QUOTENAME(@tablename)+'.'+QUOTENAME(@statsname)
  EXEC sp_executesql @sql  
  --PRINT @sql
  FETCH NEXT FROM dropstats INTO @statsname, @tablename, @ownername END
CLOSE dropstats 
DEALLOCATE dropstats 

Friday, January 13, 2012

List all the users of a particular database.

Run below query to get list of all the users of a particular Database for SQL Server 2005 and later
USE <Database_Name>

SELECT suser_sname(SID) AS ServerLevelLogin, 
       Name AS  DatabaseLevelUser, 
FROM sys.database_principals 
WHERE Principal_ID >= 5 and IS_Dixed_Role <> 1
In SQL 2000 run below query:
SELECT ISNULL(suser_sname(SID), 'No Mapping') AS ServerLevelLogin, 
       Name AS DatabaseLevelUser, 
       WHEN islogin = 1 then 'Login'
       ELSE 'Role'
       END AS Type_Desc, 
       WHEN issqluser = 1 then 'SQL User'
       ELSE 'Windows User\Group'
       END AS Type_Desc2,
FROM SysUsers
WHERE Name Not Like '[dpg][bu][_obe]%'

Thursday, January 12, 2012

How to attach MDF without LDF into MS SQL

SQL Server 2005 does not support DBCC REBUILD_LOG. So, we cannot use it to rebuild the LDF. Here is the process to attach a database only using MDF without LDF.

VS 2005 doesn't had this facility so we use VS 2008. This method can also wok with Visual Web Developer 2008 Express Edition.

Open VS.2008 and do the followings:
  1. Open a new Web site or use the existing one.
  2. Add the file to the project's App_Data folder.
  3. Right click this folder in the Solution Explorer and select Add Existing Item.
  4. Locate the MDF file you want to use and click on Add.
  5. Double-click on the MDF file that you just added; it will open Server Explorer for you.
  6. Locate and right-click the MDF file data connection on Server Explorer.
  7. Select Publish to provider...
  8. Go through the wizard; when the wizard asks you where to publish, and select Script to file. It will generate a SQL file that contains all the database schema and its data.
On the SQL 2005 Server (mine is Express version),
  1. Create a database with the same name as your MDF.
  2. Run the SQL file you just created by Server Explorer in VS.2008.
Your database should be revived now!

If you are only interested in attaching MDF to the SQL 2005 server without data extraction and replication.

Here is another way to attach MDF file to the SQL 2005 sever without LDF. In this approach, you don't need to install Visual Studio but you may need another tool for help depending on your result by running the following SQL statement. 
In the following example, we will use AdventureWorks.mdf as an example.
Execute the following statement to attach your MDF on SQL Management Studio:

USE master
ON PRIMARY (FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')

If everything works fine, you may get the similar message below and have your database attached after execution:
File activation failure. The physical file name "D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.ldf" may be incorrect. New log file 'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks_log.LDF' was created.
Otherwise, you may have the following similar error:
File activation failure. The physical file name "D:\Users\<SomeUserName>\Documents\MyCode\Test\App_Data\AdventureWorks_log.ldf" may be incorrect. The log cannot be rebuilt when the primary file is read-only. Msg 1813, Level 16, State 2, Line 3 Could not open new database 'AdventureWorks'. CREATE DATABASE is aborted.
Depending on how or where you get your MDF file (because your MDF may not be detached properly), your LDF rebuilt may fail and so may your database attach command. In this case, you need to download and use the free tool SQL Server Express Utility (SSEUtil) to interact with the server and create the LDF file for you. This tool also works well with the full version of SQL Server 2005. There are two (2) big steps you need to perform in order to make your MDF finally available on SQL Management Studio.

Step 1: Use SSEUtil to attach MDF into the database server
First, you can copy the MDF to the location where you usually store for your SQL server if you like, e.g.,C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\, or keep wherever it is now.
Then at the command prompt, run sseutil's attach database command. Before doing it, please be sure to add your path to sseutil.exe. Otherwise, you need to use its absolute path name,., e.g., c:\util\sseutil.exe.
sseutil -s .\SQLExpress -a "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf" AdverureWorks
  • -s to specify the instance of SQL server.
  • -a to attach the database by specifying its MDF file path name.
  • The last parameter is optional. It is the name of database you want to call (e.g., AdvantureWorks).
There are several options that you can use with sseutil, like -user and -pwd to specify the usr name and its password for connection. Please see the readme file of SSEUtil for details.


You can use sseutil console to interact with the server.
  • To start a sseutil console, at the command prompt, type
    sseutil -c
  • You will see
    Console mode. Type 'help' for more information.
  • To attach a MDF, type
    !attach "C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf"AdventureWorks
  • Type quit when you're done. This step is necessary to enable you to add the database into the sysdabases in the next step by removing the lock on file.

Step 2: Add MDF into sysdatabases
Now you have MDF attached to the server and have LDF recreated. But the database will still remain hidden from SQL Server Management Studio. In order to make it visible in SQL Server Management Studio, you need to go through the database attach wizard to add the database back to sysdatabases; or in the management studio, run either one of the following SQL statements:
  • USE master
    @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf',

  • USE master
    CREATE DATABASE AdventureWorks
    ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
I hope that you find this guide useful.

Wednesday, January 11, 2012

Unable to connect to SQL , “Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001”

When I try to connect to a SQL instance from SQL server Management studio I get the following error :
TITLE: Microsoft SQL Server Management Studio
An exception occurred while executing a Transact-SQL statement or batch. (Microsoft.SqlServer.ConnectionInfo)
Error executing extended stored procedure: Invalid Parameter
Error executing extended stored procedure: Invalid Parameter (Microsoft SQL Server, Error: 22001)
· I get the same error irrespective of Windows /SQL authentication mode
· The detailed error from management studio was as follows :
at System.Data.SqlClient.SqlConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.SqlInternalConnection.OnError(SqlException exception, Boolean breakConnection)
at System.Data.SqlClient.TdsParser.ThrowExceptionAndWarning(TdsParserStateObject stateObj)
at System.Data.SqlClient.TdsParser.Run(RunBehavior runBehavior, SqlCommand cmdHandler, SqlDataReader dataStream, BulkCopySimpleResultSet bulkCopyHandler, TdsParserStateObject stateObj)
at System.Data.SqlClient.SqlDataReader.ConsumeMetaData()
at System.Data.SqlClient.SqlDataReader.get_MetaData()
at System.Data.SqlClient.SqlCommand.FinishExecuteReader(SqlDataReader ds, RunBehavior runBehavior, String resetOptionsString)
at System.Data.SqlClient.SqlCommand.RunExecuteReaderTds(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, Boolean async)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method, DbAsyncResult result)
at System.Data.SqlClient.SqlCommand.RunExecuteReader(CommandBehavior cmdBehavior, RunBehavior runBehavior, Boolean returnStream, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader(CommandBehavior behavior, String method)
at System.Data.SqlClient.SqlCommand.ExecuteReader()
at Microsoft.SqlServer.Management.Common.ServerConnection.ExecuteReader(String sqlCommand)
· Although I was not able to expand the databases ,I Was able to open a new query window (from management studio) and run select @@version
· I checked to see if I had adequate privileges on the registry and found something very peculiar :
· The registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names was grayed out
· Since it was locked nothing else, including Management Studio, could read it
· I tried to view it in regedit and I got the error :Cannot open Instance Names: Error while opening key.
Restarting the "WMI" (Windows Management Instrumentation) service fixes this issue.
(Understandably a reboot of the Server and Failover in case of a clustered instance will also fix the issue)
This issue is very specific to Windows Server 2000
This occurs when the number of handles to SQL becomes exceedingly large . To confirm if I am running into this issue I can use process explorer
In the Menu I need to go to "Find Handle or DLL" and Type in the word "Instance Names" and hit "search"
This would yield the number of handles on our instance. Once you have this you can find out which process owns these handles and troubleshoot further. Based on our experience with such issues I have seen in the past, the WMIPRVSE.exe (Windows Management Instrumentation aka WMI) was one of the services that had a lot of handles open causing this issue to occur. It is possible that in your case it is another service than WMI. To identify this follow steps given above using Process Explorer.
SQL Management Studio requires to query the registry key HKEY_LOCAL_MACHINE\Software\Microsoft\Microsoft SQL Server\Instance Names
If for some reason it cannot do this using xp_regread you will see this error message.
When some application/service has too many handles open for a single object, then it is possible that new requests fail. Now handles are windows objects, and there is no hard limit as such but purely dependent on the resources available to the Operation System. But specifically for Win2K, the maximum number of handles for a program is 10,000. (Reference:
However, you can increase this number if you have the fix in this KB applied After you install this hotfix, follow the instructions in the KB to create the registry keys.