Migration from a SQL Server 2000 to 2008
-
For my situation, I'll be moving about 50 databases. I think I will do a few at first. Then every week do another 10 or so. My question: will these databases work without the LDF file and without the master and MSDB databases on SQL Server 2000? I know I won't have any history (but I wasn't doing any transaction logging) without the LDF files. I plan on exporting the database files themselves. I see they come over in an extensionless file name. But for the life of me cannot figure out why I would need the master and MSDB databases. Will anything go wrong when I try to go live with the first few databases on a new server with SQL Server 2008 without the Master and MSDB databases? (I know other things could go wrong, e.g., new keywords, services and functions no longer exist.)
-
You will need to back up your databases on the old server and restore them to the new server. There is no other way to migrate them. You should also consider scripting out any logins and creating them on the new server first.
The probability of survival is inversely proportional to the angle of arrival.
-
the master DB saves the logins, so you will have to recreate the logins on the new server.
You can use this script to transfer the logins from 2K to 2008.
-
If you're going the detach-attach route, you NEED the ldf files. Otherwise there's a chance that the databases will not attach. The log is not an optional file.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverabilityWe walk in the dark places no others will enter
We stand on the bridge and no one may pass -
A Good upgrade case study for SQL upgrade from SQL Server 2000 to 2008 is posted here
http://sqlserver-training.com/migrating-sql-server-2000-to-sql-server-2008
Thanks <br />DBATAG <br />
<br />info@sqlserver-training.com<br />
Thanks
DBATAG
Website : http://sqlserver-training.com
Email : dbatag@sqlServer-Training.com
-
Here's what I've used in the past to move the logins between 2000 and 2005 (should work fine for 2008 too): http://www.windowsitpro.com/article/query-analyser/sql-server-login-transfer.aspx
And in case this helps someone else out, here's what I use to move logins between two 2005 instances (should work with 2008 too): http://weblogs.sqlteam.com/tarad/archive/2008/06/24/How-to-transfer-SQL-logins-between-SQL-Server-2005-instances.aspx
I've had issues with sp_help_revlogin, which is why I've rolled my own. But I can't remember what those issues were!
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog -
Not specifically related to upgrading from 2000 to 2008, but great information about upgrading to 2008: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=138230
Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
Ramblings of a DBA (My SQL Server Blog)[/url]
Subscribe to my blog
Viewing 7 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply