61 votes
Accepted

Can I write portable SQL statements?

No, not for any significant and practical amount of code anyway. You can try to hew to standards (e.g., use COALESCE rather than ISNULL) but there are too many differences, big and small. Off the ...
44 votes

Can I write portable SQL statements?

There are ANSI SQL standards, see for example the part on Interoperability and standardization in the Wikipedia article. Problem is, few actually follow these standards, which are often written and ...
  • 646
40 votes
Accepted

Convert Postgres TIMESTAMP to TIMESTAMPTZ

Pay attention that the correct time zone (UTC in your case) is applied during the conversion. If you are not explicit about this, the time zone of the current session is assumed - typically not UTC. ...
25 votes
Accepted

Backup/Restore Users/Passwords/Privileges

The 'mysql' database contains users/privileges/passwords. So take the dump of mysql database along with other databases mysqldump [options] --all-databases > all_databases_dump.sql mysqldump -u ...
24 votes
Accepted

Is there a penalty for using BINARY(16) instead of UNIQUEIDENTIFIER?

Should I be concerned? Well, there are a couple of things here that are a little concerning. First: while it is true that a UNIQUEIDENTIFIER (i.e. Guid) is a 16-byte binary value, it is also true ...
22 votes
Accepted

Best way to migrate a huge SQL Server database with low downtime over network

Straight backup and restore is obviously out. I also wouldn't consider replication of any kind. Database mirroring is relatively simple to set up, but requires real-time connectivity between the two ...
21 votes
Accepted

Migrate from SQL Server 2000 to 2012 without a 2005 or 2008 instance

No, there is no workaround to upgrading a database directly from 2000 to 2012. Since you don't have a ton of data, you can do all kinds of things to move the data (but not the database as a whole), ...
19 votes
Accepted

Schema Migration: SQL Server Data Tools vs Liquibase and Flyway

SSDT is comparable to Liquibase/Flyway as it does what they do but by taking a different approach. With SSDT you have the development environment so you get things like go to definition, find ...
18 votes

Best way to migrate a huge SQL Server database with low downtime over network

I recently migrated 15tb across 6 databases using mirroring. Very simple and worked perfectly with just a couple seconds of failover time. Edits: I had two new virtualized SQL Servers. The ...
  • 36.7k
16 votes

Best practices to maximize portability in SQL Server 2016

User Aaron Bertrand made some comments that align well with my thoughts on your question. This is more of a frame challenge than an answer to your specific question, but I think it's valuable to ...
  • 28.5k
15 votes
Accepted

SQL Server data folder access rights

Have you looked under Advanced Security Settings? The arrows point to the modifications I've often had to specifically apply when installing SQL Server on Windows 2012 R2. For the SQL Server Root ...
  • 9,560
15 votes
Accepted

ERROR: column "field" must appear in the GROUP BY clause or be used in an aggregate function

This isn't my query, I'm translating a system prototyped in MySQL to PostgreSQL. This query does work in MySql. Does that constitute a bug in MySql, or a shortcoming of PostgreSQL, or just a ...
14 votes
Accepted

Archiving of old data

I think the answer to many of your questions is that it depends. What performance problems are you having? It seems unusual that a database would have performance problems just from growing to 250GB ...
12 votes

Best practices to maximize portability in SQL Server 2016

Do not enforce STD SQL. Decide first which DBMS you will use according to the needs of your project, and take advantage of it.
  • 23.2k
11 votes

How to export an image column to files in SQL Server?

I had the same problem with the extra 4 bytes being added to the beginning of all of my files as well. Instead of using -N option in my bcp command, I changed it to -C RAW. When you do this, bcp ...
11 votes

Move live SQL Server database to a new server

since you are using SQL Server 2008 Web edition, your best bet is to use LOG SHIPPING. Your database size is 100GB, so initializing the secondary from a backup should be considered. Dont use GUI to ...
  • 61.6k
11 votes

Restore a SQL Server 2012 backup file in SQL Server 2016/2014

Yes this will work. You can always go forward, you can't go backwards, at least not with backup and restore. With older version eg SQL 2000, SQL 2005 sometimes you have to do a stepped upgrade, ie ...
  • 10.3k
10 votes

Archiving of old data

I would prefer having a history schema or a second historical database over a linked server any day. It saves license costs is easier to manage and query. You can then also use simpler schema and drop ...
  • 4,619
10 votes

Best practices to maximize portability in SQL Server 2016

Not really. There is SET FIPS_FLAGGER 'FULL'. This prints out a warning for non standard SQL - but some caveats are I am unsure what specific standard this uses (and suspect it may be SQL 92) From ...
  • 80.4k
9 votes
Accepted

SQL Server 2000-2014 migration using 2008 Express as intermediary

You could use Express, but it does not have all features that the full versions do and cannot handle databases larger than 10GB each. Please note the 10 GB restriction applies to the total size of ...
9 votes

Moving Databases to New Datacenters

Your current situation : 90 GB database that you want to move to a new data center. Database is in simple recovery. You are thinking of using T-Rep to keep the data in sync. There are many tables in ...
  • 61.6k
9 votes

Quickest way to migrate mirroring to AG

If you want to ensure you minimize the amount of time you are without HA during your migration, here's a trick I recently used: Set up the AG with a "dummy" database so that your AG is ready in ...
  • 15.9k
9 votes

Migrating SQL Server databases

Basically yes, all you'd need is to backup and restore the databases. However, there's a bunch of stuff that won't do. Server level settings (MAXDOP/CTFP, etc.) Agent Jobs Maintenance plans Linked ...
  • 36.7k
9 votes
Accepted

Move data from a table to another without losing data

Consider creating a new table with the partitioning strategy you want, and add a view atop both tables that does a union all. Have people use the view, and write instead-of triggers against the ...
  • 42.4k
9 votes

MariaDB - MySQL - ALL-DB Import - Table 'user' already exists

As someone already mentioned, this has to do with the mysql.user table being changed to a view in 10.4. The problem and solution is documented on the MariaDB website in MDEV-22127. The solution is to ...
  • 209
8 votes

How to move a database from SQL Server 2012 to SQL Server 2005

In addition to the methods already suggested here, you could also try creating a BACPAC file and import it into your destination. This is similar to how Microsoft recommends migrating your databases ...
8 votes

Archiving of old data

In my experience a second database would be the preferred choice for two reasons. You can restore the data from an historic backup then drop the tables and indexes you don't need. You can move this ...
  • 177
8 votes
Accepted

Postgres 9.4 wal_level=logical increases disk space

This is an AWS DMS issue. DMS has recently added a feature WAL heartbeat [1] (run dummy queries) for replication from a PostgreSQL source so idle logical replication slots do not hold on to old WAL ...
7 votes
Accepted

moving large PostgreSQL/PostGIS database

I would recommend dumping the 9.1 database from your new 9.3 server like this: pg_dump -h remoteserver -U remoteuser remotedbname -Fc -f my_old_server_backup.dump I recommend using the 9.3 pg_dump ...
  • 3,519
7 votes
Accepted

Set "Encryption Enabled" to FALSE...what can go wrong?

Do not disable TDE. Besides being a lengthy size-of-data operation, it was established initially for a reason and you may be breaking whatever compliance/operations reason exists for TDE. Rather set ...
  • 51.4k

Only top scored, non community-wiki answers of a minimum length are eligible