0

I have several databases on SQL Server 2008 that I wish to transfer to SQL Server 2012.

The two servers are on the same network but separate domains.

I am attempting a DB Copy from the one server to the new one and it connects successfully I just am not allowed to select any of the DB instances to copy to the new server.

I have been attempting the connection from the 2012 end. I have DB sysadmin IDs at both ends.

Any advice is appreciated.

Lou

Improve this question
1
  • I have sysadmin rights and I can see the option to select multiple databas on select databases page in the wizard. I guess you are doing some mistake. I would advise you to use backup restore mechanism to move database its very easy and reliable copy database wizard has problems as it is SSMS functionality.
    – Shanky
    Jul 15, 2014 at 12:39

2 Answers 2

Reset to default
4

DB Copy has a number of limitations as describe at: http://msdn.microsoft.com/en-us/library/ms188664.aspx

This includes not supporting the copying of System databases, Databases marked for replication, or any database not in a normal status.

However, it does support moving logins, jobs, and so forth.

The typically best way to copy a database with the fewest issues is to do a BACKUP from the source server and a RESTORE on the target server. This is mentioned here in the discussion of strategies for copying databases:

http://msdn.microsoft.com/en-us/library/ms189624.aspx

If you also need to copy logins from the old to the new server, there are a couple of scripts documented at: http://support.microsoft.com/kb/246133

SQL Agent Jobs can be scripted and recreated on the target server as well.

Improve this answer
4
  • I have attempted to use backup/restore but for some weird reason I am only allowed to "verify" the backup and not actually perform the restore. Any ideas what may be causing this ?
    – scopedial
    Jul 15, 2014 at 14:07
  • 1
    Please update your question with any messages that you see from your efforts. (Add them to the end of the post.) One quick thing to check: Are the drive letters and folder names the same on both servers?
    – RLF
    Jul 15, 2014 at 14:29
  • @scopedial You shouldn't abandon the most straightforward method of copying a database because you're "not allowed." Tell us what restore statement you were using and what error message(s) you received, and we can solve that problem instead of assuming that RESTORE is not feasible (it most certainly is).
    – Aaron Bertrand
    Jul 15, 2014 at 16:27
  • I agree, I suspected that it was a privileges issue that was currently beyond me. I was using the backup and restore helpers found in the drop down menus in the object explorer of SQL studio. Although the user IDs that I am using do have sysadmin.
    – scopedial
    Jul 15, 2014 at 18:56
1

What I missed was that the 2008 server required patching. By applying the most recent patch bundle this problem has been resolved.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.

Not the answer you're looking for? Browse other questions tagged or ask your own question.