| |
 System Account
| 0 | 12/3/2021 4:11 PM | There is a question from one of our SQL Folks How to get the SQL Server expriation date? SELECT
create_date AS 'SQL Server Install Date',
DATEADD(DD, 180, create_date) AS 'SQL Server Expiry Date'
FROM sys.server_principals
WHERE name = 'NT AUTHORITY\SYSTEM' | | 12/3/2021 4:11 PM | | No | General | 0 | | | 8/13/2019 8:08 AM | | | | | | | 0 | 11/18/2019 2:13 PM | If you want to inventory your SQL environment the following PS will help you out :) [CmdletBinding()]
$Filename='SQLInventory'
$FilePath = 'E:\Inventory\serverlist.csv'
$DirectoryToSaveTo = 'E:\Inventory\'
# before we do anything else, are we likely to be able to save the file?
# if the directory doesn't exist, then create it
if (!(Test-Path -path "$DirectoryToSaveTo")) #create it if not existing
{
New-Item "$DirectoryToSaveTo" -type directory | out-null
}
#Create a new Excel object using COM
$Excel = New-Object -ComObject Excel.Application
$Excel.visible = $True
$Excel = $Excel.Workbooks.Add()
$Excel.Worksheets.Add()
$Sheet1 = $Excel.Worksheets.Item(1)
$Sheet2 = $Excel.Worksheets.Item(2)
#Counter variable for rows
$Sheet1Row = 1
$xlOpenXMLWorkbook=[int]51
#Read thru the contents of the SQL_Servers.txt file
$Sheet1.Cells.Item($Sheet1Row,1) ="InstanceName"
$Sheet1.Cells.Item($Sheet1Row,2) ="State"
$Sheet1.Cells.Item($Sheet1Row,3) ="Support_Team"
$Sheet1.Cells.Item($Sheet1Row,4) ="ComputerName"
$Sheet1.Cells.Item($Sheet1Row,5) ="NetName"
$Sheet1.Cells.Item($Sheet1Row,6) ="OS"
$Sheet1.Cells.Item($Sheet1Row,7) ="OSVersion"
$Sheet1.Cells.Item($Sheet1Row,8) ="Platform"
$Sheet1.Cells.Item($Sheet1Row,9) ="Product"
$Sheet1.Cells.Item($Sheet1Row,10) ="edition"
$Sheet1.Cells.Item($Sheet1Row,11) ="Version"
$Sheet1.Cells.Item($Sheet1Row,12) ="VersionString"
$Sheet1.Cells.Item($Sheet1Row,13) ="ProductLevel"
$Sheet1.Cells.Item($Sheet1Row,14) ="DatabaseCount"
$Sheet1.Cells.Item($Sheet1Row,15) ="HasNullSaPassword"
$Sheet1.Cells.Item($Sheet1Row,16) ="IsCaseSensitive"
$Sheet1.Cells.Item($Sheet1Row,17) ="IsFullTextInstalled"
$Sheet1.Cells.Item($Sheet1Row,18) ="Language"
$Sheet1.Cells.Item($Sheet1Row,19) ="LoginMode"
$Sheet1.Cells.Item($Sheet1Row,20) ="Processors"
$Sheet1.Cells.Item($Sheet1Row,21) ="PhysicalMemory"
$Sheet1.Cells.Item($Sheet1Row,22) ="MaxMemory"
$Sheet1.Cells.Item($Sheet1Row,23) ="MinMemory"
$Sheet1.Cells.Item($Sheet1Row,24) ="IsSingleUser"
$Sheet1.Cells.Item($Sheet1Row,25) ="IsClustered"
$Sheet1.Cells.Item($Sheet1Row,26) ="Collation"
$Sheet1.Cells.Item($Sheet1Row,27) ="MasterDBLogPath"
$Sheet1.Cells.Item($Sheet1Row,28) ="MasterDBPath"
$Sheet1.Cells.Item($Sheet1Row,29) ="ErrorLogPath"
$Sheet1.Cells.Item($Sheet1Row,30) ="BackupDirectory"
$Sheet1.Cells.Item($Sheet1Row,31) ="DefaultLog"
$Sheet1.Cells.Item($Sheet1Row,32) ="ResourceLastUpdatetime"
$Sheet1.Cells.Item($Sheet1Row,33) ="AuditLevel"
$Sheet1.Cells.Item($Sheet1Row,34) ="DefaultFile"
$Sheet1.Cells.Item($Sheet1Row,35) ="xp_cmdshell"
$Sheet1.Cells.Item($Sheet1Row,36) ="Domain"
$Sheet1.Cells.Item($Sheet1Row,37) ="IPAddress"
$Sheet1.Name = "Sql Servers"
for ($col = 1; $col –le 37; $col++)
{
$Sheet1.Cells.Item($Sheet1Row,$col).Font.Bold = $True
$Sheet1.Cells.Item($Sheet1Row,$col).Interior.ColorIndex = 48
$Sheet1.Cells.Item($Sheet1Row,$col).Font.ColorIndex = 34
}
$Sheet1Row++
#Sheet2
$Sheet2Row = 1
$Sheet2.Name = "DataBases"
$Sheet2.Cells.Item($Sheet2Row,1) ="Support_Team"
$Sheet2.Cells.Item($Sheet2Row,2) ="ComputerName"
$Sheet2.Cells.Item($Sheet2Row,3) ="DataBaseName"
$Sheet2.Cells.Item($Sheet2Row,4) ="DataBaseSize"
$Sheet2.Cells.Item($Sheet2Row,5) ="PrimaryDataFileLocation"
$Sheet2.Cells.Item($Sheet2Row,6) ="LogFileLocation"
for ($col = 1; $col –le 6; $col++)
{
$Sheet2.Cells.Item($Sheet2Row,$col).Font.Bold = $True
$Sheet2.Cells.Item($Sheet2Row,$col).Interior.ColorIndex = 48
$Sheet2.Cells.Item($Sheet2Row,$col).Font.ColorIndex = 34
}
$SQLServerList = [object]$sqlServerList = Import-CSV $filepath
foreach ($instanceName in $sqlServerList|WHERE {$_.State -eq 'Active'} )
{
[System.Reflection.Assembly]::LoadWithPartialName('Microsoft.SqlServer.SMO') | out-null
$server1 = New-Object -Type Microsoft.SqlServer.Management.Smo.Server -ArgumentList $instanceName.Server
$s=$server1.Information.Properties |Select Name, Value
$st=$server1.Settings.Properties |Select Name, Value
$CP=$server1.Configuration.Properties |Select DisplayName, Description, RunValue, ConfigValue
$dbs=$server1.Databases.count
$InstanceNameServer = $instanceName.Server
$instanceNameState = $instanceName.State
$instanceNameSupportTeam = $instanceName.SupportTeam
$BuildNumber=$s | where {$_.name -eq "BuildNumber"}|select value
$edition=$s | where {$_.name -eq "edition"}|select value
$ErrorLogPath =$s | where {$_.name -eq "ErrorLogPath"}|select value
$HasNullSaPassword =$s | where {$_.name -eq "HasNullSaPassword"}|select value
$IsCaseSensitive =$s | where {$_.name -eq "IsCaseSensitive"}|select value
$Platform =$s | where {$_.name -eq "Platform"}|select value
$IsFullTextInstalled =$s | where {$_.name -eq "IsFullTextInstalled"}|select value
$Language =$s | where {$_.name -eq "Language"}|select value
$MasterDBLogPath =$s | where {$_.name -eq "MasterDBLogPath"}|select value
$MasterDBPath =$s | where {$_.name -eq "MasterDBPath"}|select value
$NetName =$s | where {$_.name -eq "NetName"}|select value
$OSVersion =$s | where {$_.name -eq "OSVersion"}|select value
$PhysicalMemory =$s | where {$_.name -eq "PhysicalMemory"}|select value
$Processors =$s | where {$_.name -eq "Processors"}|select value
$IsSingleUser =$s | where {$_.name -eq "IsSingleUser"}|select value
$Product =$s | where {$_.name -eq "Product"}|select value
$VersionString =$s | where {$_.name -eq "VersionString"}|select value
$Collation =$s | where {$_.name -eq "Collation"}|select value
$IsClustered =$s | where {$_.name -eq "IsClustered"}|select value
$ProductLevel =$s | where {$_.name -eq "ProductLevel"}|select value
$ComputerNamePhysicalNetBIOS =$s | where {$_.name -eq "ComputerNamePhysicalNetBIOS"}|select value
$ResourceLastUpdateDateTime =$s | where {$_.name -eq "ResourceLastUpdateDateTime"}|select value
$AuditLevel =$st | where {$_.name -eq "AuditLevel"}|select value
$BackupDirectory =$st | where {$_.name -eq "BackupDirectory"}|select value
$DefaultFile =$st | where {$_.name -eq "DefaultFile"}|select value
$DefaultLog =$st | where {$_.name -eq "DefaultLog"}|select value
$LoginMode =$st | where {$_.name -eq "LoginMode"}|select value
$min=$CP | where {$_.Displayname -like "*min server memory*"}|select configValue
$max=$CP | where {$_.Displayname -like "*max server memory*"}|select configValue
$xp_cmdshell=$CP | where {$_.Displayname -like "*xp_cmdshell*"}|select configValue
$FQDN=[System.DirectoryServices.ActiveDirectory.Domain]::GetCurrentDomain().Name
$IPAddress=(Get-WmiObject Win32_NetworkAdapterConfiguration -ComputerName $instanceName.Server)|Where IPAddress
if ($HasNullSaPassword.value -eq $NULL)
{
$HasNullSaPassword.value='No'
}
if($DefaultFile.value -eq '')
{
$DefaultFile.value='NA'
}
if ($VersionString.value -like '8*')
{
$SQLServer='SQL SERVER 2000'
}
elseif ($VersionString.value -like '9*')
{
$SQLServer='SQL SERVER 2005'
}
elseif ($VersionString.value -like '10.0*')
{
$SQLServer='SQL SERVER 2008'
}
elseif ($VersionString.value -like '10.5*')
{
$SQLServer='SQL SERVER 2008 R2'
}
elseif ($VersionString.value -like '11*')
{
$SQLServer='SQL SERVER 2012'
}
elseif ($VersionString.value -like '13*')
{
$SQLServer='SQL SERVER 2016'
}
else
{
$SQLServer='Invalid'
}
if ($OSVersion.value -like '5.0*')
{
$OSVer='Windows 2000'
}
elseif ($OSVersion.value -like '5.1*')
{
$OSVer='Windows XP'
}
elseif ($OSVersion.value -like '5.2*')
{
$OSVer='Windows Server 2003'
}
elseif ($OSVersion.value -like '6.0*')
{
$OSVer='Windows Server 2008'
}
elseif ($OSVersion.value -like '6.1*')
{
$OSVer='Windows Server 2008 R2'
}
elseif ($OSVersion.value -like '6.2*')
{
$OSVer='Windows Server 2012'
}
elseif ($OSVersion.value -like '6.3*')
{
$OSVer='Windows Server 2012 R2'
}
else
{
$OSVer='NA'
}
$Sheet1.Cells.Item($Sheet1Row,1) =$instanceName
$Sheet1.Cells.Item($Sheet1Row,2) =$instanceNameState
$Sheet1.Cells.Item($Sheet1Row,3) =$instanceNameSupportTeam
$Sheet1.Cells.Item($Sheet1Row,4) =$InstanceNameServer
$Sheet1.Cells.Item($Sheet1Row,5) =$NetName.value
$Sheet1.Cells.Item($Sheet1Row,6) =$OSVer
$Sheet1.Cells.Item($Sheet1Row,7) =$OSVersion.value
$Sheet1.Cells.Item($Sheet1Row,8) = $Platform.value
$Sheet1.Cells.Item($Sheet1Row,9) = $Product.value
$Sheet1.Cells.Item($Sheet1Row,10) = $edition.value
$Sheet1.Cells.Item($Sheet1Row,11) = $SQLServer
$Sheet1.Cells.Item($Sheet1Row,12) = $VersionString.value
$Sheet1.Cells.Item($Sheet1Row,13) = $ProductLevel.value
$Sheet1.Cells.Item($Sheet1Row,14) = $Dbs
$Sheet1.Cells.Item($Sheet1Row,15) = $HasNullSaPassword.value
$Sheet1.Cells.Item($Sheet1Row,16) = $IsCaseSensitive.value
$Sheet1.Cells.Item($Sheet1Row,17) = $IsFullTextInstalled.value
$Sheet1.Cells.Item($Sheet1Row,18) = $Language.value
$Sheet1.Cells.Item($Sheet1Row,19) = $LoginMode.value
$Sheet1.Cells.Item($Sheet1Row,20) = $Processors.value
$Sheet1.Cells.Item($Sheet1Row,21) = $PhysicalMemory.value
$Sheet1.Cells.Item($Sheet1Row,22) = $Max.Configvalue
$Sheet1.Cells.Item($Sheet1Row,23) = $Min.Configvalue
$Sheet1.Cells.Item($Sheet1Row,24) = $IsSingleUser.value
$Sheet1.Cells.Item($Sheet1Row,25) = $IsClustered.value
$Sheet1.Cells.Item($Sheet1Row,26) = $Collation.value
$Sheet1.Cells.Item($Sheet1Row,27) = $MasterDBLogPath.value
$Sheet1.Cells.Item($Sheet1Row,28) = $MasterDBPath.value
$Sheet1.Cells.Item($Sheet1Row,29) = $ErrorLogPath.value
$Sheet1.Cells.Item($Sheet1Row,30) = $BackupDirectory.value
$Sheet1.Cells.Item($Sheet1Row,31) = $DefaultLog.value
$Sheet1.Cells.Item($Sheet1Row,32) = $ResourceLastUpdateDateTime.value
$Sheet1.Cells.Item($Sheet1Row,33) = $AuditLevel.value
$Sheet1.Cells.Item($Sheet1Row,34) = $DefaultFile.value
$Sheet1.Cells.Item($Sheet1Row,35) = $xp_cmdshell.Configvalue
$Sheet1.Cells.Item($Sheet1Row,36) = $FQDN
$Sheet1.Cells.Item($Sheet1Row,37) = $IPAddress.IPAddress
$Sheet1Row ++
foreach ($db in $server1.databases)
{
IF ($Sheet2Row -eq 1) #wRITE HEADER ROW
{
$icol = 4
foreach ($Property in $db.properties| where {$_.Name -ne 'ActiveConnections' -and $_.Name -ne 'PolicyHealthState' -and $_.Name -ne 'IsManagementDataWarehouse' -and $_.Name -notlike '*Guid'})
{
$Sheet2.Cells.Item(1,$icol) = $Property.Name
$Sheet2.Cells.Item(1,$icol).Font.Bold = $True
$Sheet2.Cells.Item(1,$icol).Interior.ColorIndex = 48
$Sheet2.Cells.Item(1,$icol).Font.ColorIndex = 34
$icol++
}
}
If ($db.name -ne 'Master' -and $db.Name -ne 'Model' -and $db.Name -ne 'tempdb' -and $db.Name -ne 'Msdb' ) #Exclude system databases
{ $Sheet2Row++
$Sheet2.Cells.Item($Sheet2Row,1) = $instanceNameSupportTeam #"Support_Team"
$Sheet2.Cells.Item($Sheet2Row,2) = $InstanceNameServer #"ComputerName"
Write-host $InstanceNameServer +','+ $db.Parent
$Sheet2.Cells.Item($Sheet2Row,3) = $db.Name #"DataBaseName"
$icol = 4
foreach ($Property in $db.properties| where {$_.Name -ne 'ActiveConnections' -and $_.Name -ne 'PolicyHealthState' -and $_.Name -ne 'IsManagementDataWarehouse' -and $_.Name -notlike '*Guid'})
{
write-host $Property.Name
$Sheet2.Cells.Item($Sheet2Row,$icol) = $Property
$icol++
}
}
}
}
$filename = "$DirectoryToSaveTo$filename.xlsx"
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$Sheet1.UsedRange.EntireColumn.AutoFit()
$Sheet1.Name = "Sql Servers"
cls
$Excel.SaveAs($filename, $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$Excel.Saved = $True
$Excel.Close() If you encounter any issues to run this PS let me know. Cheers, Zahid | | 11/18/2019 2:13 PM | | No | General | 6.0417974537037 | 1 | | 8/18/2019 10:40 PM | | 1 | System Account | | | | 0 | 9/11/2019 6:55 AM | DECLARE @SERVERINFO_BASIC_TSQL VARCHAR(8000)
DECLARE @OSSTATS_SQL2000_TSQL VARCHAR(8000)
DECLARE @SERVERINFO_SQL2000_TSQL VARCHAR(8000)
DECLARE @SERVERINFO_SQL2005_TSQL VARCHAR(8000)
DECLARE @SERVERINFO_TSQL VARCHAR(8000)
SET @SERVERINFO_BASIC_TSQL = '
SELECT
CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''MachineName''))) AS [ServerName],
CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''InstanceName''))) AS [InstanceName],
CONVERT(VARCHAR(50),(SELECT
CASE (SELECT LEFT(CAST(SERVERPROPERTY(''ProductVersion'') AS VARCHAR), 4))
WHEN ''13.0'' THEN ''SQL Server 2016''
WHEN ''12.0'' THEN ''SQL Server 2014''
WHEN ''11.0'' THEN ''SQL Server 2012''
WHEN ''10.5'' THEN ''SQL Server 2008 R2''
WHEN ''10.0'' THEN ''SQL Server 2008''
WHEN ''9.00'' THEN ''SQL Server 2005''
WHEN ''8.00'' THEN ''SQL Server 2000''
ELSE ''Unknown Version''
END
)
) AS [Version],
CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''ProductLevel''))) AS [Build],
CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''ProductVersion''))) AS [BuildNumber],
CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''Edition''))) AS [Edition],
CONVERT(VARCHAR(50),(SELECT
CASE (SELECT SERVERPROPERTY(''IsIntegratedSecurityOnly''))
WHEN 1 THEN ''Windows''
WHEN 0 THEN ''Mixed Mode''
END
)
) AS [Authentication],
CONVERT(VARCHAR(50),(SELECT SERVERPROPERTY(''Collation''))) AS [Collation],
GETDATE() AS [Timestamp],
'
SET @OSSTATS_SQL2000_TSQL = '
IF OBJECT_ID(''tempdb..##OSstats'') IS NOT NULL
DROP TABLE ##OSstats
CREATE TABLE ##OSstats ([Index] VARCHAR(2000), [Name] VARCHAR(2000), [Internal_Value] VARCHAR(2000), [Character_Value] VARCHAR(2000))
INSERT INTO ##OSstats EXEC xp_msver'
SET @SERVERINFO_SQL2000_TSQL = '
CONVERT(BIGINT,(SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1544'')) AS [MemoryAllocatedMB],
CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1539'')) AS [MaxDOP],
CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1538'')) AS [CTFP],
(SELECT [Internal_Value] FROM ##OSstats WHERE [name] = ''ProcessorCount'') AS [Cores],
(SELECT [Internal_Value] FROM ##OSstats WHERE [name] = ''PhysicalMemory'') AS [TotalMemoryMB],
(SELECT [crdate] FROM [master].[dbo].[sysdatabases] WHERE [name] = ''tempdb'') AS [StartupTime]'
SET @SERVERINFO_SQL2005_TSQL = '
CONVERT(BIGINT,(SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1544'')) AS [MemoryAllocatedMB],
CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1539'')) AS [MaxDOP],
CONVERT(INT, (SELECT [value] FROM [master].[dbo].[sysconfigures] WHERE [config] = ''1538'')) AS [CTFP],
(SELECT [cpu_count] FROM [master].[sys].[dm_os_sys_info]) AS [Cores],
(SELECT [physical_memory_in_bytes]/1024/1024 FROM [master].[sys].[dm_os_sys_info]) AS [TotalMemoryMB],
(SELECT [create_date] FROM [master].[sys].[databases] WHERE [name] = ''tempdb'') AS [StartupTime]'
SET @SERVERINFO_TSQL = '
CONVERT(BIGINT,(SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1544'')) AS [MemoryAllocatedMB],
CONVERT(INT,( SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1539'')) AS [MaxDOP],
CONVERT(INT,( SELECT [value] FROM [master].[sys].[configurations] WHERE [configuration_id] = ''1538'')) AS [CTFP],
(SELECT [cpu_count] FROM [master].[sys].[dm_os_sys_info]) AS [Cores],
(SELECT [total_physical_memory_kb]/1024 FROM [master].[sys].[dm_os_sys_memory]) AS [TotalMemoryMB],
(SELECT [sqlserver_start_time] FROM [master].[sys].[dm_os_sys_info]) AS [StartupTime]'
IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1)='8'
BEGIN
EXEC (@OSSTATS_SQL2000_TSQL)
EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_SQL2000_TSQL)
END
ELSE IF LEFT(CAST(SERVERPROPERTY('ProductVersion') AS VARCHAR),1)='9'
BEGIN
EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_SQL2005_TSQL)
END
ELSE
BEGIN
EXEC (@SERVERINFO_BASIC_TSQL + @SERVERINFO_TSQL)
END | | 9/11/2019 6:55 AM | | No | General | 0 | | | 8/18/2019 10:40 PM | | | | | | | 0 | 8/24/2019 4:30 PM | It is really tedious and time consuming to set schedule for maintenance job using GUI. Here is the following script to set schedule for maintenance plan. SET NOCOUNT ON IF SERVERPROPERTY('EngineEdition') <> 4 BEGIN -- All the variables needed for creating the schedules and adding them to the jobs
DECLARE @SchOwner nvarchar(max)
DECLARE @SchName01 nvarchar(max), @SchName02 nvarchar(max), @SchName03 nvarchar(max), @SchName04 nvarchar(max), @SchName05 nvarchar(max), @SchName06 nvarchar(max), @SchName07 nvarchar(max), @SchName08 nvarchar(max) DECLARE @SchFreqType01 int, @SchFreqType02 int, @SchFreqType03 int, @SchFreqType04 int, @SchFreqType05 int, @SchFreqType06 int, @SchFreqType07 int, @SchFreqType08 int DECLARE @SchFreqInt01 int, @SchFreqInt02 int, @SchFreqInt03 int, @SchFreqInt04 int, @SchFreqInt05 int, @SchFreqInt06 int, @SchFreqInt07 int, @SchFreqInt08 int DECLARE @SchSubType01 int, @SchSubType02 int, @SchSubType03 int, @SchSubType04 int, @SchSubType05 int, @SchSubType06 int, @SchSubType07 int, @SchSubType08 int DECLARE @SchSubInt01 int, @SchSubInt02 int, @SchSubInt03 int, @SchSubInt04 int, @SchSubInt05 int, @SchSubInt06 int, @SchSubInt07 int, @SchSubInt08 int DECLARE @SchRelInt01 int, @SchRelInt02 int, @SchRelInt03 int, @SchRelInt04 int, @SchRelInt05 int, @SchRelInt06 int, @SchRelInt07 int, @SchRelInt08 int DECLARE @SchFreqRec01 int, @SchFreqRec02 int, @SchFreqRec03 int, @SchFreqRec04 int, @SchFreqRec05 int, @SchFreqRec06 int, @SchFreqRec07 int, @SchFreqRec08 int DECLARE @SchStart01 int, @SchStart02 int, @SchStart03 int, @SchStart04 int, @SchStart05 int, @SchStart06 int, @SchStart07 int, @SchStart08 int DECLARE @SchEnd01 int, @SchEnd02 int, @SchEnd03 int, @SchEnd04 int, @SchEnd05 int, @SchEnd06 int, @SchEnd07 int, @SchEnd08 int DECLARE @JobName01 nvarchar(max), @JobName02 nvarchar(max), @JobName03 nvarchar(max), @JobName04 nvarchar(max), @JobName05 nvarchar(max), @JobName06 nvarchar(max), @JobName07 nvarchar(max), @JobName08 nvarchar(max), @JobName09 nvarchar(max), @JobName10 nvarchar(max), @JobName11 nvarchar(max) -- Job Names SET @JobName01 = 'Maintenance - Backups - SYSTEM - FULL' SET @JobName02 = 'Maintenance - Backups - USER - FULL' SET @JobName03 = 'Maintenance - Backups - USER - DIFF' SET @JobName04 = 'Maintenance - Backups - USER - LOG' SET @JobName05 = 'Maintenance - IndexOptimize - USER' SET @JobName06 = 'Maintenance - DatabaseIntegrityCheck - USER' SET @JobName07 = 'Maintenance - DatabaseIntegrityCheck - SYSTEM' SET @JobName08 = 'Maintenance - CommandLog Cleanup' SET @JobName09 = 'Maintenance - Output File Cleanup' SET @JobName10 = 'Maintenance - sp_delete_backuphistory' SET @JobName11 = 'Maintenance - sp_purge_jobhistory' -- Set SA account as schedule owner SET @SchOwner = SUSER_SNAME(0x01) -- Schedule Names SET @SchName01 = 'Production System Full Backups - Daily 9:30PM' SET @SchName02 = 'Production User Full Backups - Daily 12AM' SET @SchName03 = 'Production User Diff Backups - MTWThFSa 12AM' SET @SchName04 = 'Production User TLog Backups - Hourly 2AM-10PM' SET @SchName05 = 'Production Index Maintenance - TThSa 10PM' SET @SchName06 = 'Production User CheckDB - MWF 10PM' SET @SchName07 = 'Production System CheckDB - Daily 9PM' SET @SchName08 = 'Production Cleanup - Sunday 11PM' -- Frequency Type: 1 = Once, 4 = Daily, 8 = Weekly, 16 = Monthly, 32 = Monthly relativity to freq_interval, 64 = When the agent starts, 128 = When idle -- Frequency Interval, depends on freq_type: -- 1 : Unused -- 4 : Every specified number of days -- 8 : Logical OR, 1 = Sunday, 2 = Monday, 4 = Tuesday, 8 = Wednesday, 16 = Thursday, 32 = Friday, 64 = Saturday -- 16 : On the specified day of the month -- 32 : 1 = Sunday, 2 = Monday, 3 = Tuesday, 4 = Wednesday, 5 = Thursday, 6 = Friday, 7 = Saturday, 8 = Day, 9 = Weekday, 10 = Weekend day -- 64 : Unused -- 128: Unused -- Frequency Subday Type, units for freq_subday_interval: 1 = At the specified time, 2 = Seconds, 4 = Minutes, 8 = Hours -- Frequency Subday Interval, number of freq_subday_type periods to occur between each execution -- Frequency Relative Interval, only used for freq_interval of 32: 1 = First, 2 = Second, 4 = Third, 8 = Fourth, 16 = Last -- Frequency Recurrence Factor, only used for freq_type of 8, 16, or 32: Number of weeks or months between each execution -- Start Time on the 24 hr clock -- End Time on the 24 hr clock SELECT @SchFreqType01 = 4, @SchFreqType02 = 4, @SchFreqType03 = 8, @SchFreqType04 = 4, @SchFreqType05 = 8, @SchFreqType06 = 8, @SchFreqType07 = 4, @SchFreqType08 = 8 SELECT @SchFreqInt01 = 1, @SchFreqInt02 = 1, @SchFreqInt03 = 126, @SchFreqInt04 = 1, @SchFreqInt05 = 84, @SchFreqInt06 = 42, @SchFreqInt07 = 1, @SchFreqInt08 = 1 SELECT @SchSubType01 = 1, @SchSubType02 = 1, @SchSubType03 = 1, @SchSubType04 = 8, @SchSubType05 = 1, @SchSubType06 = 1, @SchSubType07 = 1, @SchSubType08 = 1 SELECT @SchSubInt01 = 0, @SchSubInt02 = 0, @SchSubInt03 = 0, @SchSubInt04 = 1, @SchSubInt05 = 0, @SchSubInt06 = 0, @SchSubInt07 = 0, @SchSubInt08 = 0 SELECT @SchRelInt01 = 0, @SchRelInt02 = 0, @SchRelInt03 = 0, @SchRelInt04 = 0, @SchRelInt05 = 0, @SchRelInt06 = 0, @SchRelInt07 = 0, @SchRelInt08 = 0 SELECT @SchFreqRec01 = 1, @SchFreqRec02 = 1, @SchFreqRec03 = 1, @SchFreqRec04 = 1, @SchFreqRec05 = 1, @SchFreqRec06 = 1, @SchFreqRec07 = 1, @SchFreqRec08 = 1 SELECT @SchStart01 = 213000, @SchStart02 = 0, @SchStart03 = 0, @SchStart04 = 20000, @SchStart05 = 220000, @SchStart06 = 220000, @SchStart07 = 210000, @SchStart08 = 230000 SELECT @SchEnd01 = 235959, @SchEnd02 = 235959, @SchEnd03 = 235959, @SchEnd04 = 215959, @SchEnd05 = 235959, @SchEnd06 = 235959, @SchEnd07 = 235959, @SchEnd08 = 235959 -- Create Schedules IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName01) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName01, @enabled = 1, @freq_type = @SchFreqType01, @freq_interval = @SchFreqInt01, @freq_subday_type = @SchSubType01, @freq_subday_interval = @SchSubInt01, @freq_relative_interval = @SchRelInt01, @freq_recurrence_factor = @SchFreqRec01, @active_start_time = @SchStart01, @active_end_time = @SchEnd01, @owner_login_name = @SchOwner; IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName02) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName02, @enabled = 1, @freq_type = @SchFreqType02, @freq_interval = @SchFreqInt02, @freq_subday_type = @SchSubType02, @freq_subday_interval = @SchSubInt02, @freq_relative_interval = @SchRelInt02, @freq_recurrence_factor = @SchFreqRec02, @active_start_time = @SchStart02, @active_end_time = @SchEnd02, @owner_login_name = @SchOwner; -- IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName03) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName03, @enabled = 1, @freq_type = @SchFreqType03, @freq_interval = @SchFreqInt03, @freq_subday_type = @SchSubType03, @freq_subday_interval = @SchSubInt03, @freq_relative_interval = @SchRelInt03, @freq_recurrence_factor = @SchFreqRec03, @active_start_time = @SchStart03, @active_end_time = @SchEnd03, @owner_login_name = @SchOwner; IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName04) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName04, @enabled = 1, @freq_type = @SchFreqType04, @freq_interval = @SchFreqInt04, @freq_subday_type = @SchSubType04, @freq_subday_interval = @SchSubInt04, @freq_relative_interval = @SchRelInt04, @freq_recurrence_factor = @SchFreqRec04, @active_start_time = @SchStart04, @active_end_time = @SchEnd04, @owner_login_name = @SchOwner; IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName05) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName05, @enabled = 1, @freq_type = @SchFreqType05, @freq_interval = @SchFreqInt05, @freq_subday_type = @SchSubType05, @freq_subday_interval = @SchSubInt05, @freq_relative_interval = @SchRelInt05, @freq_recurrence_factor = @SchFreqRec05, @active_start_time = @SchStart05, @active_end_time = @SchEnd05, @owner_login_name = @SchOwner; IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName06) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName06, @enabled = 1, @freq_type = @SchFreqType06, @freq_interval = @SchFreqInt06, @freq_subday_type = @SchSubType06, @freq_subday_interval = @SchSubInt06, @freq_relative_interval = @SchRelInt06, @freq_recurrence_factor = @SchFreqRec06, @active_start_time = @SchStart06, @active_end_time = @SchEnd06, @owner_login_name = @SchOwner; IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName07) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName07, @enabled = 1, @freq_type = @SchFreqType07, @freq_interval = @SchFreqInt07, @freq_subday_type = @SchSubType07, @freq_subday_interval = @SchSubInt07, @freq_relative_interval = @SchRelInt07, @freq_recurrence_factor = @SchFreqRec07, @active_start_time = @SchStart07, @active_end_time = @SchEnd07, @owner_login_name = @SchOwner; IF NOT EXISTS (SELECT * FROM msdb.dbo.sysschedules WHERE [name] = @SchName08) EXEC msdb.dbo.sp_add_schedule @schedule_name = @SchName08, @enabled = 1, @freq_type = @SchFreqType08, @freq_interval = @SchFreqInt08, @freq_subday_type = @SchSubType08, @freq_subday_interval = @SchSubInt08, @freq_relative_interval = @SchRelInt08, @freq_recurrence_factor = @SchFreqRec08, @active_start_time = @SchStart08, @active_end_time = @SchEnd08, @owner_login_name = @SchOwner; -- Assign Schedules to Jobs IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName01) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName01, @schedule_name = @SchName01; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName02) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName02, @schedule_name = @SchName02; -- IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName03) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName03, @schedule_name = @SchName03; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName04) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName04, @schedule_name = @SchName04; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName05) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName05, @schedule_name = @SchName05; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName06) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName06, @schedule_name = @SchName06; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName07) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName07, @schedule_name = @SchName07; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName08) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName08, @schedule_name = @SchName08; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName09) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName09, @schedule_name = @SchName08; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName10) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName10, @schedule_name = @SchName08; IF EXISTS (SELECT * FROM msdb.dbo.sysjobs WHERE [name] = @JobName11) EXEC msdb.dbo.sp_attach_schedule @job_name = @JobName11, @schedule_name = @SchName08; END Please let me know if you have any questions. Zahid | | 8/24/2019 4:30 PM | | No | General | 0 | | | 8/18/2019 10:40 PM | | | | | | | 0 | 8/19/2019 11:16 PM | How does the database recovery model impact database backups?
First the database recovery model is responsible for the retention of the transaction log entries. So based on the setting determines if transaction log backups need to be issued on a regular basis i.e. every minute, five minutes, hourly, etc. in order to keep the transaction log small and/or maintain a log shipping solution.
- Simple – Committed transactions are removed from the log when the check point process occurs.
- Bulk Logged – Committed transactions are only removed when the transaction log backup process occurs.
- Full – Committed transactions are only removed when the transaction log backup process occurs.
Is the native SQL Server 2005 backups are in clear text or in encrypted?
With SQL Server 2008 is the introduction of natively encrypted database backups. Prior to SQL Server 2008 a third party product was necessary to encrypt the database backups.
How can I verify that backups are occurring on a daily basis?
- Check all backup jobs history
- Review the SQL Server error log for backup related entries.
- Query the msdb.dbo.backupset table for the backup related entries.
- Review the file system where the backups are issued to validate they exist.
How do you know if your database backups are restorable?
- Issue the RESTORE VERIFYONLY command to validate the backup. For validating LiteSpeed backups use XP_restore_verifyonly
- Randomly retrieve tapes from off site and work through the restore process with your team to validate the database is restored in a successful manner.
How can you be notified if a native SQL Server database backup or restore fails via the native tools?
- Setup SQL Server Alerts to be sent to Operators on a failure condition.
- Include RAISERROR or TRY\CATCH logic in your backup or restore code to alert on the failure.
Does all successful SQL Server backup entries can be prevented from writing to the SQL Server Error Log by a single trace flag?
Yes – Just enable the trace flag 3226.
What are some common reasons why database restores fail?
- Sufficient space not available on drive
- User may not have sufficient permissions to perform the restore
- Unable to gain exclusive use of the database.
- LSN’s are out of sequence so the backups cannot be restored.
- Syntax error such as with the WITH MOVE command.
- Version problem
- Might be wrong backup location specified
- Service account may not have permissions on backup folder
What are the permissions required to perform backup and Restore?
The user must be a member of either of the below roles
Backup:
- sysadmin – fixed server role
- db_owner – fixed database role
- db_backupoperator – fixed database role
Restore:
- Sysadmin – fixed server role
- Dbcreator – fixed server role
- db_owner – fixed database role
What are some common post restore processes?
- Sync the logins and users
- Validate the data is accurate by running dbcc commands
- Notify the team\user community
- Cleanse the data to remove sensitive data i.e. SSN’s, credit card information, customer names, personal information, etc.
- Change database properties i.e. recovery model, read-only, etc.
Explain how you could automate the backup and restore process?
- Backups can be automated by using a cursor to loop through each of the databases and backup each one
- Restores can also be automated by looping over the files, reading from the system tables (backup or log shipping) or reading from a table as a portion of a custom solution
What is the database that has the backup and restores system tables? What are the backup and restore system tables?
The MSDB database is the database with the backup and restores system tables.
How can full backups be issued without interrupting the LSN’s?
Issue the BACKUP command with the COPY_ONLY option
How is a point in time recovery performed?
It depends on which backup types are issued. In this example let’s assume that full, differential and transaction log backups are issued.
- Restore the most recent full backup with the NORECOVERY clause
- Restore the most recent differential backup with the NORECOVERY clause
- Restore all of the subsequent transaction log backups with the NORECOVERY clause except the last transaction log backup
- Restore the last transaction log backup with the RECOVERY clause and a STOPAT statement if the entire transaction log does not need to be applied
Consider a scenario where you issue a full backup. Then issue some transaction log backups, next a differential backup, followed by more transaction log backups, then another differential and finally some transaction log backups. If the SQL Server crashes and if all the differential backups are bad, when is the latest point in time you can successfully restore the database? Can you recover the database to the current point in time without using any of the differential backups?
You can recover to the current point in time, as long as you have all the transaction log backups available and they are all valid. Differential backups do not affect the transaction log backup chain.
What are the three basic phases for database recovery and in what order do they occur?
- Analysis
- Redo – rolls forward committed transactions
- Undo – rolls back any incomplete transactions
What are your recommendations to design a backup and recovery solution? Simply what is Backup Check list?
- Determine What is Needed
- Recovery Model
- Select Backup Types
- Backup Schedule
- Backup Process
- Document
- Backup to Disk
- Archive to Tape
- Backup to Different Drives
- Secure Backup Files
- Encrypt or Password Protect Backup Files
- Compress Backup Files
- How Much to Keep on Disk
- Online Backups
- Run Restore Verify only
- Offsite Storage
What options/arguments can be specified in a BACKUP LOG statement to keep inactive log records from being truncated?
- SQL Server 2000: NO_TRUNCATE
- SQL Server 2005/2008: NO_TRUNCATE, COPY_ONLY
What are all of the backup \Restore options and their associated value?
Backup Options:
- Full – Online operation to backup all objects and data in a single database
- Differential – Backup all extents with data changes since the last full backup
- Transaction log – Backup all transaction in the database transaction log since the last transaction log backup
- File – Backup of a single file to be included with the backup when a full backup is not possible due to the overall database size
- File group – Backup of a single file group to be included with the backup when a full backup is not possible due to the overall database size
- Cold backup – Offline file system backup of the databases
- Partial Backup – When we want to perform read-write filegroups and want to exclude read-only filegroups from backup. It will be useful for huge databases (Data warehousing)
- Third party tools – A variety of third party tools are available to perform the operations above in addition to enterprise management, advanced features, etc.
Restore Options:
- Restore an entire database from a full database backup (a complete restore).
- Restore part of a database (a partial restore).
- Restore specific files or filegroups to a database (a file restore).
- Restore specific pages to a database (a page restore).
- Restore a transaction log onto a database (a transaction log restore).
- Revert a database to the point in time
How to perform the tail log backup?
As normal log backup we can perform the tail log backup. We have two options to consider
WITH NORECOVERY: When database online and you are planning to perform a restore after the tail log backup. It takes the database in restoring mode to make sure that no transactions performed after the tail log.
WITH CONTINUE_AFTER_ERROR: When database offline and does not starts. Remember we can only perform the log backup on damaged database when the log files are not damaged
How much time taken to take full backup of 500 GB database by using third party tool litesped and without using third-party tool and also how much time taken to restore same full backup using litespeed and without third-party tool
There is no specific time we can say for BACKUP & RESTORE operation.
It depends on lot of factors like Disk I/O, Network, processors etc.
SQL Server 2005:
Database Size: 1.2 TB
Time taken to Backup with Litespeed : 3:20 Hrs (80 % of compression)
Time Taken to Restore: 6Hrs
Database Size: 800 GB
Time Taken to Backup using Native Method: 11 Hrs
I never tried restoring huge db’s in native method mean native backups
SQL Server 2000:
A Database of 20 GB will take 14 Min to Backup and 22 Min to Restore the Backup
What are the issues you faced in backup and restore process?
Common Errors in Backup:
Error 3201 – when performing a backup to a network share
Sol:
Where SQL Server disk access is concerned, everything depends on the rights of the SQL Server service startup account. If you are unable to back up to a network share, check that the service startup account has write rights to that share.
Error: Cannot open the backup device:
Sol:
Either the specified location is missing or the service account under which the SQL Agent is running does not have the permissions on that folder.
Common Errors in Restore:
Error 3205 – Too many backup devices specified for backup or restore;
Sol:
The most common cause for this error is because you are trying to restore a SQL Server 2005 backup on a SQL Server 2000 instance
Error 4305 – an earlier transaction log backup is required
Sol:
There are one or more transaction log backups that need to be restored before the current transaction log backup. Using LSN number we can identify the prior log backups.
What is the difference between NO_LOG and TRUNCATE_ONLY?
Both removes the inactive part of the log without making a backup copy of it and truncates the log by discarding all but the active log. This option frees space. NO_LOG and TRUNCATE_ONLY are synonyms.
After truncating the log using either NO_LOG or TRUNCATE_ONLY, the changes recorded in the log are not recoverable. For recovery purposes, immediately execute BACKUP DATABASE to take a full or full differential backup. Always try to avoid running the truncating as it breaks the log chain, Until the next full or full differential backup, the database is not protected from media failure.
Consider a situation where I have to take a backup of one database of 60 GB. My hard drive lacked sufficient space at that moment. I don’t find 64GB free on any drive. Fortunately, I have 3 different drives where I can hold 20 GB on each drive. How can you perform the backup to three different drives? How can you restore those files? Is this really possible?
Yes it is possible. We can split the backup files into different places and the same can be restored.
- BACKUP DATABASE AdventureWorks
TO DISK = ‘D:\Backup\MultiFile\AdventureWorks1.bak’,
DISK = ‘E:\Backup\MultiFile\AdventureWorks2.bak’,
DISK = ‘F:\Backup\MultiFile\AdventureWorks3.bak’
- RESTORE DATABASE [AdventureWorks]
FROM DISK = N’D:\Backup\MultiFile\AdventureWorks1.bak’,
DISK = N’E:\Backup\MultiFile\AdventureWorks2.bak’,
DISK = N’F:\Backup\MultiFile\AdventureWorks3.bak’
What is piecemeal Restore?
Consider we have a database of 3 TB where as on primary file group is a read write filegroup of size 500 GB and we have other files groups which are read-only of size 2.5 TB. We actually need not perform backup for read-only file groups, here we can perform partial backups.
Piecemeal restore process allows us to restore the primary filegroup first and the database can be online and the remaining filegroups can be restored while the recovery the transactions are running on primary
File group. Mostly suitable for data warehousing databases.
How can we rebuild Resource system database?
Ans:
In Sql Server 2008 from installation wizard from left navigation pane select "Maintenance" and Click on Repair. It rebuilds the resource database.
Can you restore master database? If yes how?
All server level information stored in master database that includes logins information etc. Schedule a regular backup for master database and below is the process to restore a master db.
- Start the sql server in single user mode (-m)
- Issue the restore command with replace from SQLCMD prompt
- RESTORE DATABASE master FROM <backup_device> WITH REPLACE
- Restart the sql server in normal mode
- All databases as available at the time of master db backup must be attached as everything is tracked in master database.
- If any databases are missing we can manually attach the mdf-ldfs.
How can we rebuild the system databases?
We usually rebuild the system databases when they are corrupted.
Rebuild deletes the databases and recreates it hence all the existing information is vanished.
Before rebuild:
- Locate all recent backup of system databases
- Make a note on mdf and ldf file locations, server configuration, Build /hotfix /sp applied
Rebuild:
- Locate the Sql Server installation bits and run the command setup.exe fro command prompt by passing the argument as "/ACTION=REBUILDDATABASE"
- Review the summary.txt once the rebuild completes
Post Rebuild:
- Restore all the system databases from existing backups
- Move the system databases mdf/ldf files to the actual locations
As a database is recovering, after which phase will the database be available/online?
SQL Server 2000: After the Undo phase.
SQL Server 2005/2008: In all editions but Enterprise, after the Undo phase (if running FULL recovery model). With Enterprise edition, after the Redo phase. Fast recovery is possible because transactions that were uncommitted when a crash occurred reacquire whatever locks they held before the crash. While these transactions are being rolled back, their locks protect them from interference by users.
On the Enterprise Edition of SQL Server 2005/2008, users are allowed access after REDO. So the point is REDO phase is done first.
How do you respond to the increasing transaction log file?
Alternatives for responding to a full transaction log include:
- Backing up the log.
- Freeing disk space so that the log can automatically grow.
- Adding a log file on a different disk.
- Completing or killing a long-running transaction.
- Moving the log file to a disk drive with sufficient space.
- Increasing the size of a log file.
- Shrinking the log file.
Do we need installation DVD or complete binaries to rebuild system databases?
The answer is NO. In 2008 this is really a nice enhancement. While installing SQL Server these system database files are copied to the location (<MSSQL.InstanceName>\MSSQL\Binn\Templates)
In this directory you will find master, model, and msdb database and log files that were copied from your installation source as part of setup. Hence when we rebuild the databases the setup uses these files to rebuild the databases.
What should we do if cannot find these database files at ….Templates\ location?
There are two options available.
- Use the Repair feature of Setup (Available from the Maintenance option of the SQL Server Installation Center installed your machine)
OR
- Manually copy the necessary file(s) yourself. On your media source find the directory of your platform (x86, x64, or ia64). Then go to the following directory:
setup\sql_engine_core_inst_msi\PFiles\SqlServr\MSSQL.X\MSSQL\Binn\Template
Once you have copied the file into the templates directory or repairing, re-run setup with the syntax I’ve described above.
Can we rebuild resource database?
Yes! To rebuild these database files you would need to run Repair from the Installation Center.
Can we rebuild msdb?
Yes! We can directly restore it from a valid backup. If there is no valid backup available, restore all system databases as described above.
What if I have applied Updates / HotFixes after installation?
As with SQL Server 2005, if for any reason you rebuild system databases or repair the resource database, you should apply your latest update even if you restore backups of system databases.
What is the difference between Hot and Cold Backup?
Performing backup while the database is online is called Hot backup. Stopping SQL server service and copying MDF and LDF files is called cold backup which is not really happens in production.
How to restore Master or Msdb database from a valid backup?
Stop and Start the SQL Server Database Engine in Singe User Mode (Using parameter –m) Restore the Master Database from SQLCMD prompt
- From dos prompt using SQLCMD connect to the sql server and run the restore script
- RESTORE DATABASE MASTER FROM DISK=’D:\MASTER_FULL.BAK’ WITH REPLACE
- Stop and start the SQL Server Database Engine in normal mode
- Restore MSDB Database
- Connect to management studio and run the restore script for msdb
- RESTORE DATABASE MSDB FROM DISK=’D:\MSDB_FULL.BAK’ WITH REPLACE
What are the restore options available?
When you restore a backup, you can choose from 3 restore option.
- With Recovery – Database is ready to use, and user can connect to database, user can change data inside database.
- No Recovery – Database is not ready, there are few more backups that has to be applied to this database instance. User cannot connect to database because it is in Restoring Status. (Exception: Not considering Database Snapshots )
- Standby / Read Only – Database is ready to use but database is in Read Only mode, user can connect to database but they cannot change data inside database. A running database con not be changed to standby mode. Only a data in no-recovery state can be moved to standby mode. This is an option that is specified while restoring a database or transaction log.
Thanks Zahid
| | 8/19/2019 11:16 PM | | No | General | 0 | | | 8/18/2019 10:40 PM | | | | | | | 0 | 8/18/2019 10:41 PM | SET NOCOUNT ON
GO
DECLARE @dbName NVARCHAR (255);
DECLARE @SqlStatement NVARCHAR(4000); DECLARE databaseCurs CURSOR FOR
SELECT name FROM sys.databases WITH (NOLOCK)
WHERE name NOT IN ('tempdb','model', 'master', 'msdb')
OPTION (RECOMPILE); OPEN databaseCurs;
FETCH NEXT FROM databaseCurs INTO @dbName
WHILE (@@fetch_status <> -1)
BEGIN
IF (@@fetch_status <> -2)
BEGIN
SET @SqlStatement = N'ALTER DATABASE [' + @dbName + N']' + ' SET SINGLE_USER WITH ROLLBACK IMMEDIATE; ' + N'DROP DATABASE [' + @dbName + N'];';
----SET @SqlStatement = N'BACKUP LOG [' + @dbName + N'] TO DISK = ''S:\BACKUP\' + @dbName + N'.BAK'''; (For Transaction Log Backup)
EXECUTE sp_executesql @SqlStatement --print @SqlStatement
PRINT N'drop database completed suceessfully: ' + @dbName
END;
FETCH NEXT FROM databaseCurs INTO @dbName;
END;
CLOSE databaseCurs;
DEALLOCATE databaseCurs;
GO | | 8/18/2019 10:41 PM | | No | General | 0 | | | 8/18/2019 10:40 PM | | | | | | | 0 | 8/18/2019 10:40 PM | --1. Variable declaration DECLARE @path VARCHAR(500)
DECLARE @name VARCHAR(500)
DECLARE @filename VARCHAR(256)
DECLARE @time DATETIME
DECLARE @year VARCHAR(4)
DECLARE @month VARCHAR(2)
DECLARE @day VARCHAR(2)
DECLARE @hour VARCHAR(2)
DECLARE @minute VARCHAR(2)
DECLARE @second VARCHAR(2)
-- 2. Setting the backup path SET @path = '\\san-server\DB_Backup\FULL\' -- 3. Getting the time values SELECT @time = GETDATE()
SELECT @year = (SELECT CONVERT(VARCHAR(4), DATEPART(yy, @time)))
SELECT @month = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mm,@time),'00')))
SELECT @day = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(dd,@time),'00')))
SELECT @hour = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(hh,@time),'00')))
SELECT @minute = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(mi,@time),'00')))
SELECT @second = (SELECT CONVERT(VARCHAR(2), FORMAT(DATEPART(ss,@time),'00'))) -- 4. Defining cursor operations
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- system databases are excluded --5. Initializing cursor operations OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name WHILE @@FETCH_STATUS = 0
BEGIN -- 6. Defining the filename format
SET @fileName = @path + @name + '.BAK' ----+ @year + @month + @day + @hour + @minute + @second
BACKUP DATABASE @name TO DISK = @fileName WITH NOFORMAT, NOINIT, SKIP, NOREWIND, NOUNLOAD, COMPRESSION, STATS = 10
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
| | 8/18/2019 10:40 PM | | No | General | 0 | | | 8/18/2019 10:40 PM | | | | | | | 0 | 7/21/2019 3:04 PM | - Tell me about yourself. - Most of the time, this will be the first questions in your interview.
- What is a page in sql server? - The fundamental unit of data storage in SQL Server is the page. The disk space allocated to a data file (.mdf or .ndf) in a database is logically divided into pages numbered contiguously from 0 to n. Disk I/O operations are performed at the page level. That is, SQL Server reads or writes whole data pages. In SQL Server, the page size is 8 KB. This means SQL Server databases have 128 pages per megabyte. Each page begins with a 96-byte header that is used to store system information about the page. This information includes the page number, page type, the amount of free space on the page, and the allocation unit ID of the object that owns the page. Data rows are put on the page serially, starting immediately after the header. A row offset table starts at the end of the page, and each row offset table contains one entry for each row on the page. Each entry records how far the first byte of the row is from the start of the page. The entries in the row offset table are in reverse sequence from the sequence of the rows on the page.
- What is an extent in sql server? - Extents are a collection of eight physically contiguous pages and are used to efficiently manage the pages. All pages are stored in extents. Extents are the basic unit in which space is managed. An extent is eight physically contiguous pages, or 64 KB. This means SQL Server databases have 16 extents per megabyte.
To make its space allocation efficient, SQL Server does not allocate whole extents to tables with small amounts of data. SQL Server has two types of extents: - Uniform extents are owned by a single object; all eight pages in the extent can only be used by the owning object.
- Mixed extents are shared by up to eight objects. Each of the eight pages in the extent can be owned by a different object.
A new table or index is generally allocated pages from mixed extents. When the table or index grows to the point that it has eight pages, it then switches to use uniform extents for subsequent allocations. If you create an index on an existing table that has enough rows to generate eight pages in the index, all allocations to the index are in uniform extents.
- What is the relation between page and extent? - read above answer.
- How many services are installed when you install sql server first time? - SQL SERVICES, SQL AGENT SERVICE, SQL BROWSER SERVICE, SQL SERVER ACTIVE DIRECTORY HELP, SQL SERVER VSS WRITER.
- What doesn SQL Browser Service do? - The SQL Server Browser program runs as a Windows service. SQL Server Browser listens for incoming requests for Microsoft SQL Server resources and provides information about SQL Server instances installed on the computer. SQL Server Browser contributes to the following actions:
- Browsing a list of available servers
- Connecting to the correct server instance
- Connecting to dedicated administrator connection (DAC) endpoints
- What types of indexes are available in SQL Server? Cluster Index and Non Cluster Index.
- What is the difference between Cluster and Non-Clustered index?
- Why do you need Non-Clustered Index?
- What is covering index? Why do you need Covering Index?
- What is Composite Index? Why do we need it?
- Difference between Composite Index and Covering Index.
- What was your backup stretegies?
- What is the difference between Full backup vs Differential backup?
- What is tail-log backup? When do we use tail-log backup?
- What is the difference between rebuild index and reorg index?
- When do we rebuild index and reorg index?
- What is sql statistics and what kind of information it contains?
- When do you need to update statistics?
- How do you find out statistics is not updated?
- Which DMV finds the missing indexes?
- Is it good idea to add all recommended missing indexes?
- How do you find out Indexes are fragmented? How do you fix this issue?
- How do you find out the length of the column?
- How do you find out BLOB object column length?
- What is your DR strategies?
- What is the difference between Mirroring and LogShipping?
- What types of mirroring are available?
- What is the difference between Asynchronous and synchronous mirroring?
- How do you setup LogShipping in your environment?
- Have you ever worked with replication? What types of replications are available?
- How do you setup replication if database is really big?
- How does replication works? exp: Transactional Replication.
- How do you solve latency issue in sql server?
- Tell us about some DBCC commands you use in daily work.
- Tell us about some DMV commands you use in daily work.
- Have you ever worked on clustered environment? What is the difference between active and passive clustered?
- How big was your clustered environment?
- What is the difference between 2005 and 2008 clustered?
- Can you install sql server from remote machine in cluster?
- Have you ever add SAN in clustered? if Yes, what are the steps to add SAN in clustered?
- Have you ever add or move Quorum drive? What are the steps to do it?
- What are the steps to upgrade SQL on clustered environment?
- What is minimum requirements before installing SQL on Cluster environment?
- What is Index Padding and Fill Factor?
- What would you do if you want to update statistics and db is really huge?
- What is Sample rows or percentage in Update Statistics?
- Have you ever worked with reporting service? Explain your role in that.
- How to migrate reports from one server to another server?
- How to find out error in reporting server? - Reporting service has their own log file.
- Have you ever worked with SSIS? explain your role in that.
- How many ways you can deploy SSIS packages?
- What is the difference between File System and SQL Server deployment?
- How to migrate packages from File System to File system and MSDB to MSDB?
- If you are doing replication of one database and if you do rebuild index on primary server does secondary server will follow same?
- How to move one table from one disk to another disk when there is a space issue on database and you don't have any other data files?
Thank You, Zahid | | 7/21/2019 3:04 PM | | No | General | | | | | | | |
|
