make a full script of a database using query

Question

Post reply

make a full script of a database using query

khosro.forum

Newbie

Points: 4
More actions
March 5, 2014 at 1:45 pm

#289115

hello all. i need a query that make a full script (schema & data) from a database.
i can do it by SSMS , but i need to do it using query script.
is there any one guide me how i should do it?

Viewing 5 posts - 1 through 5 (of 5 total)

You must be logged in to reply to this topic. Login to reply

Lowell SSC Guru Points: 323755 More actions · Answer 1

If you need the schema and the data, the "number 1 doctor recommended " way todo that is via backup and restore;

it's flawless, and is either pass or fail; you never accidentally miss an object or dependency.

if you really want a script,there's a built in capability that uses SMO, will that do it for you?

there are a ton of options, so if they are not set correctly, you cna see things like no foreign keys, indexes and other stuff in the script results.

there's an option to script the data too, but if you have any huge tables, you can expect the scripts to choke on lots INSERT INTO...statements.

i know you can script data as well, but i seem to only see the functionality offered by my plug in from ssmstools.com.

i can't seem to find the screenshot from the built in SSMS.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Koen Verbeeck SSC Guru Points: 259207 More actions · Answer 2

You can get most information from the sys schemas (sys.tables, sys.columns) or from the INFORMATION_SCHEMA tables, but scripting the data is much harder.

Scripting out all of the data can also take up huge volumes and time. I'd rather bcp all the data out to flat files.

It seems you're giving yourself a hard time. Any reason not to use SSMS?

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

Koen Verbeeck SSC Guru Points: 259207 More actions · Answer 3

@Lowell: to script out data, you go to Generate Scripts (1st screenshot) and after selecting the objects you need to click on Advanced in the next screen. There you can choose what to script: schema, data only or schema + data.

Need an answer? No, you need a question
My blog at https://sqlkover.com.
MCSE Business Intelligence - Microsoft Data Platform MVP

khosro.forum Newbie Points: 4 More actions · Answer 4

Thank you for your replies.

i'm a software programmer & developer using C# & SQL SERVER. in one of my project i need to build a script from database of users that uses my program.most of my program users use SQLEXPRESS. i wanna build it using Built in sql query in the program. i can do it by SMO , but don't wanna use SMO. because it seems some version of OS or .NET framework does not support SMO.

Thanks again.