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 ...
- 5,907
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.
...
- 167k
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 ...
- 759
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 ...
- 67.7k
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), ...
- 179k
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 ...
- 735
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.6k
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,600
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 ...
- 96.2k
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 ...
- 8,277
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.3k
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 ...
- 119
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.8k
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,639
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 ...
- 81.3k
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.8k
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 ...
- 37.6k
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.5k
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 ...
- 453
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 ...
- 175
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 ...
- 271
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,539
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.5k
7
votes
Migrating BLOB in Oracle into PostgreSQL
From a conceptual point of view the equivalent to Oracle's BLOB datatype is bytea in Postgres.
bytea only allows you to read and write the complete data (you can't just retrieve 100KB from a 1GB ...
- 78.4k
7
votes
How do I REPLACE values in column after querying
You can use CASE for what you're using the replace for. Also, I've modified the WHERE to be more streamlined;
SELECT
CASE
WHEN column = '1' THEN 'ABC'
WHEN column = '2' THEN 'DEF'
WHEN ...
- 578
7
votes
Migrating Data from SQL Server 2005 to 2016
Max gave a decent answer which I will upvote once I'm done typing this alternate view up.
I am not a fan of restoring system databases when doing an upgrade migration and I prefer doing migrations ...
- 18k
Only top scored, non community-wiki answers of a minimum length are eligible