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.
Watch SQL in Sixty Seconds Episode on same subject.
[youtube=http://www.youtube.com/watch?v=lSh3fq-MikE]Reference: Pinal Dave (https://blog.sqlauthority.com)
59 Comments. Leave new
Hi,
I think this doesn’t copy the primary keys and constraint.
Could anybody please suggest me some free tool to copy SQL Database from remote server
Hi,
Could anybody please suggest me a free tool which can copy primary keys and all constraints when copying remote database?
yes , i had this problem and i think in this way you can’t copy keys
i use generate scripts always
this way is very faster too
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?
Hi Dave,
How to make SQL Server Agent(SQLExpress) turn on? My SQL is MS SQL 2008 R2. I couldn’t turn it on. Please help me
How can I transfer records(only selected columns) from table A in SQL 2005 to SQL 2008 located on same server? I tried folowing but it doesn’t recognize source tables
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 ):
Use [DestinationTable]
Insert into [DestinationTable].dbo.[table1]
(id, itemName,ItemCode)
Select id, itemName,ItemCode
from [SourceTable].dbo.[table1]
Is my SQL script incorrect?
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.
can u explain how to create jobs in sql server 2008R2
Step by Step
Thanks it saved me lot of time. You Rock…
Thank you very much for the valuable Article.
I have actually tried it, it ran successfully, but then i am not able to see copied DBs on destination server? Any thing i am missing?
I have SQL Server 2012 and I want to create similar DB in SQL 2008 instance, can i do that or there will be any problem with Schema due to newly introduced Data types
This Option is not available in SQL Server 2012, when migrating from SQLExpress to other instance.
Is it OK, to detach and attach ?
Hi
I need to do this using c# . Is that possible
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 Jose.
Could you be please more specific, how to create a new credentilal and new Proxy.
Thanks, Martin.
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.
Best Regards, Martin.
Thanks @Jose for helping @Martin
Thank you Jose for your valuable step by step guidance, its work for me.
Thanks Dilip.
How can i automate this process?
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?
Thanks in advance.
I get an error towards the end when its trying to execute the sql agent service. I have that agent turned on , on both instances any help please?
I assume this is the same error lots of people have been getting but I don’t see any fix yet. still waiting please .