Ahmed Ibrahim - SQL Server

Installing SQL Server 2012 + Service Pack 1 + SP1 Cumulative update in one installation using SQL Server 2012 Product Update

Ahmed Ibrahim - MSFT — Tue, 15 Jan 2013 01:58:00 GMT

1-Let us have below folder structure where media for RTM, Service pack 1 and cumulative update exist.

2-Create below configuration file that will determine all setup parameters required during installation process,The file will be saved as ConfigurationFile.ini under c:\SQL2012\

Configure the parameters highlighted in green needs to be tuned, also PID needs to be provided incase of non evaluation installations.

those are

Features to be installed.
AS SYS Admin account.
SQL SYS Admin account.
Instance name.

;SQL SERVER 2012 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter.

ACTION="Install"

; Detailed help for command line argument ROLE has not been defined yet.

ROLE="AllFeatures_WithDefaults"

; Detailed help for command line argument ENU has not been defined yet.

ENU="True"

; Setup will not display any user interface.

QUIET="True"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and
False or 1 and 0. By default SQL Server Setup will include updates that are found.

UpdateEnabled="True"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS,
MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality
Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server
Data Tools, and other shared components.

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS,DQC,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK,MDS

; Specifies the list of administrator accounts that need to be provisioned.

ASSYSADMINACCOUNTS="<ASSYSADMINACCOUNT>"

; Windows account(s) to provision as SQL Server system administrators.

SQLSYSADMINACCOUNTS="<SYSADMINACCOUNT>"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to
enable and 0 or False to disable this feature.

SQMREPORTING="False"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to
enable and 0 or False to disable this feature.

ERRORREPORTING="FALSE"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and
SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine
(SQL), Analysis Services (AS), or Reporting Services (RS).

INSTANCENAME="MSSQLSERVER"

; RSInputSettings_RSInstallMode_Description

RSINSTALLMODE="FilesOnlyMode"

; Don't provision current user as a Database Engine system administrator.

ADDCURRENTUSERASSQLADMIN="False"

UPDATESOURCE=".\Updates"

The parameters highlighted in red specify that product update feature will be enabled in this installation process and the folder where all applicable SQL Server updates reside is “C:\SQLServer2012\Updates”

3-Run below command from command prompt in elevated mode”:

setup.exe /ConfigurationFile=SQLServer2012Config.ini /IACCEPTSQLSERVERLICENSETERMS

4- Here are the results of installation process, highlighted parts of installation summary and discovery report will ensure that installation of SQL Server 2012 plus Service Pack 1 plus Service Pack Cumulative Update completed successfully.

Common questions posted on SQL Server setup & deployment Forum

Ahmed Ibrahim - MSFT — Thu, 18 Oct 2012 23:00:00 GMT

1. Licensing questions: Call (8004269400), Monday through Friday, 6:00 AM to 6:00 PM PST to speak directly to Microsoft licensing specialist.

http://www.microsoft.com/sqlserver/en/us/get-sql-server/how-to-buy.aspx
http://www.microsoft.com/sqlserver/en/us/get-sql-server/licensing.aspx
http://www.microsoft.com/licensing/about-licensing/sql2012.aspx

2. Setup failure (install, upgrade, patch, un-install): Post to SQL Setup & Upgrade MSDN forum, please upload/share SQL Server setup logs, you can use file/storage service like Skydrive to share/upload SQL Server setup logs.

3. Connection issues(error 26 or error 40): Steps to troubleshoot SQL connectivity issues

4. Supported upgrade paths

5. Hardware and Software Requirements:

6. Supported features per edition

7. Determine what features installed on the machine and at what level.: How to determine the version and edition of SQL Server and its components

8. SQL Server on windows 8/ Server 2012

For SQl Server 2005(not – supported) & SQL Server 2008(SQL Server 2008 service pack 3 required)

Recovering from SQL Server upgrade failure

Ahmed Ibrahim - MSFT — Sat, 13 Oct 2012 00:20:00 GMT

1. The scenario here we are trying to upgrade from SQL Server 2008 R2 to SQL Server 2012, so I turned off the machine (simulating power outage) while upgrade is in progress

2. When I try to run upgrade command again, I am blocked with below rule

3. Generating discovery report by running setup.exe /Action=Runsdiscovery, we will notice majority of features still at 2008 R2 level but we have AS is at SQL Server 2012 level and it is un-configured, so we need to uninstall all features that is related to SQL Server 2012, It is important to review checklist in following article before doing uninstallation for the 2012 features

http://msdn.microsoft.com/en-us/library/ms143412(v=sql.110).aspx

4. To un-install failed 2012 features (Analysis Services) we need to run below command:

Setup.exe /action=uninstall /instanceid=MSSQLSERVER /features=<Features at 11.0.2100.60> /Q

In this case since we have only AS at SQL Server 2012 level we need to run

Setup.exe /action=uninstall /instanceid=MSSQLSERVER /features=AS /Q

5. Generating the discovery report again we now have SQL Server 2008 R2 features

6. Running the upgrade command again, it ran successfully

Enabling TDE(Transparent Data Encryption) on all user databases

Ahmed Ibrahim - MSFT — Tue, 25 Sep 2012 01:59:00 GMT

Below blog post provides TSQL script that encrypts all user databases, please note:

This posting is provided "AS IS" with no warranties, and confers no rights.

References

-- 0- status before encryption

SELECT db_name(database_id), EncryptionState =

CASE encryption_state

WHEN 0 THEN 'No database encryption key present, no encryption'

WHEN 1 THEN 'Unencrypted'

WHEN 2 THEN 'Encryption in progress'

WHEN 3 THEN 'Encrypted'

WHEN 4 THEN 'Key change in progress'

WHEN 5 THEN 'Key change in progress'

WHEN 6 THEN 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.'

ELSE 'Un-recognized encryption status.'

END

FROM sys.dm_database_encryption_keys

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 1- Creating database master key (DMK)

Use master

CREATE MASTER KEY ENCRYPTION BY PASSWORD = 'Str0ng79Pa55w0rd';

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 2- Create new certificate to protect DEK.

CREATE CERTIFICATE SQLTDECert WITH SUBJECT = 'SQL Server TDE Certificate';

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 3- Create backup of the certificate with private key

BACKUP CERTIFICATE SQLTDECert TO FILE = 'c:\CertificateBackup\cert.bakup'

WITH PRIVATE KEY (

FILE = 'c:\CertificateBackup\cert_Private_Key.bakup',

ENCRYPTION BY PASSWORD = 'Cert79Passw0rd');

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 4- Create the database encryption key(DEK) encrypted with server certificate

DECLARE @dbKeyCreationStmt varchar(500);

DECLARE @selectedUserDatabases varchar(500);

@selectedUserDatabases

set @dbKeyCreationStmt = '

CREATE DATABASE ENCRYPTION KEY

WITH ALGORITHM = AES_256

ENCRYPTION BY SERVER CERTIFICATE SQLTDECert '

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Selecting user database names.

select name as DatabaseName

from sysdatabases

where ([dbid] > 4) and ([name] not like '$')

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @encryptionStmt varchar(500);

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

--=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_=_

-- 5- loop through userdatabases and start background thread that does encryption

-- Encryption SQL statment

set @encryptionStmt = 'ALTER DATABASE [' + @dbName + '] SET ENCRYPTION ON '

-- Print SQL statement

print @dbKeyCreationStmt

print @encryptionStmt

-- Execute Encryption script

EXEC (' USE [' + @dbName+'] '+@dbKeyCreationStmt);

EXEC (@encryptionStmt)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

-- Get database states during encryption

-- From http://msdn.microsoft.com/en-us/library/cc278098(v=sql.100).aspx#_Toc189384673

-- When TDE is enabled (or disabled), the database is marked as encrypted in the sys.databases catalog view

-- and the DEK state is set to Encryption In Progress. The server starts a background thread

-- (called the encryption scan or scan) that scans all database files and encrypts them

-- (or decrypts them if you are disabling TDE).

-- While the DDL executes, an update lock is taken on the database.

-- The encryption scan, which runs asynchronously to the DDL, takes a shared lock.

-- All normal operations that do not conflict with these locks can proceed. Excluded operations include modifying the file structure

-- and detaching the database. While normal database writes to disk from the buffer pool are encrypted,

-- log file writes may not be. The scan also forces a rollover for the virtual log file (VLF) to ensure

-- that future writes to the log are encrypted.

SELECT db_name(database_id), EncryptionState =

CASE encryption_state

WHEN 0 THEN 'No database encryption key present, no encryption'

WHEN 1 THEN 'Unencrypted'

WHEN 2 THEN 'Encryption in progress'

WHEN 3 THEN 'Encrypted'

WHEN 4 THEN 'Key change in progress'

WHEN 5 THEN 'Key change in progress'

WHEN 6 THEN 'Decryption in progress'

ELSE 'Protection change in progress (The certificate or asymmetric key that is encrypting the database encryption key is being changed.'

END

FROM sys.dm_database_encryption_keys

Installing SQL Server 2012 Developer edition using SQL Server 2012 Enterprise evaluation media.

Ahmed Ibrahim - MSFT — Mon, 13 Aug 2012 20:05:00 GMT

DVD corruption is common problem that may happen when installing SQL Server 2012, one way to solve this problem is to download SQL server 2012 Enterprise evaluation

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

Then in product key dialog enter PID for developer edition

Migrating SQL Server 2000 user databases to SQL server 2012

Ahmed Ibrahim - MSFT — Thu, 09 Aug 2012 01:53:00 GMT

T-SQL scripts to migrate SQL server 2000 user databases to SQL server 2012

1. Backup SQL 2000 user databases using below scripts: Please customize parts highlighted in yellow

/********************************************************************************

* Backup SQL server 2000 user databases

********************************************************************************/

use master;

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Selecting user database names.

select name as DatabaseName

from sysdatabases

where ([dbid] > 4) and ([name] not like '$')

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @backupQuery varchar(500);

-- make sure that the below path exists

set @backupPath = 'c:\SQL2000Backup\'

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

-- Backup SQL statement

set @backupQuery = 'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[SQL2000].bak'''

-- Print SQL statement

print @backupQuery

-- Execute backup script

EXEC (@backupQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

2. Restore SQL 2000 backed-up user databases to SQL server 2008 or SQL Server 2008 R2 using below scripts: Please customize parts highlighted in yellow.

/********************************************************************************

* Restore SQL server 2000 backed databases to

* SQL server 2008 or SQL Server 2008 R2 instances

********************************************************************************/

use master;

/* Please un-comment below part to enable cp_cmdshell procedures

Don't forget to disable it after migration completed.

EXEC sp_configure'xp_cmdshell', 1

RECONFIGURE

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO*/

DECLARE @Return int

IF OBJECT_ID('tempdb..#SQL2000BackedUpdatabases') IS NOT NULL DROP TABLE #SQL2000BackedUpdatabases

CREATE TABLE #SQL2000BackedUpdatabases (BakFileName varchar(400), ID int IDENTITY(1,1))

INSERT #SQL2000BackedUpdatabases EXECUTE @Return = master.dbo.xp_cmdshell 'dir /B *.bak c:\SQL2000Backup'

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Restoring user database names.

select BakFileName from #SQL2000BackedUpdatabases

where BakFileName like '%bak'

OPEN UserDatabases_CTE_Cursor

DECLARE @BakFileName varchar(100);

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @SQL2008RestoreFolder varchar(100);

DECLARE @backupQuery varchar(500);

-- make sure that the below path exists

set @backupPath = 'c:\SQL2000Backup\'

-- make sure that the below path exists

set @SQL2008RestoreFolder = 'c:\SQL2008RestoreFolder\'

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

While (@@FETCH_STATUS <> -1)

BEGIN

set @dbName = REPLACE(@BakFileName, '_[SQL2000].bak', '')

-- Restore SQL 2000 backed databases to SQL server 2008 or SQL Server 2008 R2.

set @backupQuery = 'RESTORE DATABASE ' +@dbName

+ ' FROM DISK = ''' + @backupPath + @BakFileName +''' '

+'WITH MOVE ''' + @dbName +''' TO ''' + @SQL2008RestoreFolder + @dbName + '.mdf'','

+ 'MOVE ''' + @dbName +'_Log'' TO ''' + @SQL2008RestoreFolder + @dbName +'.ldf'''

-- Print SQL statement

print @backupQuery

-- Execute backup script

EXEC (@backupQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

3. Backup SQL Server 2008 user databases using below script: Please customize parts highlighted in yellow

/********************************************************************************

* Backup SQL server 2008 user databases

********************************************************************************/

use master;

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Selecting user database names.

select name as DatabaseName

from sysdatabases

where ([dbid] > 4) and ([name] not like '$')

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @backupQuery varchar(500);

-- make sure that the below path exists

set @backupPath = 'c:\SQL2008Backup\'

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

-- Backup SQL statement

set @backupQuery = 'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[SQL2008].bak'''

-- Print SQL statement

print @backupQuery

-- Execute backup script

EXEC (@backupQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

4. Restore SQL 2008 backed-up user databases to SQL server 2012 using below scripts: Please customize parts highlighted in yellow.

/********************************************************************************

* Restore SQL server 2008 backed databases to

* SQL server 2012

********************************************************************************/

use master;

/* Please un-comment below part to enable cp_cmdshell procedures

Don't forget to disable it after migration completed.

EXEC sp_configure'xp_cmdshell', 1

RECONFIGURE

EXEC sp_configure 'show advanced options', 1

RECONFIGURE

GO*/

DECLARE @Return int

IF OBJECT_ID('tempdb..#SQL2008BackedUpdatabases') IS NOT NULL DROP TABLE #SQL2008BackedUpdatabases

CREATE TABLE #SQL2008BackedUpdatabases (BakFileName varchar(400), ID int IDENTITY(1,1))

INSERT #SQL2008BackedUpdatabases EXECUTE @Return = master.dbo.xp_cmdshell 'dir /B *.bak c:\SQL2008Backup'

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Restoring user database names.

select BakFileName from #SQL2008BackedUpdatabases

where BakFileName like '%bak'

OPEN UserDatabases_CTE_Cursor

DECLARE @BakFileName varchar(100);

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @SQL2012RestoreFolder varchar(100);

DECLARE @backupQuery varchar(500);

-- make sure that the below path exists

set @backupPath = 'c:\SQL2008Backup\'

-- make sure that the below path exists

set @SQL2012RestoreFolder = 'c:\SQL2012RestoreFolder\'

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

While (@@FETCH_STATUS <> -1)

BEGIN

set @dbName = REPLACE(@BakFileName, '_[SQL2008].bak', '')

-- Restore SQL 2008 backed databases to SQL server 2012

set @backupQuery = 'RESTORE DATABASE ' +@dbName

+ ' FROM DISK = ''' + @backupPath + @BakFileName +''' '

+'WITH MOVE ''' + @dbName +''' TO ''' + @SQL2012RestoreFolder + @dbName + '.mdf'','

+ 'MOVE ''' + @dbName +'_Log'' TO ''' + @SQL2012RestoreFolder + @dbName +'.ldf'''

-- Print SQL statement

print @backupQuery

-- Execute backup script

EXEC (@backupQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @BakFileName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

P.S This posting is provided "AS IS" with no warranties, and confers no rights.

Installing SQL Server 2008 plus Service Pack 3 using slipstream on Windows Server 2012 RC in un-attended mode.

Ahmed Ibrahim - MSFT — Fri, 20 Jul 2012 05:51:00 GMT

1-Enable .NET3.5 from the command line using the following command

Dism.exe /online /enable-feature /featurename:NetFX3 /All

2-Download SQL server 2008 service pack 3 from

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

3-Double click on package exe to install SQL 2008 latest SQL support package that supports slipstream installation.

4-Extract SQLServer2008SP3-KB2546951-x64-ENU by running the below command from command line

SQLServer2008SP3-KB2546951-x64-ENU /x:"c:\SQLSERVER2008SP3"

5-Command line to install SQL server 2008 + Service pack 3

setup.exe /Action=Install /q /InstanceName=<InstanceName>

/FEATURES=SQLEngine,FullText,AS,RS,IS,Tools /AGTSVCACCOUNT="<DomainOrMachine\User>" /AGTSVCPASSWORD="<DomainOrMachineUserPWD>"

/ASSVCACCOUNT="<DomainOrMachine\User>" /ASSVCPASSWORD="<DomainOrMachineUserPWD>" /RSSVCACCOUNT="<DomainOrMachine\User>" /RSSVCPASSWORD="<DomainOrMachineUserPWD>"

/SQLSVCACCOUNT="<DomainOrMachine\User>" /SQLSVCPASSWORD="<DomainOrMachineUserPWD>" /SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /ASSYSADMINACCOUNTS="BUILTIN\Administrators" /pcusource="c:\SQLSERVER2008SP3"

/pid=<PID key for edition>

Same installation can run in UI mode be removing the /q switch

Installing SQL Server 2012 with Cumulative Update 1 (CU1) using Product update feature

Ahmed Ibrahim - MSFT — Thu, 05 Jul 2012 06:52:00 GMT

1-let us assume that we have CU1 package located in “C:\SQL2012Updates”,

SQL server 2012 is installed from E: drive.

2-The command to install SQL server and CU1 will be

setup.exe /Action=Install /UpdateSource=”C:\SQL2012Updates”.

3-Product Updates screen will display the KB number of the CU package to be installed.

4-Ready to install screen will highlight the version of cumulative update to be installed.

For more details about SQL server 2012 product update see:

http://msdn.microsoft.com/en-us/library/hh231670.aspx

SQL Server set compatibility level for all server databases

Ahmed Ibrahim - MSFT — Tue, 12 Jun 2012 16:31:00 GMT

use master;

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Selecting user database names.

select name as DatabaseName

from sys.sysdatabases

where ([dbid] > 4) and ([name] not like '$')

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @compatQuery varchar(500);

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

-- set database compatibility level

set @compatQuery = 'ALTER DATABASE ' + @dbName + ' SET COMPATIBILITY_LEVEL = 100;'

-- Print SQL statement

print @compatQuery

-- Execute compatability script

EXEC (@compatQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

-- Compatibility level can be 80,90,100 on SQL server 2008/SQL server 2008 R2

-- On SQL Server 2012 the allowed values are 90,100,110

Installing SQL Server 2012(Enterprise & Express), SQL Server 2008 R2 (Enterprise & Express) on Windows 8 Release Preview using command line and un-attended installation.

Ahmed Ibrahim - MSFT — Fri, 01 Jun 2012 13:07:00 GMT

Installing SQL Server 2012(Enterprise\Express), SQL Server 2008 R2 (Enterprise\Express) on Windows 8 Release Preview using command line and un-attended installation.

1-Downloaded Windows 8 Release Preview from http://windows.microsoft.com/en-US/windows-8/iso.

2-All installations were run using command line prompt in elevated mode (run as Administrator)

1. SQL SERVER 2012 Enterprise:

setup.exe /Action=Install /q /IAcceptSQLServerLicenseTerms /InstanceName=<InstanceName>/ROLE=AllFeatures_WithDefaults

/SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /ASSYSADMINACCOUNTS="BUILTIN\Administrators"

/pid=<PID key for new edition>.

2. SQL SERVER 2012 EXPRESS:

<pacakge.exe> /Action=Install /q /IAcceptSQLServerLicenseTerms /InstanceName=<InstanceName> /ROLE=AllFeatures_WithDefaults /SQLSYSADMINACCOUNTS="BUILTIN\Administrators"

3. SQL SERVER 2008 R2 Enterprise

setup.exe /Action=Install /q /IAcceptSQLServerLicenseTerms /InstanceName=<InstanceName> /ROLE=AllFeatures_WithDefaults

/AGTSVCACCOUNT="<DomainOrMachine\User>" /AGTSVCPASSWORD="<DomainOrMachineUserPWD>"

/ASSVCACCOUNT="<DomainOrMachine\User>" /ASSVCPASSWORD="<DomainOrMachineUserPWD>"

/SQLSVCACCOUNT="<DomainOrMachine\User>" /SQLSVCPASSWORD="<DomainOrMachineUserPWD>"

/SQLSYSADMINACCOUNTS="BUILTIN\Administrators" /ASSYSADMINACCOUNTS="BUILTIN\Administrators"

/pid=<PID key for new edition>

During installation of SQL server 2008 R2 the below dialog will pop-up

So in order to have successful installation click on the “Download and install …” option

4-SQL Server 2008 R2 Express

<pacakge.exe> /Action=Install /q /IAcceptSQLServerLicenseTerms /InstanceName=<InstanceName> /ROLE=AllFeatures_WithDefaults /SQLSYSADMINACCOUNTS="BUILTIN\Administrators"

The highlighted installation parameters should be specified before setup starts.

T-SQL script to backup all user databases with time stamped backup file.

Ahmed Ibrahim - MSFT — Tue, 29 May 2012 22:57:00 GMT

use master;

DECLARE UserDatabases_CTE_Cursor Cursor

FOR

-- Selecting user database names.

select name as DatabaseName

from sys.sysdatabases

where ([dbid] > 4) and ([name] not like '$')

OPEN UserDatabases_CTE_Cursor

DECLARE @dbName varchar(100);

DECLARE @backupPath varchar(100);

DECLARE @backupQuery varchar(500);

-- make sure that the below path exists

set @backupPath = 'c:\SQLBackupFolder\'

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

While (@@FETCH_STATUS <> -1)

BEGIN

-- Backup SQL statement

set @backupQuery = 'backup database ' + @dbName + ' to disk = ''' + @backupPath + @dbName + '_[' + REPLACE( convert(varchar, getdate(), 109), ':', '-') + '].bak'''

-- Print SQL statement

print @backupQuery

-- Execute backup script

EXEC (@backupQuery)

-- Get next database

Fetch NEXT FROM UserDatabases_CTE_Cursor INTO @dbName

END

CLOSE UserDatabases_CTE_Cursor

DEALLOCATE UserDatabases_CTE_Cursor

The above script backs up all user databases, for each database the backup file is generated by appending database name to timestamp

P.S.

If you are interested in restore script let me know :)

Patching SQL Server 2008 R2 using command line and un attended mode.

Ahmed Ibrahim - MSFT — Tue, 08 May 2012 22:40:00 GMT

The following command can be used to patch SQL server 2008 R2 instance in silent mode using command line unattended patching.

<Servicepack.exe> /Action=Patch /InstanceName=MSSQLSERVER /Q  /IAcceptSQLServerLicenseTerms

Upgrading SQL Server 2008 to SQL Server 2008 R2 using command line in unattended mode

Ahmed Ibrahim - MSFT — Tue, 08 May 2012 05:48:00 GMT

The below command can be used to upgrade SQL server 2008 to SQL server 2008 R2 silently, please enter the correct instance name.

setup.exe /Action=Upgrade /InstanceName=MSSQLSERVER /q /IAcceptSQLServerLicenseTerms

Installing SQL Server 2012 in unattended mode using command line and configuration file

Ahmed Ibrahim - MSFT — Tue, 01 May 2012 03:09:00 GMT

1-Create cmd file with below highlighted text and save it as c:\SQLCMDInstaller\InstallSQL.CMD

<SQL media>l\setup.exe /Configurationfile="ConfigurationFile.ini" /IAcceptSQLServerLicenseTerms

replace the <SQL media> with the path where you have your SQL media.

2-Create ConfigurationFile.ini file below with the highlighted text below, and saved it in c:\SQLCMDInstaller\

Configure the parameters highlighted in green

those are

Features to be installed.
AS SYS Admin account.
SQL SYS Admin account.
Instance name.

;SQL SERVER 2012 Configuration File
[OPTIONS]

; Specifies a Setup work flow, like INSTALL, UNINSTALL, or UPGRADE. This is a required parameter. 

ACTION="Install"

; Detailed help for command line argument ROLE has not been defined yet. 

ROLE="AllFeatures_WithDefaults"

; Detailed help for command line argument ENU has not been defined yet. 

ENU="True"

; Setup will not display any user interface. 

QUIET="True"

; Specify whether SQL Server Setup should discover and include product updates. The valid values are True and 
False or 1 and 0. By default SQL Server Setup will include updates that are found. 

UpdateEnabled="True"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, 
MDS, and Tools. The SQL feature will install the Database Engine, Replication, Full-Text, and Data Quality 
Services (DQS) server. The Tools feature will install Management Tools, Books online components, SQL Server 
Data Tools, and other shared components.

FEATURES=SQLENGINE,REPLICATION,FULLTEXT,DQ,AS,RS,DQC,BIDS,CONN,IS,BC,SDK,BOL,SSMS,ADV_SSMS,SNAC_SDK,MDS


; Specifies the list of administrator accounts that need to be provisioned. 

ASSYSADMINACCOUNTS="<ASSYSADMINACCOUNT>"

; Windows account(s) to provision as SQL Server system administrators. 

SQLSYSADMINACCOUNTS="<SYSADMINACCOUNT>"

; Specify that SQL Server feature usage data can be collected and sent to Microsoft. Specify 1 or True to 
enable and 0 or False to disable this feature. 

SQMREPORTING="False"

; Specify if errors can be reported to Microsoft to improve future SQL Server releases. Specify 1 or True to 
enable and 0 or False to disable this feature. 

ERRORREPORTING="FALSE"

; Specify a default or named instance. MSSQLSERVER is the default instance for non-Express editions and 
SQLExpress for Express editions. This parameter is required when installing the SQL Server Database Engine 
(SQL), Analysis Services (AS), or Reporting Services (RS). 

INSTANCENAME="MSSQLSERVER"

; RSInputSettings_RSInstallMode_Description 

RSINSTALLMODE="FilesOnlyMode"

; Don't provision current user as a Database Engine system administrator.

ADDCURRENTUSERASSQLADMIN="False"

3-Run cmd prompt as Administrator, change the prompt to  c:\SQLCMDInstaller\

run  InstallSQL.CMD

P.S.

this will install SQL as evaluation sku, to specify required sku, PID setting needs to be specified in the configuration file.

Workaround for SQL Server 2012 setup deployment requiring access to Visual Studio media

Ahmed Ibrahim - MSFT — Sat, 28 Apr 2012 06:20:00 GMT

In some situation during SQL server setup access to VS media is required, if the media is not there then SQL setup may be blocked, here is workaround for this situation

1- Go to control panel programs and features select VS ultimate and then click Uninstall/Change.

2-Select Add or Remove Features then click download since media source is missing.

3-Select all VS ultimate features

4-Start download and install all features

5-Install Visual Studio 2010 SP1

6-Install SQL server 2012 and happily have the installation succeed