Generating SQL Server Scripts using SMO

This video show you how to generate SQL Server scripts using SMO

Tim Mitchell is a Microsoft SQL Server database developer, business intelligence consultant, writer, and speaker. He has been working professionally with SQL Server for over six years, and holds the MCTS and MCDBA certifications from Microsoft as well as a Bachelor's degree in computer science from Texas A&M University - Commerce.

ben on 10/9/2008

really nice - but where can I copy the code example?

Manish on 11/14/2008

This is really very help ful, but when I execute the generated script, I am getting error of some depending tables which are later in the list but their references were on the top. Need to do some modifications with the DependencyTree, DependencyWalker and DependencyCollection object of .net framework.

Saritha on 11/14/2008

Very Use full

Dale Moore on 11/14/2008

Very quick and simple for programmatically getting the objects from the database.

ben on 3/14/2009

great code/lesson

varma on 6/11/2009

Gud work.. it works grt.. I tried using the code Once it gets the scripts I want to backup and restore the database too I tried it as below.. its getting the scripts but not backing the database its not even prompting me any errors here i am executing this program from a remote server that should not be a problem right.. Thanks.. using System; using System.Collections.Generic; using System.ComponentModel; using System.Data; using System.Drawing; using System.Linq; using System.Text; using System.Windows.Forms; using System.IO; using System.Collections.Specialized; using Microsoft.SqlServer.Management.Smo; namespace WindowsFormsApplication1 { public partial class Form1 : Form { public Form1() { InitializeComponent(); } private void button1_Click(object sender, EventArgs e) { StreamWriter writer = new StreamWriter(@"c:\script_output.txt", false); Server s = new Server("servername"); Database db = s.Databases["AdventureWorks"]; // Set up the scripting options to be used below. ScriptingOptions so = new ScriptingOptions(); so.Permissions = true; so.Indexes = true; so.IncludeIfNotExists = true; // First script the database itself StringCollection coll = db.Script(so); foreach (string str in coll) { writer.WriteLine(str); } // Now script each of the tables foreach (Table t in db.Tables) { StringCollection col2 = t.Script(so); foreach (string str2 in col2) writer.WriteLine(str2); } // Now script each of the Users foreach (User u in db.Users) { StringCollection col3 = u.Script(so); foreach (string str3 in col3) writer.WriteLine(str3); } // Now Script each of the Userdefinedfunc foreach (UserDefinedFunction d in db.UserDefinedFunctions) { StringCollection col4 = d.Script(so); foreach (string str4 in col4) writer.WriteLine(str4); } Backup bkp = new Backup(); bkp.Devices.AddDevice(@"C:\AdventureWorks.bak", DeviceType.File); //bkp.Database(db); bkp.Database = "AdventureWorks"; bkp.Action = BackupActionType.Database; bkp.Initialize = true; bkp.PercentCompleteNotification = 10; bkp.PercentComplete += new PercentCompleteEventHandler(bkp_PercentComplete); bkp.SqlBackup(s); writer.Flush(); writer.Close(); } static void bkp_PercentComplete(object sender, PercentCompleteEventArgs e) { Console.WriteLine(e.Percent.ToString() + "% backed up"); } } }

Tonci on 9/15/2009

Just Excellent. Thank you very much.