iksrv.work - MSSQL

Details: Written by: po3dno; Category: MSSQL; Created: 08 August 2023; Hits: 394

[system.net.webrequest]::defaultwebproxy = new-object system.net.webproxy($proxy)
[system.net.webrequest]::defaultwebproxy.credentials = $cred
[system.net.webrequest]::defaultwebproxy.BypassProxyOnLocal = $true
Register-PSRepository -Default
Get-PSRepository

Name InstallationPolicy SourceLocation
---- ------------------ --------------
PSGallery Untrusted https://www.powershellgallery.com/api/v2

Install-Module sqlserver -Proxy $proxy -ProxyCredential $cred

Details: Written by: po3dno; Category: MSSQL; Created: 03 November 2022; Hits: 576

Introduction

DBA uses services accounts to run the various SQL Services. Usually, we should use a separate service account for an individual server SQL Services.

You should run SQL services having the least permissions
You should use a complex password and store it in a secure place
Its password should never expire
You should also change the password regularly, depending upon your organization’s security policy

If you maintain a large inventory of SQL Servers, you might think it is a cumbersome task to change and maintain the passwords for these servers. Once you change the service account password using SQL Server Configuration Manager, it also requires the restart of SQL Services. It might be a challenging task as well to get downtime for highly transactional applications.

We can leverage Group Managed Service Accounts (gMSA) in these cases. Let’s explore it in the subsequent section.

Details: Written by: po3dno; Category: MSSQL; Created: 16 October 2019; Hits: 1073

DECLARE @databasename VARCHAR(50) -- database name
DECLARE @sql VARCHAR(Max)

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @databasename

WHILE @@FETCH_STATUS = 0
BEGIN

set @sql = 'alter database ['+ @databasename + '] set recovery simple;'

print @sql

--exec @sql

FETCH NEXT FROM db_cursor INTO @databasename
END

CLOSE db_cursor
DEALLOCATE db_cursor

Details: Written by: po3dno; Category: MSSQL; Created: 24 June 2019; Hits: 1020

DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name

-- specify database backup directory
SET @path = 'D:\Backup\'

-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

DECLARE db_cursor CURSOR READ_ONLY FOR
SELECT name
FROM master.sys.databases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
AND state = 0 -- database is online
AND is_in_standby = 0 -- database is not read only for log shipping
AND replica_id is NULL --without replica

OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name

WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName

FETCH NEXT FROM db_cursor INTO @name
END

CLOSE db_cursor
DEALLOCATE db_cursor

Details: Written by: po3dno; Category: MSSQL; Created: 04 June 2019; Hits: 1024

DECLARE

@SearchText varchar(200),

@Table varchar(100),

@TableID int,

@ColumnName varchar(100),

@String varchar(1000);

--modify the variable, specify the text to search for

SET @SearchText = 'John';

DECLARE CursorSearch CURSOR

FOR SELECT name, object_id

FROM sys.objects

WHERE type = 'U';

--list of tables in the current database. Type = 'U' = tables(user-defined)

OPEN CursorSearch;

FETCH NEXT FROM CursorSearch INTO @Table, @TableID;

WHILE

@@FETCH_STATUS

BEGIN

DECLARE CursorColumns CURSOR

FOR SELECT name

FROM sys.columns

WHERE

object_id

@TableID AND system_type_id IN(167, 175, 231, 239);

-- the columns that can contain textual data

--167 = varchar; 175 = char; 231 = nvarchar; 239 = nchar

OPEN CursorColumns;

FETCH NEXT FROM CursorColumns INTO @ColumnName;

WHILE

@@FETCH_STATUS

BEGIN

SET @String = 'IF EXISTS (SELECT * FROM '

+ @Table

+ ' WHERE '

+ @ColumnName

+ ' LIKE ''%'

+ @SearchText

+ '%'') PRINT '''

+ @Table

+ ', '

+ @ColumnName

+ '''';

EXECUTE (@String);

FETCH NEXT FROM CursorColumns INTO @ColumnName;

END;

CLOSE CursorColumns;

DEALLOCATE CursorColumns;

FETCH NEXT FROM CursorSearch INTO @Table, @TableID;

END;

CLOSE CursorSearch;

DEALLOCATE CursorSearch;

Page 1 of 4

sqlserver

Configure Managed Service Accounts for SQL Server Always On Availability Groups

Introduction

set recovery simple for all database

Backup all database without replica

Поиск данных

Login Form

Statistics