moving away from cluster to standalone

We have 2 servers on a SQL 2000 cluster and now we would like to move away
from a clustered enviroment to just a standalone machine. Any idea how I
should proceed?
"nobody" <nobody@.nobody.com> wrote in message
news:%231FAGluoHHA.4424@.TK2MSFTNGP03.phx.gbl...
> We have 2 servers on a SQL 2000 cluster and now we would like to move away
> from a clustered enviroment to just a standalone machine. Any idea how I
> should proceed?
Build a new server, install SQL, and migrate databases.
Russ Kaufmann
MVP - Windows Server - Clustering
ClusterHelp.com, a Microsoft Certified Gold Partner
Web http://www.clusterhelp.com
Blog http://msmvps.com/clusterhelp
The next ClusterHelp classes are:
July 10-13 in Denver
July 16-19 in New York
|||Agreed, there is no procedure for converting a clustered install to a
standalone install.
Denny
MCSA (2003) / MCDBA (SQL 2000)
MCTS (SQL 2005 / Microsoft Windows SharePoint Services 3.0: Configuration /
Microsoft Office SharePoint Server 2007: Configuration)
MCITP (dbadmin, dbdev)
"Russ Kaufmann [MVP]" wrote:

> "nobody" <nobody@.nobody.com> wrote in message
> news:%231FAGluoHHA.4424@.TK2MSFTNGP03.phx.gbl...
> Build a new server, install SQL, and migrate databases.
>
> --
> Russ Kaufmann
> MVP - Windows Server - Clustering
> ClusterHelp.com, a Microsoft Certified Gold Partner
> Web http://www.clusterhelp.com
> Blog http://msmvps.com/clusterhelp
> The next ClusterHelp classes are:
> July 10-13 in Denver
> July 16-19 in New York
>

Moving an entire server(sql 2005) to a new physical machine

I'm trying to finalize the details of moving an entire sql server to a new box.

Is there a simpler way to do this? Can I restore all the system and user databases to the new server?

This is what I've done in sql 2000.

Backup all the system and user databases from the old Server (ServerB)

Install the new default server on a new box (ServerA)

script all the server level security logins from the old Server (ServerB) and create them on the new Server (ServerA)

Restore the system databases to the new Server (Server A) msdb

,tempdb,model. Restore all the user databases to the new server (Server A)

Yes...you can restore all databases to the new server.

The following articles provide more information on moving databases to another server:

How to move databases between computers that are running SQL Server

http://support.microsoft.com/kb/314546

How to move SQL Server databases to a new location by using Detach and Attach functions in SQL Server

http://support.microsoft.com/kb/224071

-Sue

Moving all DBs to new machine with same name

I need to move all my SQL DB's from an old machine to a new machine. For
ease, I'm going to keep the new server the same name and IP address.
My plan is as follows
1) Ensure all users out of all DBs
2) Disconnect server from network
3) Performance full backups of ALL databases
4) Shutdown SQL Server service
5) Re-connect server to network and transfer DB backups to new machine
6) Shut down old server
7) Rename new server to old servers name and change to old servers IP,
reboot
Now here is where I need advice. To restore all my DBs, I need existing
DBs with matching names, correct? So the master DB and the other default
DBs I can restore over, but all of my additional DBs I cannot. Do I simply
create a new DB with a matching name to my additional DBs and then restore
over them?
Should I restore the master and default DBs first, then create the intial
DBs, then restore. Or do I create teh DBs, restore them all, then do the
master? What's the correct order?First advice, if you don't have the right feeling about this, don't do it.
Which SQL Servers are you using ? Both the same version ?
Backup/restore is not the only option. Attaching and detaching is also an
option.
Server renaming will stop SQL Server 7 from running, you'll need the setup
cd to revive sql server. (not a problem with SQL2000)
You don't need new databases, they will be created during restore.
You can not restore the master DB.
Search support.microsoft.com for 'move database' on SQL Server, you'll find
a lot of info (scripts for moving users, ...)
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Evan Mann" <ask@.for.it> wrote in message
news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> I need to move all my SQL DB's from an old machine to a new machine. For
> ease, I'm going to keep the new server the same name and IP address.
> My plan is as follows
> 1) Ensure all users out of all DBs
> 2) Disconnect server from network
> 3) Performance full backups of ALL databases
> 4) Shutdown SQL Server service
> 5) Re-connect server to network and transfer DB backups to new machine
> 6) Shut down old server
> 7) Rename new server to old servers name and change to old servers IP,
> reboot
> Now here is where I need advice. To restore all my DBs, I need existing
> DBs with matching names, correct? So the master DB and the other default
> DBs I can restore over, but all of my additional DBs I cannot. Do I
simply
> create a new DB with a matching name to my additional DBs and then restore
> over them?
> Should I restore the master and default DBs first, then create the intial
> DBs, then restore. Or do I create teh DBs, restore them all, then do the
> master? What's the correct order?
>
>|||These are 2 separate machines, but which have the same exact version of SQL
Server 2000 SP3a+hotfixes. I know what is involved with renaming a server.
I won't be renaming the server with any of these DBs I am moving attached.
The backup/resotre procedure seems to be the easiest. Does this not get the
entire database, including all the users, stored procedures, etc. Why do I
need to move users if I am changing machines? Is this not all kept in the
DB itself?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
> First advice, if you don't have the right feeling about this, don't do it.
> Which SQL Servers are you using ? Both the same version ?
> Backup/restore is not the only option. Attaching and detaching is also an
> option.
> Server renaming will stop SQL Server 7 from running, you'll need the setup
> cd to revive sql server. (not a problem with SQL2000)
> You don't need new databases, they will be created during restore.
> You can not restore the master DB.
> Search support.microsoft.com for 'move database' on SQL Server, you'll
find
> a lot of info (scripts for moving users, ...)
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
>
> "Evan Mann" <ask@.for.it> wrote in message
> news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
For
existing
default
> simply
restore
intial
the
>
>|||Evan
Look at this very useful article.Vyas is explained detailed step by step how
to move all data to the new one.
http://vyaskn.tripod.com/moving_sql_server.htm
"Evan Mann" <ask@.for.it> wrote in message
news:HG9%b.138556$jH.890194@.twister.tampabay.rr.com...
> These are 2 separate machines, but which have the same exact version of
SQL
> Server 2000 SP3a+hotfixes. I know what is involved with renaming a
server.
> I won't be renaming the server with any of these DBs I am moving
attached.
> The backup/resotre procedure seems to be the easiest. Does this not get
the
> entire database, including all the users, stored procedures, etc. Why do
I
> need to move users if I am changing machines? Is this not all kept in the
> DB itself?
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
it.
an
setup
> find
> For
> existing
> default
> restore
> intial
> the
>

Moving all DBs to new machine with same name

I need to move all my SQL DB's from an old machine to a new machine. For
ease, I'm going to keep the new server the same name and IP address.
My plan is as follows
1) Ensure all users out of all DBs
2) Disconnect server from network
3) Performance full backups of ALL databases
4) Shutdown SQL Server service
5) Re-connect server to network and transfer DB backups to new machine
6) Shut down old server
7) Rename new server to old servers name and change to old servers IP,
reboot
Now here is where I need advice. To restore all my DBs, I need existing
DBs with matching names, correct? So the master DB and the other default
DBs I can restore over, but all of my additional DBs I cannot. Do I simply
create a new DB with a matching name to my additional DBs and then restore
over them?
Should I restore the master and default DBs first, then create the intial
DBs, then restore. Or do I create teh DBs, restore them all, then do the
master? What's the correct order?First advice, if you don't have the right feeling about this, don't do it.
Which SQL Servers are you using ? Both the same version ?
Backup/restore is not the only option. Attaching and detaching is also an
option.
Server renaming will stop SQL Server 7 from running, you'll need the setup
cd to revive sql server. (not a problem with SQL2000)
You don't need new databases, they will be created during restore.
You can not restore the master DB.
Search support.microsoft.com for 'move database' on SQL Server, you'll find
a lot of info (scripts for moving users, ...)
--
Nico De Greef
Belgium
Freelance Software Architect
MCP, MCSD, .NET certified
"Evan Mann" <ask@.for.it> wrote in message
news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> I need to move all my SQL DB's from an old machine to a new machine. For
> ease, I'm going to keep the new server the same name and IP address.
> My plan is as follows
> 1) Ensure all users out of all DBs
> 2) Disconnect server from network
> 3) Performance full backups of ALL databases
> 4) Shutdown SQL Server service
> 5) Re-connect server to network and transfer DB backups to new machine
> 6) Shut down old server
> 7) Rename new server to old servers name and change to old servers IP,
> reboot
> Now here is where I need advice. To restore all my DBs, I need existing
> DBs with matching names, correct? So the master DB and the other default
> DBs I can restore over, but all of my additional DBs I cannot. Do I
simply
> create a new DB with a matching name to my additional DBs and then restore
> over them?
> Should I restore the master and default DBs first, then create the intial
> DBs, then restore. Or do I create teh DBs, restore them all, then do the
> master? What's the correct order?
>
>|||These are 2 separate machines, but which have the same exact version of SQL
Server 2000 SP3a+hotfixes. I know what is involved with renaming a server.
I won't be renaming the server with any of these DBs I am moving attached.
The backup/resotre procedure seems to be the easiest. Does this not get the
entire database, including all the users, stored procedures, etc. Why do I
need to move users if I am changing machines? Is this not all kept in the
DB itself?
"Nico De Greef" <ndg@.denco.be> wrote in message
news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
> First advice, if you don't have the right feeling about this, don't do it.
> Which SQL Servers are you using ? Both the same version ?
> Backup/restore is not the only option. Attaching and detaching is also an
> option.
> Server renaming will stop SQL Server 7 from running, you'll need the setup
> cd to revive sql server. (not a problem with SQL2000)
> You don't need new databases, they will be created during restore.
> You can not restore the master DB.
> Search support.microsoft.com for 'move database' on SQL Server, you'll
find
> a lot of info (scripts for moving users, ...)
> --
> Nico De Greef
> Belgium
> Freelance Software Architect
> MCP, MCSD, .NET certified
>
> "Evan Mann" <ask@.for.it> wrote in message
> news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> > I need to move all my SQL DB's from an old machine to a new machine.
For
> > ease, I'm going to keep the new server the same name and IP address.
> >
> > My plan is as follows
> >
> > 1) Ensure all users out of all DBs
> > 2) Disconnect server from network
> > 3) Performance full backups of ALL databases
> > 4) Shutdown SQL Server service
> > 5) Re-connect server to network and transfer DB backups to new machine
> > 6) Shut down old server
> > 7) Rename new server to old servers name and change to old servers IP,
> > reboot
> >
> > Now here is where I need advice. To restore all my DBs, I need
existing
> > DBs with matching names, correct? So the master DB and the other
default
> > DBs I can restore over, but all of my additional DBs I cannot. Do I
> simply
> > create a new DB with a matching name to my additional DBs and then
restore
> > over them?
> >
> > Should I restore the master and default DBs first, then create the
intial
> > DBs, then restore. Or do I create teh DBs, restore them all, then do
the
> > master? What's the correct order?
> >
> >
> >
>
>|||Evan
Look at this very useful article.Vyas is explained detailed step by step how
to move all data to the new one.
http://vyaskn.tripod.com/moving_sql_server.htm
"Evan Mann" <ask@.for.it> wrote in message
news:HG9%b.138556$jH.890194@.twister.tampabay.rr.com...
> These are 2 separate machines, but which have the same exact version of
SQL
> Server 2000 SP3a+hotfixes. I know what is involved with renaming a
server.
> I won't be renaming the server with any of these DBs I am moving
attached.
> The backup/resotre procedure seems to be the easiest. Does this not get
the
> entire database, including all the users, stored procedures, etc. Why do
I
> need to move users if I am changing machines? Is this not all kept in the
> DB itself?
>
> "Nico De Greef" <ndg@.denco.be> wrote in message
> news:%23muWU%237%23DHA.2516@.TK2MSFTNGP11.phx.gbl...
> > First advice, if you don't have the right feeling about this, don't do
it.
> >
> > Which SQL Servers are you using ? Both the same version ?
> >
> > Backup/restore is not the only option. Attaching and detaching is also
an
> > option.
> > Server renaming will stop SQL Server 7 from running, you'll need the
setup
> > cd to revive sql server. (not a problem with SQL2000)
> > You don't need new databases, they will be created during restore.
> > You can not restore the master DB.
> >
> > Search support.microsoft.com for 'move database' on SQL Server, you'll
> find
> > a lot of info (scripts for moving users, ...)
> >
> > --
> > Nico De Greef
> > Belgium
> > Freelance Software Architect
> > MCP, MCSD, .NET certified
> >
> >
> >
> > "Evan Mann" <ask@.for.it> wrote in message
> > news:2e3%b.137969$jH.860266@.twister.tampabay.rr.com...
> > > I need to move all my SQL DB's from an old machine to a new machine.
> For
> > > ease, I'm going to keep the new server the same name and IP address.
> > >
> > > My plan is as follows
> > >
> > > 1) Ensure all users out of all DBs
> > > 2) Disconnect server from network
> > > 3) Performance full backups of ALL databases
> > > 4) Shutdown SQL Server service
> > > 5) Re-connect server to network and transfer DB backups to new machine
> > > 6) Shut down old server
> > > 7) Rename new server to old servers name and change to old servers IP,
> > > reboot
> > >
> > > Now here is where I need advice. To restore all my DBs, I need
> existing
> > > DBs with matching names, correct? So the master DB and the other
> default
> > > DBs I can restore over, but all of my additional DBs I cannot. Do I
> > simply
> > > create a new DB with a matching name to my additional DBs and then
> restore
> > > over them?
> > >
> > > Should I restore the master and default DBs first, then create the
> intial
> > > DBs, then restore. Or do I create teh DBs, restore them all, then do
> the
> > > master? What's the correct order?
> > >
> > >
> > >
> >
> >
> >
>sql

Moving a SQL DB

Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!Hi, eye,
Simple method. Backup the database from one server, restore it to the
other.
You may wind up with some orphaned users. Look at topics in the BOL such
as:
Troubleshooting Orphaned Users
http://tinyurl.com/7yjwt
Also, the commands sp_validatelogins, sp_change_users_login,
sp_resolve_logins may be helpful for straightening things out.
Russell Fields
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
> Sorry for the remedial question...Newbie here...
> I need to move a sql db from one sql machine to another, i do have access
to
> MMC...Can someone please provide me with detailed instructions?
> I have experimented with exporting the data from one and importing to
> another, but i haven't had much luck, primary keys seem to get messed up.
> Any help would be greatly appreciated.
> THanks!
>|||Use BACKUP and RESTORE commands.
For example:
--On the source server
BACKUP DATABASE YourDatabase TO
DISK='G:\MSSQL\Backup\YourDatabaseBackup
.BAK'
--Copy the backup file or share it, so that it is visible on the target
server
--Run the following command to restore it
RESTORE DATABASE YourDatabase FROM
DISK='G:\MSSQL\Backup\YourDatabaseBackup
.BAK'
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!

Moving a SQL DB

Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!
Hi, eye,
Simple method. Backup the database from one server, restore it to the
other.
You may wind up with some orphaned users. Look at topics in the BOL such
as:
Troubleshooting Orphaned Users
http://tinyurl.com/7yjwt
Also, the commands sp_validatelogins, sp_change_users_login,
sp_resolve_logins may be helpful for straightening things out.
Russell Fields
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
> Sorry for the remedial question...Newbie here...
> I need to move a sql db from one sql machine to another, i do have access
to
> MMC...Can someone please provide me with detailed instructions?
> I have experimented with exporting the data from one and importing to
> another, but i haven't had much luck, primary keys seem to get messed up.
> Any help would be greatly appreciated.
> THanks!
>
|||Use BACKUP and RESTORE commands.
For example:
--On the source server
BACKUP DATABASE YourDatabase TO
DISK='G:\MSSQL\Backup\YourDatabaseBackup.BAK'
--Copy the backup file or share it, so that it is visible on the target
server
--Run the following command to restore it
RESTORE DATABASE YourDatabase FROM
DISK='G:\MSSQL\Backup\YourDatabaseBackup.BAK'
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
"eyespike1" <eyespike1@.discussions.microsoft.com> wrote in message
news:D4CE5D5F-798A-4645-BB29-7D85CBCF94A0@.microsoft.com...
Sorry for the remedial question...Newbie here...
I need to move a sql db from one sql machine to another, i do have access to
MMC...Can someone please provide me with detailed instructions?
I have experimented with exporting the data from one and importing to
another, but i haven't had much luck, primary keys seem to get messed up.
Any help would be greatly appreciated.
THanks!

moving a project to another machine

Hey,
I have copied a C# express edition project from another machine and have opened the project up and changed the connection string but the project is not updating the database when I try to insert values from textboxes in forms. They seem do be getting into the dataset but not the sql datatabase.
I am getting alot of messages saying that it could not find the schema information for the dataset. Could these errors have anything to do with the project?
What else do I have to change in the project?

Thanks in advance.

Hi, Yorick85

What kind of database were you using?

In general, please check the database permission to your local machine. And make sure that connection has been correct.

Thanks

|||Hi,

I am using SQL server 2005 on both machines. How do I check the database permission on my local machine?
Thanks
|||

It depends on what is your connection string security mode, windows Authentication or sql server Authentication?

If it need user name or password in the Authentication, you should check the user and role in Sql Server Management Studio for particular database.

Thanks

sql

moving a project to another machine

Hey,
I have copied a C# express edition project from another machine and have opened the project up and changed the connection string but the project is not updating the database when I try to insert values from textboxes in forms. They seem do be getting into the dataset but not the sql datatabase.
I am getting alot of messages saying that it could not find the schema information for the dataset. Could these errors have anything to do with the project?
What else do I have to change in the project?

Thanks in advance.

Hi, Yorick85

What kind of database were you using?

In general, please check the database permission to your local machine. And make sure that connection has been correct.

Thanks

|||Hi,

I am using SQL server 2005 on both machines. How do I check the database permission on my local machine?
Thanks
|||

It depends on what is your connection string security mode, windows Authentication or sql server Authentication?

If it need user name or password in the Authentication, you should check the user and role in Sql Server Management Studio for particular database.

Thanks

Moving a database from one machine to another and the lack of speed

Dear All,

We have a procedure that takes12 minutes to run on the first server but
that same procedure now takes 3 hours to run on the second server using the
same data. Does anyone have any suggestions why this is happening and how
to make the procedure faster on the second server

Thanks in advance.

Jeff Magouirk"Jeff Magouirk" <magouirkj@.njc.org> wrote in message
news:113bhrh7krjtjad@.corp.supernews.com...
> Dear All,
> We have a procedure that takes12 minutes to run on the first server but
> that same procedure now takes 3 hours to run on the second server using
> the same data. Does anyone have any suggestions why this is happening and
> how to make the procedure faster on the second server
> Thanks in advance.
> Jeff Magouirk
>

No idea - the usual advice if you see something like that is to a) update
statistics, and b) use Profiler to identify what's taking so much time. You
can also capture the execution plan in Profiler (or use Query Analyzer,
which is a bit more user-friendly), and compare it against the plan on the
original server.

If that doesn't help, you'll need to give more details about your
environment - are they the same MSSQL version? The same hardware? Do you
have the same database schema (including indexes etc.)? Are the database
files exactly the same size? Are they using the same recovery model? Is the
data the same (or nearly so)? Is everything slow, or only certain
procedures? Etc.

Simon

moving a 2005 DB to 2000

I haven't tried this yet so forgive me.
But are there any issues with taking a database on SQL 2005 and putting
it on a machine that only has sql 2000? I know when you attach the 2000
DB in 2005 it does some type of upgrade - is there a cooresponding
"downgrade"?
Darin
*** Sent via Developersdex http://www.developersdex.com ***"Darin" <darin_nospam@.nospamever> wrote in message
news:uE%23qtqUHIHA.2100@.TK2MSFTNGP03.phx.gbl...
>I haven't tried this yet so forgive me.
> But are there any issues with taking a database on SQL 2005 and putting
> it on a machine that only has sql 2000? I know when you attach the 2000
> DB in 2005 it does some type of upgrade - is there a cooresponding
> "downgrade"?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***
No. You can't restore a 2005 database to a 2000 server and you can't attach
it either.
For schema objects the best option is probably to generate scripts. 2005
Management Studio gives you the option to generate backwards-compatible
scripts. For data, use BCP or DTS or link the servers and INSERT / SELECT
INTO.
Hope this helps.
--
David Portas|||Hello Darin,
There is no a direct way to accomplish this task however there are
workarounds. Check out the following page:
http://scottelkin.com/archive/2007/02/15/How-to-Downgrade-a-Database-from-SQL-Server-2005-to-SQL-Server-2000.aspx
--
Ekrem Önsoy
http://www.ekremonsoy.net , http://ekremonsoy.blogspot.com
MCBDA, MCITP:DBA, MCSD.Net, MCSE, MCBMSP, MCT
"Darin" <darin_nospam@.nospamever> wrote in message
news:uE%23qtqUHIHA.2100@.TK2MSFTNGP03.phx.gbl...
>I haven't tried this yet so forgive me.
> But are there any issues with taking a database on SQL 2005 and putting
> it on a machine that only has sql 2000? I know when you attach the 2000
> DB in 2005 it does some type of upgrade - is there a cooresponding
> "downgrade"?
> Darin
> *** Sent via Developersdex http://www.developersdex.com ***

Moving 6.5 DB's

I have a legacy database installed on a SQL Server 2000 machine, running in
6.5 compatibility mode. What is the best method to move it to another SQL
Server 2000 server, while still in 6.5 compatibility mode?
More details: The DB is about 4.8GB. It is a data source for a 3rd party
application, so accessing the client code will be difficult at best (I do
know how to modify its .ini file to redirect the connection string to another
server, as this has been done before). I am concerned with updating the
database to 2000, since I can't say for sure that the client app will still
work if I do.
I tried using a standard backup from EM, which failed. I discovered that
the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
still being available for backward compatibility , it doesn't show what the
syntax used to be or what arguments it used back then.
The method of moving the database I was trying to do was to back up the
database, and restore it to a different server. The DB is for archive
purposes only, so there are no transactions taking place.
I am concerned with the best way to do this. Since discovering that there is
no back-up of the database (The job set up by the previous DB Admin has been
failing all this time), and if I upgrade it, there is no fall-back. The only
way to recover it then is the server back-up tapes. This is the only copy of
this database, so I'm concerned with the best approach to this situation.
Of course, once I have it copied into the test environment, I will test how
it integrates with the client app after upgrading to 2000. But for now, I
just need to know how to safely move it from the production server to the
test server.
Suggestions? Thanks!Joel,
First step - make a backup. I'd also perform a DBCC CHECKDB on the database
as well. The backup/restore method should work with no issues (outside of
possible drive/path differences). Also, sp_detach_db and sp_attach_db would
probably be the easiest. You can control the compatibility mode by using
sp_dbcmptlevel (see SQL BOL) for the database.
For moving the database see (in addition to SQL BOL):
http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to
> another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will
> still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what
> the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there
> is
> no back-up of the database (The job set up by the previous DB Admin has
> been
> failing all this time), and if I upgrade it, there is no fall-back. The
> only
> way to recover it then is the server back-up tapes. This is the only copy
> of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test
> how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!|||I thought about the detach / attach options as well. However, I'm trying to
copy the database, not move it. I thought detaching & attaching was only for
moving them.
"Jerry Spivey" wrote:
> Joel,
> First step - make a backup. I'd also perform a DBCC CHECKDB on the database
> as well. The backup/restore method should work with no issues (outside of
> possible drive/path differences). Also, sp_detach_db and sp_attach_db would
> probably be the easiest. You can control the compatibility mode by using
> sp_dbcmptlevel (see SQL BOL) for the database.
> For moving the database see (in addition to SQL BOL):
> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
> >I have a legacy database installed on a SQL Server 2000 machine, running in
> > 6.5 compatibility mode. What is the best method to move it to another SQL
> > Server 2000 server, while still in 6.5 compatibility mode?
> >
> > More details: The DB is about 4.8GB. It is a data source for a 3rd party
> > application, so accessing the client code will be difficult at best (I do
> > know how to modify its .ini file to redirect the connection string to
> > another
> > server, as this has been done before). I am concerned with updating the
> > database to 2000, since I can't say for sure that the client app will
> > still
> > work if I do.
> >
> > I tried using a standard backup from EM, which failed. I discovered that
> > the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> > how
> > to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> > still being available for backward compatibility , it doesn't show what
> > the
> > syntax used to be or what arguments it used back then.
> >
> > The method of moving the database I was trying to do was to back up the
> > database, and restore it to a different server. The DB is for archive
> > purposes only, so there are no transactions taking place.
> >
> > I am concerned with the best way to do this. Since discovering that there
> > is
> > no back-up of the database (The job set up by the previous DB Admin has
> > been
> > failing all this time), and if I upgrade it, there is no fall-back. The
> > only
> > way to recover it then is the server back-up tapes. This is the only copy
> > of
> > this database, so I'm concerned with the best approach to this situation.
> >
> > Of course, once I have it copied into the test environment, I will test
> > how
> > it integrates with the client app after upgrading to 2000. But for now, I
> > just need to know how to safely move it from the production server to the
> > test server.
> >
> > Suggestions? Thanks!
>
>|||Joel,
You can detach the database, copy the file, then attach the database both
locally and remotely. Notice you'll be copying the file not moving it in
this case.
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...
>I thought about the detach / attach options as well. However, I'm trying to
> copy the database, not move it. I thought detaching & attaching was only
> for
> moving them.
> "Jerry Spivey" wrote:
>> Joel,
>> First step - make a backup. I'd also perform a DBCC CHECKDB on the
>> database
>> as well. The backup/restore method should work with no issues (outside
>> of
>> possible drive/path differences). Also, sp_detach_db and sp_attach_db
>> would
>> probably be the easiest. You can control the compatibility mode by using
>> sp_dbcmptlevel (see SQL BOL) for the database.
>> For moving the database see (in addition to SQL BOL):
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> HTH
>> Jerry
>> "Joel" <Joel@.discussions.microsoft.com> wrote in message
>> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>> >I have a legacy database installed on a SQL Server 2000 machine, running
>> >in
>> > 6.5 compatibility mode. What is the best method to move it to another
>> > SQL
>> > Server 2000 server, while still in 6.5 compatibility mode?
>> >
>> > More details: The DB is about 4.8GB. It is a data source for a 3rd
>> > party
>> > application, so accessing the client code will be difficult at best (I
>> > do
>> > know how to modify its .ini file to redirect the connection string to
>> > another
>> > server, as this has been done before). I am concerned with updating
>> > the
>> > database to 2000, since I can't say for sure that the client app will
>> > still
>> > work if I do.
>> >
>> > I tried using a standard backup from EM, which failed. I discovered
>> > that
>> > the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL
>> > for
>> > how
>> > to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE
>> > command
>> > still being available for backward compatibility , it doesn't show what
>> > the
>> > syntax used to be or what arguments it used back then.
>> >
>> > The method of moving the database I was trying to do was to back up the
>> > database, and restore it to a different server. The DB is for archive
>> > purposes only, so there are no transactions taking place.
>> >
>> > I am concerned with the best way to do this. Since discovering that
>> > there
>> > is
>> > no back-up of the database (The job set up by the previous DB Admin has
>> > been
>> > failing all this time), and if I upgrade it, there is no fall-back. The
>> > only
>> > way to recover it then is the server back-up tapes. This is the only
>> > copy
>> > of
>> > this database, so I'm concerned with the best approach to this
>> > situation.
>> >
>> > Of course, once I have it copied into the test environment, I will test
>> > how
>> > it integrates with the client app after upgrading to 2000. But for now,
>> > I
>> > just need to know how to safely move it from the production server to
>> > the
>> > test server.
>> >
>> > Suggestions? Thanks!
>>|||Hi,
To add on to Joel; Since compatibility mode is stored in Master database --
sysdatabases table; you may need to set the
db compatibility level to 65 again using
EXEC sp_dbcmptlevel 'DBNAME', 65
Note:-
If you need to keep the source database online then use BACKUP / RESTORE
commds to copy the database.
1. Backup the database
2. copy the backup file to destination
3. Restore the database
4. Set the dbcopmatibility to 65
5. sync the logins and users using sp_change_users_login
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ucTpDiUxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Joel,
> You can detach the database, copy the file, then attach the database both
> locally and remotely. Notice you'll be copying the file not moving it in
> this case.
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...
>>I thought about the detach / attach options as well. However, I'm trying
>>to
>> copy the database, not move it. I thought detaching & attaching was only
>> for
>> moving them.
>> "Jerry Spivey" wrote:
>> Joel,
>> First step - make a backup. I'd also perform a DBCC CHECKDB on the
>> database
>> as well. The backup/restore method should work with no issues (outside
>> of
>> possible drive/path differences). Also, sp_detach_db and sp_attach_db
>> would
>> probably be the easiest. You can control the compatibility mode by
>> using
>> sp_dbcmptlevel (see SQL BOL) for the database.
>> For moving the database see (in addition to SQL BOL):
>> http://support.microsoft.com/default.aspx?scid=kb;en-us;314546
>> HTH
>> Jerry
>> "Joel" <Joel@.discussions.microsoft.com> wrote in message
>> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>> >I have a legacy database installed on a SQL Server 2000 machine,
>> >running in
>> > 6.5 compatibility mode. What is the best method to move it to another
>> > SQL
>> > Server 2000 server, while still in 6.5 compatibility mode?
>> >
>> > More details: The DB is about 4.8GB. It is a data source for a 3rd
>> > party
>> > application, so accessing the client code will be difficult at best (I
>> > do
>> > know how to modify its .ini file to redirect the connection string to
>> > another
>> > server, as this has been done before). I am concerned with updating
>> > the
>> > database to 2000, since I can't say for sure that the client app will
>> > still
>> > work if I do.
>> >
>> > I tried using a standard backup from EM, which failed. I discovered
>> > that
>> > the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL
>> > for
>> > how
>> > to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE
>> > command
>> > still being available for backward compatibility , it doesn't show
>> > what
>> > the
>> > syntax used to be or what arguments it used back then.
>> >
>> > The method of moving the database I was trying to do was to back up
>> > the
>> > database, and restore it to a different server. The DB is for archive
>> > purposes only, so there are no transactions taking place.
>> >
>> > I am concerned with the best way to do this. Since discovering that
>> > there
>> > is
>> > no back-up of the database (The job set up by the previous DB Admin
>> > has
>> > been
>> > failing all this time), and if I upgrade it, there is no fall-back.
>> > The
>> > only
>> > way to recover it then is the server back-up tapes. This is the only
>> > copy
>> > of
>> > this database, so I'm concerned with the best approach to this
>> > situation.
>> >
>> > Of course, once I have it copied into the test environment, I will
>> > test
>> > how
>> > it integrates with the client app after upgrading to 2000. But for
>> > now, I
>> > just need to know how to safely move it from the production server to
>> > the
>> > test server.
>> >
>> > Suggestions? Thanks!
>>
>|||DUMP and LOAD is essentially the same as BACKUP and RESTORE, for instance:
DUMP DATABASE db
TO DISK = 'C:\a.bak'
LOAD DATABASE db
FROM DISK = 'C:\a.bak'
I don't know if new features of BACKUP and RESTORE are available for DUMP and LOAD, like RECOVERY
etc. Wouldn't think so.
However, it doesn't matter. Always execute the backup and restore commands from the master database,
and master cannot be in down-level compatibility mode. Or, use detach and attach, as suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there is
> no back-up of the database (The job set up by the previous DB Admin has been
> failing all this time), and if I upgrade it, there is no fall-back. The only
> way to recover it then is the server back-up tapes. This is the only copy of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!

Moving 6.5 DB's

I have a legacy database installed on a SQL Server 2000 machine, running in
6.5 compatibility mode. What is the best method to move it to another SQL
Server 2000 server, while still in 6.5 compatibility mode?
More details: The DB is about 4.8GB. It is a data source for a 3rd party
application, so accessing the client code will be difficult at best (I do
know how to modify its .ini file to redirect the connection string to anothe
r
server, as this has been done before). I am concerned with updating the
database to 2000, since I can't say for sure that the client app will still
work if I do.
I tried using a standard backup from EM, which failed. I discovered that
the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
still being available for backward compatibility , it doesn't show what the
syntax used to be or what arguments it used back then.
The method of moving the database I was trying to do was to back up the
database, and restore it to a different server. The DB is for archive
purposes only, so there are no transactions taking place.
I am concerned with the best way to do this. Since discovering that there is
no back-up of the database (The job set up by the previous DB Admin has been
failing all this time), and if I upgrade it, there is no fall-back. The only
way to recover it then is the server back-up tapes. This is the only copy of
this database, so I'm concerned with the best approach to this situation.
Of course, once I have it copied into the test environment, I will test how
it integrates with the client app after upgrading to 2000. But for now, I
just need to know how to safely move it from the production server to the
test server.
Suggestions? Thanks!Joel,
First step - make a backup. I'd also perform a DBCC CHECKDB on the database
as well. The backup/restore method should work with no issues (outside of
possible drive/path differences). Also, sp_detach_db and sp_attach_db would
probably be the easiest. You can control the compatibility mode by using
sp_dbcmptlevel (see SQL BOL) for the database.
For moving the database see (in addition to SQL BOL):
http://support.microsoft.com/defaul...kb;en-us;314546
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to
> another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will
> still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what
> the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there
> is
> no back-up of the database (The job set up by the previous DB Admin has
> been
> failing all this time), and if I upgrade it, there is no fall-back. The
> only
> way to recover it then is the server back-up tapes. This is the only copy
> of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test
> how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!|||I thought about the detach / attach options as well. However, I'm trying to
copy the database, not move it. I thought detaching & attaching was only for
moving them.
"Jerry Spivey" wrote:

> Joel,
> First step - make a backup. I'd also perform a DBCC CHECKDB on the databa
se
> as well. The backup/restore method should work with no issues (outside of
> possible drive/path differences). Also, sp_detach_db and sp_attach_db wou
ld
> probably be the easiest. You can control the compatibility mode by using
> sp_dbcmptlevel (see SQL BOL) for the database.
> For moving the database see (in addition to SQL BOL):
> http://support.microsoft.com/defaul...kb;en-us;314546
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>
>|||Joel,
You can detach the database, copy the file, then attach the database both
locally and remotely. Notice you'll be copying the file not moving it in
this case.
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...[vbcol=seagreen]
>I thought about the detach / attach options as well. However, I'm trying to
> copy the database, not move it. I thought detaching & attaching was only
> for
> moving them.
> "Jerry Spivey" wrote:
>|||Hi,
To add on to Joel; Since compatibility mode is stored in Master database --
sysdatabases table; you may need to set the
db compatibility level to 65 again using
EXEC sp_dbcmptlevel 'DBNAME', 65
Note:-
If you need to keep the source database online then use BACKUP / RESTORE
commds to copy the database.
1. Backup the database
2. copy the backup file to destination
3. Restore the database
4. Set the dbcopmatibility to 65
5. sync the logins and users using sp_change_users_login
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ucTpDiUxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Joel,
> You can detach the database, copy the file, then attach the database both
> locally and remotely. Notice you'll be copying the file not moving it in
> this case.
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...
>|||DUMP and LOAD is essentially the same as BACKUP and RESTORE, for instance:
DUMP DATABASE db
TO DISK = 'C:\a.bak'
LOAD DATABASE db
FROM DISK = 'C:\a.bak'
I don't know if new features of BACKUP and RESTORE are available for DUMP an
d LOAD, like RECOVERY
etc. Wouldn't think so.
However, it doesn't matter. Always execute the backup and restore commands f
rom the master database,
and master cannot be in down-level compatibility mode. Or, use detach and at
tach, as suggested.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to anot
her
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will stil
l
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for h
ow
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what th
e
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there
is
> no back-up of the database (The job set up by the previous DB Admin has be
en
> failing all this time), and if I upgrade it, there is no fall-back. The on
ly
> way to recover it then is the server back-up tapes. This is the only copy
of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test ho
w
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!

Moving 6.5 DB's

I have a legacy database installed on a SQL Server 2000 machine, running in
6.5 compatibility mode. What is the best method to move it to another SQL
Server 2000 server, while still in 6.5 compatibility mode?
More details: The DB is about 4.8GB. It is a data source for a 3rd party
application, so accessing the client code will be difficult at best (I do
know how to modify its .ini file to redirect the connection string to another
server, as this has been done before). I am concerned with updating the
database to 2000, since I can't say for sure that the client app will still
work if I do.
I tried using a standard backup from EM, which failed. I discovered that
the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
still being available for backward compatibility , it doesn't show what the
syntax used to be or what arguments it used back then.
The method of moving the database I was trying to do was to back up the
database, and restore it to a different server. The DB is for archive
purposes only, so there are no transactions taking place.
I am concerned with the best way to do this. Since discovering that there is
no back-up of the database (The job set up by the previous DB Admin has been
failing all this time), and if I upgrade it, there is no fall-back. The only
way to recover it then is the server back-up tapes. This is the only copy of
this database, so I'm concerned with the best approach to this situation.
Of course, once I have it copied into the test environment, I will test how
it integrates with the client app after upgrading to 2000. But for now, I
just need to know how to safely move it from the production server to the
test server.
Suggestions? Thanks!
Joel,
First step - make a backup. I'd also perform a DBCC CHECKDB on the database
as well. The backup/restore method should work with no issues (outside of
possible drive/path differences). Also, sp_detach_db and sp_attach_db would
probably be the easiest. You can control the compatibility mode by using
sp_dbcmptlevel (see SQL BOL) for the database.
For moving the database see (in addition to SQL BOL):
http://support.microsoft.com/default...b;en-us;314546
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to
> another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will
> still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for
> how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what
> the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there
> is
> no back-up of the database (The job set up by the previous DB Admin has
> been
> failing all this time), and if I upgrade it, there is no fall-back. The
> only
> way to recover it then is the server back-up tapes. This is the only copy
> of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test
> how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!
|||I thought about the detach / attach options as well. However, I'm trying to
copy the database, not move it. I thought detaching & attaching was only for
moving them.
"Jerry Spivey" wrote:

> Joel,
> First step - make a backup. I'd also perform a DBCC CHECKDB on the database
> as well. The backup/restore method should work with no issues (outside of
> possible drive/path differences). Also, sp_detach_db and sp_attach_db would
> probably be the easiest. You can control the compatibility mode by using
> sp_dbcmptlevel (see SQL BOL) for the database.
> For moving the database see (in addition to SQL BOL):
> http://support.microsoft.com/default...b;en-us;314546
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>
>
|||Joel,
You can detach the database, copy the file, then attach the database both
locally and remotely. Notice you'll be copying the file not moving it in
this case.
HTH
Jerry
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...[vbcol=seagreen]
>I thought about the detach / attach options as well. However, I'm trying to
> copy the database, not move it. I thought detaching & attaching was only
> for
> moving them.
> "Jerry Spivey" wrote:
|||Hi,
To add on to Joel; Since compatibility mode is stored in Master database --
sysdatabases table; you may need to set the
db compatibility level to 65 again using
EXEC sp_dbcmptlevel 'DBNAME', 65
Note:-
If you need to keep the source database online then use BACKUP / RESTORE
commds to copy the database.
1. Backup the database
2. copy the backup file to destination
3. Restore the database
4. Set the dbcopmatibility to 65
5. sync the logins and users using sp_change_users_login
Thanks
Hari
SQL Server MVP
"Jerry Spivey" <jspivey@.vestas-awt.com> wrote in message
news:ucTpDiUxFHA.2312@.TK2MSFTNGP14.phx.gbl...
> Joel,
> You can detach the database, copy the file, then attach the database both
> locally and remotely. Notice you'll be copying the file not moving it in
> this case.
> HTH
> Jerry
> "Joel" <Joel@.discussions.microsoft.com> wrote in message
> news:A22F722F-0274-431E-869D-E7AE35B0F6E6@.microsoft.com...
>
|||DUMP and LOAD is essentially the same as BACKUP and RESTORE, for instance:
DUMP DATABASE db
TO DISK = 'C:\a.bak'
LOAD DATABASE db
FROM DISK = 'C:\a.bak'
I don't know if new features of BACKUP and RESTORE are available for DUMP and LOAD, like RECOVERY
etc. Wouldn't think so.
However, it doesn't matter. Always execute the backup and restore commands from the master database,
and master cannot be in down-level compatibility mode. Or, use detach and attach, as suggested.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joel" <Joel@.discussions.microsoft.com> wrote in message
news:C8C1EC22-A943-4E1F-BD2D-39424025DBBC@.microsoft.com...
>I have a legacy database installed on a SQL Server 2000 machine, running in
> 6.5 compatibility mode. What is the best method to move it to another SQL
> Server 2000 server, while still in 6.5 compatibility mode?
> More details: The DB is about 4.8GB. It is a data source for a 3rd party
> application, so accessing the client code will be difficult at best (I do
> know how to modify its .ini file to redirect the connection string to another
> server, as this has been done before). I am concerned with updating the
> database to 2000, since I can't say for sure that the client app will still
> work if I do.
> I tried using a standard backup from EM, which failed. I discovered that
> the backup method changed between ver's. 6.5 & 7.0. I lookeed in BOL for how
> to do it. Although it mentions the DUMP DATABASE / LOAD DATABASE command
> still being available for backward compatibility , it doesn't show what the
> syntax used to be or what arguments it used back then.
> The method of moving the database I was trying to do was to back up the
> database, and restore it to a different server. The DB is for archive
> purposes only, so there are no transactions taking place.
> I am concerned with the best way to do this. Since discovering that there is
> no back-up of the database (The job set up by the previous DB Admin has been
> failing all this time), and if I upgrade it, there is no fall-back. The only
> way to recover it then is the server back-up tapes. This is the only copy of
> this database, so I'm concerned with the best approach to this situation.
> Of course, once I have it copied into the test environment, I will test how
> it integrates with the client app after upgrading to 2000. But for now, I
> just need to know how to safely move it from the production server to the
> test server.
> Suggestions? Thanks!
sql

Moved SQL DB to SQL2005 and it is slow

Hello,
I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
The same DB on a slower machine running 2000 runs mutch faster.
For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
seconds, 20 times slower.
I reindexed and that did not help.
Any Ideas?
Why the same DB with same indexes behaving that way, what Am I missing'
This is the release version of sql2005, Stardard edition, RTM...
Thanks
SAAre you accessing the database from a .Net 1.1 application using the standar
d
SqlConnection / related classes? I found that my .NET 1.1 apps would not
connect to a SQL Server 2005 instance using a shared memory connection.
Recompiling the same code with the .NET 2.0 framework resolved the issue -
the application again connected using shared memory and was sigificantly
faster. I don't know how to go about determining what mode (tcp / names
pipes / shared memory) a given connection is using, but I'm sure a quick
search will answer that.
Ross
"MSDN" wrote:

> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I missing'
'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>
>|||Have you run UPDATE TATISTICS with FULLSCAN option?
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>|||Sorry
UPDATE STATISTICS
"Uri Dimant" <urid@.iscar.co.il> wrote in message
news:OMQ9gf3NGHA.3864@.TK2MSFTNGP10.phx.gbl...
> Have you run UPDATE TATISTICS with FULLSCAN option?
>
>
> "MSDN" <sql_agentman@.hotmail.com> wrote in message
> news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
>|||MSDN (sql_agentman@.hotmail.com) writes:
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
As Uri pointed you must run UPDATE STATISTICS WITH FULLSCAN on all your
tables. The statistics from SQL 2000 are invalidated when you upgrade.
There may be more to it than that, but start there.
If you need further assistence, please be more specific of what is slow.
Is it certain queries, or is it slower overall? If you run queries from
Query Analyzer, is there still any differences (to rule out connection
issues as suggested in Ross's post). If you run from the local machine
(to exclude network issues)?
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||Perhaps it's not the database, but configuration of the SQL Server
installation. Also, if this database is running on a new server box, it may
be related hardware or OS configuration.
The following was copied from the MSDN article titled: Checklist: SQL Server
Performance
Use default server configuration settings for most applications.
Locate logs and the tempdb database on separate devices from the data.
Provide separate devices for heavily accessed tables and indexes.
Use the correct RAID configuration.
Use multiple disk controllers.
Pre-grow databases and logs to avoid automatic growth and fragmentation
performance impact.
Maximize available memory.
Manage index fragmentation.
Keep database administrator tasks in mind.
http://msdn.microsoft.com/SQL/2000/...enetcheck08.asp
"MSDN" <sql_agentman@.hotmail.com> wrote in message
news:OD02m01NGHA.2668@.tk2msftngp13.phx.gbl...
> Hello,
> I moved a sql 2000 DB to SQL 2005 and it seems to be very slow.
> The same DB on a slower machine running 2000 runs mutch faster.
> For example one SP on 2000 takes 5 seconds but on SQL 2005 it takes 102
> seconds, 20 times slower.
> I reindexed and that did not help.
> Any Ideas?
> Why the same DB with same indexes behaving that way, what Am I
> missing'
> This is the release version of sql2005, Stardard edition, RTM...
>
> Thanks
> SA
>sql

Move SQLserver to use another IP

Hi All,
I need to get the SQLserver (2000) on my Win2K machine to use another IP
than the one it is currently using.
I don't seem to be able to find out how to do it. Any leads/suggestions?
Thank you very much in advance.
Sincerely,
SSQL Server should bind to any IP address that is configured on any NIC
installed in the machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||My problem is the following:
I need to run SQLserver 2000 on port 80 on the same machine as I run IIS5 on
port 80.
I want to achieve this by disabling connection pooling and using one IP for
IIS and the other for SQLserver.
But the SQLserver seems to bind to the IP that I am using for my IIS
service. How can I make it only bind the the other IP address?
Thank you very much for any ideas.
Sincerely,
S
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:AJqjHKU2DHA.3532@.cpmsftngxa07.phx.gbl...
> SQL Server should bind to any IP address that is configured on any NIC
> installed in the machine.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>|||Susan,
Short answer is you can't. SQL Server has no facility to limit binding to
certain nics.
Long answer is - why would you want SQL Server to listen on port 80? If it's
because this gets around a firewall issue, then don't even consider it -
opening SQL Server directly to the internet is a bad idea in almost all
circumstances.
> My problem is the following:
> I need to run SQLserver 2000 on port 80 on the same machine as I run IIS5 on
> port 80.
> I want to achieve this by disabling connection pooling and using one IP for
> IIS and the other for SQLserver.
> But the SQLserver seems to bind to the IP that I am using for my IIS
> service. How can I make it only bind the the other IP address?
> Thank you very much for any ideas.
> Sincerely,
> S
> "Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:AJqjHKU2DHA.3532@.cpmsftngxa07.phx.gbl...
> > SQL Server should bind to any IP address that is configured on any NIC
> > installed in the machine.
> >
> > Rand
> > This posting is provided "as is" with no warranties and confers no rights.
> >
>
Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvforums/UK/default.asp?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Move SQLserver to use another IP

Hi All,
I need to get the SQLserver (2000) on my Win2K machine to use another IP
than the one it is currently using.
I don't seem to be able to find out how to do it. Any leads/suggestions?
Thank you very much in advance.
Sincerely,
SSQL Server should bind to any IP address that is configured on any NIC
installed in the machine.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||My problem is the following:
I need to run SQLserver 2000 on port 80 on the same machine as I run IIS5 on
port 80.
I want to achieve this by disabling connection pooling and using one IP for
IIS and the other for SQLserver.
But the SQLserver seems to bind to the IP that I am using for my IIS
service. How can I make it only bind the the other IP address?
Thank you very much for any ideas.
Sincerely,
S
"Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
news:AJqjHKU2DHA.3532@.cpmsftngxa07.phx.gbl...

quote:

---

> SQL Server should bind to any IP address that is configured on any NIC
> installed in the machine.
> Rand
> This posting is provided "as is" with no warranties and confers no rights.
>

---

|||Susan,
Short answer is you can't. SQL Server has no facility to limit binding to
certain nics.
Long answer is - why would you want SQL Server to listen on port 80? If it'
s
because this gets around a firewall issue, then don't even consider it -
opening SQL Server directly to the internet is a bad idea in almost all
circumstances.

quote:

---

> My problem is the following:
> I need to run SQLserver 2000 on port 80 on the same machine as I run IIS5
on
> port 80.
> I want to achieve this by disabling connection pooling and using one IP fo
r
> IIS and the other for SQLserver.
> But the SQLserver seems to bind to the IP that I am using for my IIS
> service. How can I make it only bind the the other IP address?
> Thank you very much for any ideas.
> Sincerely,
> S
> "Rand Boyd [MS]" <rboyd@.onlinemicrosoft.com> wrote in message
> news:AJqjHKU2DHA.3532@.cpmsftngxa07.phx.gbl...
>

---

Neil Pike MVP/MCSE. Protech Computing Ltd
Reply here - no email
SQL FAQ (484 entries) see
http://forumsb.compuserve.com/gvfor...p?SRV=MSDevApps
(faqxxx.zip in lib 7)
or www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
or www.sqlserverfaq.com
or www.mssqlserver.com/faq

Move sql database from development machine to server with active directory

Hi all! I know I might sound stupid, but I have this problem. I have developed my database on my laptop which does not use Active directory. Lets say I access the database with: SPIDERMAN\DB1 on SQL server, where the name of my laptop is SPIDERMAN, and the database is DB1. Now, I create an SQL script from sql server and make the necessary changes, like changing the computer name to say, SUPERMAN where my deployment server's name is SUPERMAN. But when I run the SQL script on the SUPERMAN server, it gives me an error like: "User or role SUPERMAN\DB1 does not exist in this database". I have deployed databases in this way on servers without Acive directory, so I'm sure its not a mistake on my side. So how am I supposed to go around this active directory thingi! Please help me out! I'm counting on u guys!

WesI am not a guy and not an admin, Microsoft MCDBA requirements but you need the Domain account name used to install SQL Server on that network to run the script ask the Network Admin to give you the info. If there is no SQL Server on the network tell the Admin to create a Domain User account that is configured as a service account so you can install SQL Server with Network permissions because you cannot run Replication in SQL Server if SQL Server is running on the Local Systems account because SQL Server Agent will not have network permissions. Hope this helps.

Kind regards,
Gift Peddie|||Thanks, Gift Peddie! I owe u a lot! But basically, I did not really get a single word you just said! It was like arrows flying over my head! Could you pleeeeeaaaasssse elaborate a little? I would be really grateful!

Thanks anyways.
I really appreciate it.

Wes.|||Thanks I see what you are saying, sorry it must be the holidays. You need the SQL Server System Admin name and pass word to modify your script before you run it. Thanks again. Hope this helps.

Kind regards,
Gift Peddie|||Thanks,
This might help me a bit. I'm understanding it a bit now. (LOL) I can be a real idiot sometimes! Don't mind me!

Regards,
Wes.

Move RS from old server to new server

I need some assistance in moving RS databases from an old server that has SQL
Server 2000 to a new machine. I was able to move the ReportServer and
ReportServerTempDB to the new machine and seems to see all the reports, but
I'm getting an error when trying to run them. "An error has occurred during
report processing. (rsProcessingAborted). Bad Data".
I had tried following http://support.microsoft.com/default.aspx?scid=842425
but no luck.
Here's the steps that I had done.
1) Detach Both databases from old server that host Reporting Services.
(ReportServer and ReportServerTempDB)
2) Attached them to new server.
3) Did "Delete from Keys where client > -1" on the new server
4) Did rsactivate to initialize.
5) Did RSConfig (Not sure if this needs to be done on source or
destination) I did this on the new server.
Only thing i left out is RSKeyMgmt.exe since that says that it will extract
the key from my source. Not sure if this will mess up the source server
since that is still be use until I can move everything to the new server.
So can someone that has move Reporting Services to a new server please help
me out?
P.S. The reporting services server does not host any of our ERP databases.
It is on a different server and the reports will grab those using datasources.
Thanks in advance.To answer your question about extracting the Key, the reason it asks you if
you want to extract the key is because this is a security issue if you leave
the file out in an un-secure location. You RS installation will continue
just fine after extracting the key; think of this as more of a BACKUP then as
an EXTRACTION.
"chang" wrote:
> I need some assistance in moving RS databases from an old server that has SQL
> Server 2000 to a new machine. I was able to move the ReportServer and
> ReportServerTempDB to the new machine and seems to see all the reports, but
> I'm getting an error when trying to run them. "An error has occurred during
> report processing. (rsProcessingAborted). Bad Data".
> I had tried following http://support.microsoft.com/default.aspx?scid=842425
> but no luck.
> Here's the steps that I had done.
> 1) Detach Both databases from old server that host Reporting Services.
> (ReportServer and ReportServerTempDB)
> 2) Attached them to new server.
> 3) Did "Delete from Keys where client > -1" on the new server
> 4) Did rsactivate to initialize.
> 5) Did RSConfig (Not sure if this needs to be done on source or
> destination) I did this on the new server.
> Only thing i left out is RSKeyMgmt.exe since that says that it will extract
> the key from my source. Not sure if this will mess up the source server
> since that is still be use until I can move everything to the new server.
> So can someone that has move Reporting Services to a new server please help
> me out?
> P.S. The reporting services server does not host any of our ERP databases.
> It is on a different server and the reports will grab those using datasources.
> Thanks in advance.

move report to other machine

Hi,
How can I move a report I developed in my computer (in Reporting Services
for SQL Server 2000) to another computer that already has SQL Server 2000
and Reporting Services installed?
Is it with Import/Export, generated scripts, ... ?
Thanks,
Nuno MachadoCopy your RDL file from your computer, to the other computer.
Go to Report Manager on the other computer, and click on the Upload File
button.
Find your RDL file where you copied it to and upload it.
Try your report.
You might have to create new datasources on the other report server. Just
copy whatever settings you have on the first one and do any adjustments you
need to do.
If you can access the other server from your computer (type in
http://whatever/reportserver ), then you don't have to copy the file, just
upload it from your web page. Or try deploying it directly through Visual
Studio, if you have the right access rights.
Kaisa M: Lindahl
"Nuno Machado" <nuno.machado@.vpconsulting.pt> wrote in message
news:er%23rSxCHGHA.1180@.TK2MSFTNGP09.phx.gbl...
> Hi,
> How can I move a report I developed in my computer (in Reporting Services
> for SQL Server 2000) to another computer that already has SQL Server 2000
> and Reporting Services installed?
> Is it with Import/Export, generated scripts, ... ?
> Thanks,
> Nuno Machado
>

move report server to new machine

Can someone tell me how I can move report server web sites into a new machine without reinstall it?

I like to keep the current report server database intact, it’s residing on a third server.

I copied the report server file folders including bin (dlls) into the new machine and set up in the new machine the Reportserver and Reports web sites pointing to these two folders while keeping the report server database still residing on the third machine, but the new site did not work, I got "Event source Report Server does not exist. (rsEventLogSourceNotFound)" error

Any advice will be greatly appreciated.

Thanks

Jerry

Well, the short answer is you can't. :) You'll need to install RS on the new machne. Licensing requires that if your RS and SQL are on different machines, you will need a seperate license for each.|||

Thanks James.

Well my next quest is: when I install RS on new machine with the license, I think I should install the new RS database on the same SQL server/database as the old RS server does, then will the newly installed RS DB overwrite the existing RS DB? or it will just makes use it without change anything in it?

Thanks a lot!

Jerry

|||It will never overwrite your RS database. Since you are doing a RS only install, you will only be able to use the "files-only" option (you will see this in the installation wizard). Post-install, you need to use the configuration tool to either create the RS databases, or set RS to connect to your old databases. The decision is up to you, but you will need to do it manually.|||Thanks a lot James, I will try.