Quite often I get the request regarding how to copy all the objects – including schema and data from any database and re-create it on another instance.
SQL Server 2008 and SQL Server 2008 R2 has script generator wizard which does it for us. I ask you to pay special attention to image #5.
After the script is generated, the next challenge often users face is how to execute this large script as SQL Server Management Studio does not open the file. One can use SQLCMD for the same. See that in the last image of this post.
Pay attention to the option Types of data to script – select option ‘Schema and data’
As the file with data will be very large, use SQLCMD to execute the large script which will create database with schema & data.
Please let me know if you have any question or suggestion on this blog post.
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)
122 Comments. Leave new
Why use SQLCMD in the command prompt in the end? Any alternatives/issues with the UI way?
We are planning to move databases from SQL 2003 to SQL 2008 using detach and attach procedure. Please guide me the full procedure including the logins on source to be created on destination server.
The sqlcmd is the only option when the script file is too big (for example 1gb)
I agree @ebluz
What I personally prefer as a method of transferring schema objects and data across databases or even across instances is the scripting method above together with the SSIS.
The method of scripting you mention above is quite good, and I use it to generate the script of the schema. It is very flexible, since I get to chose which objects and what granularity of detail to script. The scripting in SSMS also checks for dependencies and scripts the objects in a particular order.
For moving data, though, I find it very clumsy, especially with larger amount of data.
The best way to do it is to script the schema and then use SSIS to load the data. SSIS is very easy to use and it offers isolation and lock handling which speed up the process of the data loading.
Feodor
Hi Feodor..
I want to import/export DB schema using SSIS ..
How can i do that
how could we do same thing in sqlserver 2005
@Sunil: the scripting of data is new for SQL 2008. In SQL 2005 you can script only the schema. Scripting the schema is sufficient, though; look at my comment above and you will see that you can use the scripted schema and then SSIS to move the data.
Feodor
Thanks Pinal And Feodor for the explanation on Scripting and loading of the data.
Thanks for sharing. Nice picture guide.
Hi,
This is good way but if you have altered the table those schema is not coming when you use Generate Scripts. I’m using this only for generating the schema but many times the table that i altered are skipped(i.e. new columns or datatype changes are not reflected). I don’t know the reason But whethere I go to Design editor and alter or use command its not reflected. Have any of you faced this problem???
All,
I have generated .sql scripts from both the Studio Express tool as well as from a dos prompt and now I’m creating a .bat file to execute the .sql scripts. One script drops the database, the next script (schema/database) creates the database and tables and the last script is a data only script to insert the data back into the database/tables, when I execute the .bat the scripts all run, the database gets dropped, database & tables get created and it appears the data load script runs however the tables don’t get populated, I created a bat_log.txt file that captured the dos stuff and in there it tells me that XX rows affected (the xx number matches the number of rows I expect in each tables) however the data doesn’t get into the database tables, any ideas why?
thanks,
~Scott.
We just perform a backup of the source db & then restore it to the target server.
About all that’s left to do then is clean up the list of user accounts and reset permissions.
It’s not especially elegant, but it’s fast, and it works every time.
Mike
if i want new Database not same name. can i change name on script ?
Thanks, I was losing my mind over the “ADVANCED” button !!!
What about doing the opposite? Can we not script the database with all objects from SQLCMD?
Thanks.
How can I apply filter on table to create data script of selected data?
–Shalini
I am using SQL Server 2008 but the Choose Objects page in Generate Scripts does not display DDL Triggers as shown above, and these are what I want to script. Any ideas??
It appears there is no longer any option to script the actual Create Database statement?
(& wow thats one cranky commenting system)
I have learned something new, and this is very good as far as it went. However, I’m missing something. I used the generate script to copy the users and schemas for one DB on a server to a clone on another server. I used the drop and create option. After it executed, the hundreds of securables for the users where I generated the script are missing from my target database.How can I copy this data for the database users?
I am working on an instance of SQL Server Standard Edition and the Script Wizard Choose Object Types screen only has Tables and Users. The other object types are not there to be selected. Any idea why this is?
Hi Pinal,
I am genertaing Sql Script for all objects like Tables, Storedprocedure,Triggers, Function etc. Its Generating Scuccessfully and showing all the objects as scuccess in the final report but some of the tables are missing in the script. I had selected all the objects and aslo showing those objects scuccess in the report. Can u please Help me to find the reason that why this happening.
Hello, this is happening to me too :(.
Dear Pinal, do you know why could it happen? We are dealing with a big database and many objects are missing :(