Importing Access DB: servers

Showing posts with label servers. Show all posts

Monday, March 12, 2012

Importing databases in SQL 2005

I'm having a lot of trouble importing/moving databases between SQL2005
servers.
-If i detach/copy files/attach, I get user security problems (ie. user
defined in database "does not exist" on new server, but cannot be removed
from database as owns schemas etc.)
-Same with Backup/restore
-If I try to copy or import data after creating a "blank" database with only
the user ID in question as dbo, I lose primary keys.
All actions are being performed through Management Console with the SQL
admin ID. Most databases are running in 2000 compatability mode following the
recent upgrades. Servers are not on Active Directory.
Can someone give me some hints about how to migrate databases?Seems that some Login of the Source Server is not existing on the Target
Server. If it is so, then first create those Logins on the Target Server and
then try the Backup/Restore method.
"The Vogon" wrote:
> I'm having a lot of trouble importing/moving databases between SQL2005
> servers.
> -If i detach/copy files/attach, I get user security problems (ie. user
> defined in database "does not exist" on new server, but cannot be removed
> from database as owns schemas etc.)
> -Same with Backup/restore
> -If I try to copy or import data after creating a "blank" database with only
> the user ID in question as dbo, I lose primary keys.
> All actions are being performed through Management Console with the SQL
> admin ID. Most databases are running in 2000 compatability mode following the
> recent upgrades. Servers are not on Active Directory.
> Can someone give me some hints about how to migrate databases?|||Thanks for the reply... I'll try that again and let you know...
"Absar Ahmad" wrote:
> Seems that some Login of the Source Server is not existing on the Target
> Server. If it is so, then first create those Logins on the Target Server and
> then try the Backup/Restore method.
> "The Vogon" wrote:
> > I'm having a lot of trouble importing/moving databases between SQL2005
> > servers.
> > -If i detach/copy files/attach, I get user security problems (ie. user
> > defined in database "does not exist" on new server, but cannot be removed
> > from database as owns schemas etc.)
> > -Same with Backup/restore
> > -If I try to copy or import data after creating a "blank" database with only
> > the user ID in question as dbo, I lose primary keys.
> >
> > All actions are being performed through Management Console with the SQL
> > admin ID. Most databases are running in 2000 compatability mode following the
> > recent upgrades. Servers are not on Active Directory.
> >
> > Can someone give me some hints about how to migrate databases?

Friday, March 9, 2012

Importing Data into SQL 2005

I tried to import a table from SQL 2000 server to SQL 2005 server. Between
SQL 2000 servers I can choose to drop the current table if it exists at
destination. However in SQL Server Management Studio there is no option to
drop the existing table. Do I miss something here? How do you guys import
in this case? Thanks.It gets worse. The "object transfer" component doesn't take indexes,
identity properties, constraints or pretty much anything except the raw
table structure. DTS was a good data migration utility and a decent ETL
platform. SSIS is a great ETL tool and a poor data migration tool.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>I tried to import a table from SQL 2000 server to SQL 2005 server. Between
>SQL 2000 servers I can choose to drop the current table if it exists at
>destination. However in SQL Server Management Studio there is no option to
>drop the existing table. Do I miss something here? How do you guys import
>in this case? Thanks.
>|||You are correct.
now it comes to fun part: to create SSIS package I must purchase license for
Visual Studio?
"Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
> It gets worse. The "object transfer" component doesn't take indexes,
> identity properties, constraints or pretty much anything except the raw
> table structure. DTS was a good data migration utility and a decent ETL
> platform. SSIS is a great ETL tool and a poor data migration tool.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
>
>
> "ME" <ME@.mail.com> wrote in message
> news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>>I tried to import a table from SQL 2000 server to SQL 2005 server.
>>Between SQL 2000 servers I can choose to drop the current table if it
>>exists at destination. However in SQL Server Management Studio there is
>>no option to drop the existing table. Do I miss something here? How do
>>you guys import in this case? Thanks.
>|||SQL Server 2005 Developer edition is $50 and includes the BI workbench.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"ME" <ME@.mail.com> wrote in message
news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license
> for Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>> It gets worse. The "object transfer" component doesn't take indexes,
>> identity properties, constraints or pretty much anything except the raw
>> table structure. DTS was a good data migration utility and a decent ETL
>> platform. SSIS is a great ETL tool and a poor data migration tool.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "ME" <ME@.mail.com> wrote in message
>> news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>>I tried to import a table from SQL 2000 server to SQL 2005 server.
>>Between SQL 2000 servers I can choose to drop the current table if it
>>exists at destination. However in SQL Server Management Studio there is
>>no option to drop the existing table. Do I miss something here? How do
>>you guys import in this case? Thanks.
>>
>|||> now it comes to fun part: to create SSIS package I must purchase license for Visual Studio?
No, the Visual Studio IDE is shipped with SQL Server. It is called Business Intelligence Development
Studio.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"ME" <ME@.mail.com> wrote in message news:u0yaw2wGHHA.4904@.TK2MSFTNGP04.phx.gbl...
> You are correct.
> now it comes to fun part: to create SSIS package I must purchase license for Visual Studio?
>
> "Geoff N. Hiten" <SQLCraftsman@.gmail.com> wrote in message
> news:%23r$SwowGHHA.1264@.TK2MSFTNGP03.phx.gbl...
>> It gets worse. The "object transfer" component doesn't take indexes, identity properties,
>> constraints or pretty much anything except the raw table structure. DTS was a good data
>> migration utility and a decent ETL platform. SSIS is a great ETL tool and a poor data migration
>> tool.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>>
>>
>> "ME" <ME@.mail.com> wrote in message news:ONJ1$SwGHHA.3540@.TK2MSFTNGP02.phx.gbl...
>>I tried to import a table from SQL 2000 server to SQL 2005 server. Between SQL 2000 servers I can
>>choose to drop the current table if it exists at destination. However in SQL Server Management
>>Studio there is no option to drop the existing table. Do I miss something here? How do you guys
>>import in this case? Thanks.
>>
>

Monday, March 12, 2012

Importing databases in SQL 2005

Friday, March 9, 2012

Importing Data into SQL 2005

Importing Data into SQL 2005

Importing Data into SQL 2005

Importing Access DB

Blog Archive

About Me