Help needed - Moving database from one server to another server

I need to move database from an SQL 2000 SP4 to a SQL 2005 SP1 server. I
tried the import/export wizard but it is giving me an error. Would I be
able to do a backup from the SQL 2000 SP4 server and then do a restore on
the SQL 2005 SP1.
Any help would be appreciated...
Eric Sabo
It's easier to do a backup/restore or detach/copy/attach.
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
..
"Sabo, Eric" <sabo_e@.cup.edu> wrote in message
news:%23vPMQWqAHHA.2276@.TK2MSFTNGP03.phx.gbl...
I need to move database from an SQL 2000 SP4 to a SQL 2005 SP1 server. I
tried the import/export wizard but it is giving me an error. Would I be
able to do a backup from the SQL 2000 SP4 server and then do a restore on
the SQL 2005 SP1.
Any help would be appreciated...
Eric Sabo

Help needed - Moving database from one server to another server

I need to move database from an SQL 2000 SP4 to a SQL 2005 SP1 server. I
tried the import/export wizard but it is giving me an error. Would I be
able to do a backup from the SQL 2000 SP4 server and then do a restore on
the SQL 2005 SP1.
Any help would be appreciated...
Eric SaboIt's easier to do a backup/restore or detach/copy/attach.
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Sabo, Eric" <sabo_e@.cup.edu> wrote in message
news:%23vPMQWqAHHA.2276@.TK2MSFTNGP03.phx.gbl...
I need to move database from an SQL 2000 SP4 to a SQL 2005 SP1 server. I
tried the import/export wizard but it is giving me an error. Would I be
able to do a backup from the SQL 2000 SP4 server and then do a restore on
the SQL 2005 SP1.
Any help would be appreciated...
Eric Sabo

Help needed - Moving database from one server to another server

I need to move database from an SQL 2000 SP4 to a SQL 2005 SP1 server. I
tried the import/export wizard but it is giving me an error. Would I be
able to do a backup from the SQL 2000 SP4 server and then do a restore on
the SQL 2005 SP1.
Any help would be appreciated...
Eric SaboIt's easier to do a backup/restore or detach/copy/attach.
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
.
"Sabo, Eric" <sabo_e@.cup.edu> wrote in message
news:%23vPMQWqAHHA.2276@.TK2MSFTNGP03.phx.gbl...
I need to move database from an SQL 2000 SP4 to a SQL 2005 SP1 server. I
tried the import/export wizard but it is giving me an error. Would I be
able to do a backup from the SQL 2000 SP4 server and then do a restore on
the SQL 2005 SP1.
Any help would be appreciated...
Eric Sabo

HELP MS Access moving to SQL and dont know how

I have been using MS Access for a long time. The last couple of months my sites have really picked up in business (good rankings) and we keep crashing the server (No sites on the server that use MS Access will run) We get the error

System.Data.OleDb.OleDbException: Unspecified error at System.Data.OleDb.OleDbConnection.ProcessResults(Int32 hr) at System.Data.OleDb.OleDbConnection.InitializeProvider()
at System.Data.OleDb.OleDbConnection.Open()
at DreamweaverCtrls.DataSet.DoInit()

Also I am finding out from some of my customers that their sites they can't use at times because of the error but the server hasn't crashed yet because others are up. Well I believe we are exceeding MS Access very badly because it crashes the server daily now and sometimes more then once a day.

So I am going to start moving my MS Access database to SQL but have NO CLUE how where to start etc or use SQL correctly. If people can point me in the right direction that would be very helpful. Everything I find online is people needing help but no solutions that work for me.

My new server is a windows server running ASP.Net 2.0 and to edit my SQL I have "ASP.Net Enterprise Manager" and Plesk for the server.

1. A good book to follow and easy to follow for SQL (connections / how tos) or website

2. Good examples or book on "ASP.Net Enterprise Manager"

3. How to convert access databases into SQL easy (some have over 50,000 listings)

4. how to convert Tab files into SQL easy.

Thank you so much for any help. Also I am using Dreamweaver for most of the edditing and connections. I know its not your favorite but I don't have the time to write everything by hand and it works good for what I do on the websites.

Thanks again,

Rusty

Rusty,

To easily convert your Access tables to SQL Express, set up an ODBC System DSN for the SQL Server. Then when in Access, right click on the tables, one at a time, and choose export. From there choose the ODBC option and select the system DSN you just set up. Your tables should transfer automatically.

Of course I am assuming you have SQL Express setup already.

|||

I have it setup but I can't get it connected by dreamweaver. I get the error

[DBNETLIB][ConnectionOpen (Connect()).]SQL Server does not exist or access denied."

I haven't found anything on exactly how to get it connected yet and still doing the trial and error thing.. LOL Now my access 2003 works perfect my server on the server online but no luck with SQL yet

Help moving users to from old instance

Hello,
I know there is a kb article on how to move users from an old instance
to a new one, but I cannot find it. Can someone point me in the right
direction please? We are moving our instances to new hardware. Thanks.
INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default...;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default...;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default...;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default...;en-us;Q274188
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake Smythe" <rondican@.hotmail.com> wrote in message
news:uWBV%238nJFHA.2628@.tk2msftngp13.phx.gbl...
> Hello,
> I know there is a kb article on how to move users from an old instance
> to a new one, but I cannot find it. Can someone point me in the right
> direction please? We are moving our instances to new hardware. Thanks.
>

Help moving users to from old instance

Hello,
I know there is a kb article on how to move users from an old instance
to a new one, but I cannot find it. Can someone point me in the right
direction please? We are moving our instances to new hardware. Thanks.INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/default.aspx?scid=kb;en-us;Q274188
--
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake Smythe" <rondican@.hotmail.com> wrote in message
news:uWBV%238nJFHA.2628@.tk2msftngp13.phx.gbl...
> Hello,
> I know there is a kb article on how to move users from an old instance
> to a new one, but I cannot find it. Can someone point me in the right
> direction please? We are moving our instances to new hardware. Thanks.
>

Help moving users to from old instance

Hello,
I know there is a kb article on how to move users from an old instance
to a new one, but I cannot find it. Can someone point me in the right
direction please? We are moving our instances to new hardware. Thanks.INF: How To Transfer Logins and Passwords Between SQL Servers
http://support.microsoft.com/defaul...b;en-us;Q246133
PRB: User Logon and/or Permission Errors After Restoring Dump
http://support.microsoft.com/defaul...b;en-us;Q168001
INF: How to Resolve Permission Issues When a Database is Moved Between SQL
Servers
http://support.microsoft.com/defaul...b;en-us;Q240872
PRB: "Troubleshooting Orphaned Users" Topic in Books Online is Incomplete
http://support.microsoft.com/defaul...b;en-us;Q274188
HTH
Jasper Smith (SQL Server MVP)
http://www.sqldbatips.com
I support PASS - the definitive, global
community for SQL Server professionals -
http://www.sqlpass.org
"Jake Smythe" <rondican@.hotmail.com> wrote in message
news:uWBV%238nJFHA.2628@.tk2msftngp13.phx.gbl...
> Hello,
> I know there is a kb article on how to move users from an old instance
> to a new one, but I cannot find it. Can someone point me in the right
> direction please? We are moving our instances to new hardware. Thanks.
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?
master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

help moving system databases

sql 2000 sp3.
i need to move my system databases to a new drive. i've been reading kb
article 224071. it says to do model before msdb, but it doesn't say
when to do master. so do you do master first or last?master is independent from the others, as you don't attach or reattach
master, you just change the location in the startup properties in Enterprise
Manager.
--
Jacco Schalkwijk
SQL Server MVP
"ch" <ch@.dontemailme.com> wrote in message
news:409F91AB.493BB3C4@.dontemailme.com...
> sql 2000 sp3.
> i need to move my system databases to a new drive. i've been reading kb
> article 224071. it says to do model before msdb, but it doesn't say
> when to do master. so do you do master first or last?
>
>

Help moving from SQL Server Express to SQL Server Developer

I built a web application using my VS2005 installation, which includes SQL Server Express.

All works nicely. However, when I tried to move this application to a system with SQL Server Developer, I cannot get it to work at all - data access receives several errors, with the most common being this one:

Cannot open database "xyz.mdf" requested by the login. The login failed. Login failed for user 'MACHINE\ASPNET'.

Here is my connection string (with specific names changed, obviously):

Data source=MACHINE;Initial Catalog=xyz.mdf;Integrated Security=SSPI;User=Me;Password=MyPassword

I have given users MACHINE\ASPNET and MACHINE\Me all possible permissions (that I know) within SQL Server.

I have spent days on this, with no solution - help would be most appreciated.

use the following syntax for the connection string 

<connectionStrings> <add name="DatabaseConnection" connectionString="Data Source=(local);Initial Catalog=Northwind;IntegratedSecurity=SSPI;" /></connectionStrings>

|||

Thank you!

I must admit, I do not exactly understand the difference between my example and yours - but yours worked!

Much appreciated.

|||

Hi,

This is because unlike SQL Express, connecting to the SQL Server Developer edition requires you to use the instance name instead of the database file name.

Also, you need to remove the username and password to make sure that you're connecting with the windows integrated authentication.

help moving comma delimited data

I posted this originally in the incorrect forum I believe so I am reposting this in here which I believe is the proper place.

I need to move essentially a flat file from one server to another one and export it into a database on the second server. Does anyone have an easy process to accomplish this? I am currently at a loss. Any advice would be much appreciated.

Thanksuse BCP to create the export-flat-file
and the Bulk Insert command to import it in the other database|||I can give you examples if this is what you need|||Not that I'm a fan of it...but DTS?

I like Karolyn's idea better though...

Just make sure the server is mapped...|||learned my lessons well...
dts, bcp, bulk insert, ...
i'm getting there !|||Yeah, I almost exclusivley use bcp...

Where in France?|||Originally posted by Brett Kaiser
Yeah, I almost exclusivley use bcp...

Where in France?

Thanks Karolyn and Brett. Much appreciated. This may be a really silly question, but what does BCP stand for?|||BCP: Bulk Copy Program (Very original, huh)

DTS: Data Transformation Service

DDL: Data Definition Language

DML: Data Manipulation Language

DCL: Data Control Language

BOL: Books Online

M$: Evil Empire (But hey, it pays the bills)

IBM: (HAL +1)

OS/2: Whatever happend to that great OS

NT: Nice Try...but no OS/2

DB2: Bullet proof RDBMS

MOO: My Own Opinion...

Democratic Convention: Loony Bin

George Bush: Evil Doer seeker (It's embarrassing)|||Originally posted by Brett Kaiser
BCP: Bulk Copy Program (Very original, huh)

DTS: Data Transformation Service

DDL: Data Definition Language

DML: Data Manipulation Language

DCL: Data Control Language

BOL: Books Online

M$: Evil Empire (But hey, it pays the bills)

IBM: (HAL +1)

OS/2: Whatever happend to that great OS

NT: Nice Try...but no OS/2

DB2: Bullet proof RDBMS

MOO: My Own Opinion...

Democratic Convention: Loony Bin

George Bush: Evil Doer seeker (It's embarrassing)

lol thanks Brett my day is now complete :)|||Example of Bulk Insert

Bulk Insert Base.Proprio.Table
From 'Path + File to Import'
With (FieldTerminator = ',',
RowTerminator = ',\n',
CodePage = 'ACP',
TabLock) ;|||Example for BCP :

DECLARE @.cmd varchar(8000), @.sql varchar(8000);

Select @.sql = '"Select ' + '''Folio''' + ' + ' +
'Left(DTMAJL,19)' + ' + ' +
'''Date extraction''' + ' + ' +
'''N de table''' + ' + ' +
'''Table''' + ' + ' +
'''Version''' + ' as Entte, ' +
' * ' +
'From Base..Table ' +
'Where DTMAJL > (Select DTLOTVAL From Base..V89TBPL0) And DTMAJL is not NULL"';

SET @.cmd = 'bcp ' + @.sql
+ ' queryout ' + 'Chemin et nom du fichier gnrer'
+ ' -c -t ' + 'bidule carr'
+ ' -r ' + 'bidule carr\n'
+ ' -U ' + 'username' +' -P ' + 'password' +' -S ' + @.@.servername
+ ' h ' + '"TABLOCK"';

EXEC master..xp_cmdshell @.cmd;|||Originally posted by Karolyn
Example for BCP :

DECLARE @.cmd varchar(8000), @.sql varchar(8000);

Select @.sql = '"Select ' + '''Folio''' + ' + ' +
'Left(DTMAJL,19)' + ' + ' +
'''Date extraction''' + ' + ' +
'''N de table''' + ' + ' +
'''Table''' + ' + ' +
'''Version''' + ' as Entte, ' +
' * ' +
'From Base..Table ' +
'Where DTMAJL > (Select DTLOTVAL From Base..V89TBPL0) And DTMAJL is not NULL"';

SET @.cmd = 'bcp ' + @.sql
+ ' queryout ' + 'Chemin et nom du fichier gnrer'
+ ' -c -t ' + 'bidule carr'
+ ' -r ' + 'bidule carr\n'
+ ' -U ' + 'username' +' -P ' + 'password' +' -S ' + @.@.servername
+ ' h ' + '"TABLOCK"';

EXEC master..xp_cmdshell @.cmd;

Thanks Karolyn and Brett for all the help. Time to see if I can get this to work :)|||Say I need to have a sql server just listen for updates that are made in a web server. Currently these two are on seperate boxes but I need to have data passed only from the web server to the sql server. Using the load command is there a simple way to have the sql server continually listening for updates while keeping security high and preventing any possible accidents to the sql database.

Web server takes input, the sql server listens for updates to the web server and when updates take place the sql server imports the data for storage and review at later dates.

Does that make sense?

Would setting up a virtual drive as a holding area and then just pointing the sql database be a good option for this?

Thanks again for all the prior help.|||How would SQL Server "see" The Web Changes?

Where is the Web Data stored? And why is it not stored in SQL Server in the first place? Doesn't the app talk to the database?|||Originally posted by Brett Kaiser
How would SQL Server "see" The Web Changes?

Where is the Web Data stored? And why is it not stored in SQL Server in the first place? Doesn't the app talk to the database?

The web server and database server are linked using a ODBC connection. What happens is that the data when inputed on the web server is being saved to the web server just not being send to the database server. Can the Load Data Infile command be used to import data to the databse server from the web server with an ODBC connection? I am not having much luck finding that information on the net.

Thanks|||Brett: Wasn't OS/2 only half an operating system? ;-)|||True multitasking...

Ahhh the good old days...

Toasted Onion (I think that's a cool handle)

What do you mean "Load Data Infile"

Not much of a presentation layer guy...just a lowly dba scrub....|||I read on the mysql website and in other forums that you can use the Load Data Infile command to import data from text files like excel and such.|||Originally posted by Brett Kaiser
True multitasking...

Ahhh the good old days...

Toasted Onion (I think that's a cool handle)

What do you mean "Load Data Infile"

Not much of a presentation layer guy...just a lowly dba scrub....

i like that scrubby look ;)

Help Moving a Database, Associated Logins and Objects

Hello. Newbie here....
I am attempting to move a database, logins, objects from 7 to 2000.
I've tried the copy database wizard, but it gives me a "failed to
create the share OMWWIZE" error.
I've search the MSDN, but the only solution says there are problems
with permissions. I've logged in as SA on both boxes...
Please help, I'm new to this and confused.A likely cause of the error is that the MSSQLServer service account does not
have permissions to the share. However, it's a fairly simple task to do
this manually using the following steps:
1) make note of the existing file locations:
EXEC sp_helpdb 'MyDatabase'
2) detach database
EXEC sp_detach_db 'MyDatabase'
3) copy database files to new location
4) attach database files from new location:
EXEC sp_attach_db 'MyDatabase',
'E:\MyDbDataFiles\MyDatabase.mdf',
'F:\MyDbLogFiles\MyDatabase_Log.ldf'
See http://support.microsoft.com/support/kb/articles/Q224/0/71.ASP for more
information.
--
Hope this helps.
Dan Guzman
SQL Server MVP
"JD" <whatchoogot@.hotmail.com> wrote in message
news:b809817a.0311260547.44aeaefe@.posting.google.com...
> Hello. Newbie here....
> I am attempting to move a database, logins, objects from 7 to 2000.
> I've tried the copy database wizard, but it gives me a "failed to
> create the share OMWWIZE" error.
> I've search the MSDN, but the only solution says there are problems
> with permissions. I've logged in as SA on both boxes...
> Please help, I'm new to this and confused.