Step By Step Installing SQL Server 2012 SP1 on Windows 2012 Datacenter Server

Installation od SQL Server 2012 Sp1 is fairly easy to its counterpart DBMS available in the market.

Download the evaluation version if you want to try on your own from the Microsoft’s website. Here is the link :-

http://www.microsoft.com/en-us/download/details.aspx?id=29066

I have used the SQL Server 2012 developer version on the VMware 9/Windows 2012 datacenter.

Click on the setup.exe file provided with the setup folder :-

When you see the SQL Server Installation Center window it means the set up is ready to install on the system and the system pre-check was successful.

Click “Installation” from the left pane and select “New SQL Server stand-alone installation or add features to an existing installation”. Setup will open up a window to check “Setup Support Rules” click Ok on it if failed message is “0” else click “re-run” button.

Next is to provide the product key came along with the package CD. If you have downloaded the evaluation version from the Microsoft website then choose Specify Free version as “Evaluation” which is having a time limit of 180 days to expire. You can also choose “Express” or leave the product key as of now to fill in later. Click next to start the installation.

Select the “I accept the license terms” and click “Next” button.

Choose the default feature “SQL Server Feature Installation”,then press “Next”button.

Select the features and change the “Shared feature directory” if you want, otherwise press “Next” button. For this demo installation I have chosen to install Database engine,Reporting and Analysis service.

Setup will check installation rules and if failed is “0” then click next

Next is Instance configuration here I have chosen default as this is the only instance present on my server.If you want change the “Instance root directory” to the path where you want to install the SQL instance and click next.

“Disk Usage Summary” is shown on the screen to review and if disk space is not available then setup will not proceed , click next when done.

Next screen “Server Configuration” allows  you to choose the Service Accounts for each service and its startup option in Windows. I have chosen default account name and startup option as automatic. This screen also provides option to change the Collation if you want. Click next when done.

Next screen “Database Engine Configuration” lets you to change the authentication mode, data directories and filestream if any.It also lets you to add the users as SQL Server Administrators by letting you to choose the current windows user or any other windows user to choose from.Here you can also choose the password for the SA account if the authentication mode chosen is “Mixed mode”. Click next when done.

If you have chosen the Analysis Services in the feature selection window then the next step will ask you to set up the Analysis Services. Here, I have selected “Multidimensional and Data Mining Mode” in “Server Mode” and chose the administrator for the analysis services. Also change the “Data Directories” if you want ,note you can select only one server mode to use: “Multidimensional and Data Mining Mode” or “Tabular Mode”. If you want both, you need to run the setup again after the first instance setup otherwise Press “Next” button.

Next window will let you configure the Reporting services if “Reporting Services” has been chosen in the Feature Selection.I have chosen Reporting Services Native Mode as “Install and configure” and press “Next” button.

Tick the Error reporting option if you want to send the information regarding setup to Microsoft.

Click next to begin the installation if failed “0” otherwise review the error.

Review the Installation Summary and click next

Installation will start and progress is displayed on the screen.

Once completed successfully the set up will display the results.

Verify the installation by connecting to SQL Server Management Studio.

SQL Server 2012 is ready to use and explore new features in it.

Query to find the set of backups taken!!

This query finds the backups taken for the database.I found it very useful while troubleshooting the backup issues.

SELECT  
      bkSet.Backup_Start_Date as BackupStartDate,
      bkSet.Backup_Finish_Date as BackupEndDate,
    MedFam.Physical_Device_Name AS BackupPath,
    MedSet.Software_Name AS SoftwareUsedForBackup,
    bkSet.User_Name AS BackupTakenBy,
    bkSet.Server_Name AS ServerName,
    bkSet.Database_Name As DatabaseName,
    CASE bkSet.Type  
            WHEN 'L' THEN 'TransactionLogBackup'
            WHEN 'D' THEN 'FullBackup'
            WHEN 'F' THEN 'FileBackup'
            WHEN 'I' THEN 'DifferentialBackup'
        WHEN 'G' THEN 'DifferentialFileBackup'
        WHEN 'P' THEN 'PartialBackup'
        WHEN 'Q' THEN 'DifferentialPartialBackup'
        ELSE NULL END AS BackupType,
    CAST((bkSet.Backup_Size/1048576) AS NUMERIC(10,2)) AS BackupSizeInMB
FROM    
      msdb..BackupMediaFamily MedFam
INNER JOIN
      msdb..BackupMediaSet MedSet
ON
      MedFam.Media_Set_ID = MedSet.Media_Set_ID
INNER JOIN
      msdb..BackupSet bkSet
ON
      bkSet.Media_Set_ID = MedSet.Media_Set_ID
WHERE  
      --keep your database name in condition
      bkSet.Database_Name = 'MENTIS'
ORDER BY
      bkSet.Backup_Finish_Date DESC


Source:-http://www.sqlhub.com

Date Formats in SQL Server

Standard Date Formats
Date Format	Standard	SQL Statement	Sample Output
Mon DD YYYY 1	Default	SELECT CONVERT(VARCHAR(20), GETDATE(), 100)	Jan 1 2005 1:29PM 1
HH:MIAM (or PM)
MM/DD/YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 1) AS [MM/DD/YY]	11/23/1998
MM/DD/YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 101) AS [MM/DD/YYYY]	11/23/1998
YY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(8), GETDATE(), 2) AS [YY.MM.DD]	72.01.01
YYYY.MM.DD	ANSI	SELECT CONVERT(VARCHAR(10), GETDATE(), 102) AS [YYYY.MM.DD]	1972.01.01
DD/MM/YY	British/French	SELECT CONVERT(VARCHAR(8), GETDATE(), 3) AS [DD/MM/YY]	19/02/72
DD/MM/YYYY	British/French	SELECT CONVERT(VARCHAR(10), GETDATE(), 103) AS [DD/MM/YYYY]	19/02/1972
DD.MM.YY	German	SELECT CONVERT(VARCHAR(8), GETDATE(), 4) AS [DD.MM.YY]	25.12.05
DD.MM.YYYY	German	SELECT CONVERT(VARCHAR(10), GETDATE(), 104) AS [DD.MM.YYYY]	25.12.2005
DD-MM-YY	Italian	SELECT CONVERT(VARCHAR(8), GETDATE(), 5) AS [DD-MM-YY]	24-01-98
DD-MM-YYYY	Italian	SELECT CONVERT(VARCHAR(10), GETDATE(), 105) AS [DD-MM-YYYY]	24-01-1998
DD Mon YY 1	-	SELECT CONVERT(VARCHAR(9), GETDATE(), 6) AS [DD MON YY]	04 Jul 06 1
DD Mon YYYY 1	-	SELECT CONVERT(VARCHAR(11), GETDATE(), 106) AS [DD MON YYYY]	04 Jul 2006 1
Mon DD, YY 1	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 7) AS [Mon DD, YY]	Jan 24, 98 1
Mon DD, YYYY 1	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 107) AS [Mon DD, YYYY]	Jan 24, 1998 1
HH:MM:SS	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 108)	3:24:53
Mon DD YYYY HH:MI:SS:MMMAM (or PM) 1	Default +	SELECT CONVERT(VARCHAR(26), GETDATE(), 109)	Apr 28 2006 12:32:29:253PM 1
	milliseconds
MM-DD-YY	USA	SELECT CONVERT(VARCHAR(8), GETDATE(), 10) AS [MM-DD-YY]	1/1/2006
MM-DD-YYYY	USA	SELECT CONVERT(VARCHAR(10), GETDATE(), 110) AS [MM-DD-YYYY]	1/1/2006
YY/MM/DD	-	SELECT CONVERT(VARCHAR(8), GETDATE(), 11) AS [YY/MM/DD]	98/11/23
YYYY/MM/DD	-	SELECT CONVERT(VARCHAR(10), GETDATE(), 111) AS [YYYY/MM/DD]	11/23/1998
YYMMDD	ISO	SELECT CONVERT(VARCHAR(6), GETDATE(), 12) AS [YYMMDD]	980124
YYYYMMDD	ISO	SELECT CONVERT(VARCHAR(8), GETDATE(), 112) AS [YYYYMMDD]	19980124
DD Mon YYYY HH:MM:SS:MMM(24h) 1	Europe default + milliseconds	SELECT CONVERT(VARCHAR(24), GETDATE(), 113)	28 Apr 2006 00:34:55:190 1
HH:MI:SS:MMM(24H)	-	SELECT CONVERT(VARCHAR(12), GETDATE(), 114) AS [HH:MI:SS:MMM(24H)]	11:34:23:013
YYYY-MM-DD HH:MI:SS(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(19), GETDATE(), 120)	1/1/1972 13:42
YYYY-MM-DD HH:MI:SS.MMM(24h)	ODBC Canonical	SELECT CONVERT(VARCHAR(23), GETDATE(), 121)	35:24.5
	(with milliseconds)
YYYY-MM-DDTHH:MM:SS:MMM	ISO8601	SELECT CONVERT(VARCHAR(23), GETDATE(), 126)	1998-11-23T11:25:43:250
DD Mon YYYY HH:MI:SS:MMMAM 1	Kuwaiti	SELECT CONVERT(VARCHAR(26), GETDATE(), 130)	28 Apr 2006 12:39:32:429AM 1
DD/MM/YYYY HH:MI:SS:MMMAM	Kuwaiti	SELECT CONVERT(VARCHAR(25), GETDATE(), 131)	28/04/2006 12:39:32:429AM

 

¹ To make the month name in upper case, simply use the UPPER string function.

Extended Date Formats in SQL Server

Extended Date Formats
Date Format	SQL Statement	Sample Output
YY-MM-DD	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 8) AS [YY-MM-DD]	99-01-24
	SELECT REPLACE(CONVERT(VARCHAR(8), GETDATE(), 11), '/', '-') AS [YY-MM-DD]
YYYY-MM-DD	SELECT CONVERT(VARCHAR(10), GETDATE(), 120) AS [YYYY-MM-DD]	1/24/1999
	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 111), '/', '-') AS [YYYY-MM-DD]
MM/YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 3), 5) AS [MM/YY]	Aug-99
	SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 3), 4, 5) AS [MM/YY]
MM/YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 103), 7) AS [MM/YYYY]	Dec-05
YY/MM	SELECT CONVERT(VARCHAR(5), GETDATE(), 11) AS [YY/MM]	99/08
YYYY/MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 111) AS [YYYY/MM]	2005/12
Month DD, YYYY 1	SELECT DATENAME(MM, GETDATE()) + RIGHT(CONVERT(VARCHAR(12), GETDATE(), 107), 9) AS [Month DD, YYYY]	July 04, 20061
Mon YYYY1	SELECT SUBSTRING(CONVERT(VARCHAR(11), GETDATE(), 113), 4, 8) AS [Mon YYYY]	Apr 2006 1
Month YYYY 1	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [Month YYYY]	February 2006 1
DD Month1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) AS [DD Month]	11 September 1
Month DD1	SELECT DATENAME(MM, GETDATE()) + ' ' + CAST(DAY(GETDATE()) AS VARCHAR(2)) AS [Month DD]	September 11 1
DD Month YY 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + RIGHT(CAST(YEAR(GETDATE()) AS VARCHAR(4)), 2) AS [DD Month YY]	19 February 72 1
DD Month YYYY 1	SELECT CAST(DAY(GETDATE()) AS VARCHAR(2)) + ' ' + DATENAME(MM, GETDATE()) + ' ' + CAST(YEAR(GETDATE()) AS VARCHAR(4)) AS [DD Month YYYY]	11 September 2002 1
MM-YY	SELECT RIGHT(CONVERT(VARCHAR(8), GETDATE(), 5), 5) AS [MM-YY]	Dec-92
	SELECT SUBSTRING(CONVERT(VARCHAR(8), GETDATE(), 5), 4, 5) AS [MM-YY]
MM-YYYY	SELECT RIGHT(CONVERT(VARCHAR(10), GETDATE(), 105), 7) AS [MM-YYYY]	May-06
YY-MM	SELECT RIGHT(CONVERT(VARCHAR(7), GETDATE(), 120), 5) AS [YY-MM]	92/12
	SELECT SUBSTRING(CONVERT(VARCHAR(10), GETDATE(), 120), 3, 5) AS [YY-MM]
YYYY-MM	SELECT CONVERT(VARCHAR(7), GETDATE(), 120) AS [YYYY-MM]	2006-05
MMDDYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 1), '/', '') AS [MMDDYY]	122506
MMDDYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 101), '/', '') AS [MMDDYYYY]	12252006
DDMMYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 3), '/', '') AS [DDMMYY]	240702
DDMMYYYY	SELECT REPLACE(CONVERT(VARCHAR(10), GETDATE(), 103), '/', '') AS [DDMMYYYY]	24072002
Mon-YY 1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(9), GETDATE(), 6), 6), ' ', '-') AS [Mon-YY]	Sep-02 1
Mon-YYYY1	SELECT REPLACE(RIGHT(CONVERT(VARCHAR(11), GETDATE(), 106), 8), ' ', '-') AS [Mon-YYYY]	Sep-2002 1
DD-Mon-YY 1	SELECT REPLACE(CONVERT(VARCHAR(9), GETDATE(), 6), ' ', '-') AS [DD-Mon-YY]	25-Dec-05 1
DD-Mon-YYYY 1	SELECT REPLACE(CONVERT(VARCHAR(11), GETDATE(), 106), ' ', '-') AS [DD-Mon-YYYY]	25-Dec-20051

 

¹ To make the month name in upper case, simply use the UPPER string function.

DB_NAME (Function)

 

This returns the name of the supplied DB_ID.If no argument is passed then the name of the current database is returned.The return type is nvarchar(128).

To find the current database name:-

select DB_NAME() as Current_Database_name;

go

select DB_NAME(6) as Database_name;

go

DB_ID (Function)

 

DB_ID () function returns int valued ID of the supplied database. If no argument is provided then it return the ID of the current database.

1. To find the ID of current database:-

select DB_ID( ) as Database_ID;

go

select DB_ID(‘AdventureWorks2008R2’) ad Database_ID;

go

2. Using DB_ID to specify the value of a system function parameter

The following example uses DB_ID to return the database ID of the AdventureWorks2008R2 database in the system function sys.dm_db_index_operational_stats. The function takes a database ID as the first parameter.

DECLARE @db_id int;
DECLARE @object_id int;
SET @db_id = DB_ID(N'AdventureWorks2008R2');
SET @object_id = OBJECT_ID(N'AdventureWorks2008R2.Person.Address');
IF @db_id IS NULL
  BEGIN;
    PRINT N'Invalid database';
  END;
ELSE IF @object_id IS NULL
  BEGIN;
    PRINT N'Invalid object';
  END;
ELSE
  BEGIN;
    SELECT * FROM sys.dm_db_index_operational_stats(@db_id, @object_id, NULL, NULL);
  END;
GO

Oracle Cheat Sheet

Some useful commands:-

To start a session as sysdba:
sqlplus sys@tnsname as sysdba;

To start a sysdba session under Windows (9iAS):
sqlplus "/as sysdba"

To list all tables in current schema:
SELECT table_name FROM user_tables;

or, all tables current user has access to:
SELECT table_name FROM all_tables;

To list all schemas:
SELECT username FROM all_users ORDER BY username;

To turn pause on:
SET PAUSE ON;

To list top n rows of a table in order:
SELECT * FROM (SELECT * FROM t ORDER BY c) WHERE ROWNUM <= n;

Show current database:
SELECT * FROM global_name;

Use database:
CONNECT schema/password@tnsname;

Show who I am:
SHOW USER;

Describe table:
DESC tablename;

Set display rows:
SET PAGESIZE 66;

Read field constraints:
SELECT constraint_name,search_condition FROM user_constraints WHERE table_name='tablename';

Copy table from foreign host to here:
COPY FROM user@tnsname CREATE tablename USING SELECT * FROM tablename;

Start SQLPLUS without login:
SQLPLUS /NOLOG

Change a user's password:
ALTER USER user IDENTIFIED BY password;

Unlock an account
ALTER USER user ACCOUNT UNLOCK;

Oracle has no autonumbers like SQL Server, Access, or MySQL. One way to do autonumbers is by using a combination of a sequence and a trigger, as in the following example:

CREATE SEQUENCE sequence-name;

CREATE OR REPLACE TRIGGER trigger-name
BEFORE INSERT ON table-name
FOR EACH ROW
WHEN (NEW.field-name IS NULL OR NEW.field-name = '<new>')
BEGIN
SELECT 'PR-' || sequence-name.NEXTVAL INTO :NEW.field-name FROM DUAL;
END;

Best Site for daily deals!!

One of my favourite sites over the net is deals2buy dot com.Its one of the best site to purchase electronics gadgets n more.Best part is its updated daily for the best deals on the inetrnet.

So keep buying your favourite gadgets at lower price..

Recover Production Box

1 - Copy the master.mdf & .ldf files from the corresponding dev/test server. It should then be at the same SP level and collation as the production box. This will allow SQL to start up successfully, but other databases and logins will be missing and probably marked suspect.
2 - Restore master from the backup. This will put the logins back and you will definitely be back at the correct SP level and have the info about what databases were on the box. In addition, the admin procs will be there for you if there are any.
3 - Restore the LiteSpeed database to get the LiteSpeed backup history and any procs needed to perform further restores if you use Litespeed for backup/restore.
4 - Restore msdb, model, admin db's. The SQL agent should now be able to start, the jobs should all be there, the jobs will run successfully.
5 - Restore the remainder of the databases and take backups.

how to find object in SQL server !

-- Part 1
Declare @sqlstr nvarchar(200)

-- Part 2
/* drop the temporary table if already exists */
If Object_Id('tempdb..#tblDBObjects') is Not Null
Drop table #tblDBObjects
/* create temporary table */
Create TABLE #tblDBObjects (
dbName sysname,
objName varchar(200),
objtype char(2)
)

-- Part 3
/*assign string value to variable */
Select @sqlstr = 'sp_msforeachdb ''Insert #tblDBObjects select ''''?'''' as DBName, name, xtype From ?..sysobjects'''
/* execute SQL string */
Exec sp_executesql @sqlstr

-- Part 4
/* select from temp table */
Select * From #tblDBObjects Where objname like 'PORTFOLIO_GROUP%'
RETURN

How to identify your SQL Server version and edition

To determine which version of Microsoft SQL Server 2005 is running, connect to SQL Server 2005 by using SQL Server Management Studio, and then run the following Transact-SQL statement:
SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The results are:
• The product version (for example, "9.00.1399.06").
• The product level (for example, "RTM").
• The edition (for example, "Enterprise Edition").

For example, the result looks similar to:9.00.1399.06 RTM Enterprise Edition
The following table lists the Sqlservr.exe version number: Release Sqlservr.exe
RTM 2005.90.1399
SQL Server 2005 Service Pack 1 2005.90.2047

How to determine which version of SQL Server 2000 is running

To determine which version of SQL Server 2000 is running, connect to SQL Server 2000 by using Query Analyzer, and then run the following code:

SELECT SERVERPROPERTY('productversion'), SERVERPROPERTY ('productlevel'), SERVERPROPERTY ('edition')

The results are:

• The product version (for example, 8.00.534).
• The product level (for example, "RTM" or "SP2").
• The edition (for example, "Standard Edition"). For example, the result looks similar to:

8.00.534 RTM Standard Edition
The following table lists the Sqlservr.exe version number:
Release Sqlservr.exe RTM 2000.80.194.0
SQL Server 2000 SP1 2000.80.384.0
SQL Server 2000 SP2 2000.80.534.0
SQL Server 2000 SP3 2000.80.760.0
SQL Server 2000 SP3a 2000.80.760.0
SQL Server 2000 SP4 2000.8.00.2039


How to determine which version of SQL Server 7.0 is running
To determine which version of SQL Server 7.0 is running, connect to SQL Server 7.0 by using Query Analyzer, and then run the following code:

SELECT @@VERSION

The results look similar to the following:Microsoft SQL Server 7.00 - 7.00.623 (Intel X86)
Nov 27 1998 22:20:07
Copyright (c) 1988-1998 Microsoft Corporation
Desktop Edition on Windows NT 5.1 (Build 2600: )
Note In this example, the version number is 7.00.623.

Use the version number in the following table to identify the product or service pack level:
Version Number Service Pack
7.00.1063 SQL Server 7.0 Service Pack 4 (SP4)
7.00.961 SQL Server 7.0 Service Pack 3 (SP3)
7.00.842 SQL Server 7.0 Service Pack 2 (SP2)
7.00.699 SQL Server 7.0 Service Pack 1 (SP1)
7.00.623 SQL Server 7.0 RTM (Release To Manufacturing)
If the version number that is reported by @@VERSION is not listed in this table, SQL Server is running with a hotfix or a security update build. For example, if @@VERSION reports a version number of 7.00.859, you are running SQL Server 7.0 SP2 with a hotfix installed. The version number increases with each new version of the Sqlservr.exe executable file. See to the Readme.txt file for your hotfix or security update for more information.


How to determine which version of SQL Server 6.5 is running
To determine which version of Microsoft SQL Server 6.5 is running, connect to SQL Server 6.5 by using Isql_w, and then run the following code:SELECT @@VERSION
Use the version number in the following table to identify the product or service pack level:Version Number Service Pack
6.50.479 SQL Server 6.5 Service Pack 5a (SP5a) Update
6.50.416 SQL Server 6.5 Service Pack 5a (SP5a)
6.50.415 SQL Server 6.5 Service Pack 5 (SP5)
6.50.281 SQL Server 6.5 Service Pack 4 (SP4)
6.50.258 SQL Server 6.5 Service Pack 3 (SP3)
6.50.240 SQL Server 6.5 Service Pack 2 (SP2)
6.50.213 SQL Server 6.5 Service Pack 1 (SP1)
6.50.201 SQL Server 6.5 RTM

If the version number that is reported by @@VERSION is not listed in this table, SQL Server is running with a hotfix or a security update build. The version number increases with each new version of the Sqlservr.exe executable file. See to the Readme.txt file for your hotfix or security update for more information.

How to determine which edition of SQL Server is running
If you are not sure about what edition of SQL Server you are running, the last line of output that is returned by @@VERSION reports the edition to which you have connected. The example that is used in this article is the Standard Edition of SQL Server 2000 on Windows NT 5.0 (Build 2195: Service Pack 2).

Note The build and service pack information provided earlier is for the operating system, not for SQL Server.Standard Edition on Windows NT 5.0 (Build 2195: Service Pack 2)

more info @ microsoft