Migration of SQLServer user databases to another SQLServer

Guys,
I would like to migrate a number of user databases from one SQLSERVER (name:
LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to another (na
me: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3)).
Please can you outline the steps involved and any pitfalls I should watch fo
r? I am new to SQLServer and this is live data. This needs completing today
as well!!
TIA, KenKen,
Have a look at:
INF: Moving SQL Server databases to a new location with Detach/Attach
http://support.microsoft.com/defaul...kb;EN-US;224071
Mark Allison, SQL Server MVP
Ken Jones wrote:

> Guys,
> I would like to migrate a number of user databases from one SQLSERVER (nam
e: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to another (
name: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3)).
> Please can you outline the steps involved and any pitfalls I should watch
for? I am new to SQLServer and this is live data. This needs completing toda
y as well!!
> TIA, Ken
>
Mark Allison, SQL Server MVP
http://www.allisonmitchell.com|||Hi,
Since you need to upgrade only the user databases you can do one of the
folowing
1. Detach and Attach databases
2. Backup and Restore the databases
1. Detach and Attach
a. Use SP_DETACH_DB to detach the MDF and LDF files
b. Copy the files to Destination server
c. Use SP_ATTACH_DB to attach the databases
(Refer books online for information)
2. Backup and Restore
a. Backup the source databases using BACKUP DATABASE command
b. Copy the .BAK files to destination
c. Restore the database using RESTORE DATABASE command
After this steps you may need to move the logins from source server to
destination server.
Have a look into the below link for information.
http://www.databasejournal.com/feat...cle.php/2228611
Thanks
Hari
MCDBA
"Ken Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:1442724A-12BF-4AF2-8A02-B558DCA60658@.microsoft.com...
> Guys,
> I would like to migrate a number of user databases from one SQLSERVER
(name: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to
another (name: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3)).
> Please can you outline the steps involved and any pitfalls I should watch
for? I am new to SQLServer and this is live data. This needs completing
today as well!!
> TIA, Ken
>|||Mark,
So I can detach (using sp_detach) from the source database, ftp to new serve
r and then attach (using sp_attach)'
Is that all there is to it? Will the sp_attach alert master as to the appear
ance of x number of new user databases as I am moving from one SQLServer (li
ve) to a completely different, functioning SQLServer(live)?
TIA, Ken.|||Thanks for your swift replies - WOW!!|||Hi Hari,
Some of my files are .DAT files. Will this affect the detach and attach proc
ess?
TIA, Ken|||DAT files could mean they are 6.5 database files. You can't move such across
to 7.0/2000. You need to have those databases available in a 6.5 install and
use the upgrade wizard from there.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ken Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:40772738-BE5B-4C98-86D9-DE1DB7959FFB@.microsoft.com...
> Hi Hari,
> Some of my files are .DAT files. Will this affect the detach and attach
process?
> TIA, Ken|||"I would like to migrate a number of user databases from one SQLSERVER (name
: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to another (n
ame: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3))."
Both are SQLServer8 servers. I am going to try the sp_detach_db and sp_attac
h_db by ftp'ing the LDF and MDF files. I'll see if this works.
Ken|||OK I just mentioned the 6.5 stuff because in 6.5 DAT were the default
extension for both database and backup files.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Ken Jones" <anonymous@.discussions.microsoft.com> wrote in message
news:63CF9224-D4BF-4550-AF7E-4B139C9C1E69@.microsoft.com...
> "I would like to migrate a number of user databases from one SQLSERVER
(name: LECOMMS02 - Windows NT - 5.0(2195), SQLServer 8.00.534(SP2)) to
another (name: LEPIV01 - Windows NT - 5.0(2195), SQLServer 8.00.780(SP3))."
> Both are SQLServer8 servers. I am going to try the sp_detach_db and
sp_attach_db by ftp'ing the LDF and MDF files. I'll see if this works.
> Ken