Eureka Moment database:mssql

database:mssql:backup_to_od

Anonymous (anonymous@undisclosed.example.com) — 2020-12-09T13:02:00+00:00

Backup MSSQL to OneDrive 'dat.exe' is a utility that outputs the current date in a form of Year,Month,Day,Hour,Minute,Second,DayOfWeek - all with leading zeros. Contact me if you require it. Otherwise change the script to work with system date and time tools

database:mssql:find_gaps

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Find gaps in sequences in SQL tables So, you have auto increment column (id) and and want to know if any records have been deleted. For start, you could not delete the records. Instead, use an additional Boolean column, name it 'deleted' and use it to mark them as deleted. And when selecting data, simply add 'where not deleted'. This way, you retain all the data and may choose to 'empty the trash' when you decide.

database:mssql:fs_acl

Anonymous (anonymous@undisclosed.example.com) — 2021-01-27T13:10:00+00:00

Grant MSSQL instance service access to new data folder * Using Windows Explorer, navigate to the file system location where the database files are stored. Right-click the file system folder, and then click Properties. * On the Security tab, click Edit, and then Add.

database:mssql:get_serial

Anonymous (anonymous@undisclosed.example.com) — 2019-12-12T11:14:00+00:00

Get MSSQL serial Function Get-SqlServerKeys { <# .SYNOPSIS Gets SQL Server Product Keys from local and remote SQL Servers. Works with SQL Server 2005-2014 .DESCRIPTION Using a string of servers, a text file, or Central Management Server to provide a list of servers, this script will go to each server and get the product key for all installed instances. Clustered instances are supported as well. Requires regular user access to the SQL instances, SMO installed locally, Remote Regist…

database:mssql:install_on_server_core

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Install MSSQL on Windows Server Core Setup.exe /QS /ACTION=Install /FEATURES=SQL,Replication,IS /INSTANCENAME=MSSQL /SQLSVCACCOUNT="NT Authority\System" /SQLSYSADMINACCOUNTS="VK7\Administrator" /AGTSVCACCOUNT="NT AUTHORITY\Network Service" /SECURITYMODE=SQL /SAPWD="SA_PASSWORD" /TCPENABLED=1 /IACCEPTSQLSERVERLICENSETERMS

database:mssql:list_jobs

Anonymous (anonymous@undisclosed.example.com) — 2020-12-02T14:35:00+00:00

MSSQL - Query Jobs with Owners SELECT s.name AS JobName, l.name AS JobOwner FROM msdb..sysjobs s LEFT JOIN master.sys.syslogins l ON s.owner_sid = l.sid WHERE l.name IS NOT NULL ORDER by l.name USE msdb ; GO EXEC dbo.sp_manage_jobs_by_login @action = N'REASSIGN', @current_owner_login_name = N'sa' @new_owner_login_name = N'QUICK\Pinal', ; GO

database:mssql:optimize_instance_shpt

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Optimize SQL instance for SharePoint Alrighty then! You've succesfully installed SQL and now you need to install Sharepoint and “hook it up” to Your fresh new instance. Well STOP here and do continue reading this. It just might save You a lot of fuss later.

database:mssql:start

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

MSSql * Backup MSSQL to OneDrive * Find gaps in sequences in SQL tables * Get MSSQL serial * Grant MSSQL instance service access to new data folder * Install MSSQL on Windows Server Core * MSSQL - Query Jobs with Owners * Optimize SQL instance for SharePoint * Sum datetime difference in MSSQL

database:mssql:sum_datetime_diff

Anonymous (anonymous@undisclosed.example.com) — 2019-10-31T09:04:00+00:00

Sum datetime difference in MSSQL Get the SUM of seconds: SET @Seconds = SELECT SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME])) Turn that into a datetime object: SELECT CONVERT(VARCHAR(8), dateadd(SECOND, @Seconds, 0), 108) Or as 1 query: SELECT CONVERT(VARCHAR(8), dateadd(SECOND, SUM(DATEDIFF(SECOND, [START_TIME], [END_TIME])), 0), 108)