March 7, 2003 at 6:51 am
I Am new to SQL server Adminstration. Basically I am An Oracle DBA, But have some tsk to complete. Need help of you guys. I have few SQL SERVER 7.0 Databases and needs to move to another machine which has SQL SERVER 2000 Installed. I need to move all these databases to This SQLSERVER 2000 Machine. I try to use Export, But I Don't know why while Exporting My destination Database is showing Only tables and Views. Users stored procedure and otehr objects didn't got copied. Can some one help me in moving my databases?. Appreciate you guys help.
March 7, 2003 at 10:10 am
When you did the Export did you select "copy objects and data beween SQL Sever databases" then make sure that the "copy all objects is selected" This option should include all objects you want.
March 7, 2003 at 10:15 am
I use All object option while export, I dont know why its just copied tables and views only. Any way does any one know any better way of making a copy of database to another machine?
March 7, 2003 at 10:39 am
run sp_detach_db <db name) to detach the databases.
Then copy the .mdf and .ldf files to the new server and attach them.
http://www.sqlservercentral.com/columnists/ckempster/deattachandreattachdatabases.asp
Steve Jones
March 7, 2003 at 11:34 am
Steve,
I am trying to use Enterprises Manager GUI- Copy Database wizard and getting following Error. Any work around
setp error source: Microsoft SQL-DEMO:
SQLSTATE:HYTOOO
Step error description : Microspft ODBS SQl server Driver time out
ste error code :- 80000405
I can connect to both Source and destnation databases from my machine, But when I try to use Database copy wizard in EM, You got this error any help.
Ajay
March 7, 2003 at 11:53 am
That thing is flaky. I'd not use it.
If you can afford downtime, right click the db, all tasks, detach db. Copy the mdf/ldf to the new server and then right click "databases", all tasks, attach database and browse to the mdf and ldf files.
Steve Jones
March 13, 2003 at 8:46 am
I'll start by looking for "sp_detach_db" in the documentation that came with SQL Server. At the bottom of the documentation for the "sp_detach_db" you should be able to see a reference to the "sp_attach_db" which is the system stored procedure that you'll use to attach/reactivate the database on the new server.
It is self explanatory but if you need help with is you can post another message here with your questions. Make sure you have sysadmin rights to the server.
[font="Verdana"]Sal Young[/font]
[font="Verdana"]MCITP Database Administrator[/font]
March 13, 2003 at 9:31 am
Steve,
I try to dettach and attach through SQL SERVER ENTERPRISES manager, But I don't know why when I right click on DB the all tass my Dettach option is disable. I don;t know if i have to take my DB offline or not?. Can you Pl. confirm what I have to do?. I summerize my steps as follows :-
1. Take Backup of database by using EM backup facility.
2. Right click on DB then All TASk then dettach( my Dettach option is disable).
3. Move files on new server
4. Attache teh file on new server.
Can you Pl. lso confirm how I can keep source database also up for few days. Once user will complete their testing I will shutdown source databas. Thnaks fro your help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply