SQL Server: How to run multiple scripts in batch mode

 

1. In the SQL Management Studio open a new query and type all files as below

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   

2. Go to Query menu on SQL Management Studio and make sure SQLCMD Mode is enabled

3. Click on SQLCMD Mode; files will be selected in grey as below

   :r c:\Scripts\script1.sql
   :r c:\Scripts\script2.sql
   :r c:\Scripts\script3.sql
   

4. Now execute

SQL Server: Running Jobs Query

 SELECT j.name AS job_name, 
       ja.start_execution_date AS StartTime,
   COALESCE(CONVERT(VARCHAR(5),ABS(DATEDIFF(DAY,(GETDATE()-ja.start_execution_date),'1900-01-01'))) + ' '
               +CONVERT(VARCHAR(10),(GETDATE()-ja.start_execution_date),108),'00 00:00:00') AS [Duration] 
FROM msdb.dbo.sysjobactivity ja 
LEFT JOIN msdb.dbo.sysjobhistory jh ON ja.job_history_id = jh.instance_id
JOIN msdb.dbo.sysjobs j ON ja.job_id = j.job_id
WHERE ja.session_id = (SELECT TOP 1 session_id FROM msdb.dbo.syssessions ORDER BY session_id DESC)
  AND start_execution_date is not null
  AND stop_execution_date is null;

SQL Server: alter physical file names

select 'ALTER DATABASE ' + d.name +' MODIFY FILE ( NAME = ' + f.name + ', FILENAME = ''' + f.physical_name +''' );'

from sys.master_files f, sys.databases d

where f.database_id > 4

and f.database_id = d.database_id

and f.physical_name like 'C:\ClusterStorage\Test UDB\Data\%'

order by d.name

SQL Server set databases offline

select 'ALTER DATABASE [' + A.Name + '] SET OFFLINE WITH ROLLBACK IMMEDIATE;'

from sys.databases a

where a.database_id > 4

and state_desc ='ONLINE'

and a.name not in ('SSISDB')

order by a.name

SQL Server set databases online

select 'ALTER DATABASE [' + A.Name + '] SET ONLINE;'

from sys.databases a

where a.database_id > 4

and state_desc ='OFFLINE'

and a.name not in ('SSISDB')

order by a.name

SQL Server ETA of running SQL e.g. dbcc

 select  T.text, R.Status, R.Command, DatabaseName = db_name(R.database_id)
        , R.cpu_time, R.total_elapsed_time, R.percent_complete
from    sys.dm_exec_requests R
        cross apply sys.dm_exec_sql_text(R.sql_handle) T

SQL Server Agent Job Startup to remove a tempdb file

 USE [tempdb]
GO
DBCC SHRINKFILE (N'temp5', EMPTYFILE)
GO
ALTER DATABASE [tempdb] REMOVE FILE [temp5]
GO

SQL Server Detach / Attach all User Databases Script

 USE [master];
GO
DECLARE @database NVARCHAR(200) ,
    @cmd NVARCHAR(1000) ,
    @detach_cmd NVARCHAR(4000) ,
    @attach_cmd NVARCHAR(4000) ,
    @file NVARCHAR(1000) ,
    @i INT ,
    @DetachOrAttach BIT;
SET @DetachOrAttach = 1;
-- 1 Detach 0 - Attach
-- 1 Generates Detach Script
-- 0 Generates Attach Script
DECLARE dbname_cur CURSOR STATIC LOCAL FORWARD_ONLY
FOR
    SELECT  RTRIM(LTRIM([name]))
    FROM    sys.databases
    WHERE   database_id > 4;
 -- No system databases
OPEN dbname_cur
FETCH NEXT FROM dbname_cur INTO @database
WHILE @@FETCH_STATUS = 0 
    BEGIN
        SELECT  @i = 1;
        SET @attach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
            + 'EXEC sp_attach_db @dbname = ''' + @database + '''' + CHAR(10);
      -- Change skip checks to false if you want to update statistics before you detach.
        SET @detach_cmd = '-- ' + QUOTENAME(@database) + CHAR(10)
            + 'EXEC sp_detach_db @dbname = ''' + @database
            + ''' , @skipchecks = ''true'';' + CHAR(10);
      -- Get a list of files for the database
        DECLARE dbfiles_cur CURSOR STATIC LOCAL FORWARD_ONLY
        FOR
            SELECT  physical_name
            FROM    sys.master_files
            WHERE   database_id = DB_ID(@database)
            ORDER BY [file_id];
        OPEN dbfiles_cur
        FETCH NEXT FROM dbfiles_cur INTO @file
        WHILE @@FETCH_STATUS = 0 
            BEGIN
                SET @attach_cmd = @attach_cmd + '    ,@filename'
                    + CAST(@i AS NVARCHAR(10)) + ' = ''' + @file + ''''
                    + CHAR(10);
                SET @i = @i + 1;
                FETCH NEXT FROM dbfiles_cur INTO @file
            END
        CLOSE dbfiles_cur;
        DEALLOCATE dbfiles_cur;
        IF ( @DetachOrAttach = 0 ) 
            BEGIN
            -- Output attach script
                PRINT @attach_cmd;
            END
        ELSE -- Output detach script
            PRINT @detach_cmd;
        FETCH NEXT FROM dbname_cur INTO @database
    END
CLOSE dbname_cur;
DEALLOCATE dbname_cur;

DISM Commands

 DISM.exe /Online /Cleanup-Image /Restorehealth

Windows 10/11 De-bloater

 iwr -useb https://git.io/debloat|iex

Generic Keys for Windows 11

 

Windows 11 Edition	Generic Key
Windows 11 Home	YTMG3-N6DKC-DKB77-7M9GH-8HVX7
Windows 11 Home N	4CPRK-NM3K3-X6XXQ-RXX86-WXCHW
Windows 11 Home Home Single Language	BT79Q-G7N6G-PGBYW-4YWX6-6F4BT
Windows 11 Home Country Specific	N2434-X9D7W-8PF6X-8DV9T-8TYMD
Windows 11 Pro	VK7JG-NPHTM-C97JM-9MPGT-3V66T
Windows 11 Pro N	2B87N-8KFHP-DKV6R-Y2C8J-PKCKT
Windows 11 Pro for Workstations	DXG7C-N36C4-C4HTG-X4T3X-2YV77
Windows 11 Pro for Workstations N	WYPNQ-8C467-V2W6J-TX4WX-WT2RQ
Windows 11 Pro Education	8PTT6-RNW4C-6V7J2-C2D3X-MHBPB
Windows 11 Pro Education N	GJTYN-HDMQY-FRR76-HVGC7-QPF8P
Windows 11 Education	YNMGQ-8RYV3-4PGQ3-C8XTP-7CFBY
Windows 11 Education N	84NGF-MHBT6-FXBX8-QWJK7-DRR8H
Windows 11 Enterprise	XGVPP-NMH47-7TTHJ-W3FW7-8HV2C
Windows 11 Enterprise N	WGGHN-J84D6-QYCPR-T7PJ7-X766F
Windows 11 Enterprise G N	FW7NV-4T673-HF4VX-9X4MM-B4H4T

How to Bypass Secure Boot & Trusted Platform Module to Install Windows 11 - Registry Editor

Start the Windows 11 installation until you see "This PC can't run Windows 11."

Press Shift + F10

At the Command Prompt type in regedit, and press Enter.

Go to Computer\HKEY_LOCAL_MACHINE\SYSTEM\Setup

Right-click on Setup and click New > Key. Name that LabConfig

Click on LabConfig, then right-click on the right pane, and click New > DWORD (32-bit Value).

Create 3 DWORD (32-bit Value):

BypassTPMCheck

BypassRAMCheck 

BypassSecureBootCheck

Set each Value data to 1, and press OK.

Upgrade MySQL 8

rpm --import https://repo.mysql.com/RPM-GPG-KEY-mysql-2022

sudo yum update mysql-community*

Netstat: find connections to remote IP's

 netstat -ano 2 | findstr 192.168.1.1

gmail cleanup

 category:promotions older_than:2y is:unread 

ASUS N56VM Enter the BIOS

 While the computer isn’t powered on yet, press and hold the [F2] button of the keyboard, and then press the [Power button] (Do not release F2 button until the BIOS configuration display.).

Linux: Change swap file

 swapoff /dev/vg1/swap

lvextend -L +30GB /dev/vg1/swap

mkswap /dev/vg1/swap

swapon /dev/vg1/swap

MySQL: Increase InnoDB buffer cache memory allocation

 On each host (as root):

1. Backup the existing MySQL configuration:

cp /etc/my.cnf /etc/my.cnf.bak

2. Using vi, add the following parameters to /etc/my.cnf

[mysqld]

innodb_buffer_pool_size=100G

innodb_flush_method=O_DIRECT

3. Restart the MySQL database

service mysql55-mysqld restart

mysqladmin flush-hosts -p

Sophos UTM Web Proxy!!!

 Sophos UTM Web Proxy is a pain in the backside.

It allows access to web-based admin pages  EVEN THOUGH the FIREWALL doesn't allow such access! The firewall doesn't allow access, but the web proxy does!! FFS!!!

So, for example, your ISP router configuration interface could be accessible by a guest user, albeit protected by a password. Then your ISP router is susceptible to password guessing or script-based attacks.

And it's not just your ISP router is at risk, it's everything with an admin page.

On top of that, it re-badges your (valid) connection as though it's coming from the DESTINATION network. FFS! I guess it's a proxy, so that's sort of expected.

The web proxy is sort of necessary, since it has the dual-AV scanning engine. Therefore, disabling the web proxy and using only the firewall will result in web traffic not being scanned for malware.

SQL Server: How to DBCC REPAIR_ALLOW_DATA_LOSS

 USE master;

ALTER DATABASE Prod_Clone SET SINGLE_USER WITH ROLLBACK IMMEDIATE;

GO

USE Prod_Clone

go

--DBCC CHECKTABLE ('AIFDOCUMENTLOG', REPAIR_ALLOW_DATA_LOSS) WITH NO_INFOMSGS;

--DBCC CHECKTABLE ('AIFDOCUMENTLOG') WITH NO_INFOMSGS,ESTIMATEONLY ;

DBCC CHECKDB ('Prod_Clone') WITH NO_INFOMSGS;

GO

--ALTER DATABASE Prod_Clone SET MULTI_USER;

--GO