asked
How to automate generate scripts in SQL Server (on a weekly or a daily basis)
Hi, I need a take a script backup of a database on a daily basis. I don't need a full database backup. I need only the script of the tables, procedures, functions etc... and to be placed in the specified location. Please help on this. Regards, Nandhini
I would either write a command-line C#-program using SMO (Sql Server Management Objects) classes to script out the database schema and schedule that program to run daily/nightly, alternatively use one of the free tools already available for doing this. SchemaZen (
https://github.com/sethreno/schemazen/releases) seems to have received some public liking. There are also commercial products where you'll probably also be able to get support for the product, but if you only want to script out the database schema, I wouldn't pay hundreds of dollars. I would NOT try to do this using T-SQL scripting - SMO is much, much simpler even for a someone like me who very rarely writes C#-code.
I've done this both with SMO and with T-SQL, and I agree it's much easier with SMO. If anyone isn't convinced, just try scripting foreign key create statements with only T-SQL. I recommend it as an excellent opportunity to learn the catalog views, but the SMO approach is much simpler.
And you can easily utilize SMO through PowerShell and easily schedule by Agent.
17 People are following this question.
