Database Administrators Stack Exchange
Database Administrators Stack Exchange

Questions tagged [migration]

the process of moving databases or data from one place to another

Examples of migrations:

  • Moving databases across machines or instances (e.g. host1 -> host2)
  • Moving databases across different platforms (e.g. MySQL -> SQL Server)
  • Moving large amounts of data in a one-time change (e.g. ETL)
917 questions
169
votes
13 answers

How can I move a database from one server to another?

How can I move MySQL tables from one physical server to another? Such as this exact scenario: I have a MySQL server that uses innodb table and is about 20GB size. I want to move it to a new server, what's the most efficient way to do this?
John
  • 1,851
  • 2
  • 14
  • 10
81
votes
5 answers

Are SQL Server in-place upgrades as ill advised as they used to be?

I have been working with SQL server on and off since SQL Server 6.5, the old advice that still rings in my head was never to do an in-place upgrade. I'm currently upgrading my 2008 R2 DEV and TEST systems to SQL Server 2012 and need to use the same…
DamagedGoods
  • 2,591
  • 6
  • 34
  • 48
59
votes
1 answer

Best practices for schema changes and data migrations to a live database without downtime?

How do you make schema changes to a live database without downtime? For instance, lets say I have a PostgreSQL database with a table including various user data like email addresses etc, all associated with specific users. If I wanted to move the…
Dan Leary
  • 693
  • 1
  • 6
  • 5
38
votes
5 answers

Can I write portable SQL statements?

I am wondering if it is possible to write SQL statements that are 100% interoperable with most or all databases including: MariaDB/MySQL/Percona Postgres Microsoft SQL Oracle SQLite (Eg, can I just follow a specific SQL standard? Eg, is there…
James Shewey
  • 495
  • 5
  • 8
35
votes
2 answers

Convert Postgres TIMESTAMP to TIMESTAMPTZ

I have a decently-sized (~50k rows) time-series database running on Postgres, with some other structured data (in another database instance) which is much smaller. Stupidly, when I initially designed the thing I had all the fields as TIMESTAMP…
GTF
  • 453
  • 1
  • 4
  • 6
34
votes
4 answers

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

What are my options if I need to move a database from SQL Server 2012(32bit) to SQL Server 2005(64bit)? I know I cannot: restore a backup of the database on SQL Server 2005 detach & attach I know I can: use import data wizard, and I tried it on…
Greg Bala
  • 1,025
  • 3
  • 10
  • 20
31
votes
5 answers

Archiving of old data

We're currently running into some performance problems since our database is getting too big. There are data stored from the last 10 years and I don't see a reason why the data older than 2 years have to be stored in the same tables as the new…
xeraphim
  • 597
  • 2
  • 5
  • 11
28
votes
3 answers

Backup/Restore Users/Passwords/Privileges

I am moving from one server to another and I want to backup all databases + users/privileges/passwords from my MySQL Server. I found to backup a database using mysqldump, but I cannot figure out, how to backup all users and the given privileges. Is…
Nidhoegger
  • 383
  • 1
  • 3
  • 7
26
votes
2 answers

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

Problem definition Our database server needs to be transferred to an other datacenter. It runs on Microsoft SQL Server 2012 Enterprise (64-bit) and contains two databases of about 2TB and 1TB. Having little to no downtime for this would be…
Val F.
  • 363
  • 1
  • 3
  • 6
25
votes
3 answers

Change existing column in PG to auto-incremental primary key

I have a database in Postgresql, which was migrated from SQL Server (only data). On SQL Server, a table from this database has these columns: measure_id datum measure where measure_id is auto-incremental primary key, datum is datetime and measure…
zetah
  • 355
  • 1
  • 3
  • 5
24
votes
4 answers

What is your workflow for planning a data migration?

So many times I've been brought in at the end of a software development effort and been told something like "okay, we've got all this new code and it requires tables to change and data to be migrated". It seems like every time it's a one-off,…
randomx
  • 3,944
  • 4
  • 31
  • 44
23
votes
1 answer

Upgrade SQL Server 2000 database to 2008 R2 and enable new features

I have recently upgraded SQL Server 2000 database to 2008 R2. What I did was: Shutdown SQL Server 2000 (express) service on old machine, Move datafiles (mydatabase.mdf and mydatabase.ldf) to new machine, Run SQL Server Management Studio…
saelic
  • 345
  • 1
  • 3
  • 9
23
votes
6 answers

Need to migrate SQL Server to MySQL

I have a database on SQL Server 2008 on a Windows server and I want to move all of the data to a MySQL database on a Ubuntu server. I have tried using the SQL Server Import and Export Wizard with the MySQL ODBC driver, and it correctly accesses both…
murgatroid99
  • 371
  • 1
  • 2
  • 8
22
votes
3 answers

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

I've recently inherited a SQL Server database that uses BINARY(16) instead of UNIQUEIDENTIFIER to store Guids. It does this for everything including primary keys. Should I be concerned?
Jonathan Allen
  • 3,612
  • 7
  • 25
  • 25
21
votes
6 answers

How to solve UTF8 invalid byte sequence copy errors on a restore, when the source database is encoded in UTF8?

I was given the task to migrate a PostgreSQL 8.2.x database to another server. To do this I'm using the pgAdmin 1.12.2 (on Ubuntu 11.04 by the way) and using the Backup and Restore using the custom/compress format (.backup) and UTF8 encoding. The…
pedrosanta
  • 3,463
  • 3
  • 17
  • 7
1
2 3
…
61 62