Server UpTime OR Online Duration

net stats srv | find "Statistics"

Data Compression

Syntax:-
=================

USE [student]
GO
Select
'ALTER TABLE ['+B.Name+'].['+ A.Name+ '] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)'
from sys.objects A INNER JOIN
sys.schemas B on A.Schema_id=B.Schema_id
where type = 'U' and A.name not like 'dtproperties'

ALTER TABLE [dbo].[Category] REBUILD PARTITION = ALL WITH (DATA_COMPRESSION = PAGE)

For All Tables :-
===============
EXEC sp_MSforeachtable @command1 = 'alter table ? REBUILD WITH (DATA_COMPRESSION = PAGE);'


To Verify:-
========

SELECT
SCHEMA_NAME(sys.objects.schema_id) AS [SchemaName]
,OBJECT_NAME(sys.objects.object_id) AS [ObjectName]
,[rows]
,[data_compression_desc]
,[index_id] as [IndexID_on_Table]
FROM sys.partitions
INNER JOIN sys.objects
ON sys.partitions.object_id = sys.objects.object_id
WHERE data_compression > 0
AND SCHEMA_NAME(sys.objects.schema_id) <> 'SYS'
ORDER BY SchemaName, ObjectName

Retore T-Log With Move

Here in the syntax we can you with move.



RESTORE LOG [Student] FROM
DISK =  N'P:\student_after_secfile.trn' WITH  FILE = 1,NORECOVERY,

MOVE N'Student_new' TO N'P:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\Student_new2.ndf',
NOUNLOAD, STATS = 10
GO



Another Scenario While Restoring Tlog and Space Crunch:-

RESTORE LOG [DB] FROM
DISK = N’D:\DB.trn’
WITH FILE = 1,
STANDBY = N’D:\ROLLBACK_UNDO_DB.BAK’,
MOVE N’DBFILENAME′ TO N’P:\Data\DB.ndf’,
NOUNLOAD, STATS = 10
GO

Collecting and Saving Configuration Information for a Number of Servers.

Help Link https://www.simple-talk.com/sql/sql-training/how-to-document-and-configure-sql-server-instance-settings/

PowerShell Script:-
=============

SET<#
This PowerShell routine creates an excel spreadsheet with the current configuration settings of all your servers. Each instance is on a different worksheet and the worksheets have the name of the instance. It works by using ODBC connections created locally on your workstation.
Once the spreadsheet is created, the PowerShell script is no longer needed since the data can be refreshed from within Excel. This means that you will have a live record of the configuration settings of your instances.
#>
#change these following settings to your environment
$DirectoryToSaveTo='E:\Scripts\'
$Filename='DatabaseConfiguration'
#
<#
Just make your list of servers here as an XML fragment
these instance names are actually User ODBC DSNs DSNs. Better for Excel.
We associate a version number with each just so you are sure
of a script that will run (You can't get this info from the DSN, and
Excel only allows one select statement in an Excel query
#>

$Servers=[xml] @'

        version="2008" name="Angry" />
       

version="2008" name="Grumpy" />
        version="2005" name="Dozy" />
        version="2000" name="Sleepy" />

'@
 
# constants.
$xlCenter=-4108
$xlTop=-4160
$xlOpenXMLWorkbook=[int]51
# and we put the queries in here
$SQL2005=@"
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;

"@
$SQL2008=@"
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;
"@

$SQL2000=@"
SELECT  Name, c.Value, low AS [minimum], high AS [Maximum],
        master.dbo.syscurconfigs.value AS [Value In Use],
        c.comment AS [Description]
FROM    master.dbo.spt_values v
        INNER JOIN master.dbo.sysconfigures c ON number = c.config
        INNER JOIN master.dbo.syscurconfigs ON number = master.dbo.syscurconfigs.config
WHERE   type = 'C'
ORDER BY LOWER(name)

"@

# 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
  }
$excel = New-Object -Com Excel.Application #open a new instance of Excel
$excel.Visible = $True #make it visible (for debugging more than anything)
$wb = $Excel.Workbooks.Add() #create a workbook
$currentWorksheet=1 #there are three open worksheets you can fill up
foreach ($server in $servers.serverlist.server)
      { #only create the worksheet if necessary
      if ($currentWorksheet-lt 4) {$ws = $wb.Worksheets.Item($currentWorksheet)}
      else  {$ws = $wb.Worksheets.Add()} #add if it doesn't exist
      $currentWorksheet += 1 #keep a tally
      if  ($server.version -eq 2005) {$SQL=$SQL2005} #get the right SQL Script
      if  ($server.version -eq 2008) {$SQL=$SQL2008}
      if ($server.version -eq 2000) {$SQL=$SQL2000}
      $currentName=$server.name  # and name the worksheet
      $ws.name=$currentName # so it appears in the tab
      # note we create the query so that the user can run it to refresh it
      $qt = $ws.QueryTables.Add("ODBC;DSN=$currentName", $ws.Range("A1"), $SQL)
      # and execute it
      if ($qt.Refresh()) #if the routine works OK
            {
            $ws.Activate()
            $ws.Select()
            $excel.Rows.Item(1).HorizontalAlignment = $xlCenter
            $excel.Rows.Item(1).VerticalAlignment = $xlTop
            $excel.Rows.Item(1).Orientation = -90
            $excel.Columns.Item("G:H").NumberFormat = "[Red][=0]û;[Blue][=1]ü"
            $excel.Columns.Item("G:H").Font.Name = "Wingdings"
            $excel.Columns.Item("G:H").Font.Size = 12
              $excel.Rows.Item("1:1").Font.Name = "Calibri"
            $excel.Rows.Item("1:1").Font.Size = 11
            $excel.Rows.Item("1:1").Font.Bold = $true
            $Excel.Columns.Item(1).Font.Bold = $true
            }
      }
$filename=$filename -replace  '[\\\/\:\.]',' ' #remove characters that can cause problems
$filename = "$DirectoryToSaveTo$filename.xlsx" #save it according to its title
if (test-path $filename ) { rm $filename } #delete the file if it already exists
$wb.SaveAs($filename,  $xlOpenXMLWorkbook) #save as an XML Workbook (xslx)
$wb.Saved = $True #flag it as being saved
$wb.Close() #close the document
$Excel.Quit() #and the instance of Excel
$wb = $Null #set all variables that point to Excel objects to null
$ws = $Null #makes sure Excel deflates
$Excel=$Null #let the air out
# Hristo Deshev's Excel trick 'Pro Windows PowerShell' p380
[GC]::Collect()
==================================================================
SQL Required Info Querys :-
===================

SQL2005
----------
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;
SQL2008
----------
SELECT  name, value, minimum, maximum, value_in_use as [Value in use],
        description, is_dynamic AS [Dynamic?], is_advanced AS [Advanced?]
FROM    sys.configurations ORDER BY name ;
 
SQL2000
----------
SELECT  Name, c.Value, low AS [minimum], high AS [Maximum],
        master.dbo.syscurconfigs.value AS [Value In Use],
        c.comment AS [Description]
FROM    master.dbo.spt_values v
        INNER JOIN master.dbo.sysconfigures c ON number = c.config
        INNER JOIN master.dbo.syscurconfigs ON number = master.dbo.syscurconfigs.config
WHERE   type = 'C'
ORDER BY LOWER(name)

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

Adding Secondary Data File To Mirrored Database Steps

Adding Secondary Data File To Mirrored Database Steps

Steps to add secondary data file to Mirrored Database
========================================

1. Make sure mirrored database is in synchronized state using below query - at principal server

SELECT db_name(database_id) as database_name, mirroring_state_desc,
mirroring_role_desc,  mirroring_partner_name,  mirroring_partner_instance
FROM sys.database_mirroring where mirroring_state_desc = 'SYNCHRONIZED'
2. Disable backup jobs at principal server if any

3. Run the below command to stop the mirroring -- at principal server
  Alter database MirrorNew set partner off

Now you can observe db mirroring will be removed and mirror server database state becomes restoring

4. Add secondary data file / filegroup to principal server database - at principal server
alter database mirrornew add file
 (name='second_datafile',
  filename='E:\Program Files\Microsoft SQL Server\DATA\MirrorNew_second.ndf',
  size = 50mb)
5. Make sure file got added to principal server database -- at principal server
select * from MirrorNew..sysfiles

6. Generate log backup of principal database  -- at principal server
backup log mirrornew to disk='e:\backups\mirrornewwithsecondary.trn' with init

7. You can verify whether log backup has newly added file or not using the below command
restore filelistonly from disk='e:\backups\mirrornewwithsecondary.trn'

8. Copy the log backup to mirror server

9. Run the below command to verify the file location of the database
select * from sysaltfiles where dbid = DB_ID('mirrornew')

10. Restore the log file on mirror server database using with move option -- at mirror server

restore log mirrornew from disk='e:\backups\mirrornewwithsecondary.trn'
with norecovery, move 'second_datafile' to 'E:\Program Files\Microsoft SQL Server\\DATA\MirrorNew_second.ndf'
11. Verify newly created file added to database or nore   -- at mirror server
select * from sysaltfiles where dbid = DB_ID('mirrornew')

12. Reinitiate the mirroring from mirror server -- at mirror server
alter database mirrornew set partner ='tcp://kalyanmirror:5022' (Verify properties of DB-Choose Mirroring) -- It moves database into Inrecovery mode

13. Finally initiate mirroring step from principal server -- at principal server
alter database mirrornew set partner='tcp://kalyanprimary:5023'


Thanks :- http://calyansql.blogspot.in/2013/08/adding-secondary-data-file-to-mirrored_9.html

Script To List The Server Roles For Logins

select P.Rolememberships from (select  'EXEC sp_addsrvrolemember @rolename =' + SPACE(1)
        + QUOTENAME(DB_Roles.Roles, '''') + ', @loginame =' + SPACE(1)
        + QUOTENAME(sl.name, '''') as 'Rolememberships'
,sl.sid
from master..syslogins sl
cross join
(
select 'sysadmin' as Roles
union all
select 'securityadmin'
union all
select 'serveradmin'
union all
select 'setupadmin'
union all
select 'processadmin'
union all
select 'diskadmin'
union all
select 'dbcreator'
union all
select 'bulkadmin'
) DB_Roles

where
Case When sl.sysadmin=1 and DB_Roles.Roles='sysadmin' then 'sysadmin'
  When sl.securityadmin=1 and DB_Roles.Roles='securityadmin' then 'securityadmin'
  When sl.serveradmin=1 and DB_Roles.Roles='serveradmin' then 'serveradmin'
  When sl.setupadmin=1 and DB_Roles.Roles='setupadmin' then 'setupadmin'
  When sl.processadmin=1 and DB_Roles.Roles='processadmin' then 'processadmin'
  When sl.diskadmin=1 and DB_Roles.Roles='diskadmin' then 'diskadmin'
  When sl.dbcreator=1 and DB_Roles.Roles='dbcreator' then 'dbcreator'
  When sl.bulkadmin=1 and DB_Roles.Roles='bulkadmin' then 'bulkadmin'
  else null end=DB_Roles.Roles
  ) as p join sysusers su on p.sid=su.sid

Script to list DB User Roles for Logins

  SELECT 'EXEC sp_addrolemember N''' + USER_NAME(groupuid) + ''', N''' + USER_NAME(memberuid) + ''''+';'
FROM sysmembers where USER_NAME(memberuid) not in ('dbo', 'sa', 'public')