Install SQL Server 2008R2 With ConfigurationFile.(Configuration.ini)

Install SQL Server 2008R2 With ConfigurationFile.(Configuration.ini)

 Step 1:-

Run Sql Server 2008 R2 Set Up .

Step 2:-

Run the Sql Server Setup untill Ready To Install option not shown and copy the path of Configuration File(Configuration.INI) and click on the cancel installation.

Step :3

 Edit the configuration file as follows:

1.       Set QUIET to “True”. This specifies that Setup will run in a quiet mode without any user interface

i. QUIET="True"

2.       Set SQLSYSADMINACCOUNTS to “BUILTINADMINISTRATORS”. This will ensure that administrators on the machine are added as members of the sysadmin role. You can set its value based on your needs (Ex: SQLSYSADMINACCOUNTS=”domainYourUser”), but this is the more generic approach. I have added My 

user instead of BUILTINADMINISTRATORS, to secure SQL server from unwanted logins.

i. SQLSYSADMINACCOUNTS="BUILTINADMINISTRATORS" or local system name

3.       Add PID and set its value to your product license key. If your setup.exe already comes preloaded with the key, there is no need to add this option to the configuration file.

4.       Add IACCEPTSQLSERVERLICENSETERMS and set its value to “True”. This is to require to acknowledge acceptance of the license terms at time of unattended installations.

i. IACCEPTSQLSERVERLICENSETERMS="True

5.       Remove the ADDCURRENTUSERASSQLADMIN parameter. The reason is that this parameter can’t be used when SQLSYSADMINACCOUNTS is specified, and it only applies to Express installations.

6.       Remove the UIMODE parameter as it can’t be used with the QUITE parameter.

7.       Remove INSTALLSHAREDDIR, INSTALLSHAREDWOWDIR, INSTANCEDIR parameters if you want to install on the default installation directories or mention appropriate directories for installation.

8.       You can add or remove the feature you want to install, Select FEATURES=SQLENGINE,SSMS,ADV_SSMS in the configuration file. You can change that based on your needs.

9.       The full list of available feature parameters and their descriptions : http://msdn.microsoft.com/en-us/library/ms144259.aspx#Feature

10.   Now, Your configuration file is ready, you need to create a batch file that will run the silent unattended setup. Create a new file ”SQLServer2012_SilentInstall” with extension = “.bat”.

We have added, Date time to get the time taken by complete installation. Edit below script with your setup & configuration file location.

@ECHO offecho Installing SQL Server 2008 R2date/ttime /t “D:SQLFULL_x86_ENUsetup.exe” /ConfigurationFile=”D:ConfigurationFile.ini” date/t time /t save this into .BAT file and run

MY EDIT Configration.ini file For Sql Server 2008R2

==================================================================

;SQLSERVER2008 Configuration File

[SQLSERVER2008]

; Specify the Instance ID for the SQL Server features you have specified. SQL Server directory structure, registry structure, and service names will reflect the instance ID of the SQL Server instance. 

INSTANCEID="SQL2008Rcmd"

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

ACTION="Install"

; Specifies features to install, uninstall, or upgrade. The list of top-level features include SQL, AS, RS, IS, and Tools. The SQL feature will install the database engine, replication, and full-text. The Tools feature will install Management Tools, Books online, Business Intelligence Development Studio, and other shared components. 

FEATURES=SQLENGINE,REPLICATION,FULLTEXT

; Displays the command line parameters usage 

HELP="False"

; Specifies that the detailed Setup log should be piped to the console. 

INDICATEPROGRESS="False"

; Setup will not display any user interface. 

QUIET="True"

SAPWD=”access”

; Setup will display progress only without any user interaction. 

QUIETSIMPLE="False"

; Specifies that Setup should install into WOW64. This command line argument is not supported on an IA64 or a 32-bit system. 

X86="False"

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

ENU="True"

; 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 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 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="SQL2008Rcmd"

; Agent account name 

AGTSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

; Auto-start service after installation.  

AGTSVCSTARTUPTYPE="Automatic"

; Startup type for Integration Services. 

ISSVCSTARTUPTYPE="Automatic"

; Account for Integration Services: Domain\User or system account. 

ISSVCACCOUNT="NT AUTHORITY\NetworkService"

; Controls the service startup type setting after the service has been created. 

ASSVCSTARTUPTYPE="Automatic"

; The collation to be used by Analysis Services. 

ASCOLLATION="Latin1_General_CI_AS"

; The location for the Analysis Services data files. 

ASDATADIR="Data"

; The location for the Analysis Services log files. 

ASLOGDIR="Log"

; The location for the Analysis Services backup files. 

ASBACKUPDIR="Backup"

; The location for the Analysis Services temporary files. 

ASTEMPDIR="Temp"

; The location for the Analysis Services configuration files. 

ASCONFIGDIR="Config"

; Specifies whether or not the MSOLAP provider is allowed to run in process. 

ASPROVIDERMSOLAP="1"

; A port number used to connect to the SharePoint Central Administration web application. 

FARMADMINPORT="0"

; Startup type for the SQL Server service. 

SQLSVCSTARTUPTYPE="Automatic"

; Level to enable FILESTREAM feature at (0, 1, 2 or 3). 

FILESTREAMLEVEL="0"

; Set to "1" to enable RANU for SQL Server Express. 

ENABLERANU="False"

; Specifies a Windows collation or an SQL collation to use for the Database Engine. 

SQLCOLLATION="SQL_Latin1_General_CP1_CI_AS"

; Account for SQL Server service: Domain\User or system account. 

SQLSVCACCOUNT="NT AUTHORITY\NETWORK SERVICE"

IACCEPTSQLSERVERLICENSETERMS="True"

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

SQLSYSADMINACCOUNTS="GHOST-PC\GHOST"

; The default is Windows Authentication. Use "SQL" for Mixed Mode Authentication. 

SECURITYMODE="SQL"

; Specify 0 to disable or 1 to enable the TCP/IP protocol. 

TCPENABLED="1"

; Specify 0 to disable or 1 to enable the Named Pipes protocol. 

NPENABLED="0"

; Startup type for Browser Service. 

BROWSERSVCSTARTUPTYPE="Automatic"

; Specifies how the startup mode of the report server NT service.  When 

; Manual - Service startup is manual mode (default).

; Automatic - Service startup is automatic mode.

; Disabled - Service is disabled 

RSSVCSTARTUPTYPE="Automatic"

; Specifies which mode report server is installed in.  

; Default value: “FilesOnly”  

RSINSTALLMODE="FilesOnlyMode"

; Add description of input argument FTSVCACCOUNT 

FTSVCACCOUNT="NT AUTHORITY\LOCAL SERVICE"

========================================================================

When was my database last used ??

Use <DatabaseName>
GO
SELECT DB_NAME() as DatabaseName,
s1.sql_handle,
(SELECT TOP 1 SUBSTRING(s2.text,statement_start_offset / 2+1 ,
( (CASE WHEN statement_end_offset = -1
THEN (LEN(CONVERT(nvarchar(max),s2.text)) * 2)
ELSE statement_end_offset END) - statement_start_offset) / 2+1)) AS sql_statement,
execution_count,
plan_generation_num,
last_execution_time,
total_worker_time,
last_worker_time,
min_worker_time,
max_worker_time,
total_physical_reads,
last_physical_reads,
min_physical_reads,
max_physical_reads,
total_logical_writes,
last_logical_writes,
min_logical_writes,
max_logical_writes
FROM sys.dm_exec_query_stats AS s1
CROSS APPLY sys.dm_exec_sql_text(sql_handle) AS s2
WHERE s2.objectid is null
ORDER BY s1.sql_handle, s1.statement_start_offset, s1.statement_end_offset

When was my table last scanned or updated?

When was my table last scanned or updated?
Hi i am posting a blog related to how to check when your table last updated or scanned. This issue occurred when more than one person work on same project and some some mistakenly changed by anyone.

USE DatabaseName  ----your database name--
GO
SELECT
t.name,
i.last_user_lookup,
i.last_user_scan,
i.last_user_seek,
i.last_user_update
FROM sys.dm_db_index_usage_stats i
INNER JOIN sys.tables t
ON i.object_id = t.object_id
WHERE
database_id = db_id( 'DatabaseName' )  ----your database name--

How could i insert the data into my secondary files(.ndf)?

Step 1:- Create a file group with secondary file (.ndf file)

( NAME = N'FileBackupsTest',
FILENAME = N'E:\FileBackupsTest.mdf' ,
SIZE = 5120KB , FILEGROWTH = 5124KB ),
FILEGROUP [SecondaryFile]
( NAME = N'FileBackupsTestUserData1',
FILENAME = N'e:\FileBackupsTestUserData1.ndf' ,
SIZE = 512KB , FILEGROWTH = 512KB )
LOG ON
( NAME = N'FileBackupsTest_log',
FILENAME = N'e:\SQLData\FileBackupsTest_log.ldf' ,
SIZE = 1024KB , FILEGROWTH = 512KB )
GO
USE [FileBackupsTest]
GO

Step 2: create a table with secondary file group name.By default a table created on Primary file (.mdf) file but if you want to enter data into secondory file(.ndf) than mention seondory File group name.
CREATE TABLE Demo
(
name char(12),
city char(12),
roll int
)
ON [secondaryFile]



Step : 3 insert some data into this table and check the size of your secondary file will grow in size. check the size with sp_helpdb 'FileBackupsTest1'
GO and To Find which object resides in which FileGroup in a database, run the below queries.


T-SQL - Find which Object resides in which FileGroup
To Find which object resides in which FileGroup in a database, run the below queries.

/**************** For SQL 2000 ****************/

Select Distinct OBJECT_NAME(SI.id) AS ObjectName
        ,SO.type AS ObjectType
        ,FG.GroupName AS FileGroupName
from sysindexes SI , sysfilegroups FG ,sysobjects SO
where SI.groupid = FG.groupid and
      SI.id = SO.id
Order by ObjectName

/**************** For SQL 2005 and SQL 2008 ****************/

Select Distinct OBJECT_NAME(SI.object_id) AS ObjectName
        ,OBJECTPROPERTYEX(SI.object_id,'BaseType') AS ObjectType
        ,FG.Name AS FileGroupName
from sys.indexes SI , sys.filegroups FG
where SI.data_space_id = FG.data_space_id
Order by ObjectName

Backup on network Drive with Map Drive

STEP 1:-
-- First Execute this command ---
Exec master.dbo.sp_configure 'show advanced option',1
reconfigure
Exec Master.dbo.SP_configure 'XP_cmdshell',1
reconfigure
--OutPut Like This
--Configuration option 'show advanced options' changed from 1 to 1. Run the RECONFIGURE statement to install.
--Configuration option 'xp_cmdshell' changed from 1 to 1. Run the RECONFIGURE statement to install.

-----------------------------BIND DRIVE WITH SYSTEM------------------------------------------------------------------------
STEP 2:-
Exec XP_CMDSHELL 'net use z: \\satan\BACKUP HPDC2011@ /user:satan\ashish'
----outPut Should be like this
--The command completed successfully.
--NULL
--NULL
----------------------VARIFY DRIVE INFO------------------------
STEP 3:-

Exec XP_cmdshell 'dir z:'

--OutPut Like This
-- Volume in drive Z has no label.
-- Volume Serial Number is B481-00AF
--NULL
-- Directory of Z:\
--NULL
--03/29/2013  04:43 AM    <DIR>          .
--03/29/2013  04:43 AM    <DIR>          ..
--03/29/2013  04:43 AM         3,165,696 m.bak
--               1 File(s)      3,165,696 bytes
--               2 Dir(s)  14,739,963,904 bytes free
--NULL

--Exec XP_CMDSHELL 'net use y: \\satan\myfile HPDC2011@ /user:satan\ashish'
---------------------Run BackUp Command--------------
STEP 4:-

Backup database master to disk='\\satan\BACKUP\master.bak'

---Processed 376 pages for database 'master', file 'master' on file 2.
--Processed 3 pages for database 'master', file 'mastlog' on file 2.
--BACKUP DATABASE successfully processed 379 pages in 2.448 seconds (1.206 MB/sec).

Full Back Up With Understanding of Parameters Like,init,Format,Name,Stat

A simple T-SQL script for full backups

Take a look at Listing 3-8, which shows a script that can be used to take a full backup of the newly populated DatabaseForFullBackups database.

USE master GO BACKUP DATABASE [DatabaseForFullBackups] TO DISK = N'C:\SQLBackups\Chapter3\DatabaseForFullBackups_Full_Native_2.bak' WITH FORMAT, INIT, NAME = N'DatabaseForFullBackups-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10 GO

Listing 3-8: Native full backup T-SQL script.

This script may look like it has some extra parameters, compared to the native GUI backup that we did earlier, but this is the scripted output of that same backup, with only the output file name modified, and a few other very minor tweaks. So what do each of these parameters mean?

The meaning of the first line should be fairly obvious; it is instructing SQL Server to perform a full backup of the DatabaseForFullBackups database. This leads into the second line where we have chosen to back up to disk, and given a complete file path to the resulting backup file. The remainder of the parameters are new, so let's go through each one.108 Chapter 3: Full Database Backups

                        FORMAT This option tells SQL Server whether or not to overwrite the media header infor­mation. The FORMAT option will erase any information in a backup set that already exists when the backup is initialized (NOFORMAT will preserve it).

                        INIT By default, when scripting a backup generated by the Backup wizard, this parameter will be set to NOINIT, which lets SQL Server know not to initialize a media set when taking the backup and instead append any new backup data to the existing backup set. However, since we adopt the rule of one backup per backup set, it's useful to use INIT instead, to make sure that, if a command gets run twice, we overwrite the existing set and still end up with only one backup in the set.

                        NAME The NAME parameter is simply used to identify the backup set. If it is not supplied, the set will not record a name.

                        SKIP Using the SKIP parameter will cause SQL Server to skip the expiration check that it normally does on the backup set. It doesn't care if any backups existing in the backup set have been marked for availability to be overwritten.

                        NOREWIND This parameter will cause SQL Server to keep a tape device open and ready for use when the backup operation is complete. This is a performance boost to users of tape drives since the tape is already at the next writing point instead of having to search for the correct position. This is obviously a tape-only option.

                NOUNLOAD When backing up to a tape drive, this parameter instructs SQL Server not to unload the tape from the drive when the backup operation is completed.

                        STATS This option may prove useful to you when performing query-based backups. The STATS parameter defines the time intervals on which SQL Server should update the "backup progress" messages. For example, using stats=10 will cause SQL Server to send a status message to the query output for each 10 percent of the backup completion.

As noted, if we wished to overwrite an existing backup set, we'd want to specify the INIT parameter but, beyond that, none of these secondary parameters, including the backup set NAME descriptor, are required. As such, we can actually use a much simplified BACKUP command, as shown in Listing 3-9.

BACKUP DATABASE [DatabaseForFullBackups] TO DISK = N'C:\SQLBackups\Chapter3\DatabaseForFullBackups_Full_Native_2.bak' GO

Listing 3-9: Slimmed-down native T-SQL backup code.

Go ahead and start Management Studio and connect to your test server. Once you have connected, open a new query

Get last backup details of all databases in a server

This stored procedure give you the information about latest backups happened on all databases in a server.
This SP works for SQL server 2005 and up.

Create Proc sp_BackupDetails AS DECLARE @BackupDetails table ([Server Name] nvarchar(500), [Database Name] nvarchar(500), [Last Full Backup] nvarchar(500), [Last Differential Backup] nvarchar(500), [Last Log Backup] nvarchar(500), [Last File or filegroup Backup] nvarchar(500), [Last Differential file Backup] nvarchar(500), [Last Partial Backup] nvarchar(500), [Last Differential Partial Backup] nvarchar(500) ) DECLARE @DBName nvarchar(500) Declare DBName Cursor for Select name from sys.databases Open DBName Fetch Next from DBName into @DBName While @@fetch_status = 0 BEGIN Insert into @BackupDetails select @@ServerName as [Server Name]        ,SDB.name AS [Database Name]        ,(select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='D') AS [Last Full Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='I') AS [Last Differential Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='L') AS [Last Log Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='F') AS [Last File or filegroup Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='G') AS [Last Differential file Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='P') AS [Last Partial Backup]        ,(Select COALESCE(Convert(nvarchar(20), MAX(backup_finish_date), 100),'NA') frommsdb..backupset where database_name=@DBName and type='Q') AS [Last Differential Partial Backup] from sys.databases SDB where SDB.name =@DBName Fetch Next from DBName into @DBName END Close DBName DEALLOCATE DBName Select * from @BackupDetails GO

Usage:

Exec sp_BackupDetails

go

Out Put After Execute 

Server Name Database Name Last Full Backup Last Differential Backup Last Log Backup Last File or filegroup Backup Last Differential file Backup Last Partial Backup Last Differential Partial Backup

GHOST-PC\INS10 master Mar 19 2013 12:00PM NA NA NA NA NA NA

GHOST-PC\INS10 tempdb NA NA NA NA NA NA NA

GHOST-PC\INS10 model NA NA NA NA NA NA NA

GHOST-PC\INS10 msdb NA NA NA NA NA NA NA

GHOST-PC\INS10 demo Mar 19 2013 12:13PM NA NA NA NA NA NA

GHOST-PC\INS10 demo1 Mar 19 2013 12:14PM NA NA NA NA NA NA