Is it impossible to access a SQL 2005 server instance running on XP from SQL 2008R BIDS or Management Studio running on Window 7?

Question

Last time with the help of the forum page explaining how to connect SQL 2008R on Window 7 to SQL 2008R running on XP. This time, I am trying to do the same thing to connect to SQL 2005 running also on the same old XP to reuse some of the tables. Despite all the checks I have made based on the same forum page on both sides of the computers, I am unable to connect to the old SQL 2005. Am I right in assuming that this is not possible?

If so, how can I transfer some old tables on SQL 2005 to the new SQL 2008R database? As I cannot use SSIS with the absence of the active connection, I am assuming that one must export the database from SQL 2005 and import it into SQL 2008R and I am totally unfamiliar with this kind of operation. Could you suggest any good place where I can learn how to do it. Thank you very much. Tim

---

tatsuo nishimura manabe

Answer 1

 go into management studio and type this command:

BACKUP DATABASE YourDatabaseName TO DISK = '\\yourServerName\c$\yourBackupPath\YourDatabaseName.bak'

note:

If you aren't set up for network drives, just replace the \\yourServerName\c$  reference to = 'c:\localpath\'

To restore the backup you just made on the other server, type:

restore filelistonly
 from disk = '\\yourServerName\c$\yourBackupPath\YourDatabaseName.bak'

What this statement does is display ONLY what and where the restore mdf (data file) and ldf (log file) locations are from the OLD server.

in management studio, go to the target server you are interested in putting your restored database on and highlight/right-click on the server name (e.g. servername(SQL Server 10.50.1617 - domain\youruserid) . The dialog box called "Server Properties - yourservername" will appear. Click on "Database Settings" . You will see Data: and Log: -- these are where your new server keeps the mdf and ldf log files. Write that down, or capture the info to your clipboard/notepad for later.

To restore the database to the target server you just looked at, type:

  RESTORE DATABASE YourDatabaseName  FROM DISK = '\\yourServerName\c$\yourBackupPath\YourDatabaseName.bak'  WITH REPLACE,MOVE 'YourDatabaseName' TO 'drive:\yourtarget_mdf_path\YourDatabaseName.mdf',
   MOVE 'YourDatabaseName_log' TO 'drive:\yourtarget_ld_fpath\YourDataBaseName_log.ldf'

This will completely backup your existing 2005 databases on server X and restore them to your target 2008R2 server.

Answer 2

Dear plditallo

Thank you for your help. I was so foolish being bogged down with the connection issue. Although my old XP is in perfect private network link with other computers, either BIDS or Server Management Studio running on Window7 refuses to connect to the old SQL 2005 Server. Strangely, Visutal Studio 2008 running on the old XP connects to both SQL 2008R and 2005 without any problem!

After I gave up the connection, everything went so easy. There was already the backup file from the 2005 sitting on the old XP, so as a trial, I attemped to restore it to 2008R using Management Studio, expecting no success at all. Was I surprised! It restored instantly without any problem on 2008R. I looks like these two versions are interchangeable! From there, I used Import and Export Data(32 bit) to easily transfer the tables from the old Database to the new Database running on the same 2008R Server.

Although all this saved me from learning the T-SQL script codes, I will copy your code examples and keep it for future reference when I must write and run a package.  As you know, Import and Export Data does not copy the contraints and I may need your code samples soon. Thank you again.  tim

---

tatsuo nishimura manabe