April 25, 2007 at 1:07 pm
Hi everybody,
We are trying to copy everything including all databases, logins, jobs and DTS's from an old SQL server with Windows 2000 and SQL 2000 to a new server with Windows 2003 and SQL 2000. The two servers are both on the network and have different computer names thus different SQL instance names. Both SQL servers have the same setup and configuration. I wonder if the following will work (some downtime after hours is ok for us):
Stop the SQL services on both SQL server. Copy the data files, transaction log files and all other related SQL files from the old server to the new server. Start both SQL server services.
Will the databases and everything be Ok in the new server? Does the server name\instance name matter for the Master database, msdb and model databases to work properly?
Another way to do this would probably be Backup and Restore. Can we backup and restore Master, msdb and model databases to a different server that has different server and SQL instance name?
What would be the best way to do this?
Thank you in advance for your input!
Sharon
April 26, 2007 at 3:16 am
the os has been upgrade, so it's better to use backup/restore to migrate db.
of couse you can restore all the system db, but only master/msdb is needed.
April 26, 2007 at 3:49 am
do not copy the master database. Look up script sp_help revlogin and use that to copy logins or use the dts transfer logins task as servers on same network. Check out default databases, languages still ok afterwards.
do not bother to copy model unless you have made changes to it you want to see in all new app databases
to save downtime use backup/restore for msdb and app databases. Else bring down source sql server, copy the database files for these databases and use sp_attach_db. for app databases. MSDB I would definitely do by backup\restore.
In msdb will then have to run update msdb..sysjobs det originating_server = 'new server name'
backup destination system databases before you start
---------------------------------------------------------------------
April 26, 2007 at 9:41 am
Thank you very much for replying.
We did backup/restore of all the user databases but not master and msdb databases yet. So all the differences right now between the two servers should be: the new server does not have all the logins and users; the new server does not have local DTS's and any jobs. Everything else should be the same right?
April 27, 2007 at 4:09 am
correct. though new server has the users, they are defined in the app databases, just does not have logins , which are defined in master. So you can go ahead and backup/restore msdb ( provisos - exact same version + collation of SQL, and sqlagent must be down when you do the restore).
Please do not backup/restore the master database, use scripts or dts to transfer logins. (script better as it will keep the sids and you won't get orphaned users)
---------------------------------------------------------------------
April 27, 2007 at 9:31 am
Thanks again.
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply