February 28, 2005 at 9:04 am
Hi -
How do you perform database structure backup in SQL Server 2000?. Basically want to backup the database without data.
Want to schedule daily backup job which will backup database structure only. Like in Oracle you perform database export with rows=N.
Thanks in advance.
Gufran
February 28, 2005 at 11:01 am
Maybe if you could provide a few more details related to what your goal is that you are doing this for, it will cause additional replys.
The only suggestion I could come up with based on my current understanding of what you are trying to accomplish is to Generate a SQL Script of the entire database structure. This is useful to store with database backups in case structure changes later. But I am guessing you want more than that, empty tables(?) for some reason ?
Mike
February 28, 2005 at 12:58 pm
My goal is to perform partial database recovery.
To restore stored procedure(s) or function(s) or view(s) or revert to old table structures without having to restore whole database to acheive this.
To store entire database structure as part of my backup and to automate this process.
I am aware of generate SQL Script of entire database through Enterprise Manager, but how do you automate this?.
Does SQL Server provides any tool to perform partial recovery?.
Gufran
March 1, 2005 at 3:42 am
I had a client who wanted this sort of recovery. All I could end up doing was backing up the database, restoring it to another replica database, truncating all tables in the replica and then backing up the replica - not an elegant soultion.
You could try running a trace whilst you script out the database objects and then try to put this into a script to schedule.
March 1, 2005 at 8:01 am
You may also try the following. It's geared more for change management, but I think it could possibly accomodate your needs. I simply found it doing a quick Google search, so I can't speak on it beyond what I read in the first couple paragraphs. You may try similar searches in Google.
http://www.sqlservercentral.com/products/bwunder/archiveutility/
As for additional Google searching, you may try:
"SQL Server" "stored procedure" "generate DDL"
March 1, 2005 at 8:23 am
You could also use DTS (copy SQL Server objects task) to copy the objects without data (there is a Copy Data check box) to a backup database. Then you would have stored procs, tables, etc. all without data. Since it is a DTS package, you could schedule this to run as a job. I do this procedure for one of my databases and it works pretty well.
March 1, 2005 at 11:15 am
I had done this for a client once. I used vbscript and SQL-DMO objects to do the "Generate Script" functionality. (I beleived I had used the GenerateSQL method of the Database object.) Then, in the same job, I would drop the database and rebuild it with the text stream from the script. And by also writing status updates to the event log, it made for a complete unattended scheduled package. If I remember correctly, it was only about 50 lines of code.
Mark
October 11, 2005 at 10:31 am
There is an easy of doing this, you can run this executable to script out database objects.
mssql/upgrade/
scptxfr.exe
Closing this thread. Thanks for all the help.
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply