February 25, 2013 at 2:18 am
Hi All,
Automate database script in sql server 2008 either in T-sql or SSIS Package.
If any one knows Please share
Regards,
Saranya
February 25, 2013 at 2:27 am
Can you provide more details ?
What scripts you are trying to automate , backups ?
February 25, 2013 at 2:30 am
Not Backups... Schema alone of tables,views,storedprocedures, permissions, Indexes etc..
February 25, 2013 at 2:39 am
Hi,
go to following steps:
Right click on DB...> goto...> tasks....> generate scrip....> select radio button(Script entire database and all database objects)
goto advance tab----> types of data to script( hear select what you want script only or script with data)
then give the file name.....> click on next...> click on next finish the wizard
regards,
Mahesh
February 25, 2013 at 2:45 am
Need to Automate on Weekly Basis
February 25, 2013 at 2:51 am
Got it.
You mean generate database scripts periodically.
In fact there is not out of the box solution for this in SSIS.
However you can use SMO using c# to acheive this.
http://stackoverflow.com/questions/3488666/how-to-automate-script-generation-using-smo-in-sql-server
You can use the above code in script task.
February 25, 2013 at 3:04 am
its Not working for me
February 25, 2013 at 3:04 am
ho ok...
so you need to do job scheduling,
opsion 1 : In SSIS carete package and take EXEC tast put the following sql query.. and do it package scheduling for weekly or monthly what ever you required .
2: just jobschedule following like sp
http://support.microsoft.com/kb/2019698
hope it will be help full to you...
regards ,
Mahesh
February 25, 2013 at 3:36 am
Not for daily backup's.
Script only for db schema's
Regards,
V.Saranya
February 25, 2013 at 3:57 am
What is the error you are getting for the C# script.
Also you must add refernce to classses
using Microsoft.SqlServer.Management.Smo;
using System.Collections.Specialized;
You need to add references in SSIS project for
Microsoft.SqlServer.Management.Smo
and
Microsoft.SqlServer.ConnectionInfo
February 25, 2013 at 4:14 am
Which script i Need to check?
In mentioned Blog 3 scripts are available
February 25, 2013 at 8:11 pm
If you have not found already.
Server srv = new Server();
//Reference the AdventureWorks2008R2 database.
Database db = srv.Databases["AdventureWorks2008R2"];
//Define a Scripter object and set the required scripting options.
Scripter scrp = new Scripter(srv);
scrp.Options.ScriptDrops = false;
scrp.Options.WithDependencies = true;
scrp.Options.Filename="C:\\testOutput.sql";
//Iterate through the tables in database and script each one. Display the script.
//Note that the StringCollection type needs the System.Collections.Specialized namespace to be included.
Microsoft.SqlServer.Management.Sdk.Sfc.Urn[] smoObjects = new Microsoft.SqlServer.Management.Sdk.Sfc.Urn[1] ;
foreach (Table tb in db.Tables) {
smoObjects[0] = tb.Urn;
if (tb.IsSystemObject == false) {
System.Collections.Specialized.StringCollection sc;
sc = scrp.Script(smoObjects);
}
}
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply