Tag Info

New answers tagged

0

This is the solution I came up with: Enable xp_cmdshell with EXEC sp_configure 'show advanced options', 1 COMMIT GO RECONFIGURE GO EXEC sp_configure 'xp_cmdshell', 1 COMMIT GO RECONFIGURE GO If needed create a directory with xp_cmdshell to get the needed permissions. EXEC master..xp_cmdshell 'mkdir C:\exportdir' Use BCP with queryout EXEC ...


0

Basically what Filip De Vos said but with triggers on the old table to keep the shadow copy in sync while data is transferred. In the past I've used a view to achieve fast switch-over and fail-back from old to new table. In preparation for go-live rename DataTable to DataTable_old; create a view DataTable which simply select <all columns> from ...


1

What I do in this case is: create a shadow table with the new schema move over all the data xlock the original table apply the changes since moving the data rename the original table to bak rename the "new" table to the correct name This makes your downtime minimal at the cost of disk space.


0

Have you tried pt-online-schema-change tool (free) from Percona? It may solve your locking problem. http://www.percona.com/doc/percona-toolkit/2.1/pt-online-schema-change.html


0

I feel your pain on this issue and while I do not have an insightful way of speedily adding fields I can tell you what every company I have ever worked for has used as the approach to these sorts of live data problems. In all cases no matter what other clever tricks we have been using the company will announce a scheduled maintenance window. With the best ...


0

I've promoted about 2X speed by named pipe. First step:(create a named pipe) mkfifo p1 Second step:(waiting to convert) cat p1| iconv -f GBK -t UTF-8 > target.file Last step:(export to p1, see the sql scripts below) SET OPTION TEMP_EXTRACT_NAME1 = 'p1'; SELECT * FROM MY_TABLE; SET OPTION TEMP_EXTRACT_NAME1 = '';


2

Create a sequence and use it as the default value for the column: create sequence measures_measure_id_seq owned by measures.measure_id; alter table measures alter column measure_id set default nextval('measures_measure_id_seq'); commit; That essentially what serial does. See the manual for details: ...


0

WAL vs statement log ordering Do the statements in the log file appear in the same order as they do in the WAL file? That doesn't really make sense. Statements do not appear in the WAL at all. Their effects do, in terms of transaction begins/commits/rollbacks, changes to table heap and index blocks, etc, but the actual statement is never recorded in ...


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


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



Top 50 recent answers are included