Tag Info

Hot answers tagged

59

Really Short Answer - In Place is okay. You can review your configuration afterwards and implement the best practices for SQL Server 2012. A Longer Answer on SQL Server Upgrades/Migrations So this is an opinion thing and there isn't a necessarily wrong or right answer but I prefer migration style upgrades over in-place for a lot of reasons. That being said ...


31

My favorite way is to pipe a sqldump command to a sql command. You can do all databases or a specific one. So, for instance, mysqldump -uuser -ppassword myDatabase | mysql -hremoteserver -uremoteuser -premoteserver You can do all databases with mysqldump --all-databases -uuser -ppassword | mysql -hremoteserver -uremoteuser -premoteserver The only ...


28

I recently moved a 30GB database with the following stragegy: Old Server Stop mysql server Copy contents of datadir to another location on disk (~/mysqldata/*) Start mysql server again (downtime was 10-15 minutes) compress the data (tar -czvf mysqldata.tar.gz ~/mysqldata) copy the compressed file to new server New Server install mysql (don't start) ...


23

You don't even need mysqldump if you're moving a whole database schema, and you're willing to stop the first database (so it's consistent when being transfered) Stop the database (or lock it) Go to the directory where the mysql data files are. Transfer over the folder (and its contents) over to the new server's mysql data directory Start back up the ...


16

You can follow any method below : Note: If you are using any new features like new data types, etc then you have to test out as it will throw errors. METHOD 1: Using Native Tools Script out the database SCHEMA_ONLY and recreate an empty database on the destination server. Below are the screenshots : Use BCP OUT and BULK INSERT to insert data. ...


15

The foremost step to do is to run the Upgrade Advisor on SQL 2000 database and address all the issues reported by it. As a best practice, use the Upgrade Advisor tool on your SQL Server 2000 legacy database and import a trace file to the Upgrade Advisor tool for analysis. The trace file lets the Upgrade Advisor detect issues that might not show up in a ...


14

You almost have your answer already: Create the new structure in parallel Start writing to both structures Migrate old data to the new structure Only write and read new structure Delete old columns As for step 3, use something like this (in one transaction): Insert what is not there yet: INSERT INTO new_tbl (old_id, data) SELECT old_id, data FROM ...


13

I have two suggestions: 1) I hate bringing up commercial products but there is a $49.00 tools to Migrate MSSQL to MySQL 2) Try MySQL's MSSQL Migration forums for further suggestions UPDATE 2011-06-03 18:03 EDT There is an old product that went EOL back in January 2010 called the MySQL Migration Toolkit. If you can get a hold of it, you can use it. ...


13

Scripting out the tables, then using SSIS to transfer the data would be the most reliable and effective way of moving the data to the new database.


13

Personally, I would avoid the detach/attach mechanisms. Especially in SQL Server 2000, I just don't trust that you will always bring the server back up and be able to attach those files. I've heard plenty of stories where this didn't happen cleanly - just because you have a Plan B doesn't automatically make Plan A sensible. With backup / restore, you don't ...


13

According to the MySQL 5.0 Certification Study Guide, Chapter 32 Section 32.3.4, Pages 456,457 describe the Conditions for Binary Portability which bring out the following: Binary portability is important if you want to take a binary backup that was made on one machine and use it on another machine that has a different architecture. For example, ...


12

Every time I've done it, we've gone for two passes ... take a snapshot, and working on a different server, use that to determine what has to be done for the migration, and script it. once they have the script in hand, the snapshop is restored on the test system, and it's timed to see if it'll run within the required time, or it's tuned and modified until ...


12

In my experience, the same decision making process should be made as prior. AFAIK there hasn't been any 'world changers' with SQL Server installation, within the MS SQL Server product in itself, and the potential problems you have when rolling out software with millions of lines of code. Something bad could happen and now you're stuck with no 'ROLLBACK' ...


12

The best answer to this question is a bit of a short one, but go look at the books online articles that talk about what features are due to be removed in future versions and are no longer there in various versions. For instance.. In SQL Server 2008 the "old-style" left outer join syntax (WHERE Table2.col1 *= Table1.Col1) is listed as deprecated (there but ...


10

If you cannot take the databases offline, tne you need to do backup/restore. I would suggest the following: Install SQL 2008 on the new box, using the same file structure as the old box for MDF and LDF files. Take backups of all databases on the old box. Restore master from the old box to the new box having the SQL started in single user mode. Restore ...


10

So someone let our SQL server 2008 R2 enterprise trial expire on a production web server Violation of the EULA. Is express or compact capable of running a small website? Both are capable of running a small website. Express is also capable of running medium or large websites. Personally I would go for Express if possible, Express handles concurrent ...


9

I think that simplest way is to use that --hex-blob switch on mysqldump and restore by psql, with decode(string text, type text). However it's not that simple, because you need to change a little produced dump (sed, awk), adding that decode function. For example: mysqldump -u root -p --skip-quote-names --hex-blob --skip-triggers \ --compact ...


9

The Oracle Administrators Guide says the following: Use the ALTER TABLE...MODIFY statement to modify an existing column definition. You can modify column data type, default value, column constraint, column expression (for virtual columns) and column encryption. You can increase the length of an existing column, or decrease it, if all ...


8

One of the easiest ways to use a full backup to prepare, a differential to migrate. First off, you do a full backup and restore to prepare the files on the new server disks, but use the RESTORE .. WITH NORECOVERY option to allow more restores. At the time of migration, you do a differential backup/restore. This is far quicker and you use RESTORE .. WITH ...


8

If you have your first CSV loaded into a table, you can just as easily load the other one into a staging table (presumably with the same structure as the 'real' one). Then you can get the new rows by SELECT * FROM staging_table EXCEPT SELECT * FROM real_table ; Rows missing from the new CSV can be get reversing the two sides around EXCEPT. However, ...


8

Not if you used any persisted Enterprise features. If you used any of such there will be an entry in sys.dm_db_persisted_sku_features and when trying to open/attach/restore this database on any edition lower than Enterprise you will gen an exception and the database won't open. The list possible of persisted SKU features are: Compression. Indicates that ...


7

You mention 24/7 operation, so the best way to do this is to create a database mirror on the new system, then you can simply switch over with minimum - perhaps zero, depending on how your application is structured - downtime using a client-side redirect. The basic steps are: Make sure your primary database is running in FULL recovery mode. Backup the ...


7

I used to move databases almost constantly, due to SAN reconfiguration and migrations. Assuming that you are moving a whole server at a time, I would go with something like your path #2. (If you are moving one database at a time, and eventually doing every database on a server, that would be more problematic since you would have to be changing paths to the ...


7

I'd consider scripting the table out, or use a compare tools (eg Red Gate) to generate the tables in the target database. Without indexes or constraints yet. Then I'd consider restoring the database with a different name on the same server and doing INSERT newdb.dbo.newtable SELECT * FROM olddb.dbo.oldtable .. for each table, with SET IDENTITY INSERT ON ...


7

I think backing up the "data directory" is what you want. Moving the whole directory to a new server should be what you might look into. This will move everything within the cluster. From there, you can drop, move,... your single databases as you need to.


7

It is not safe to assume that the data is OK just because PostgreSQL starts. Pg does not do any kind of comprehensive verification run on the DB contents at startup. If it did it could take hours (or days or weeks for bigger DBs) to start. It doesn't have any verification tools. The argument is that these should not be needed if the data is managed ...


6

If you have ssh access you can use mysqldump from the command line If you don't have ssh access but you have phpMyAdmin access you can use it to export/import If you don't have phpMyAdmin access there are some handy php scripts that will dump and import ( however speaking from my own experience i never found one that is as reliable as phpMyAdmin ). There ...


6

We actually did it using a lot of manual scripting in conjunction with the Import wizard, but this morning I found a better answer, courtesy of Tibor Karaszi's blog article. Part of our frustration here was that the SQL 2000 "DTS Import/Export Wizard" actually makes this almost trivially easy by selecting "Copy Objects and Data": This third option is the ...


6

The reason for the truncation is quite simple. Some characters (accented ones, for example) in the WE8ISO8859P1 character set are stored as a single byte, but in AL32UTF8 they end up being stored as multiple bytes. As a result of conversion, a 4000 character string may end up actually requiring more than 4000 bytes. By way of example, this query shows you ...


6

That really depends on what the directory structure underneath /home/mysql/mydb - if that's just the data files for one database, you'll need to extend your reach. If you're going to use rsync (or take file-based backups in general), you'll need to make sure that your backup includes, at the very least: Your innodb logfiles (ib_logfile*) Your main innodb ...



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