March 31, 2003 at 4:22 pm
Hope this doesn't come off as an extremely stupid question. We have a development server where the developers have free reign to create their own stored procedures, functions, data types, etc. My question is how can I systematically backup these sql objects without having to backup the entire database since we don't really care about the actual data? Is there a way to do it through the system tables? I'd like to have it setup as a job that runs each night.
Once again, not sure if this is a dumb one or not. I just don't want to have to manually script the entire database each day.
Thanks in advance.
Adrian Porter
Adrian Porter
March 31, 2003 at 5:02 pm
One way would be to script out everything each night using DMO. Bill Wunder has posted a freeware utility that does just that and checks into VSS. Lately I've been experimenting with something similar, only keeping the data in a table. It's doable. Backing up system tables - maybe by copying the contents in a shell db that would get backed up would work. For procs I think all you need is syscomments.
Andy
March 31, 2003 at 9:23 pm
Agree on the VSS.
Following will script database to drive c$ on the SQL server.
Replace Exec statement with a Select statement to get generated statement.
Can be scheduled as a job.
Scptxfr.exe is a MS supplied util.
Declare @C Varchar(1000)
Select @C='Exec master..xp_cmdshell ''scptxfr.exe /s '+@@SERVERNAME+' /d '+db_name()+' /f '+@@SERVERNAME+
'\C$\'+@@Servername+'_'+db_name()+'_'+
Replace(convert(Varchar(8),getdate(),112)+'_'+convert(Varchar(8),getdate(),114),':','')+
'.sql /r /O /H /G '''
Exec (@C)
April 1, 2003 at 5:21 am
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply