PowerShell Help

-

Enabling PSRemoting :-


 
Enable-PSRemoting -SkipNetworkProfileCheck
get-netconnectionprofile
Set-NetConnectionProfile -InterfaceAlias Wi-Fi -NetworkCategory Private
WinRM quickconfig
Method 2: Successful

 1. Enable-PSRemoting -Force
2. Set-Item wsman:\localhost\client\trustedhosts *
3.  Restart-Service WinRM
4. Test-WsMan COMPUTER (servername)
5. Enter-PSSession -ComputerName servername -Credential kkolguri
6. once you connect to the remote session - type sqlps and start working on the powershell

 Enter-PSSession -ComputerName zeun76sqpstg01 -Credential kkolguri

Setting up Mirror Using PowerShell:



#Check to make sure database is in full recovery mode
#Creates a new backup of data file and log on primary, then secondary restores from that location


$dbnames = Invoke-Sqlcmd -Query "select name   from sys.databases where database_id  in (9,10,12)" | Select-Object -ExpandProperty name #| Out-File F:\dbnames.txt

$dbnames=
#$dbnames= gc F:\dbnames.txt | where {$_ -ne ""}

foreach ($db in $dbnames)

{


$MYDATABASE= "$db"

$PRINCIPAL=  "bnhhtdbstg03"
$MIRROR  ="bnhhtdbstg05"
#$WITNESS = "dtgewitprd01"
$BACKUPPATH ="F:\SQLBackup\"
$RESTOREPATH= "\\bnhhtdbstg03\SQLBackup\"

#Invoke-Sqlcmd -ServerInstance $PRINCIPAL -QueryTimeout 7000  -Query "

-- Database needs to be in full recovery mode
ALTER DATABASE $MYDATABASE SET RECOVERY FULL WITH NO_WAIT

BACKUP DATABASE $MYDATABASE TO  DISK =  '$BACKUPPATH$MYDATABASE.bak'
WITH INIT,   COMPRESSION,  STATS = 1
GO

BACKUP LOG $MYDATABASE TO  DISK = '$BACKUPPATH$MYDATABASE.trn'
WITH NOFORMAT, INIT,   COMPRESSION,  STATS = 1
GO
"
Invoke-Sqlcmd -ServerInstance $MIRROR  -QueryTimeout 7000 -Query "

-------------------------------------------------
--:connect $MIRROR

RESTORE DATABASE $MYDATABASE FROM  DISK = '$RESTOREPATH$MYDATABASE.bak'
WITH NORECOVERY,  NOUNLOAD,  STATS = 1
GO

RESTORE LOG $MYDATABASE FROM  DISK = '$RESTOREPATH$MYDATABASE.trn'
WITH NORECOVERY,  STATS = 1
GO

ALTER DATABASE $MYDATABASE SET PARTNER = N'TCP://$PRINCIPAL.INTERNAL.MYWEBGROCER.COM:5022'
GO
"


Invoke-Sqlcmd -ServerInstance $PRINCIPAL  -QueryTimeout 7000 -Query "
--:connect $PRINCIPAL

ALTER DATABASE $MYDATABASE SET PARTNER = N'TCP://$MIRROR.INTERNAL.MYWEBGROCER.COM:5022'
GO

-- Remove comment if you would like to leave database online on partner. Ex, change owner to sa.
--ALTER DATABASE $MYDATABASE SET PARTNER FAILOVER

--if '$WITNESS' <> ''
--ALTER DATABASE $MYDATABASE SET WITNESS = 'TCP://$WITNESS.INTERNAL.MYWEBGROCER.COM:5022'
--print '*** Setting the timeout on the principal to $TIMEOUT seconds ***'
--ALTER DATABASE $MYDATABASE SET PARTNER TIMEOUT $TIMEOUT

--GO

"

Write-Host "completed Mirror setup on $db "

}

Comments

Post a Comment

Popular posts from this blog

Netezza Commands

-
Table Size  select tablename, used_bytes/pow(1024,4) as used_TB   from _v_table_storage_stat  where tablename  in (  select tablename from _v_table where tablename not like  '_VT_%' and tablename not like '_T_%'); Objects owned by a user: select o.objname, d.database, oc.classname, u.usename from _t_object o, _t_user u, _t_object_classes oc, _v_database d where u.usename='username' and u.usesysid=o.objowner and o.objclass=oc.objclass and o.objdb=d.objid; Tip: Use NZ_HELP to get   syntax of all commands Like -    nz_help | grep grant [nz@dfw86inzb-04 ~]$ nz_help | grep grant nz_db_group_access_listing           To show what groups have been granted access to what databases. nz_db_user_access_listing            To show what users have been granted access to what databases. nz_ddl_grant_group    ...
Read more

MS SQL Server Tips

-
Image
Unable to Shrink the DataFile If you are unable to shrink the datafile then try the following Error :  File ID 3 of database ID 8 cannot be shrunk as it is either being shrunk by another process or is empty. DBCC execution completed. If DBCC printed error messages, contact your system administrator. Solution:   Add at least 2-3 MB to the data file and start shrinking again. Index Fragmentation Level SELECT distinct dbschemas.[name] as 'Schema', dbtables.[name] as 'Table',  dbindexes.[name] as 'Index', indexstats.avg_fragmentation_in_percent, indexstats.page_count, i.rowcnt as 'totalrows' FROM sys.dm_db_index_physical_stats (DB_ID(), NULL, NULL, NULL, NULL) AS indexstats INNER JOIN sys.tables dbtables on dbtables.[object_id] = indexstats.[object_id] INNER JOIN sys.schemas dbschemas on dbtables.[schema_id] = dbschemas.[schema_id] INNER JOIN sys.indexes AS dbindexes ON dbindexes.[object_id] = indexstats....
Read more
-
Maintenacne Plan creation USE [msdb] GO DECLARE @sqlInstance varchar(20) SET @sqlInstance = '$(instanceName)' DECLARE @BackUpSubPlan sysname SET @BackUpSubPlan = 'Back Up Database Task' DECLARE @BackUpJobName sysname SET @BackUpJobName = 'System databases.' + @BackUpSubPlan DECLARE @ChkDbSubPlan sysname SET @ChkDbSubPlan = 'Check Database Integrity Task' DECLARE @ChkDbJobName sysname SET @ChkDbJobName = 'System databases.' + @ChkDbSubPlan DECLARE @RebuildIndexSubPlan sysname SET @RebuildIndexSubPlan = 'Rebuild Index Task' DECLARE @RebuildIndexJobName sysname SET @RebuildIndexJobName = 'System databases.' + @RebuildIndexSubPlan DECLARE @UpdStatsSubPlan sysname SET @UpdStatsSubPlan = 'Update Statistics Task' DECLARE @UpdStatsJobName sysname SET @UpdStatsJobName = 'System databases.' + @UpdStatsSubPlan DECLARE @HistClnSubPlan sysname SET @HistClnSubPlan = 'History Cleanup Task' ...
Read more