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.
Reference : Pinal Dave (https://blog.sqlauthority.com)
122 Comments. Leave new
Hello, i need to make the same action ( generate script ) but, i need to do it using t-sql (without using the graphical environment)..
that’s possible?
thanks
I need the same
Same problem/question here, ie to generate the scripts automatically using scheduled process, instead of doing it through GUI. Anyone has the answer?
Thanks,but please sir database change name problem to another server script run time
i didn’t generate script of my live database.
Its showing error
What is the error you are getting?
Thank you verrymuch. Nice picture guide.
Hi,
I am facing one problem while trying to generate Database script of one database,
i have tried different servers with 4 core & high configurations, its simple database & i am just trying to generate script of Procedure,Functions & Triggers not including tables & data, still its taking too long (approx more than 2 hours) to generate script.
Enviornment : SQL Server 2008 R2
* This problem is for specific one database only, in all other database its working good.
Can anyone please help me ?
very nice . Thanks you all
it doesnt script the Triggers (table level) nor Stored Procedures. nor Functions.
have you explored the options at individual object level ??
hi…i want to generate script with Master Key,Certificates and symmetric keys in sql server 2008…
Hi, I am using sql server 2008 with 2 types of db. But in one db can’t generate scripts.it is showing error like “Index is out of the boundary”. but i tried another db. it is generating scripts good. what is that exact issue? do u know?
After creating the script if i run that script on another computer then it is giving me error like can not find mdf file. do this script does not create new database into the specified path
Is there any way to generate this script with data using query? Please Help…..
Is there a way to generate script of limited number of rows and not all the data?
Sir, Please tell me after creation script file .sql format then how to restore again if script file so large.
Hi! I have a query and it is as follows:
I wish to create the table structures in excel and create tables in SQL Server 2008 R2. Could you please guide ?
i have problem with this
1st : i using option ( script to file ) for table contain large data
2nd : now when i try to copy the script for this table to another table its give to me an error message and stooped me to do that
so how can i solve this problem , how can i copy this large script to new table
Can We Take script of Single table with Data?
Check Balmukund’s blog https://sqlserver-help.com/2013/12/13/did-you-know-you-can-generate-insert-statement-with-data-using-management-studio/
Can we do this without SQL wizard? do we have some way to do it using TSQL? if not all the data I want to replicate entire schema including indexes, defaults, partitions etc.
Hi,
Is there any way to generate database schema and with complete data using sql query without using sql wizard.
Why do you don’t want to use tools available? If you want script then you can use SMO or PowerShell also.
How would I copy schema [dbo] into same database [dbobackup]? Also, why does my work want me to do that?
My company wants me to copy the data in the default database schema [dbo] into the same database with a new schema called [dbobackup]. Why would they want to do this? Most importantly: How would I do this?