My company has grown from 50 SQL Servers to over 200 overnight in a merger acquisition. HELP! My questions are: - How can I perform CU updates on this many servers and still have a life and keep my sanity? SCCM doesn't seem to be able to perform CU updates. - Has anyone else been able to perform CU updates on 100s of servers? - What are some other alternatives?

I have a database that contains a version table with one value stored in it which is the database version number. I would like to be able to update the database version automatically using numbered scripts. If the database is version "6" and I have a dir of scrips numbered inconsistently as per the below: 001.sql, 02.sql, 3.sql, 4script.sql, 5.sql, 6.upgrade.sql, 7.sql, 8.data.sql, 009.updated.sql What is the best way to only run scripts which are numbered greater than the version number and to do this in sequence? Should I use an SQL script or create a bash script? I am a little stuck on how to approach this so any pointers or advice will be much appreciated...

Trying to export SQL Objects via DBAtools in Powershell. Get-DbaAgentJob -SqlInstance 'TestServer01' -Job 'mydatabase-daily-backup' | Export-DbaScript -FilePath C:\temp\mydatabase-daily-backup.sql The same script working fine and exporting the job details. But, not working on a few servers, and returns the below error/warning > WARNING: [06:05:42][Get-DbaAgentJob] Failure | The value's length for key 'Data Source' exceeds its limit of '128'.

How I can configure AutoMySQLBackup to backup some selected tables ones per week while the other tables will be backed up every day?

There are other Installation related questions with similar details here and here . I could not find anything related to listing the installed features in sql server 2016 though. I can do it manually looking at the logs - as per the info below, but I would like to automate it. After installing SQL Server 2016 I get a log file - located somewhere in these folders: C:\Program Files\Microsoft SQL Server\130\Setup Bootstrap\Log\20200109_205540 A file which the name starts with Summary_ +my_server_name in that file, amongst other things I can find a list of the Sql Server Features installed: After installation, later on when the systems are already in use, logins and permissions applied, firewall fixed, etc. Is there a simple way to get hold of the list of features installed on a sql server instance?

There is a modification project in our Process Plant related to SCADA application (Process Automation System) and I am looking for reliable advice/ suggestions from experienced members related to SQL Database Management. **Background:** We have multiple Workstations each running Stand-alone SCADA application with SQL Express for writing Real-time Alarms to SQL DB and occasional reading. We need to forward these Alarms SQL DB (Table only) from all Workstations to Centralized Station, merge all Alarm Tables into one, and then import to SQL Express DB on Centralized Station for Read-Only Purpose. These tasks need to be automated and triggered at appropriate frequency (for eg. every 1 Hour). Please note that all Workstations are Workgroups and Stand-alone. Please also note that communication between Workstations and Centralized Station is strictly uni-directional i.e. Workstation -> Centralized Workstation (so no option for SQL Server Replication). *** ### My Rough Plan #### On Each Workstation: 1. Duplicate the Production_DB to Offline_DB (in same SQL Server Instance) 2. Synchronous/Asynchronous update of Alarms Table from Production_DB to Offline_DB. 3. Periodic Conversion of the Alarms Table from Offline_DB to CSV. 4. Forwarding the CSV to Centralized Workstation. #### On Centralized Workstation: 5. Creating Workstation_DB for each Workstation in SQL Server instance. 6. Converting and importing CSV file in each Workstation_DB to Alarms Table. 7. Creating one Central_DB and merging all Workstations Alarms Table to one Table. 8. Assign Central_DB to SCADA Application on Central Workstation. Please advise if the rough plan needs correction or optimization. I have no background knowledge of SQL scripting and I would be grateful if someone also guide me through the execution of each step in SQL (excluding step 4 and step 8).

Working on a script to drop databases . In some of the environments our databases are regularly restored from live, replication (merge or transactional) established, processes run and tested, then, the databases need to be dropped, as all of the development has been deployed to live, now another cycle starts. as part of automating the dropping of the database so far I came out with this script below:

select
DROP_DB_SCRIPT='use master;' + char(13) 
+ case when source_database_id is not null  -- this is a database snapshot
     then '' 
      else 
	    ' alter database ' + name + ' set single_user with rollback immediate ' +              -- put db in single user mode 
	    case when (sb.is_published = 1 or sb.is_merge_published = 1) 
		     then 'EXEC sp_removedbreplication ' + '''' + name + ''''                           -- remove db from the replication 
		     else '' 
		end +char(13) 
  end  +char(13) +
' drop database ' + quotename(name) +char(13) 

,*
from sys.databases sb
where database_id >= 5
and is_distributor= 0

The script above will not do anything other than produce a script that I can use to drop a desired database or set of databases. it takes into consideration replication, (you need to remove the replication separately), and if the database is a database snapshot , which is a different thing . I have not considered the following: Should an OFFLINE database be SET EMERGENCY before dropping? the question here is: Is there anything, any check, that you can see I should have added but have not? I have dealt with 2 things: 1. database is a snapshot 2. database is involved in replication I have not dealt with: 1. Database is part of an availability group (in that case I would need to remove it from the AG first) 2. anything else that you can see?

I am migrating SQL Server DBs to a new instance. I have been told that it is possible to dynamically build a RESTORE script from available backups in the system catalog. Does anyone know of a sample script to do this? Thanks!

on this page: DDL Events you can see a complete list of the ddl events. these events are server or database things that happen - like - create a database, alter a sequence, update statistics, create a table, create a trigger, create an availability group, etc, the complete list you can find by running this query:

set transaction isolation level read uncommitted
set nocount off

drop table if exists #Radhe

;WITH DirectReports(name, parent_type, type, level, sort) AS   
(  
    SELECT CONVERT(varchar(255),type_name), parent_type, type, 1, CONVERT(varchar(255),type_name)  
    FROM sys.trigger_event_types   
    WHERE parent_type IS NULL  
    UNION ALL  
    SELECT  CONVERT(varchar(255), REPLICATE ('|   ' , level) + e.type_name),  
        e.parent_type, e.type, level + 1,  
    CONVERT (varchar(255), RTRIM(sort) + '|   ' + e.type_name)  
    FROM sys.trigger_event_types AS e  
        INNER JOIN DirectReports AS d  
        ON e.parent_type = d.type   
)  
SELECT parent_type, type, name  
 into #Radhe
FROM DirectReports  
ORDER BY sort  

SELECT parent_type = 0
      ,type='execute at the database level'
	  ,name='Events listed under DDL_DATABASE_LEVEL_EVENTS execute at the server (instance) or database level'
	  --,level=0
	  --,sort=0
UNION ALL
SELECT parent_type = 0
      ,type='execute at the server level'
	  ,name='Events listed under DDL_SERVER_LEVEL_EVENTS execute only at the server level'
--	  ,sort=0
UNION ALL
SELECT parent_type = 0
      ,type='------------------------------------------------'
	  ,name='-------------------------------------------------------------------------------------------------------------------------------------------------------------------------------'
--	  ,sort=0
UNION ALL
SELECT parent_type, cast(type as varchar(150)), name  
 from #Radhe

partial view: question is: what in this list I can use to "do somethings" before I restore a database? I can always do it manually but is there a way I could intercept the event of restore into a database, or restoring into a new database and take action before it happens? Actually the trigger does not get fired before It works afterwards, it just throws an error so rolls the change back. There is no way to make a DDL trigger fire before the action was executed, you can only catch it immediately afterwards in the same transaction. - thanks for the clarification to Charlieface in the comments.

I'm using SQL Server, and I received an error indicating that one or more of my SQL views is out of sync with its underlying SQL table. > 'MySQLServerName' returned data that does not match expected data > length for column 'MyColumnName'. The (maximum) expected data length > is 50, while the returned data length is 52. This occurs if you update the definition of the underlying SQL table, but forget to update the associated SQL view(s). A quick fix for this error is to run sp_refreshview : use MySQLDatabaseName go EXECUTE sp_refreshview N'MyViewName'; **But what if I have a long list of broken views, or I don't even know which views are broken, or I don't know which underlying table definitions are no longer matching those views? What if I want to save time by running sp_refreshview across every view in my database. What's an easy way to accomplish that task?**

while trying to prevent autogrowth from happening, I got this nice script here . however, if it does happen, I need to know how to find it out. that I can do using this very nice script by Max Vernon .

print @@servername + ' - ' + SUBSTRING(@@version,1,COALESCE(CHARINDEX('Copyright',@@version,0)-1,108))
						SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
						SET NOCOUNT OFF


/*
    Description:    display growth events for all databases on the instance
    by:             Max Vernon
    date:           2014-10-01
*/
DECLARE @Version NVARCHAR(255);
DECLARE @VersionINT INT;
SET @Version = CONVERT(NVARCHAR(255),SERVERPROPERTY('ProductVersion'));
SET @VersionINT = CONVERT(INT, SUBSTRING(@Version,1 ,CHARINDEX('.',@Version)-1));
DECLARE @cmd NVARCHAR(2000);
SET @cmd = '';
IF @VersionINT >= 9
BEGIN
    SET @cmd = 
'
DECLARE @trcfilename VARCHAR(1000);

SELECT @trcfilename = path 
FROM sys.traces WITH(NOLOCK)
WHERE is_default = 1;

IF COALESCE(@trcfilename,'''')  ''''
BEGIN
SELECT [Radhe]=
    '''''''' + @@SERVERNAME + '''''','''''' +
     DB_NAME(mf.database_id) + '''''','''''' +
     mf.name + '''''','' +
     CONVERT(VARCHAR(255), a.NumberOfGrowths) + '','' +
     CONVERT(VARCHAR(255), CAST(a.DurationOfGrowthsInSeconds AS decimal(38, 20)))
    FROM
    (
        SELECT
            tt.DatabaseID AS database_id,
            tt.FileName AS LogicalFileName,
            COUNT(*) AS NumberOfGrowths,
            SUM(tt.Duration / (1000 * 1000.0)) AS DurationOfGrowthsInSeconds
            FROM sys.fn_trace_gettable(@trcfilename, default) tt
            WHERE (EventClass IN (92, 93))
            GROUP BY
                tt.DatabaseID,
                tt.FileName
    ) a
    INNER JOIN sys.master_files mf ON
        (mf.database_id = a.database_id) AND
        (mf.name = a.LogicalFileName);
END
ELSE
BEGIN
    SELECT @@SERVERNAME, ''NO TRACE FILE'';
END
';
EXEC sp_executesql @cmd;
END
ELSE
BEGIN
    SELECT [SERVER NAME]=@@SERVERNAME, [Product Version]=SERVERPROPERTY('ProductVersion');
END

what is missing in and I would like to add is - when did the autogrowth happen? how can I find that out?

when I run the following script:

-- ——— SCRIPT GRANTS for Object Privileges—————

IF OBJECT_ID('[sys].[sysrowsets]') IS NOT NULL   GRANT CONTROL on [sys].[sysrowsets] to [db_myrole_BA]

I get this error message: Msg 15151, Level 16, State 1, Line 271 Cannot find the object 'sysrowsets', because it does not exist or you do not have permission. However: when I run this:

select radhe=OBJECT_ID('[sys].[sysrowsets]')

My original script comes from here:

-- SCRIPT GRANTS for Objects Level Privilegs
PRINT '-- ——— SCRIPT GRANTS for Object Privileges—————'
SELECT
[GRANTS for Object Privileges]='IF OBJECT_ID(''['+ sys.schemas.name + '].[' + sys.objects.name + ']''' + ') IS NOT NULL ' + CHAR(13) + SPACE(1) +
state_desc + ' ' + permission_name + ' on ['+ sys.schemas.name + '].[' + sys.objects.name + '] to [' + sys.database_principals.name + ']' COLLATE SQL_Latin1_General_CP1_CI_AS
from sys.database_permissions
join sys.objects on sys.database_permissions.major_id = 
sys.objects.object_id
join sys.schemas on sys.objects.schema_id = sys.schemas.schema_id
join sys.database_principals on sys.database_permissions.grantee_principal_id = 
sys.database_principals.principal_id
where sys.database_principals.name not in ( 'public', 'guest')
--order by 1, 2, 3, 5

How is it possible I am getting that error? I want to save all these permissions, so that when I restore the database - I can re-apply those permissions.

today I had to check if in-transit Encryption was up and running in a particular server (sql server) and if not then set it up. the way it is done is: > In SQL Server Configuration Manager, expand SQL Server Network > Configuration, right-click Protocols for , and then > select Properties. On the Flags tab, in the ForceEncryption box, > select Yes, and then select OK to close the dialog box. Restart the > SQL Server service. the picture below is after I restarted the sql server service . now, I am one DBA that likes DBA paradise, and all automation and things done through scripts. How could I get this done through scripts? partial solutions also valid. and it could be powershell,T-SQL, or any other scripting language.

How to automate an update in a table, after a change in another table ? Say (please see below) I have a table countries, and a table cities. How to make that, when adding a city in table cities, the total column is automatically updated (incremented for the corresponding city) in the table countries ? Kind of : --------- BEGIN; INSERT INTO cities VALUES ('Tokyo', 'Japan'); UPDATE countries SET total=1 WHERE name='Japan'; COMMIT; , but automatic, **do a countries.the_city.total++ when adding cities.the_city**. Countries: ---------- postgres=# SELECT * FROM countries; name | total -------+------- USA | 0 Japan | 0 (2 rows) Cities: ------- postgres=# SELECT * FROM cities; name | country ------+--------- (0 rows) Commands: --------- CREATE DATABASE geo; \c geo; CREATE TABLE countries ( name VARCHAR(15) PRIMARY KEY, total int ); CREATE TABLE cities ( name VARCHAR(15), country VARCHAR(15), FOREIGN KEY (country) REFERENCES countries(name) ); Context: -------- Standard SQL, or Postgres v16

I have here a simple PostgreSQL instance, generated by a build script. The goal is to make it automatically safe shutdown, if it has no new connection request for a while (like 5 min or so). What is the best way to do it? postgresql.conf and similars are surprisingly silent in such question.

I have a lots of tables in postgres with a lots of columns per each table. I need to write a program in such a way that it can identify the columns that can be a potential primary and foreign key pair. The way I'm achieving this right now is by iterating through every combination and comparing columns based on their distinct data. the pseudocode is something like this: for(int i=0;i