Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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

share|improve this question
    
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 at 12:39

2 Answers 2

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.

share|improve this answer
    
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 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 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 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 at 18:56

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

share|improve this answer

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

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