0

Our database has tons of tables, but there are only a few tables & stored procedures that need to be backed-up on a weekly basis.

To backup, I right-click the DB, click Generate Scripts, and then select the objects that I want to back up. I usually change Script Drop to True, Script Data to True, and a few other changes. Then I begin selecting tables and SPs, and finally generate the script.

Is there a way to automate this? That way I don't have to change those options or select the same tables every time. I do not want to (and cannot) backup the database.

I also saw this link asking for a command-prompt version, but I do not want to mess with VB or any other 3rd-party language just to automate this.

We use Sql Server 2008.

Thanks.

Community
  • 1
  • 1
fdkgfosfskjdlsjdlkfsf
  • 3,165
  • 2
  • 43
  • 110

2 Answers2

0

I'm not a DBA, but perhaps it would be wise to put those few tables into a different filegroup and just have a weekly job that would back that filegroup up?

Read here how to backup specific filegroup: SQL Server Filegroup Backups

As for stored procedures and other objects. I would simply use a database versioning tool, such as Git, SVN, TFS or something else to version your objects.

This question covers some details how to version your DB code using Git: How to use git as source control provider for SQL Server Management Studio

Community
  • 1
  • 1
Evaldas Buinauskas
  • 13,739
  • 11
  • 55
  • 107
  • Believe me, if I could do that, it would've been done already. Unfortunately I can't. – fdkgfosfskjdlsjdlkfsf Aug 23 '16 at 19:36
  • @rbhatup do object names have to be identical? I would suggest to have a table of objects that need to be constantly backed up and simply insert data into some temporary schema with matching object names plus the date they have been updated. For stored procedures you could use object_definition function to create their backups in temporary Schema as well. Does any of it make sense? Or even better just store stored procedure version as a text in that table by making use of the very same function object_definition. – Evaldas Buinauskas Aug 23 '16 at 20:21
  • I was under the impression that filegroups were something else, such as creating a separate database for these tables. – fdkgfosfskjdlsjdlkfsf Aug 24 '16 at 21:20
  • Can I add tables to a filegroup? For example, I have filegroup 'ReadOnly'; how can I add ten different tables to that filegroup? The link only seems to show how to back up a filegroup. Thanks. – fdkgfosfskjdlsjdlkfsf Aug 24 '16 at 21:20
  • I'm looking at this [SO link](http://stackoverflow.com/questions/712449/how-do-i-move-a-table-to-a-particular-filegroup-in-sql-server-2008) where someone asks the same thing, but the replies are to move indexes and PKs. – fdkgfosfskjdlsjdlkfsf Aug 24 '16 at 21:23
  • @rbhatup This seems to be quite informative: https://www.mssqltips.com/sqlservertip/2442/move-data-between-sql-server-database-filegroups/ – Evaldas Buinauskas Aug 25 '16 at 05:53
0

Here at Redgate we have a free tool called DLM Dashboard. This monitors a database for schema changes and snapshots it each time it sees a change, so is completely automated. If required, alerting can be set up when changes are made.

I'd be curious to know if this meets your needs.

David Atkinson
  • 5,759
  • 2
  • 28
  • 35