5
votes
What if we do not use MAX function
Sometimes you get lucky. Your old query would be assigning the variable @MaxId to the last result it got from your query (because it was constantly reassigning the variable per result)
SELECT [Id] ...
- 2,632
5
votes
Accepted
What is Oracle Tablespace equivalent in SQL Server
Tablespaces are physical structures on disk, directories or files, that hold table data, indexes, and other things. One tablespace can hold objects that belong to multiple tables. Each tablespace can ...
- 24.3k
4
votes
Why do indexes, statistics, and full text need to be rebuilt/updated/repopulated after migration?
I'm going to address 1 and 2:
Who says that you should rebuild indexes and update statistics? There is nothing inherent to a migration regarding these two operations.
Statistics is included in the ...
- 16.5k
3
votes
Accepted
MySQL | Set Column default with value of an existing database that is a SELECT result
No, you can't use a subquery in a DEFAULT clause.
In MySQL 8.0.13, they added a feature to allow a DEFAULT clause to be an expression, but it still can't be a subquery:
https://dev.mysql.com/doc/...
- 12.6k
3
votes
What if we do not use MAX function
A SELECT statement does not have a guaranteed order by unless you ask for it. In the case it works, it fills the variable 'accidentally' with the last value, as that seems for SQL the most efficient ...
- 1,795
3
votes
Upgrate postgresql database
You are using pg_restore incorrectly. -f is not the input file, but the output file. pg_restore is waiting for the dump on standard input. On top of all that, pg_restore cannot be used to restore ...
- 42.6k
2
votes
How do I migrate a Northwind DB to Cassandra?
Migrating data from a relational database to Cassandra isn't very straightforward since it requires data modelling to redesign the tables to fit your application's needs.
For most cases, the main ...
- 2,697
2
votes
Accepted
MySQL InnoDB migrations custom implementation: How to deal with DML statements which trigger a commit in the background?
I think you mean DDL. DML statements are like SELECT/INSERT/UPDATE/DELETE, and I can't think of one of the DML statements that cause an implicit commit.
If you're concerned about a crash partway ...
- 12.6k
2
votes
Accepted
How to replicate between On-Premises and Azure in Mongodb
Basically, you add those three nodes as secondary to your current on-premise replica set. (rs.add(FQDN))
Not necessary to be hidden, but set priority lower than nodes at the premises, to prevent ...
- 5,608
2
votes
Should I backup and restore the SMK when performing a server upgrade and migration?
The service master key should be backed up and stored in a secure, off-site location. Creating this backup should be one of the first administrative actions performed on the server.
Backup:
https://...
- 4,045
2
votes
How to migrate sql server (large db size) and retain all settings and backup/job history?
I would suggest to use dbatools.io cmsdlet Start-DbaMigration (https://docs.dbatools.io/Start-DbaMigration) to migrate sever objects like logins, linked servers, etc.
It will create a copy of your ...
- 319
2
votes
Error Code: 2068. LOAD DATA LOCAL INFILE file request rejected due to restrictions on access
In case this helps someone else. Posting the solution:
It turns out that my workbench client requires this parameter :
OPT_LOCAL_INFILE=1
Refer to this blog here:
https://bugs.mysql.com/bug.php?id=...
- 21
2
votes
Accepted
Create non clustered indexes in background without awaiting the outcome in Sql Server
What you could do is to break down your script into multiple scripts/queries:
Script does something
Non-clustered index creation
Script continues to do something
Then the solution would be to use ...
- 5,182
1
vote
Accepted
How to check at what level/version is Db2 database?
You can look in sysibm.sysversions table with SQL by creating appropriate queries depending on which detail you seek.
After you successfully run upgrade database the column VERSIONBUILDLEVEL will show ...
- 180
1
vote
Accepted
Migrating legacy data from SQL Server 2000 to 2019 , log block error - is there a painless way of moving over tables with autoinc identity columns?
I would attempt to get SQL Server to rebuild the transaction log. Based on the error message, that might sort out the situation.
You first use sp_detach_db to detach the database. It is now very ...
- 16.5k
1
vote
Accepted
Move timescaledb hypertable from a postgresql server to another one
First, let's make sure that you are getting any data from the source:
psql -U user -h source_server -d source_db -c "COPY (SELECT * FROM <<source>>
LIMIT 5) TO STDOUT WITH CSV ...
- 61
1
vote
MySQL Database migration for 1.5 TB database size from MySQL 5.6 to MySQL 8.0.25
You should look into using pt-archiver
I wrote a script recently to copy 170 millions rows from one table to another
I used these options
PT_ARCHIVER_OPTS="--analyze=sd"
PT_ARCHIVER_OPTS=&...
- 179k
1
vote
Migrate SQL Server 2012 databases on premises to Azure SQL
Is SQL Server managed instance suitable
From What is Azure SQL Managed Instance?:
SQL Managed Instance has near 100% compatibility with the latest SQL Server (Enterprise Edition) database engine, ...
- 31.9k
1
vote
Tables character-set migration without huge downtime- mysql
If downtime is a concern, then use one of the online schema change tools:
pt-online-schema-change. You can perform most types of ALTER TABLE statements with nearly no downtime. See more of my notes ...
- 12.6k
1
vote
Accepted
Postgres nondeterministic collation performance
I ended up finding this article with clear performance and reasoning behind the case insensitive pattern matching configuration in PostgreSQL.
https://www.cybertec-postgresql.com/en/case-insensitive-...
1
vote
Migrate databases between Azure managed Instance and Azure SQL VM
If this is a one off migration, where you won't need to keep the SQL Server databases in sync with the MI databases, you could potentially use snapshot replication.
Unlike transactional replication, ...
- 9,802
1
vote
MySQL 8.0.x: Discard temporary table left behind during a pre-migration (5.7.x) crash when the parent schema is already dropped
I had to create an empty directory with the name of the database in the data directory, only then was MySQL happy to drop it.
- 133
1
vote
SQL Managed Instance - Cross Subscription Restores/Migration
Bradley Ball has a really good walk-through of the steps required to restore a Managed Instance database between 2 subscriptions in Cross Subscription Prod Refresh on SQL Managed Instance using TDE ...
- 127
1
vote
Convert Postgres TIMESTAMP to TIMESTAMPTZ
Would just like to add a cautionary note to Erwin Brandstetter's answer (couldn't comment because not enough reputation): whether SET DEFAULT now() is a good default really depends on the specific ...
- 11
Only top scored, non community-wiki answers of a minimum length are eligible