May 18, 2012 at 12:57 pm
You can use SQL Server Database Publisher Wizard to do this. Very simple and easy to use via gui interface. You can also call it via command line to make it automated.
This is a free tool from Microsoft and I believe is now built in with SQL Server 2008 and later. It can script the entire database with data. Also can pick and choose what tables and objects to script. It can script both the table with indexes and constraints and the data or each one seperately.
In turn you can also use the Microsoft.SqlServer.Management.Smo Namespace in VB, C# or what I have been using it in, Powershell.
http://msdn.microsoft.com/en-us/library/microsoft.sqlserver.management.smo.aspx
May 18, 2012 at 1:29 pm
We use 2005
Where can I find the following tool ?
SQL Server Database Publisher Wizard
May 18, 2012 at 1:31 pm
May 22, 2012 at 8:35 am
This tool is great.
Unfortunately it does not allow you to select one or few tables for scripting. ( See attached JPG file )
Our database has more than 2000 tables with some tables having more than million rows.
May 22, 2012 at 8:58 am
If all you are looking for are insert statements generated from an existing table then I would recommend the SSMS Tools Pack:
Generate Insert statements from resultsets, tables or databases
Insert statements for the whole database are generated by the order of PK-FK relationships. Top tables with no FK's are scripted first. Binary data is by default fully scripted. If you wish you can also set the scripting data limit between 0 and 10 Mb. Larger values than the limit are then scripted as NULL. Insert statements for the data in result grids are scripted into a new temporary table for each grid. For example from 5 result grids insert statements for 5 temporary tables get created.
I use it and recommend it to forum-goers here since it is an easy way to generate the "sample data" often times needed to post when requesting help.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 9:59 am
Is this tool free of charge ?
May 22, 2012 at 10:06 am
mw112009 (5/22/2012)
Is this tool free of charge ?
I am sure you will find all the information you need at the link I provided 😉
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 10:19 am
scripting objects or table contents can all be done using Microsoft management studio. right click the database and choose "tasks, generate scripts". select the objects you want to script. in the advanced options you can define to script the objects themselves or the containing data.
for backup purposes only there might be other possibility like creating kind of history tables using triggers or change tracking function. have a look at bol for further information.
May 22, 2012 at 10:24 am
weberharter (5/22/2012)
scripting objects or table contents can all be done using Microsoft management studio. right click the database and choose "tasks, generate scripts". select the objects you want to script. in the advanced options you can define to script the objects themselves or the containing data.
Very true. I could have mentioned SSMS as well, but the built-in functionality is cumbersome IMO. SSMS Tools Pack provides a way to get there in less clicks and the thing I really like about it is that it allows you to provide a filter (i.e. where-clause) when generating INSERT statements in case you do not want to script the entire table.
There are no special teachers of virtue, because virtue is taught by the whole community.
--Plato
May 22, 2012 at 11:02 am
In 2005 you do not have an "Advance Options" button.
May 22, 2012 at 12:19 pm
mw112009 (5/22/2012)
This tool is great.Unfortunately it does not allow you to select one or few tables for scripting. ( See attached JPG file )
Our database has more than 2000 tables with some tables having more than million rows.
Here is a link to the version 1.3 that i use and can select one or more objects.
Viewing 11 posts - 16 through 25 (of 25 total)
You must be logged in to reply to this topic. Login to reply