Copy All Tables From One Database To Another In SQL Server

Step 1. Let's say if you have two SQL Server databases, olddbname and newdbname. The following script creates a stored procedure that will copy all tables and their data from olddbname to newdbname. 

1. create proc mypro_for_copy_database  
2. @newdbname varchar(100),  
3. @olddbname varchar(100)  
4. as  
5. begin  
6. declare @sq nvarchar(max)  
7. set @sq='create database '+@newdbname  
8. exec sp_executesql @sq  
9. declare @s nvarchar(100)  
10. set @s='use '+@olddbname  
11. exec sp_executesql @s  
12. declare @oldname varchar(100)  
13. declare @sql nvarchar(max)  
14. declare create_database cursor for select name from sys.tables  
15. open create_database  
16. fetch next from create_database into @oldname  
17. while @@FETCH_STATUS=0  
18. begin  
19. set @sql='use '+@olddbname+' select * into '+@newdbname+'.dbo.'+@oldname+' from '+@olddbname+'.dbo.'+@oldname+''  
20. exec sp_executesql @sql  
21. fetch next from create_database into @oldname  
22. end  
23. close create_database  
24. deallocate create_database  
25. end  

If you don't want to create this as a SP, you can run this query direct.

 

Step 2. Execute SP by using this query. Here you pass your two databases names. 

1. exec mypro_for_copy_database 'newdatabase','currentdatabse'  

Note: Do not create new databse manually. This stored procedure will automatically create a new database with the name you give in the query.

 

Step 3. Enjoy. Your task is completed without a complex query.

 

Here are two recommended detailed tutorials:

1. Copy Tables Data from One Database to Another In SQL Server
2. Copy Table Schema and Data from One SQL Server Database to Another SQL Server Database