Questions tagged [automation]

The tag has no usage guidance.

Filter by
Sorted by
Tagged with
19
votes
2answers
9k views

Is there a cool way of performing CU updates for SQL Server on hundreds of machines?

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 ...
7
votes
3answers
11k views

Restore All Databases Script

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 ...
7
votes
2answers
1k views

How can I install the SQL Server PowerShell module on an offline machine?

I have some SQL Server machines that cannot connect to any external sites. I have been using PowerShell more and more when managing databases, so I definitely need this module to be installed so ...
5
votes
3answers
39k views

script to show all the permissions for a table

inside my database I have a procedure called spGen2_tblIE_SchedProcess_Update. when I run this script select * from sys.objects where name = 'spGen2_tblIE_SchedProcess_Update' Inside the same ...
4
votes
2answers
7k views

How to find Oracle's installed version and release from a program?

I need to determine from a program what version of Oracle is installed in each of the Oracle Homes on a server. As there may not be any databases created in the Home yet, I need to be able to do this ...
4
votes
2answers
1k views

how to script out the user defined table types?

I can Get name and definition of all table types using either of the following scripts: SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED IF OBJECT_ID('TEMPDB..#RADHE') IS NOT NULL DROP TABLE #...
3
votes
2answers
2k views

how to list all features installed in a sql server instance?

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 ...
3
votes
2answers
514 views

updating statistics using STATS_STREAM or WITH FULLSCAN?

the syntax for creating statistics is: -- Syntax for SQL Server and Azure SQL Database UPDATE STATISTICS table_or_indexed_view_name [ { { index_or_statistics__name ...
3
votes
3answers
700 views

Automated regular tests of your backups

I was doing some investigation on how other DBAs go about performing automated regular tests of their database backups to ensure recoverability in case of disaster and I'm quite disappointed at what ...
3
votes
1answer
1k views

automatically change value on the table mysql

i have table invoice in my database that contain atributes 'due date', 'date' and 'status'. What i want is data on 'status' change automatically. Example, status (unpaid change to expired) when date ...
3
votes
2answers
661 views

What are points to consider when replacing the SQL Server service account?

In What are the implications of changing the SQL Server Service Logon Account? it mentions that when replacing the SQL Server service account, the service will need to be restarted. But there is more ...
2
votes
1answer
149 views

Trace flag is running, but not message when deadlock happens

Trace flags are used to temporarily set specific server characteristics or to switch off a particular behavior More information on the trace flags are here DBCC TRACESTATUS this above, is the ...
2
votes
3answers
6k views

Script to backup all databases

I'm working on automating backups of SQL Server databases on SQL 2008 R2 Express. These backups are initiated daily by the Windows Task Scheduler, which execute a batch file, which in turns executes a ...
2
votes
1answer
39 views

How to use grouping sets in this query that shows database sizes - adding the last time it was updated

I have the following query that shows me the database sizes, including log dna data files. SELECT [Database Name] = DB_NAME(s.database_id), [Type] = CASE WHEN s.Type_Desc = 'ROWS' THEN 'Data ...
2
votes
1answer
248 views

How to automate the changes of parameters in ssis package run by a job?

I have a job that runs a package with specific parameters as you can see on the picture below: I want to set up those parameters (the 2 dates in yellow) to 1900-01-01 instead of 2019-02-27. I need ...
2
votes
1answer
2k views

How to configure AutoMySQLBackup?

How I can configure AutoMySQLBackup to backup some selected tables ones per week while the other tables will be backed up every day? Thanks. P.S. My google search ninja skills is not working. :)
2
votes
0answers
927 views

How do I create an automated SQL Server restore script for multiple backups? [duplicate]

I basically have 6 daily SQL Server backups (.bak) that need a restore everyday. I want to automate this process by having the SQL Agent run the script daily. I have the following restore script ...
1
vote
2answers
4k views

is it possible to alter the primary key of a table (from non clustered to clustered) in the subscriber only and not break the replication?

I have a table that has an clustered unique index and a non clustered primary key with the same structure as the index. IF OBJECT_ID('[dbo].[tblBAccountHolder]') IS NOT NULL DROP TABLE [dbo].[...
1
vote
2answers
225 views

how to find out how long the last DBCC checkdb took to complete?

In most of my servers I have dbcc checkdb scheduled to run. I can get the last known good DBCC Checkdb through DBCC DBINFO(). I can read the tracks left on the sql server log and I get something ...
1
vote
1answer
207 views

Having the backup file, how to work out the total disk space required to do the restore?

I have a nice query that gives me the date of last full backup, size and location, even when I backup to multiple backup files. what I don't have is an estimate of the total backup size (for each of ...
1
vote
1answer
7k views

How to automate CSV-exports of queries in PL/SQL Developer (Oracle)?

I'm no pro in Oracle or PL/SQL Developer (I mostly use and admin mySQL and MS SQL Server). I'm not an admin of any sort, but have read access to many tables in Oracle via PL/SQL Developer. I need ...
1
vote
1answer
69 views

Get a list of Databases which were not modified with in a date range - SQL server [closed]

can we get an output for list of databases which were not modified for last two months in a server and based on that we have to delete all of them. I have tried below is the query: --identify when a ...
1
vote
1answer
170 views

Zapier & MS SQL - Use azure DB to increase security?

I'm interested in using Zapier to automate some processes that should be fired upon certain records appearing as the result of a custom query. Zapier seems to be a good tool as it can do things based ...
1
vote
1answer
139 views

how to automate when altering jobs in Always on availability groups?

I often have to alter jobs i.e. adding steps, changing the stored procedures the job calls, etc. and I always need to do the same thing on all servers in my availability group. when dealing with jobs,...
1
vote
1answer
51 views

Unable to replicate schema changes in transactional replication

I have implemented a transactional replication on one of my dbs. In the documents provided by Microsoftsql (here and here), I should be able to change the schema and that these changes will be ...
1
vote
1answer
61 views

How can I script administrator permissions on a SSAS server?

I am an administrator for a bunch of SSAS Instances. Sometimes I need to install or migrate SSAS instances to different machines or even different AD domains (we have a couple of different AD domains ...
1
vote
1answer
42 views

Mongodb automatic Replica Set creation with JS driver

The MongoDB docs state that one can use the mongo client console to initialize replica sets by running rs.initiate([config]). However, I don't want to have to manually use the command line for this. ...
1
vote
1answer
92 views

PostgreSQL - automating database grants [closed]

We're managing about 200 instances of postgres on Amazon RDS. The problem that we're constantly experiencing is that our "DBA" accounts can't access stuff. Basic stuff. Tables, views, etc. Is there ...
1
vote
1answer
64 views

Naming convention for automated table outputs (processed with Python)

I have several tables that I process nightly with Python. For example, I have read-only access to an incidents table. I process the table with a specialized Python library called ArcPy. The script ...
1
vote
3answers
465 views

Any script to auto test tune and benchmark mysql config?

I'm looking or willing to do a script to benchmark mysql config variations for a specific system. A script that would change certain mysql config values and do a sysbench, save results, change ...
1
vote
0answers
11 views

Is there some parameter/flag I can use to make pgAdmin 4 update silently without the GUI?

Every single time pgAdmin 4 has an update, I have made it so that it grabs the latest installer (this is on Windows) and puts it on my desktop, then notifies me. I then have to manually run this GUI ...
1
vote
1answer
251 views

How to automate basic ddl scripts execution and management [closed]

Looking for suggestions (A Script, third party tool, software etc.) which can help automate some of the administration tasks around execution of DDL scripts across environments. Database is usually ...
1
vote
1answer
88 views

How to upgrade a MySQL database using numbered scripts based on a version field

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 ...
1
vote
3answers
4k views

How can I use Invoke-SqlCmd with variables to output the SQL command only or some similar method?

I have a big *.sql file that I run with Invoke-Sqlcmd and variables to do some tasks, and when I have an issue, it's a pain to debug because of the variables and I want to do logging of the SQL script ...
1
vote
0answers
133 views

Data warehousing (ETL): Scheduled data migration from MySQL to PostgreSQL

I've been assigned task of data warehousing for Reporting and data analysis. Let me first explain what we are going to do. Step 1. Replicate production server MySQL database. Step 2. Scheduled ETL: ...
1
vote
0answers
249 views

Can you script the MDS database upgrade after SQL Patch applied

I have recently automated the SQL patching of ~200 development servers. Currently we have 6 servers that run the Microsoft Master Data Services feature. After a SQL patch is applied there is a ...
1
vote
0answers
1k views

Start/stop postgresql with pg_ctl or service without root access on RHEL6

We plan to use postgresql on RHEL 6. DBAs won't have root access but they will need to start/stop the databases. They'll be able to do so with pg_ctl as postgres. However databases should also start ...
0
votes
2answers
81 views

Help error message inside dynamic T-SQL

I have a requirement to come up with reporting for indexes for our whole production environment: I found the below script online and modified it according to my requirement and trying to execute it ...
0
votes
1answer
337 views

Alternate of trigger in SQL server

is there any other way beside creating a trigger in MSDB system tables to invoke a particular job when any job failed? Basically, I want a particular job ran automatically only if any of the other ...
0
votes
2answers
5k views

T-SQL: How create job that run monthly

I already know how to schedule monthly jobs using SQL Server Agent > Jobs, I now would like to learn how to schedule such job using T-SQL. I know how to schedule a job daily at a specific time: ...
0
votes
1answer
161 views

is there any reason to explicitly grant the server permission CONNECT SQL?

I have been using this script from Kenneth Fisher to get the server permissions. -- Server Permissions SELECT Grantee.principal_id AS GranteePrincipalId, Grantee.name AS GranteeName, Grantor.name ...
0
votes
2answers
606 views

StorageEngineUsed set to TabularMetadata. For databases in this mode, you must use Tabular APIs to administer the database

I am an administrator in SSAS. I have many data warehouse servers. In some servers I have SQL Server and SSAS on the same machine. I successfully backup the SSAS databases and even check if the ...
0
votes
1answer
63 views

Application/MS SQL Server Express Installer

We're developing a small(ish) application that collects and stores data. It's low-throughput, low-volume, but needs to be able to archive and retrieve data from (almost) arbitrarily earlier times, ...
0
votes
1answer
91 views

how to script server side permissions - specifically EXECUTE ON SYS.XP_PROP_OLEDB_PROVIDER?

I could not script the following server side permissions: GRANT VIEW DEFINITION ON LOGIN::[site_helpdesk] TO [Radhe] but thanks to the question below I found a solution to that: How to script out ...
0
votes
1answer
289 views

Autopostgresqlbackup - DBEXCLUDE doesn't works

I'm trying to exclude some databases using the DBEXCLUDE option,but there's daily backups anyways. # =============================== # === Debian specific options === #================================...
0
votes
1answer
3k views

SQL Mail Failing To Send

SQL Sever 2016 with the latest updates. Running on WS2012R2. Following error message is received: The mail could not be sent to the recipients because of the mail server failure. (Sending Mail ...
0
votes
1answer
22 views

Is there a way to optimize the process of uninstalling and installing SSMS?

in terms of patching sql server 2016 to the latest cumulative patches, I found out that stopping the services helps avoiding lots of inconveniences, specially if there is log shipping and replication ...
0
votes
1answer
56 views

MariaDB Master / Slave failover / failback automation

Good day all, I'm hoping someone might be able to point me in the right direction. I'm working on a new dual data center project, and I'm trying to come up with a plan for failover (for patching and ...
0
votes
2answers
52 views

Delete old records based on its identifier

For example, I have a table similar to this: deviceid | servertime 1 2019-05-13 12:00:00 1 2019-05-14 12:00:00 2 2019-05-13 13:00:00 2 2019-05-12 13:00:00 2 ...
0
votes
0answers
10 views
+50

is there a DDL generator out there where you can start with CSV/TSV to create DDL for Cassandra

Is there a DDL generator out there where you can start with CSV/TSV (or maybe even JSON) to create DDL for Cassandra?