migration from SQL 2000 to SQL 2008

[iccsi]

I am working on to migrate my SQL 2000 database to SQL 2008.
I use SSMS to connect 2 SQL Servers and want to backup SQL 2000
database and restore on SQL 2008.

I just realized that I only can access SQL 2000 server when I backup
and I only can access SQL 2008 when I want to restore.

I am looking for a method that I can backup SQL 2000 database to let
2008 access to restore.

Any information is great appreciated,

iccsi

[Erland Sommarskog]

Post by iccsi
I am working on to migrate my SQL 2000 database to SQL 2008.
I use SSMS to connect 2 SQL Servers and want to backup SQL 2000
database and restore on SQL 2008.
I just realized that I only can access SQL 2000 server when I backup
and I only can access SQL 2008 when I want to restore.
I am looking for a method that I can backup SQL 2000 database to let
2008 access to restore.
Any information is great appreciated,

I'm not sure what your problem is. From one query window you run the BACKUP
command on SQL 2000, and from another query window you run the restore
command.

If you want all in one script, you can set up a linked server on the
SQL 2008 instance to the SQL 2008 instance. Then you can say:

EXEC('BACKUP DATABASE ....') AT MySQL2000instance

(You must run this from SQL 2008, because EXEC AT is not available in
SQL 2000.)

[iccsi]

I use SSMS user interface.
The SQL Server 2000 and SQL Server 2008 are not on the same machine
say SQL Server 2000 is on machine A and SQL 2008 is on machine B.
When I use back then I need select media and it only allows me to
choose the media at C:\Program files\Microsoft SQL Server\MSSQL....
I can not backup the BAK file on any other place. It is same as SQL
Server 2008. I am unable to restore any backup database from other
directory.

I understand you use query directory. I can try to see can I backup
database to any directory other than MS SQL Server folder.

Thanks again for helping,


iccsi

Post by Erland Sommarskog

Post by iccsi
I am working on to migrate my SQL 2000 database to SQL 2008.
I use SSMS to connect  2 SQL Servers and want to backup SQL 2000
database and restore on SQL 2008.
I just realized that I only can access SQL 2000 server when I backup
and I only can access SQL 2008 when I want to restore.
I am looking for a method that I can backup  SQL 2000 database to let
2008 access to restore.
Any information is great appreciated,

I'm not sure what your problem is. From one query window you run the BACKUP
command on SQL 2000, and from another query window you run the restore
command.
If you want all in one script, you can set up a linked server on the
  EXEC('BACKUP DATABASE ....') AT MySQL2000instance
(You must run this from SQL 2008, because EXEC AT is not available in
SQL 2000.)
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[iccsi]

BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
WITH FORMAT;
GO

Thanks for message and help,
I would like to know that above SQL statement derive Z; is on the
client machine or server machine?
Because I use SSMS as a client and run the query on the client
machine, I would like to know does the drive Z: is drive on client
machine or server machine.
SSMS backup always go to server machien drive.
Thanks again for helping,


iccsi

Post by Erland Sommarskog

Post by iccsi
I am working on to migrate my SQL 2000 database to SQL 2008.
I use SSMS to connect  2 SQL Servers and want to backup SQL 2000
database and restore on SQL 2008.
I just realized that I only can access SQL 2000 server when I backup
and I only can access SQL 2008 when I want to restore.
I am looking for a method that I can backup  SQL 2000 database to let
2008 access to restore.
Any information is great appreciated,

I'm not sure what your problem is. From one query window you run the BACKUP
command on SQL 2000, and from another query window you run the restore
command.
If you want all in one script, you can set up a linked server on the
  EXEC('BACKUP DATABASE ....') AT MySQL2000instance
(You must run this from SQL 2008, because EXEC AT is not available in
SQL 2000.)
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

[Erland Sommarskog]

Post by iccsi
BACKUP DATABASE AdventureWorks2008R2
TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
WITH FORMAT;
GO
Thanks for message and help,
I would like to know that above SQL statement derive Z; is on the
client machine or server machine?
Because I use SSMS as a client and run the query on the client
machine, I would like to know does the drive Z: is drive on client
machine or server machine.
SSMS backup always go to server machien drive.
Thanks again for helping,

The drive mapping is in your user space. Use UNC path instead:
\\MACHINE\share\path\,,,

[iccsi]

Thanks for the message and help,
Yes, it is good idea to use UNC path instead.
I just checked SQL Server 2000 document that I need create a logical
device using sp_addumdevice.

I think that I need to do this for SQL Server 2000 database.
Thanks again

iccsi

Post by Erland Sommarskog

Post by iccsi
BACKUP DATABASE AdventureWorks2008R2
 TO DISK = 'Z:\SQLServerBackups\AdvWorksData.bak'
   WITH FORMAT;
GO
Thanks for message and help,
I would like to know that above SQL statement derive Z; is on the
client machine or server machine?
Because I use SSMS as a client and run the query on the client
machine, I would like to know does the drive Z: is drive on client
machine or server machine.
SSMS backup always go to server machien drive.
Thanks again for helping,

\\MACHINE\share\path\,,,
--
Books Online for SQL Server 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online for SQL Server 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Erland Sommarskog]

Post by iccsi
I just checked SQL Server 2000 document that I need create a logical
device using sp_addumdevice.

There is no need to. sp_addumpdevice is a very old remnant from SQL 6.5
and older days. Hardly anyone uses it these days - or did when SQL 2000
ruled the world. You can use DISK = '<filepath>' with SQL 2000 as well.

That said, defining a dumpdevice may be convenient if you are going to
backup the database to the same path many times, which you may do if
you are migrating.

Keep in mind that by default BACKUP appends to the backup file, so if
you say RESTORE without using WITH FILE to specify which backup you
will get the oldest in the set. You can use WITH INIT when you backup
to delete the older backups.

[iccsi]

Post by Erland Sommarskog

Post by iccsi
I just checked SQL Server 2000 document that I need create a logical
device using sp_addumdevice.

There is no need to. sp_addumpdevice is a very old remnant from SQL 6.5
and older days. Hardly anyone uses it these days - or did when SQL 2000
ruled the world. You can use DISK = '<filepath>' with SQL 2000 as well.
That said, defining a dumpdevice may be convenient if you are going to
backup the database to the same path many times, which you may do if
you are migrating.
Keep in mind that by default BACKUP appends to the backup file, so if
you say RESTORE without using WITH FILE to specify which backup you
will get the oldest in the set. You can use WITH INIT when you backup
to delete the older backups.
--
SQL 2008:http://msdn.microsoft.com/en-us/sqlserver/cc514207.aspx
SQL 2005:http://msdn.microsoft.com/en-us/sqlserver/bb895970.aspx

RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 9
RECOVERY;


I have database backup, but fail to restore.
Do I need to use 'WITH FILE =6 ' to restore database?

thanks again for helping,

iccsi

[Erland Sommarskog]

Post by iccsi
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 6
NORECOVERY;
RESTORE DATABASE AdventureWorks2008R2
FROM DISK = 'Z:\SQLServerBackups\AdventureWorks2008R2.bak'
WITH FILE = 9
RECOVERY;
I have database backup, but fail to restore.
Do I need to use 'WITH FILE =6 ' to restore database?

If you want to restore a copy of the database, you should specify WITH
RECOVERY. You use NORECOVERY if you also want to apply transaction logs.
If file 9 is a log backup, you need to say RESTORE LOG, not RESTORE
DATABASE.

[Erland Sommarskog]

I use the following SQL to restore database
restore database MyDB
from DISK = 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.CSC
\MSSQL\Backup\MyDB.bak'
but I got following error message
Msg 3154, Level 16, State 4, Line 1
The backup set holds a backup of a database other than the existing
'MyDB' database.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
It looks like my SQL is not correct or I missing something there.
I assume that the restore database name must be same backup database
name
Thanks again for helping,

So the error in the first case was that you specified a backup file that
did not exist. (Operating system error 2, use NET HELPMSG 2 to find out
what it means.)

You need to specify WITH REPLACE to overwrite an existing database.

Often when you copy databases, you also need to specify MOVE. The
command then goes:

RESTORE DATABASE db FROM DISK = '<backuppath>'
WITH MOVE '<name1>' TO '<newpath1>',
MOVE '<name2>' TO '<newpath2>',
REPLACE

The name1 and name2 the the logical name for the database files. You
can retrieve them with sp_helpdb on the source. It's the first column
in the second result set. newpath1 and newpath2 are simply the path
for the database files on the server.

[jsfromynr]

Post by iccsi
I am working on to migrate my SQL 2000 database to SQL 2008.
I use SSMS to connect  2 SQL Servers and want to backup SQL 2000
database and restore on SQL 2008.
I just realized that I only can access SQL 2000 server when I backup
and I only can access SQL 2008 when I want to restore.
I am looking for a method that I can backup  SQL 2000 database to let
2008 access to restore.
Any information is great appreciated,
iccsi

Hello There,

May be u should review this http://weblogs.asp.net/jgalloway/archive/2005/12/02/432088.aspx.

Here author (Jon Galloway) has shared nice idea for backup and
restore.

Hope it helps.

With Warm regards
Jatinder Singh
http://jatindersingh.blogspot.com