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.