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
How come when I try to generate a script, the table triggers are not generated along with the table schema? And is there a way that it could be included automatically?
Sir… am getting one error during generating script that error is “Could not read metadata, possibly due to insufficient access rights” how to solve this? assist me thanks in advance…
Exactly, I need to generate this script daily or multiple times a day. So how to achieve this objects script without doing manually. Please help me and your help is highly appreciated
Thanks,
Chandra
Hi I am chandrasekhar,
I want create script only not data for all objects such as tables, views, sp’s, functions and all related keys. i want to
exactly what have done using tasks and script wizard.
I have been waiting for your response please…..
Is there a way to so this with SQL code rather than using the wizard? I want to drop all stored procedures in a database and recreate them from another database.
I can’t get this to compile when copying/pasting. Can you attach a word doc or email to me?
I want to import data from another database into an empty copy of it. Can I do that with this script and does it take relational constraints into account so that it imports the data into parent tables before populating the child tables dependent on their Primary Keys?
How to backup DataBase which is Live MS SQL
Very helpful, thanks. Do you know if this can be done through a stored procedure? without any manual interaction
How can I generate the script from commands, ie without having to enter the script generator?
Hi pinal,
thanks for above solution, i am trying to generate the script using SMO but it wont work. it generate the redudent script..
Hi Pinal,
Kindly help to generate individual objects data base wise by the way of automation sql job.
Am having limited access to server every month needs to generate scripts for 10 databases, it’s very difficult to me every time to get access.
Thank in advance
We have two databases – a live and a dev. I have created the script for a table (schema, data, sprocs, etc) that I wish to copy to the dev.
How do I copy it to the dev database ?
why not go with backup/restore method?
Is there any way to automate or create a sql job to run on weekly basis?
I am having different issue…
my database is hosted on server having 2016 ver
i am using 2014 ver locally
i am having issues with restore locally
and need to generate script remotely
so as to get it synced locally
is there any solution?
Hi,
Is there a way to automate sql server schema backup (no data)?
I have a 700GB database that needs to be backedup(schema only). This way, when i need to restore a table structure,I dont have to restore 700GB data.
Thanks,
Krishna
Hi Pinal,
What collation sequence would the columns in the new database have in this case? Would it be the same as those in the source database, or would it be the default collation sequence for the target server? Is it easy to force the columns to have the same collation sequence as the target server (and not those of the source database)? If this doesn’t happen by default, can you recommend a method of editting the very large script file so as to force the collation sequence to be the default collation sequence of the target server?
Thanks
Richard.