Tag Info

Hot answers tagged

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


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


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

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


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

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


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

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

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

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

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


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


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


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

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


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



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