Tag Info

Hot answers tagged

21

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 ...


7

Unless the data you are importing is very simplistic using SSMS (Sql Server Management Studio) tends to produce a lot of errors and not a lot of actual data. I find that if my spreadsheet (and/or csv, txt file) is very large or has large text fields, or date/time fields I almost always end up using SQL Server Integration Services (SSIS). The first time you ...


6

Disclaimer : SQL Server 2005 is out of mainstream support. - extended support ends for sp4 on 04/12/2016 Migration always have a downtime. So depending on how much your downtime is, you can go with setting up either : Logshipping ( I have used this method and it reduces the downtime considerably) a. Set up logshipping from the source to destination ...


6

In order. Some easy options: You could look at the start->run services.msc and see which services are there; you could run powershell and look at run gsv *SQL*; or you could look at the Start menu->Microsoft SQL Server 2008 R2 and see what's there. If you're a database newbie, I would take a look at the excellent database maintenance scripts from Ola ...


6

In SMSS you can right click a database and select Tasks -> Import data.


4

The columns in the connection manager that your datasource is using need to be defined as "Unicode String (DT_WSTR)".


4

By reading the official documentation, I'll say that your guess is right, the dump script will override the DB1 database. MySQL Use db syntax The USE db_name statement tells MySQL to use the db_name database as the default (current) database for subsequent statements. The database remains the default until the end of the session or another USE statement ...


4

I want to know the best and the most convenient way to migrate the whole data includes: - Security and permission - Users and memberships - All of the databases and .. Best method to migrate data and all objects related to particular database is by using backup and restore method.Perhaps since you want to migrate whole database to new instance you can ...


3

The first thing you need to identify in your db are which tables require all their data (the seed data, sometimes called "domain" data), and which tables are suitable for bringing only recent data. You have a couple choices then. 1: Use a tool like SSDT to deploy your SCHEMA to dev & staging, keep the domain data as a bunch of INSERT statements in SSDT ...


3

Since you mentioned you want to copy table data only I guess SSIS would be perfect. I have been using it and it works just fine for me. It also depends on level of competency you hold with SSIS. You can go for backup restore but it would also restore tables whoes data you dont want to change. If you want complete refresh then of course no better option than ...


3

The question suggests that the tool is being used to migrate data and not schema, which implies that you are referring to SQL Data Compare and not SQL Compare. In which case, yes, indexes on the comparison key columns will speed on SQL Data Compare. I've consulted the SQL Data Compare project manager, who provides the following insight: "Usually ...


3

Things you are likely to need to do include, preferably before you move any data: Normalize Add constraints (keys, domain checking, referential integrity, validation, etc). Both are fairly huge topics :)


3

And to get your columns to DT_WSTR as liam.confrey mentions, you will want to click on your flat file connection manager and redefine each column type from string [DT_STR] to Unicode string [DT_WSTR] Do note that if you misclick like I usually manage to do and select something like 'two-byte unsigned integer [DTUI2]', classic off by one, the ...


3

I would choose Option 2 If you use Option 1 and revoke privileges, you have to put them back. The mysql grant tables are MyISAM. Should any crash, human error, or other unexpected event corrupt the tables, you have a mess to clean up. Only those with SUPER privilege can perform writes when read_only is enabled. SUPER is not a database-level grant. With ...


3

Sometimes, the lack of an uint type in SQL Server can come in handy. If Order-id is defined as identity(1,1) in DB2, you likely do not have any negative Order-ids. Consider importing all the legacy DB1..Orders.Order-id fields as the same number, but multiplied by -1. If there's a FK to Order-id in DB1, you would use the same multiple-by-negative-one ...


2

Since you're staying with 2008R2 (both source and destination) there is nothing stopping you from just backing up and restoring all of your databases including the system databases. As Shanky pointed out, this would save a great deal of time: 1) Backup and Restore Databases - Is this the best option? It's an option. If you need the downtime to be less, you ...


2

Z ON DBNAME_NODE0000.out instructs the DB2 command line processor to redirect all output to a file named DBNAME_NODE0000.out in the current directory. Apparently, you don't have permissions to create files in that directory.


2

Aside from using sed to remove the USE db1 statement, if you can take a new dump of the database: mysqldump db1 > newdump.sql should generate the dump without the USE db1 statement.


2

As long as all the changes happen only on your first instance, transactional replication is what you are looking for. It was designed for this purpose and usually operates close to realtime. You can find an introduction to replication here: http://www.sqlservercentral.com/stairway/72401/ If you need to be able to apply changes on both machines, merge ...


2

I would do something like: pg_dump -h localhost -W -U postgres --clean -f dbname.dump DBName The move the file to the new server, create the new database with createdb DBName then restore with: psql -d DBName -f dbname.dump -v ON_ERROR_STOP=1 Remember that if you have tablespaces created on the old DB you will have to manually recreate them in the ...


2

The simplest approach is to simply use pg_dumpall -f cluster.dump, then restore with psql -f cluster.dump on the new host. Slightly more sophisticated is to dump globals, then dump each DB separately: pg_dumpall --globals-only > globals.sql psql -qAt -c 'SELECT datname FROM pg_database WHERE NOT datistemplate;' -0 |\ xargs -0 -i pg_dump -Fc -f ...


2

In reality, you could just script it yourself using mysqldump. Here my posts using the --where option for mysqldump: Aug 15, 2011 : Is it possible to mysqldump a subset of a database required to reproduce a query? Mar 14, 2012 : Can I mysqldump an entire database while using where='condition' on individual tables? Jul 15, 2013 : Export just rows ...


2

Upgrading a major version of PostgreSQL (i.e. 8.3 to 8.4) requires a database dump and restore - simply copying in data from an older version will most likely not work. There are still some copies of 8.3 that you can download from EnterpriseDB to get you going. However it would still be best to upgrade to the later version afterwards if possible.


2

Although the representation of the data differs between the old and new schema the data itself should be the same. This means a series of reconciliation queries will provide certainty that the migration was without error. Say you have an Orders table. The total number of Orders per year, month or day will be the same in the old and new databases. ...


1

Nothing bad happened even if you took the config server offline during a chunk migration. In order for a chunk to be marked as migrated, all three config servers need to be up (Contrary to popular belief they do not form a replica set.). (The following is a tiny bit simplified for the sake of shortness.) When a chunk is moved, a global (read cluster wide) ...


1

Data Extraction into a Sandbox CREATE DATABASE IF NOT EXIST sms_sandbox; CREATE TABLE IF NOT EXISTS sms_sandbox.mo_sms LIKE DB2.mo_sms; CREATE TABLE IF NOT EXISTS sms_sandbox.mt_sms LIKE DB2.mt_sms; # # Drop All Indexes From the Sandbox # ALTER TABLE sms_sandbox.mo_sms DROP INDEX `ix_carrier`, DROP INDEX `ix_fecha_sms`, DROP INDEX ...


1

This can be done if you have clustered indexes (either as the primary key constraint or on their own) on the tables that you wish to move. So restore the database, add the new filegroup and then... For primary keys:- ALTER TABLE [SCHEMA].[TABLE NAME] DROP CONSTRAINT [CONSTRAINT NAME] ALTER TABLE [SCHEMA].[TABLE NAME] ADD PRIMARY KEY CLUSTERED ( ...


1

I worked out this issue and share it here, in case someone needs it in future. The default collation setting is determined by the server locale. For most English speaking countries is it : Latin1_General_CI_AS If the server locale setting has been left as default which is English (United States), the collation at time of MS SQL Management Studio would be ...


1

I am considering setting up a SQL Azure account to act as the second instance. SQL Azure database does not support replication. See SQL Server Feature Limitations (Windows Azure SQL Database) I need to schedule the two tables above to be copied to a new instance, say every 12 hours. Can anyone recommend which approach they might take to achieve ...



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