SQL Server has feature which copy database from one database to another database and it can be automated as well using SSIS.

Make sure you have SQL Server Agent Turned on as this feature will create a job. The same job will execute the task. Make sure that SSIS is properly configured as well with necessary security permissions. You can automate this process as well control error logging.

Following are the steps to copy database from one instance to another instance.

Specify Source Server Specify Destination Server Here you can select option if you want to keep the database ONLINE when it is being copied. You can also select option of MOVE or COPY database as well.

Give appropriate database name. On this screen you can select additional options to copy as well. You create the package over here. You can schedule the package using SQL Server Agent.

When this process is over it will show the success message and database will be copied to another server.

You can see how easy is the process to copy the database to another server.

This is a basic UI in SSMS for the extended features for transferring objects which can be found in SSIS. I think that SSIS is much more flexible, since it provides conditional flow features and variables as well as endless possibilities of container properties changes. What I am trying to say is, that SSIS should be used in more complicated scenarios, since it is more flexible and more advanced.

@Prasad: yes, you can copy a database from 2005 to R2, if you have specify the proper server names and you have the proper logins. I recommend you use the SSIS, though.

The only issue that i have with this is that it will copy the SQL Logins from one server to another and maintain the permissions that were granted on the orginal server but it won’t copy the password(as far as i can tell). So i have to reset the password in the DB and application of hunt down the password and set it to the correct value on the new server.

I am using this copy database method to copy database from one server to another server. while going through the wizard, there are 2 option to copy 1. detach database 2. copy while being online. Now, I would like to know which is a good option to follow? Since my database is around 20GB big. I want the highperfance task and error free transfer.

If you have a similar error, On destination server, you need to create a new credential, asociated with a login in the destination server that has sysadmin server role, later, when you have the crendential, you need to create, on the destination server, a new proxy, it has to asociate with your new credential, later run again Copy database wizard on the source server, and in “Schedule the package” page, change — integration Services Account with your new Proxy. :D

Dave, I really enjoy your articles. They are often very insightful. Can you direct me to finding out how the copy database works? Is it create scripts that migrate the data? Are they copying the underlying datafile?

Thank you for your carticles. I always find them useful. I have a question regarding copy database. Could you please help me?

I was trying to save a copy of a database on our production Server before updating that database. I used the Copy Database wizard with Detach and Attach method. Everything went well except it fails on the step “Execute SQL Server Agent Job”.

Now I found the copy [databaseName] is added to the SQL Server Agent Jobs as one of the job which executed on the day which the copy database failed.

Could you please let me know what could be the cause of this failure, and why the SQL Server Agent Job was created? Thank you!

This blogpost just brightened my day as I wanted exactly this, but then the option to copy the DB isnt appearing in my context in tasks… SQL Server agent is ruuning though. I am using SQL Server 2008 R2… Appreciate your insight

I am geeting an error While trying to find a folder on SQL an OLE DB error was encountered with error code ….(Login failed for user ‘user’). I am passing correct credential for source and destination server ( they are different ) Any help?

How can I transfer records(only selected columns) from table A in SQL 2005 to SQL 2008 located on same server select/insert ? I tried following but it doesn’t recognize source table (error: Invalid object name SourceTable ):

I am new to sql server. I have to copy one database to another machine which runs sql server 2008R2> I have created agent job but its fails. source machine is in Domain but destination machine is not in domain. it is in network but in workgroup

i want to daily update my sql-server daily which is hosted from the local server how can i do this ? For eg :- 1 Table having total 10 records next days their are total 15 records so 5 new records have been added, so i want to update this daily.

I am experiencing the same error at the Execute SQL Server Agent Job step as many of the others here.

3 scenarios: 1. works – source and destination db are on the same SQL server instance 2. SQL Server Agent Job (error) – source and destination db are on the same server, but different SQL server instance (same version of SQL 11.0/2012) 3. SQL Server Agent Job (error) – source and destination db are on different servers (same version of SQL 11.0/2012)

Admin permissions doesn’t appear to be the issue.

Hopefully, someone will be able to come up with a resolution for this error.

If you have a similar error, On destination server, you need to create a new credential, asociated with a login in the destination server that has sysadmin server role, later, when you have the crendential, you need to create, on the destination server, a new proxy, it has to asociate with your new credential, later run again Copy database wizard on the source server, and in “Schedule the package” page, change — integration Services Account with your new Proxy. :D

Hi martin, Follow this steps: 1.- Open your destination instance with Microsoft SQL Management Studio, later clic on Security -> Credential -> Right clic and clic on New Credential (create a new credential asociate with an account that has sysadmin server role;). 2.- Later, You need to activate your SQL Server Agent, after that, clic on Sql Server Agent -> Proxies -> Right clic and clic on New Proxies (You create a new proxies asociated with the credential ).

Thanks Jose. I have done this, but i still getting the same error (Execute SQL Server Agent Job (Error). Any idea why? I want to copy database on the same server (SQL Server 2008 R2) from one instance to another.

I have an MSSQL server with more than 30 databases of various size connected (in overall they are near 1TB size) In the same time i have a ‘test’ database that is connected to my application. I want to copy database to ‘test’ db without restoring them from backup into a new db with a new name, etc. SQL copy wizard does this perfect, but i have no idea how to automate this.

Generating the SQL scripts for each db also works, but such sql script also is very big (some of my db’s are near 100GB and storing the such sql scripts or backup files will require x2 additional server space, which is not appropriate solution).

The only possible way seems to use SQL Copy Database Wizard, but i am not able to run it manually every time i need to restore database into a new one. Any ideas how to improve this process?

When I run Copy Database Wizard I receive an error on ‘Execute SQL Server Agent job’. Destination server Windows logs shows this error “Message: ERROR : errorCode=-1073548784 description=Executing the query “sys.sp_addrolemember @rolename = N’ifm_usr’, @mem…” failed with the following error: “The role ‘ifm_usr’ does not exist in the current database.”. Possible failure reasons: Problems with the query, “ResultSet” property not set correctly, parameters not set correctly, or connection not established correctly.” CDW doesn’t create ‘ifm_usr’ Database role automatically

SQL Agent job is running under domain account, whom I provided sysadmin rights both on source and destination servers. This account was also added into server admins group (I was desperate :) ).

This example uses the Detach and Attach method as with every other example that I have seen on the web. The issue with this method is that it requires the database to be taken off-line. The alternative method i.e. SQL Management Object method is never demonstrated in any tutorial I have found and I have never managed to get it to work – it always fails. I wonder if there is a known bug in this method and that is why no one ever demonstrates it. Has anyone else tried the alternative method with any success?

Pinal Dave is a technology enthusiast and an independent consultant. He has authored 11 SQL Server database books, 21 Pluralsight courses and have written over 3800 articles on the database technology on his blog at a http://blog.sqlauthority.com. Along with 14+ years of hands on experience he holds a Masters of Science degree and a number of database certifications. For any SQL Server Performance Tuning Issue send email at pinal @ sqlauthority.com .

Nupur Dave is a social media enthusiast and and an independent consultant.