We help IT Professionals succeed at work.

SQL 2005 database move to Windows 7 64 Bit with SQL 2008 R2 Engine installed

MarvinEMarshall
MarvinEMarshall used Ask the Experts™
on
Yesterday I did a FULL BACKUP of a large SQL database running SQL 2005 and Windows 2003 Server.
I then copied the newly created sql_song1.bak file to an identical Folder on the same lettered D drive on the Windows 7 computer via my personal network.
I then performed a RESTORE query in Microsoft Management Studio. That was successful.  However when I pulled up the newly formed sql_song1 database, it showed all the tables (about 40) of them and all had the proper fields.   But, when I would right-click on a table, there was no option to OPEN TABLE.  Therefore I did everything except put the data in the tables.  What did I do wrong?
Thanks for any assistance.  I can supply the exact Backup and Restore query text if needed.
Comment
Watch Question

Do more with

Expert Office
EXPERT OFFICE® is a registered trademark of EXPERTS EXCHANGE®

Commented:
verify if the SQL server login used user is mapped to a database user.

If needed use http://msdn.microsoft.com/en-us/library/ms174378.aspx for help
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Have you faced any issues during installation of SQL Server Management Studio..

Author

Commented:
No issues, I uninstalled any previous SQL installation.  Then installed SQL 2008 R2 with no problems and the SQL Server Management Studio came up fine.
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Then your previous installation had some issues and hence reinstallating fixed it..

Author

Commented:
danubian,
I tried you suggestion to modify the User Name and Password to my SQL database.  After succerssfully executing a REPORT I executed the following -
USE sql_song1
GO
EXEC sp_change_users_login 'Auto_Fix', 'wilson', NULL, '6mem7pf2'
GO
..... that was successful.  Now I now longer can log in as 'sa' and the above password.  I then logged in with the 'wilson' user name and same password.  But now the the database doesn't show ANY TABLES.  Not good.  So, since I set up SQL 2008 R2 in MIXED MODE I tried Windows authorization and my tables came back, but still no data.  It doesn't make sense to me that the password and login shows the tables and fields but blocks seeing the data.  Seems as if it would block all the data including the tables and their fields
If you are quite sure the user name and password is the very likely to be the problem, I'll continue down this path.  I would think my RESTORE needs an adjustment.  Here is the one I used -
-- Restore
RESTORE DATABASE [sql_song1] FROM  DISK = N'C:\SQLA\sql_song1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO

Author

Commented:
rrjegan17
Previous installation didn't not have issues, but previously I was not successful in moving the SQL database.  Thought maybe it was due to downloading the wrong version, so download two other versions of SQL, same problem.  So, uninstalled all and reinstalled the one I wanted in the first place SQL 2008 R2.  However this time I did a FULL BACKUP, which was successful and preformed this Query, which showed successful - but no data in any of the 40+ tables, just columns and fields.
The RESTORE query -
-- Restore
RESTORE DATABASE [sql_song1] FROM  DISK = N'C:\SQLA\sql_song1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
>> but previously I was not successful in moving the SQL database.

Kindly let me know how you tried to move Database..

>> However this time I did a FULL BACKUP, which was successful and preformed this Query, which showed successful - but no data in any of the 40+ tables, just columns and fields.

do you really had data in those 40+ tables because RESTORE command you tried has nothing to do with data loss you are mentioning..

Author

Commented:
rrjegan17
Thanks for the prompt response.  Here is the BACKUP query that I used on my production blade Dell server running Windows Server 2003 and SQL 2005.  (This database has a huge amount of data - sql_song1.bak file is 323,629 kb.
-- Backup
BACKUP DATABASE [sql_song1] TO  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\sql_song1.bak' WITH NOFORMAT, NOINIT,  NAME = N'HFC_DB-Full Database Backup', SKIP, NOREWIND, NOUNLOAD,  STATS = 10
GO
Then I copied accross my local network the newly created sql_song1.bak to my new development Windows 7 - 64 bit Ultimate with the newly install SQL 2008 R2. then then run the RESTORE query -
-- Restore
RESTORE DATABASE [sql_song1] FROM  DISK = N'C:\SQLA\sql_song1.bak' WITH  FILE = 1,  NOUNLOAD,  STATS = 10
GO
Raja Jegan RSQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018

Commented:
Your Backup and Restore commands should not have lost or responsible for loss of any data..
To confirm this, run the below command in both databases 2005 and 2008 to confirm

create table #temp (table_name varchar(100),record_count int);

insert into #temp
exec sp_msforeachtable 'select ''?'', count(*) from ? '

select * from #temp

Author

Commented:
rrjegan17
Very good suggestion.  On the database 2005 has 36 tables all with counts varying up to 177,068.
The database on the 2008 showed no errors, but both columns were blank.  Am I correct that that verifies that indeed only the blank tables were successfully moved and no actual data was moved?
So, what do you suggest next?
Thanks so much, Marvin E Marshall
SQL Server DBA & Architect, EE Solution Guide
Awarded 2009
Distinguished Expert 2018
Commented:
>> The database on the 2008 showed no errors, but both columns were blank.  Am I correct that that verifies that indeed only the blank tables were successfully moved and no actual data was moved?

Then there is some problem with your RESTORE either RESTORE haven't happened or you have restored the wrong backup file..
Can you try restoring it again..

Try taking a new backup is that can be done..

Author

Commented:
rrjegan17,
  Great.  I now have data.  I did make one change, after re-reviewing the FILELISTONLY quiery on the sql_song1.bak, I see that the Physical files were all listed at D:\SQL\....
I had the sql_song1.bak copied from the 2005 Server to C:\SQLA\   I'm not sure if that's why I had no data, but I do have now.  Thanks so much for all the coaching.  I will be posting another question, as when I setup the 2008 R2 I set the authoriization in MIXED MODE.  The database on the 2005 Server it is User Name and Password, which of course I have both.  I would like to change the 2008 copy to the same User Name and Password.  I will post this current problem as "accept as solution" and immediately post this question.  Thanks so much