SQLTeam.com | Weblogs | Forums

How to take backup databaseobjects(tables scripts,storeprocedure and functions etc) in sqlserver using sql scripts

tsql
sql2012

#1

Hi All,
I have one doubt in sql server .
how to take Script database objects automaticaly using sql script and keep into specificfoleder(c:\backup)

Create monthly
tables structure( create without data),views,procedures,functions and triggers backups with unique names and all are in one file and keep it into
specifice folder(current system: (c:\backup))in SQL Server.

I have tried like below using ssms manualy
: right click on database and click task...>generate scripts..>choose objects...>select entire database
and all datbase objects(tables,view,function and triggers..etc) ....>
set scripting option choos the required dirctoryname((c:\backup))and save file

please tell me how to achive this task in sql server .


#2

We tackle this differently. In DEV each individual object (e.g. SProc, Trigger, View) and also all DDL change scripts, are in separate files. We store them on disk, in folders organised by Client / Project, and they are stored in a Revision Control System (in our case that is SVN). So every single change is stored, rather than just a snapshot of the database structure once-a-month, or similar.

I've known of situations where two developers worked on the same Procedure - they just Right-Click and EDIT in SSMS and then "Apply" and the second developers work overwrote the first, and no easy way to get that earlier work back again. Using revision control system and all source code in physical files on the disk that problem does not happen as all changes are preserved, and when second developer checks into Repository they are made aware of the collision and have the chance to Merge their changes with the first developer's work.


#3

If you need the details for third party systems then something like the following should get you started: