March 2011 – Page 33

March 3, 2011DataBase

Creating (Installing) SQL (named) Instance in SQL 2005 Express

For creating new instance of SQL server, you need to launch the SQL installer (usually the setup.exe) and choose the “Named Instance” option at the Instance Name prompt during the install setup wizard. Here is the detailed step-by-step screencast of this whole process:

March 3, 2011DataBase

MS SQL Query to list all active connections to all DBs on a given SQl instance

Query: To List all active connections to all DBs on given SQL instance SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE dbid > 0GROUP BY dbid, loginame Results: DatabaseName NoOfConnections LoginName Db01 2 NT AUTHORITYSYSTEM Db02 1 NT AUTHORITYSYSTEM master 16 sa QADB01 1 Testadmin-user Query: To List all active connections to specific DB on given SQL instance SELECT db_name(dbid) as DatabaseName, count(dbid) as NoOfConnections, loginame as LoginName FROM sys.sysprocesses WHERE (dbid > 0 AND db_name(dbid) like ‘%db0%’) GROUP BY dbid, loginame Results: DatabaseName NoOfConnections LoginName Db01 2 NT AUTHORITYSYSTEM Db02 1 NT AUTHORITYSYSTEM

March 2, 2011Technological

Restoring MS SQL DB

DB restoration is one of a often needed SQL operation. Error: while transferring a SQL DB from one server to other server. —————————— TITLE: Microsoft SQL Server Management Studio Express —————————— Restore failed for Server ‘TEST-SRVSQLEXPRESS’. (Microsoft.SqlServer.Express.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&EvtSrc=Microsoft.SqlServer.Management.Smo.ExceptionTemplates.FailedOperationExceptionText&EvtID=Restore+Server&LinkId=20476 —————————— ADDITIONAL INFORMATION: System.Data.SqlClient.SqlError: The operating system returned the error ‘5(Access is denied.)’ while attempting ‘RestoreContainer::ValidateTargetForCreation’ on ‘C:Program Files (x86)Microsoft SQL ServerMSSQL.1MSSQLTEST-DB.mdf’. (Microsoft.SqlServer.Express.Smo) For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=9.00.4035.00&LinkId=20476 —————————— BUTTONS: OK —————————— Fix: Ensure that you have the appropriate permissions to the location where you are trying to restore the DB. Alternatively, you can use other location where you have […]