instead of copy a SQL DB make a replica

Hi Experts,

I have a SQL2008 server with some DBs.
And now I installed a test server with SQL2012.

I need to copy my DB to the new test SQL server.
Is it possible to do this with a task from SQL Management Studio ?
What about to create a mirror or a replica ?
And if its possible how to do this ?
Eprs_AdminSystem ArchitectAsked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Vitor MontalvãoMSSQL Senior EngineerCommented:
What you need? A one shot copy or something to be always up to date?
If it's a one shot copy then I'll recommend to restore the DB in SQL Server 2012 from a backup of the DB in SQL Server 2008. After restore it in SQL Server 2012 you'll need to change the database compatibility to SQL Server 2012.

To be always up to date, mirroring and log shipping aren't good because the database wouldn't be available. For that I'll recommend a Snapshot or Replication.
Eprs_AdminSystem ArchitectAuthor Commented:
I need a one shot copy.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then just run a backup for you SQL Server 2008 database and restore it on SQL Server 2012. Just simple as that.

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Acronis True Image 2019 just released!

Create a reliable backup. Make sure you always have dependable copies of your data so you can restore your entire system or individual files.

Eprs_AdminSystem ArchitectAuthor Commented:
ok will do it.
Both Servers are in a workgroup.
But for some reason I cannot connect the new server - Error 5 Access Denied.
I can ping the new server from the old one and vica verca.
Do you have any hints ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
It's a general error. Can't do nothing but guess.
Wrong user name? Wrong password?
Do you have permissions for remote login?
Are you local administrator of the server?
Eprs_AdminSystem ArchitectAuthor Commented:
I can login with the SA account.
Where do I set the permissions for remote login ?
Eprs_AdminSystem ArchitectAuthor Commented:
see the error

sql error 5
Vitor MontalvãoMSSQL Senior EngineerCommented:
I meant remote login with Terminal Services / Remote Desktop.
Since you can login with SA account then it's all fine. You can continue.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Why are you using the "Copy Database Wizard"?
Eprs_AdminSystem ArchitectAuthor Commented:
The Copy Wizard is available for use, just want to try it.
But on the second step I always get the error -> Access denied.
And I don´t know why.

When I use Backup Exec to restore the DB, I also have problems to connect.
But why ?
Vitor MontalvãoMSSQL Senior EngineerCommented:
You need to give me more information. In which server are you logged in and what exactly you are trying to perform.

Are you a DBA or someone that it's only trying to help? I need to know that to see if I'll lead you with T-SQL commands or GUI options.
Eprs_AdminSystem ArchitectAuthor Commented:
On Server 1 is a DB, this needs to be copied to server 2.
Both servers just in workgroups.
I can ping each server from each other.

When I start the SQL GUI I can login with Windows Auth and SA Account.
But I cannot select the DB of the other server, then I get Access Denied.

I am a DBA.
Vitor MontalvãoMSSQL Senior EngineerCommented:
Can you perform a backup command on SQL Server 2008 database?
Eprs_AdminSystem ArchitectAuthor Commented:
Vitor MontalvãoMSSQL Senior EngineerCommented:
Then do it and when finished copy the backup file to the SQL Server 2012 machine.
Eprs_AdminSystem ArchitectAuthor Commented:
ok then I go this way. Thanks.
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2008

From novice to tech pro — start learning today.