Data transformation services: migration

Showing posts with label migration. Show all posts

Thursday, March 29, 2012

Data storage question

I am migrating Sybase databases to SQL 2000. For the most part I am using a
Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
problem that I'm running into is that the size of the databases are 3, 4 or
more times bigger in SQL then they where in Sybase. The database that I
moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
Some mentioned to me to how SQL is setup in regards to number of rows per
page. I've let to find anything that will help me to look this information
up. I've been through sp_configure but I didn't see anything that jump out
at me. Any help would be nice.
TIA All.
JoeIt's probably due to lots of page splits. Did you add the indexes before or
after you imported the data? Make sure each table has a clustered index and
try reindexing the table. That should free up a lot of space and then you
can do a one time shrink to a reasonable size. You will want to reindex
again after the shrink though since the shrink moves data around again.
--
Andrew J. Kelly SQL MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Thanks Andrew, I'll take a look and see about the indexes.
JD
"Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> It's probably due to lots of page splits. Did you add the indexes before
> or after you imported the data? Make sure each table has a clustered
> index and try reindexing the table. That should free up a lot of space
> and then you can do a one time shrink to a reasonable size. You will want
> to reindex again after the shrink though since the shrink moves data
> around again.
> --
> Andrew J. Kelly SQL MVP
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||reindexing your database will olso increase your performance as well as it
will organize the rows splited into pages
remember to make a backup before and after of this process and that the
database will not be abailable during this time
use dbcc dbreindex instead of dbcc indexdefrag
"Joe D" wrote:
> Thanks Andrew, I'll take a look and see about the indexes.
> JD
>
> "Andrew J. Kelly" <sqlmvpnooospam@.shadhawk.com> wrote in message
> news:ue2C9vlrFHA.3440@.TK2MSFTNGP10.phx.gbl...
> > It's probably due to lots of page splits. Did you add the indexes before
> > or after you imported the data? Make sure each table has a clustered
> > index and try reindexing the table. That should free up a lot of space
> > and then you can do a one time shrink to a reasonable size. You will want
> > to reindex again after the shrink though since the shrink moves data
> > around again.
> >
> > --
> > Andrew J. Kelly SQL MVP
> >
> >
> > "Joe D" <jkdriscoll@.qg.com> wrote in message
> > news:df4s9i$j23$1@.sxnews1.qg.com...
> >>I am migrating Sybase databases to SQL 2000. For the most part I am using
> >>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> >>The problem that I'm running into is that the size of the databases are 3,
> >>4 or more times bigger in SQL then they where in Sybase. The database that
> >>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> >>why' Some mentioned to me to how SQL is setup in regards to number of
> >>rows per page. I've let to find anything that will help me to look this
> >>information up. I've been through sp_configure but I didn't see anything
> >>that jump out at me. Any help would be nice.
> >>
> >> TIA All.
> >>
> >> Joe
> >>
> >
> >
>
>|||Hi Joe
Another reason for ballooning of db size could be if you have a lot of fixed
length columns that have a lot NULL values. I believe Sybase still works
like older versions of SQL Server, and any column that is NULL takes 0
bytes. In SQL Server 7 and 2000, a fixed width column takes the full number
of bytes, whether it is NULL or not..
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
> I am migrating Sybase databases to SQL 2000. For the most part I am using
> a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
> The problem that I'm running into is that the size of the databases are 3,
> 4 or more times bigger in SQL then they where in Sybase. The database that
> I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
> why' Some mentioned to me to how SQL is setup in regards to number of
> rows per page. I've let to find anything that will help me to look this
> information up. I've been through sp_configure but I didn't see anything
> that jump out at me. Any help would be nice.
> TIA All.
> Joe
>|||Very good point Kalen.
--
Andrew J. Kelly SQL MVP
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Another explanation for the increased space requirements is that clustered
index keys are also stored in non-clustered index leaf pages. These keys
are used as 'pointers' to the data. This can add up if you have wide
clustered index keys and a lot of non-clustered indexes
--
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df4s9i$j23$1@.sxnews1.qg.com...
>I am migrating Sybase databases to SQL 2000. For the most part I am using
>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool. The
>problem that I'm running into is that the size of the databases are 3, 4 or
>more times bigger in SQL then they where in Sybase. The database that I
>moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know why'
>Some mentioned to me to how SQL is setup in regards to number of rows per
>page. I've let to find anything that will help me to look this information
>up. I've been through sp_configure but I didn't see anything that jump out
>at me. Any help would be nice.
> TIA All.
> Joe
>|||Morning Kalen,
This was my thought but I seemed unable to find any supporting
documentation. I appreciate the feed back.
Also, Andrew's suggestion of re-indexing has proved to be of help. Now I
need to go back through the databases that I have already migrated and
re-index those.
Thanks much,
Joe
"Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
> Hi Joe
> Another reason for ballooning of db size could be if you have a lot of
> fixed length columns that have a lot NULL values. I believe Sybase still
> works like older versions of SQL Server, and any column that is NULL takes
> 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the full
> number of bytes, whether it is NULL or not..
> HTH
> Kalen Delaney
> www.solidqualitylearning.com
>
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the databases
>> are 3, 4 or more times bigger in SQL then they where in Sybase. The
>> database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>> like to know why' Some mentioned to me to how SQL is setup in regards to
>> number of rows per page. I've let to find anything that will help me to
>> look this information up. I've been through sp_configure but I didn't see
>> anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>
>|||Are there any work around for this Dan?
Thanks for you input.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
> Another explanation for the increased space requirements is that clustered
> index keys are also stored in non-clustered index leaf pages. These keys
> are used as 'pointers' to the data. This can add up if you have wide
> clustered index keys and a lot of non-clustered indexes
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am using
>>a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS tool.
>>The problem that I'm running into is that the size of the databases are 3,
>>4 or more times bigger in SQL then they where in Sybase. The database that
>>I moved today went from 13GB in Sybase to 81GB in SQL. I'd like to know
>>why' Some mentioned to me to how SQL is setup in regards to number of
>>rows per page. I've let to find anything that will help me to look this
>>information up. I've been through sp_configure but I didn't see anything
>>that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>|||Regarding index space requirements, I can only suggest that you re-evaluate
your indexing strategy. Keep in mind that although storing the clustered
index key in non-clustered indexes may increase disk space requirements, it
also reduces non-clustered index maintenance because the non-clustered
indexes won't need to change when page splits occur. Also, non-clustered
indexes can better cover certain queries.
As with any DBMS migration, you need to consider architectural differences
in your migration. A simple migration tool may get you part of the way
there but additional work is often needed.
Hope this helps.
Dan Guzman
SQL Server MVP
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7052$vdu$1@.sxnews1.qg.com...
> Are there any work around for this Dan?
> Thanks for you input.
> Joe
>
> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the databases
>>are 3, 4 or more times bigger in SQL then they where in Sybase. The
>>database that I moved today went from 13GB in Sybase to 81GB in SQL. I'd
>>like to know why' Some mentioned to me to how SQL is setup in regards to
>>number of rows per page. I've let to find anything that will help me to
>>look this information up. I've been through sp_configure but I didn't see
>>anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>|||Thanks Dan for your advise and it does help.
Joe
"Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
news:OiG%23X3vrFHA.2588@.tk2msftngp13.phx.gbl...
> Regarding index space requirements, I can only suggest that you
> re-evaluate your indexing strategy. Keep in mind that although storing
> the clustered index key in non-clustered indexes may increase disk space
> requirements, it also reduces non-clustered index maintenance because the
> non-clustered indexes won't need to change when page splits occur. Also,
> non-clustered indexes can better cover certain queries.
> As with any DBMS migration, you need to consider architectural differences
> in your migration. A simple migration tool may get you part of the way
> there but additional work is often needed.
>
> --
> Hope this helps.
> Dan Guzman
> SQL Server MVP
> "Joe D" <jkdriscoll@.qg.com> wrote in message
> news:df7052$vdu$1@.sxnews1.qg.com...
>> Are there any work around for this Dan?
>> Thanks for you input.
>> Joe
>>
>> "Dan Guzman" <guzmanda@.nospam-online.sbcglobal.net> wrote in message
>> news:uyGx1cprFHA.1684@.TK2MSFTNGP14.phx.gbl...
>> Another explanation for the increased space requirements is that
>> clustered index keys are also stored in non-clustered index leaf pages.
>> These keys are used as 'pointers' to the data. This can add up if you
>> have wide clustered index keys and a lot of non-clustered indexes
>> --
>> Hope this helps.
>> Dan Guzman
>> SQL Server MVP
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>>I am migrating Sybase databases to SQL 2000. For the most part I am
>>using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>>tool. The problem that I'm running into is that the size of the
>>databases are 3, 4 or more times bigger in SQL then they where in
>>Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>>in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>>in regards to number of rows per page. I've let to find anything that
>>will help me to look this information up. I've been through sp_configure
>>but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>|||Supporting documentation?
I guess you haven't read "Inside SQL Server". :-)
It shows you down to the byte level how rows are organized on your pages for
each datatype, and even has examples for null columns of large width. It
also provides the tools for you to examine the page layouts for yourself so
you don't have to take anybody's word for it. You can answer you own
questions.
The book also talks a lot about indexing strategies. (And yes, I am the
author.)
HTH
Kalen Delaney
www.solidqualitylearning.com
"Joe D" <jkdriscoll@.qg.com> wrote in message
news:df7001$vdq$1@.sxnews1.qg.com...
> Morning Kalen,
> This was my thought but I seemed unable to find any supporting
> documentation. I appreciate the feed back.
> Also, Andrew's suggestion of re-indexing has proved to be of help. Now
> I need to go back through the databases that I have already migrated and
> re-index those.
> Thanks much,
> Joe
>
> "Kalen Delaney" <replies@.public_newsgroups.com> wrote in message
> news:%23rTo0KnrFHA.464@.TK2MSFTNGP15.phx.gbl...
>> Hi Joe
>> Another reason for ballooning of db size could be if you have a lot of
>> fixed length columns that have a lot NULL values. I believe Sybase still
>> works like older versions of SQL Server, and any column that is NULL
>> takes 0 bytes. In SQL Server 7 and 2000, a fixed width column takes the
>> full number of bytes, whether it is NULL or not..
>> HTH
>> Kalen Delaney
>> www.solidqualitylearning.com
>>
>> "Joe D" <jkdriscoll@.qg.com> wrote in message
>> news:df4s9i$j23$1@.sxnews1.qg.com...
>> I am migrating Sybase databases to SQL 2000. For the most part I am
>> using a Microsoft tool named 'Sybase Migration Wizard'. It really a DTS
>> tool. The problem that I'm running into is that the size of the
>> databases are 3, 4 or more times bigger in SQL then they where in
>> Sybase. The database that I moved today went from 13GB in Sybase to 81GB
>> in SQL. I'd like to know why' Some mentioned to me to how SQL is setup
>> in regards to number of rows per page. I've let to find anything that
>> will help me to look this information up. I've been through sp_configure
>> but I didn't see anything that jump out at me. Any help would be nice.
>> TIA All.
>> Joe
>>
>>
>
>

Tuesday, March 27, 2012

Data Source issues through migration

We are using SQL Server 2000 Reporting Services. Our data resides in
Teradata. We have a Development/Acceptance Teradata server (TDDEV) and a
Production Teradata server (TDPROD). Our databases are named slightly
differently on each. For example, a database name EDM_D in development is
named EDM_A for acceptance and EDM_P in Production. What is the best way to
manage the need to change the schema name through migration? We want to
avoid opening every dataset, searching and replacing the schema name. I have
found that I can use a report parameter, but that stilll requires opening
multiple reports. Is there a way to create a global parameter for the
project? I am at my wit's end!Here's how I handle this.
The reports are created using a shared data source, let's say EDM in your
example.
Then in the target folder in each environment, I create a data source named
EDM, and enter an appropriate Connection string that connects to the
appropriate database.
"LoveBoxers" wrote:
> We are using SQL Server 2000 Reporting Services. Our data resides in
> Teradata. We have a Development/Acceptance Teradata server (TDDEV) and a
> Production Teradata server (TDPROD). Our databases are named slightly
> differently on each. For example, a database name EDM_D in development is
> named EDM_A for acceptance and EDM_P in Production. What is the best way to
> manage the need to change the schema name through migration? We want to
> avoid opening every dataset, searching and replacing the schema name. I have
> found that I can use a report parameter, but that stilll requires opening
> multiple reports. Is there a way to create a global parameter for the
> project? I am at my wit's end!

Thursday, March 22, 2012

Data Schema Comparison and Migration Tools

I've been testing a variety of database schema migration tools. Our
company purchased Embarcadero Change Manager a while ago but we've
been less than satisfied with the results.

We are looking for a tool that can compare a source/developer database
with a target/client database and then make the necessary changes.
The tool will need to update stored procedures, tables, indexes,
constraints, etc. The tool will also need to make the changes in the
correct order based on dependencies and relationships.

Anyone have any recommendations?snow@.steton.com (Roger Corman) wrote in message news:<4625da9f.0401230727.521295c7@.posting.google.com>...
> I've been testing a variety of database schema migration tools. Our
> company purchased Embarcadero Change Manager a while ago but we've
> been less than satisfied with the results.
> We are looking for a tool that can compare a source/developer database
> with a target/client database and then make the necessary changes.
> The tool will need to update stored procedures, tables, indexes,
> constraints, etc. The tool will also need to make the changes in the
> correct order based on dependencies and relationships.
> Anyone have any recommendations?

http://www.red-gate.com/ seems to be the best. We have tried a fewsql

Wednesday, March 7, 2012

Data Migration using Transactional Replication

I need to move my database servers from one location to another. The issue is that I have over 200 databases to move and my clients can't afford a downtime. The collective volume of all the databases is over 2.5 TB and growing.

I am thinking to copy these databases in batches over the WAN to the new location and replicate them using Transactional replication till I have all the databases moved and synchronized.

Will it be wise enough to use replication for synchronizing 200 databases or is there a better approach which I can use to move these databases with minimum downtime and compromise on performance of applications.

Note: Migration is from SQL Server 2000 to SQL Server 2005.

Thanks in advance.

Riteshi do not think replication is going to be easy and effective solution for 200 DB with 2.5 TB. replication has its own set of rules, issues and overheads. better move a few db at a time (backup/restore or detach/attach) and then point the application using those databases to the new db server.

Data Migration using SSIS in SQL 2005

Greetings,

I am using SQL Server 2005 and I want a clue if there is some good built-in support to do the following.

I want to transfer all the data from one source database to another destination database having same number to tables and schema like source database. I also want to ensure that the old data on the destination database should be secure too.

I have an idea that this can be done through SSIS in coding but can anyone give me a guide, tip, link, sample code that can be helpful.

Thanx a million in advance.

Regards

Try the link below for code samples from DTS/SSIS expert. Hope this helps.

http://www.sqlis.com/

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

Using the wizard I am trying to import data from SQL 2000 to SQL 2005. The issue I face is to manage the Idenity columns. I do not want the identity values from the SQL 2000 DB which may be at say 5000, instead I want to use the identity values from the SQL 2005 database which will problably start at 1

Is there a way to do this, should I use a custom query instead of a direct table dump. If using a custom query do I need to just specify a Select query or does it have to be a specific "INSERT INTO Table ..." type query

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

You need to ignore the IDENTITY column in the source table. Use a custom query to specify the remaining columns.|||Could you give me diffrent kinds of query of Data migration using DTS package 2000 to 2005.

Data migration using Import/export wizard SQL 2000 to SQL 2005

I am trying to import data from SQL 2000 to SQL 2005

The Data schemas are the same in both (tables, relationships, key and triggers).

If I do import the data directly with Identity "ON" how can I advance the identity column value to current +1 on the SQL 2005 table.

Thanks

javahar

Data Migration Strategy from corporate offices to new datacenter

Are there any migration strategy docs for moving data from one location to
another ?Hassan,
I think this question would need a whole lot more details to be answered
properly. For instance, if it is a one-off then SSIS, DTS, BCP, log
shipping, database mirroring might all be relevant. What are the data source
and destination (DB2, Oracle, SQL Server (version?)...). Is there
connectivity between the 2 machines? If it is a regular/continuous process
then replication might be relevant. In this case do you want to replicate
changes to the source system, filter the data in some way, can the data be
changed on the destination server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Data Migration Strategy from corporate offices to new datacenter

Are there any migration strategy docs for moving data from one location to
another ?
Hassan,
I think this question would need a whole lot more details to be answered
properly. For instance, if it is a one-off then SSIS, DTS, BCP, log
shipping, database mirroring might all be relevant. What are the data source
and destination (DB2, Oracle, SQL Server (version?)...). Is there
connectivity between the 2 machines? If it is a regular/continuous process
then replication might be relevant. In this case do you want to replicate
changes to the source system, filter the data in some way, can the data be
changed on the destination server?
Cheers,
Paul Ibison SQL Server MVP, www.replicationanswers.com

Data Migration Strategy from corporate offices to new datacenter

Data Migration Recommendations Needed

I often have to copy an entire database from production server onto a staging
server. I was looking on other ways this can be done without headaches.
This is what I do. There are probably better ways out there.
1. Perfrom Full backup of the database I need to copy.
2. Use this command to restore to my new location.
Restore FilelistOnly From Disk = 'E:\NCR\NCR'
Restore Database NCR from Disk = 'E:\NCR\NCR'
With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
3. Run the following script to create this stored proc sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
4. Then I run that sp (sp_help_revlogin) and copy that data to my new
server to fix the users.
The problem I have is that the users logins all need to be fixed I will run
this to fix them
use DatabaseName
go
sp_change_users_login Auto_Fix, 'user'
go
Sometimes this doesnt work. Get the users from one server to another is my
biggest problem. Pleassssssse Help. Thanks alot
HOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
AMB
"Lontae Jones" wrote:

> I often have to copy an entire database from production server onto a staging
> server. I was looking on other ways this can be done without headaches.
> This is what I do. There are probably better ways out there.
> 1. Perfrom Full backup of the database I need to copy.
> 2. Use this command to restore to my new location.
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
> 3. Run the following script to create this stored proc sp_help_revlogin
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> 4. Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
> The problem I have is that the users logins all need to be fixed I will run
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
> Sometimes this doesnt work. Get the users from one server to another is my
> biggest problem. Pleassssssse Help. Thanks alot

Data Migration Recommendations Needed

I often have to copy an entire database from production server onto a stagin
g
server. I was looking on other ways this can be done without headaches.
This is what I do. There are probably better ways out there.
1. Perfrom Full backup of the database I need to copy.
2. Use this command to restore to my new location.
Restore FilelistOnly From Disk = 'E:\NCR\NCR'
Restore Database NCR from Disk = 'E:\NCR\NCR'
With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
3. Run the following script to create this stored proc sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
4. Then I run that sp (sp_help_revlogin) and copy that data to my new
server to fix the users.
The problem I have is that the users logins all need to be fixed I will run
this to fix them
use DatabaseName
go
sp_change_users_login Auto_Fix, 'user'
go
Sometimes this doesnt work. Get the users from one server to another is my
biggest problem. Pleassssssse Help. Thanks alotHOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
AMB
"Lontae Jones" wrote:

> I often have to copy an entire database from production server onto a stag
ing
> server. I was looking on other ways this can be done without headaches.
> This is what I do. There are probably better ways out there.
> 1. Perfrom Full backup of the database I need to copy.
> 2. Use this command to restore to my new location.
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
> 3. Run the following script to create this stored proc sp_help_revlogin
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + '
)'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd +
')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> 4. Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
> The problem I have is that the users logins all need to be fixed I will r
un
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
> Sometimes this doesnt work. Get the users from one server to another is m
y
> biggest problem. Pleassssssse Help. Thanks alot

Data Migration Recommendations Needed

I often have to copy an entire database from production server onto a staging
server. I was looking on other ways this can be done without headaches.
This is what I do. There are probably better ways out there.
1. Perfrom Full backup of the database I need to copy.
2. Use this command to restore to my new location.
Restore FilelistOnly From Disk = 'E:\NCR\NCR'
Restore Database NCR from Disk = 'E:\NCR\NCR'
With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
3. Run the following script to create this stored proc sp_help_revlogin
USE master
GO
IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
DROP PROCEDURE sp_hexadecimal
GO
CREATE PROCEDURE sp_hexadecimal
@.binvalue varbinary(256),
@.hexvalue varchar(256) OUTPUT
AS
DECLARE @.charvalue varchar(256)
DECLARE @.i int
DECLARE @.length int
DECLARE @.hexstring char(16)
SELECT @.charvalue = '0x'
SELECT @.i = 1
SELECT @.length = DATALENGTH (@.binvalue)
SELECT @.hexstring = '0123456789ABCDEF'
WHILE (@.i <= @.length)
BEGIN
DECLARE @.tempint int
DECLARE @.firstint int
DECLARE @.secondint int
SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
SELECT @.firstint = FLOOR(@.tempint/16)
SELECT @.secondint = @.tempint - (@.firstint*16)
SELECT @.charvalue = @.charvalue +
SUBSTRING(@.hexstring, @.firstint+1, 1) +
SUBSTRING(@.hexstring, @.secondint+1, 1)
SELECT @.i = @.i + 1
END
SELECT @.hexvalue = @.charvalue
GO
IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
DROP PROCEDURE sp_help_revlogin
GO
CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
DECLARE @.name sysname
DECLARE @.xstatus int
DECLARE @.binpwd varbinary (256)
DECLARE @.txtpwd sysname
DECLARE @.tmpstr varchar (256)
DECLARE @.SID_varbinary varbinary(85)
DECLARE @.SID_string varchar(256)
IF (@.login_name IS NULL)
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name <> 'sa'
ELSE
DECLARE login_curs CURSOR FOR
SELECT sid, name, xstatus, password FROM master..sysxlogins
WHERE srvid IS NULL AND name = @.login_name
OPEN login_curs
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
IF (@.@.fetch_status = -1)
BEGIN
PRINT 'No login(s) found.'
CLOSE login_curs
DEALLOCATE login_curs
RETURN -1
END
SET @.tmpstr = '/* sp_help_revlogin script '
PRINT @.tmpstr
SET @.tmpstr = '** Generated '
+ CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
PRINT @.tmpstr
PRINT ''
PRINT 'DECLARE @.pwd sysname'
WHILE (@.@.fetch_status <> -1)
BEGIN
IF (@.@.fetch_status <> -2)
BEGIN
PRINT ''
SET @.tmpstr = '-- Login: ' + @.name
PRINT @.tmpstr
IF (@.xstatus & 4) = 4
BEGIN -- NT authenticated account/group
IF (@.xstatus & 1) = 1
BEGIN -- NT login is denied access
SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
PRINT @.tmpstr
END
ELSE BEGIN -- NT login has access
SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
PRINT @.tmpstr
END
END
ELSE BEGIN -- SQL Server authentication
IF (@.binpwd IS NOT NULL)
BEGIN -- Non-null password
EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
IF (@.xstatus & 2048) = 2048
SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
ELSE
SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
PRINT @.tmpstr
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
ELSE BEGIN
-- Null password
EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
+ ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
END
IF (@.xstatus & 2048) = 2048
-- login upgraded from 6.5
SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
ELSE
SET @.tmpstr = @.tmpstr + '''skip_encryption'''
PRINT @.tmpstr
END
END
FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
END
CLOSE login_curs
DEALLOCATE login_curs
RETURN 0
GO
4. Then I run that sp (sp_help_revlogin) and copy that data to my new
server to fix the users.
The problem I have is that the users logins all need to be fixed I will run
this to fix them
use DatabaseName
go
sp_change_users_login Auto_Fix, 'user'
go
Sometimes this doesnt work. Get the users from one server to another is my
biggest problem. Pleassssssse Help. Thanks alotHOW TO: Transfer Logins and Passwords Between Instances of SQL Server
http://support.microsoft.com/kb/246133
AMB
"Lontae Jones" wrote:
> I often have to copy an entire database from production server onto a staging
> server. I was looking on other ways this can be done without headaches.
> This is what I do. There are probably better ways out there.
> 1. Perfrom Full backup of the database I need to copy.
> 2. Use this command to restore to my new location.
> Restore FilelistOnly From Disk = 'E:\NCR\NCR'
> Restore Database NCR from Disk = 'E:\NCR\NCR'
> With Move 'NCR_Data' TO 'E:\mssql7\data\NCR_Data.mdf',
> Move 'NCR_Log' To 'E:\mssql7\data\NCR_Log.ldf', stats
> 3. Run the following script to create this stored proc sp_help_revlogin
>
> USE master
> GO
> IF OBJECT_ID ('sp_hexadecimal') IS NOT NULL
> DROP PROCEDURE sp_hexadecimal
> GO
> CREATE PROCEDURE sp_hexadecimal
> @.binvalue varbinary(256),
> @.hexvalue varchar(256) OUTPUT
> AS
> DECLARE @.charvalue varchar(256)
> DECLARE @.i int
> DECLARE @.length int
> DECLARE @.hexstring char(16)
> SELECT @.charvalue = '0x'
> SELECT @.i = 1
> SELECT @.length = DATALENGTH (@.binvalue)
> SELECT @.hexstring = '0123456789ABCDEF'
> WHILE (@.i <= @.length)
> BEGIN
> DECLARE @.tempint int
> DECLARE @.firstint int
> DECLARE @.secondint int
> SELECT @.tempint = CONVERT(int, SUBSTRING(@.binvalue,@.i,1))
> SELECT @.firstint = FLOOR(@.tempint/16)
> SELECT @.secondint = @.tempint - (@.firstint*16)
> SELECT @.charvalue = @.charvalue +
> SUBSTRING(@.hexstring, @.firstint+1, 1) +
> SUBSTRING(@.hexstring, @.secondint+1, 1)
> SELECT @.i = @.i + 1
> END
> SELECT @.hexvalue = @.charvalue
> GO
> IF OBJECT_ID ('sp_help_revlogin') IS NOT NULL
> DROP PROCEDURE sp_help_revlogin
> GO
> CREATE PROCEDURE sp_help_revlogin @.login_name sysname = NULL AS
> DECLARE @.name sysname
> DECLARE @.xstatus int
> DECLARE @.binpwd varbinary (256)
> DECLARE @.txtpwd sysname
> DECLARE @.tmpstr varchar (256)
> DECLARE @.SID_varbinary varbinary(85)
> DECLARE @.SID_string varchar(256)
> IF (@.login_name IS NULL)
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name <> 'sa'
> ELSE
> DECLARE login_curs CURSOR FOR
> SELECT sid, name, xstatus, password FROM master..sysxlogins
> WHERE srvid IS NULL AND name = @.login_name
> OPEN login_curs
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> IF (@.@.fetch_status = -1)
> BEGIN
> PRINT 'No login(s) found.'
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN -1
> END
> SET @.tmpstr = '/* sp_help_revlogin script '
> PRINT @.tmpstr
> SET @.tmpstr = '** Generated '
> + CONVERT (varchar, GETDATE()) + ' on ' + @.@.SERVERNAME + ' */'
> PRINT @.tmpstr
> PRINT ''
> PRINT 'DECLARE @.pwd sysname'
> WHILE (@.@.fetch_status <> -1)
> BEGIN
> IF (@.@.fetch_status <> -2)
> BEGIN
> PRINT ''
> SET @.tmpstr = '-- Login: ' + @.name
> PRINT @.tmpstr
> IF (@.xstatus & 4) = 4
> BEGIN -- NT authenticated account/group
> IF (@.xstatus & 1) = 1
> BEGIN -- NT login is denied access
> SET @.tmpstr = 'EXEC master..sp_denylogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> ELSE BEGIN -- NT login has access
> SET @.tmpstr = 'EXEC master..sp_grantlogin ''' + @.name + ''''
> PRINT @.tmpstr
> END
> END
> ELSE BEGIN -- SQL Server authentication
> IF (@.binpwd IS NOT NULL)
> BEGIN -- Non-null password
> EXEC sp_hexadecimal @.binpwd, @.txtpwd OUT
> IF (@.xstatus & 2048) = 2048
> SET @.tmpstr = 'SET @.pwd = CONVERT (varchar(256), ' + @.txtpwd + ')'
> ELSE
> SET @.tmpstr = 'SET @.pwd = CONVERT (varbinary(256), ' + @.txtpwd + ')'
> PRINT @.tmpstr
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', @.pwd, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> ELSE BEGIN
> -- Null password
> EXEC sp_hexadecimal @.SID_varbinary,@.SID_string OUT
> SET @.tmpstr = 'EXEC master..sp_addlogin ''' + @.name
> + ''', NULL, @.sid = ' + @.SID_string + ', @.encryptopt = '
> END
> IF (@.xstatus & 2048) = 2048
> -- login upgraded from 6.5
> SET @.tmpstr = @.tmpstr + '''skip_encryption_old'''
> ELSE
> SET @.tmpstr = @.tmpstr + '''skip_encryption'''
> PRINT @.tmpstr
> END
> END
> FETCH NEXT FROM login_curs INTO @.SID_varbinary, @.name, @.xstatus, @.binpwd
> END
> CLOSE login_curs
> DEALLOCATE login_curs
> RETURN 0
> GO
> 4. Then I run that sp (sp_help_revlogin) and copy that data to my new
> server to fix the users.
> The problem I have is that the users logins all need to be fixed I will run
> this to fix them
> use DatabaseName
> go
> sp_change_users_login Auto_Fix, 'user'
> go
> Sometimes this doesnt work. Get the users from one server to another is my
> biggest problem. Pleassssssse Help. Thanks alot

Data migration overview help

Hello,

This is my first project with SSIS and I'm having some problems migrating our current data import to it. Generally: right now we're pulling hourly data (which has a timestamp on it) in from a customer's db(which can't be modified, and is big and growing quickly) into our db. To do this we have been using a field to keep track of the last data download time (in our db) and then using that then when querying the customer's db to reduce the number of rows returned.

Will a similar system work in SSIS? I'm having trouble pulling the last download date out of the "target" database and using it in the query on the customer's db. Any advice on what I should be looking into to do this and am I headed in the right direction or just not thinking about this in a SSIS way?

How powerful/common are variables in SSIS and should I be trying to use them here?

Thanks,
Jeff

Variables are the way to go with this. Use an ExecuteSQL task to grab the timestamp and store it in a variable. Then either use the variable as a parameter on your data source SELECT statements in the data flow, or build the SQL dynamically, using expression based variables. Generally, if your data source is not SQL Server, it seems like you are better off using the expression based variables.

Saturday, February 25, 2012

Data Migration Oracle 7.16 to SQL Server 2000

Hi,

I have been asked to look at optimising a Data Migration Strategy for one of our clients. I am quite new to Data Migration (I'm actually a system designer!).

The migration is from a legacy Oracle 7.16 Db to a SQL Server 2000 environment.

The legacy Db has some large tables - 30 Million+ rows

Table/column Mapping is 1:1

At present we are using DTS to import into the SQL environment.

My question is: Is there any way to leverage the Oracle 7.16 export to speed up the migration process?

Any advice would be appreciated.

Many thanks

Mike

this forum is for sql server 2005 integration services...not sql server 2000 data transformation services.

i suggest that you post your question to the microsoft dts newsgroup.

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Because of update issue we need to merge data from an already installed database into the new compact edition. Are there some tools we could use in our installation routine (InstallShield) or are there some recommendations how we could do this data merging?

Thanks @. all
Daniel

Hello Daniel,

I am going to begin investigating this issue myself very soon and I'll be more than happy to provide you with any info. I'm amazed that nobody has gotten to you, especially since you made this post well over a month ago. Have you received any information from others that might be relevant here? If so, I'd really like to hear your input.

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data migration MSDE -> SQL Compact

Hi all

Currently we are using the MSDE as SQL Server and decide to switch to use the smaller and file based SQL Server 2005 Compact Edition.

Thanks @. all
Daniel

Hello Daniel,

Thanks,

Corey

|||

Please see the reply from Pragya on this thread: http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=1300136&SiteID=1

"To make this migration task really easy, we are coming out of a data migration wizard soon!"

Data migration into SQL server 2005 from xml files

Friends

Any one of you please provide step by step info for migrating sql server 2005 from xml files.

cheers :)

satish

Try to provide as much detail as you can so that someone can help you.

Thursday, March 29, 2012

Data storage question

Data storage question

Data storage question

Tuesday, March 27, 2012

Data Source issues through migration

Thursday, March 22, 2012

Data Schema Comparison and Migration Tools

Wednesday, March 7, 2012

Data Migration using Transactional Replication

Data Migration using SSIS in SQL 2005

Data migration using Import/export wizard SQL 2000 to SQL 2005

Data migration using Import/export wizard SQL 2000 to SQL 2005

Data Migration Strategy from corporate offices to new datacenter

Data Migration Strategy from corporate offices to new datacenter

Data Migration Strategy from corporate offices to new datacenter

Data Migration Recommendations Needed

Data Migration Recommendations Needed

Data Migration Recommendations Needed

Data migration overview help

Saturday, February 25, 2012

Data Migration Oracle 7.16 to SQL Server 2000

Data migration MSDE -> SQL Compact

Data migration MSDE -> SQL Compact

Data migration into SQL server 2005 from xml files

Data transformation services

Blog Archive

About Me