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