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'  
GO


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'  

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
BEGIN--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 
END--clean up objects
CLOSE Cursor_db 
Deallocate Cursor_db 

SQL Server 2008 Database Mirroring

Check below videos for Database Mirroring

Improve SQL Server performance using profiler and tuning advisor

These videos shows a simple explanation of what is a SQL server profiler and performance tuning wizard.

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.

List all Tables in Database With / Without Primary Key

USE <DatabaseName>;
GO
SELECT SCHEMA_NAME(schema_id) AS Schema_Name, name AS Table_Name
FROM sys.tables
WHERE OBJECTPROPERTY(OBJECT_ID,'TableHasPrimaryKey') = 0
ORDER BY Schema_Name, Table_Name;
GO

SELECT i.name 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
ON i.OBJECT_ID = ic.OBJECT_ID
AND i.index_id = ic.index_id
WHERE i.is_primary_key = 1

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';
ALTER DATABASE Database_Name SET EMERGENCY
DBCC checkdb('Database_Name')
ALTER DATABASE Database_Name SET SINGLE_USER WITH ROLLBACK IMMEDIATE
DBCC CheckDB ('Database_Name', REPAIR_ALLOW_DATA_LOSS)
ALTER DATABASE Database_Name SET MULTI_USER


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

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]

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"

 

List the modified objects in SQL Server

For viewing the modified date of Stored Procs and UDF alone:

Select
Routine_name, Routine_Type, Created, Last_altered
From Information_schema.routines
Where
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:
Select
[name] as 'Object Name',
[type] as 'Object Type',
create_date,
modify_date
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

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.

USE Demo_DB

EXEC SP_Change_User_Login 'Report'

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

USE Demo_DB

EXEC SP_Change_User_Login 'Auto_Fix', 'SampleUser'

Above example displays usage of Auto_fix

USE Demo_DB

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:

http://technet.microsoft.com/en-us/library/ms174378.aspx

http://blogs.msdn.com/blakhani/archive/2008/02/25/script-map-all-orphan-users-to-logins-else-create-login-and-map.aspx

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.

Drop All Indexes and Stats in one Script

SQL Server 2005/2008

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR

SELECT indexes.name, objects.name, schemas.name 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 1 FROM sys.objects WHERE objects.name = indexes.name) 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 = 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

GO DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR

SELECT stats.name, objects.name, schemas.name 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 = 0 ORDER BY objects.OBJECT_ID, stats.stats_id DESC

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 

SQL Server 2000 Code

DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @indexname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropindexes CURSOR FOR


SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, 'IsStatistics') = 0
  AND sysobjects.TYPE = N'U'
  AND NOT EXISTS (SELECT 1 FROM sysobjects WHERE sysobjects.name = sysindexes.name) ORDER BY sysindexes.id, indid DESC


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


GO DECLARE @ownername SYSNAME
DECLARE @tablename SYSNAME
DECLARE @statsname SYSNAME
DECLARE @sql NVARCHAR(4000) DECLARE dropstats CURSOR FOR


SELECT sysindexes.name, sysobjects.name, sysusers.name FROM sysindexes JOIN sysobjects ON sysindexes.id = sysobjects.id JOIN sysusers ON sysobjects.uid = sysusers.uid WHERE indid > 0
  AND indid < 255
  AND INDEXPROPERTY(sysobjects.id, sysindexes.name, '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 

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, 
       Principal_ID, 
       Type_Desc, 
       Create_Date, 
       Modify_Date 
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, 
       CASE
       WHEN islogin = 1 then 'Login'
       ELSE 'Role'
       END AS Type_Desc, 
       CASE
       WHEN issqluser = 1 then 'SQL User'
       ELSE 'Windows User\Group'
       END AS Type_Desc2,
       CreateDate, 
       UpdateDate 
FROM SysUsers
WHERE Name Not Like '[dpg][bu][_obe]%'

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
GO
CREATE DATABASE AdventureWorks
ON PRIMARY (FILENAME = N'D:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
FOR ATTACH_REBUILD_LOG
GO 

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

where

• -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.


or


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.
  1>
• 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
  GO
  EXEC SP_ATTACH_DB
  @filename1=N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf',
  @dbname=N'AdventureWorks
  
  or
  
• USE master
  GO
  CREATE DATABASE AdventureWorks
  ON PRIMARY (FILENAME = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\AdventureWorks.mdf')
  FOR ATTACH
  GO 

I hope that you find this guide useful.
Ref: http://blog-rat.blogspot.com

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)

------------------------------

ADDITIONAL INFORMATION:

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.

Resolution

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)

INFORMATION

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

http://technet.microsoft.com/en-us/sysinternals/bb896653.aspx

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: http://support.microsoft.com/kb/326591

However, you can increase this number if you have the fix in this KB applied http://support.microsoft.com/kb/326591. After you install this hotfix, follow the instructions in the KB to create the registry keys.

Associated forum link : http://social.msdn.microsoft.com/forums/en-us/sqlgetstarted/thread/193EC2F0-125D-4141-B867-8A3349BB0606