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
Where is Save ?
Create Script to Copy Database Schema and All The Objects
Hello,
I’m doing it to transfer a db from sql 2008 to 2005.
The file with the datas is to large to be executed in sql 2005.
Even with sql cmd it’s the same result, not working.
What should i do?
Getting a lot of errors in the restore process. Seems like this method is not fool proof. Oh well
hello all,
I just want to take complete backup of databases as above process is doing.
But the problem is I want it by command so that i can use it in batch to schedule it. Please let me know hot to set that .
Anyone has the answer to this question? I.e. Have scripts instead of GUI so the job of creating schema scripts and/or data scripts can be automated.
HOW TO GENERATE SCRIPTS FOR STORED PROCEDURES IN MASTER DATABASE. I HAVE CREATED SO MANY SP IN MASTER DB.
HI Deepak, You have answer for this please? Anyone?
How to go about invoking generate scripts and everything by scripts and with no manual intervention?
Pinal in reading this comment listing I found some fairly significant questions that I would like the answer to.
Is there anyway you could take the time to go through these comments and answer some of these questions?
HI i generated the scripts in 2008 but the scripts not working in 2005. Please help me how to execute in 2005
Backward restore is not possible
Pinal
This was perfect – I find myself hitting your blogs many times when I have SQL questions – thanks a million for taking the effort to post this items.
Hi
I am using ms sql server R2 2008 .For generating script from remote server(2005) to my local server(2008) ,i have used the approach u told here,
but getting an error while saving to new query window since data is bulk i guess.
This is the error
System.Data.SqlClient.SqlException: Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.
at Microsoft.SqlServer.Management.SqlScriptPublish.GeneratePublishPage.worker_DoWork(Object sender, DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.OnDoWork(DoWorkEventArgs e)
at System.ComponentModel.BackgroundWorker.WorkerThreadStart(Object argument)
The company I work for is doing data migration from SQL server 2005 to 2008 R2 Enterprise. The former DBA sent me the generated schema for the database in a .sql file, which I can open and see in SSMS. The DBA also sent me the tables in .dat format.
How can I have the tables transfer to the new 2008 R2 with the schema of the original 2005?
Thanks a lot for your help.
Hi Pinal,
It will generate whole scripts which also includes tables from other databases which depends the current selected DB.
Choose “Select Specific database objects” option, (3rd image)
Thanks for this post.
Hello everyone!
I have an issue doing this… the export of the objects works fine (I’m doing it without data) but when i open the file in a text editor i can’t find some objects that the wizard said was exported… Any suggestions?
perfect
Iam doing it to transfer db from device to device .but how to restore this script file to the second device please help me .
thanks
HI pinal,
I am getting “Login failed” error , when i run the script on cmd.
Please , help me.
Thanks in advance.
date datatype conversion error is getting in one table while generating script for converting sql 2008 to sql 2005
Backward restore is not possible at all
I have recently started learning SQL, and was instructed by DBA that you can not export the sysdiagrams, that with 2005 it just breaks it and with 2008 r2, it is related to the way that the SQL translates the sysdiagram upon exporting, where it is into an excel or flat-file(which is all I am experienced on). They database diagram is what officially makes the database a relational database(is the way I understand it), so my question is why can I not or how can I export they sysdiagrams along with the tables? If it can’t be done via excel or flat-file, then why? I am just wanted to understand why every time I am doing this, I must recreate the relationships between PK, CPK, and FKs. It would be so convienant if I could just export the sysdiagrams along with them and then not be forced to re-invent the wheel everytime.
So I was told, sysdiagrams can’t be exported and imported by excel or flat-file, but why is this? All that was explained was it has to do with the way the sql code translates xml(what’s that even got to do with it). xml is scripting, so I am not seeing the big picture.
Please advise. I want to make sure I understand completely how and why this is not possible?
How to deny permission to user for generating scripts.I want to restrict some users so that they cannot generate the script.
Hello Pinal…
I want to generate database script using store procedure or through query..
How can i do it..??
The output sql file is huge in size. It is taking hrs to run. May we not take multiple .sql files – one after another? Any 3rd party tool for this?