SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


need to backup database schema


need to backup database schema

Author
Message
rubes
rubes
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 398
I've got a large reporting database. I don't need to back this up as its data can be recreated. However, I would like to backup its schema so I can recreate the database if needed. What are some ways that I can automate this task (SQL 2005, BTW)? I don't want to use 3rd party tools. I guess I could use sp_help, sp_helptext, etc. I would prefer to use SSIS but have not found a way to do it.

Regards,
Rubes
Gethyn Ellis
Gethyn Ellis
SSCrazy
SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)SSCrazy (2.8K reputation)

Group: General Forum Members
Points: 2813 Visits: 2905
Hi

Sorry if this sounds obvious, you can script the entire db using management studio, right click on the db go to all tasks and script db.

You can also expand the security folder and then the schema folder, and script each db schema individually.

Hope this helps

Gethyn Ellisgethynellis.com
Jeffrey Williams 3188
Jeffrey Williams 3188
SSCoach
SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)SSCoach (19K reputation)

Group: General Forum Members
Points: 19583 Visits: 10042
I know you stated you did not want to use third party tools, but I am assuming that is because of cost. So, visit www.quest.com and download their free database comparison tool for SQL Server.

Once installed, create a new blank database then start your comparison. Select your current report server as the source and this new blank database as the destination. The comparison tool will build all of the scripts necessary to synchronize the schema in both databases.

Jeffrey Williams
Problems are opportunities brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs

Greg Charles
Greg Charles
SSChampion
SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)SSChampion (11K reputation)

Group: General Forum Members
Points: 11661 Visits: 5985
Have you looked at the Transfer SQL Server Objects Task in SSIS? You could create a package containing the task (configured to copy table schema without data) and schedule it to run regularly.

Greg
Will1922
Will1922
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: General Forum Members
Points: 1975 Visits: 481
I don't know if it will work in 2005, but to script everything in all of your databases the following command works great for me:

exec master..sp_MSforeachdb "exec master..sp_ScriptDatabase ?"



Source code for the script:


CREATE proc sp_ScriptDatabase @dbname sysname
as
declare @command varchar(1000)
declare @texttime varchar(10)
set @texttime = convert(varchar, getdate(), 102)
set @command = '"C:\Program Files\Microsoft SQL Server\MSSQL\Upgrade\scptxfr.exe" /s IDEV /I /q /G /d ' + @dbname + ' /f d:\BACKUPightly1\'+LTRIM(RTRIM(@@SERVERNAME))+'_' + @dbname + '_' + @texttime + '.txt /r'

print @command
exec master..xp_cmdshell @command
GO






This will create a file for each of your databases that has all of the objects scripted.

I would suggest all dbas implement this script into their nightly processes. I then zip up all the files into one zip file. Presto! I have a daily history of the schemas of all of my databases.

If this does work in 2005, please post that it does. My 2005 test server is down at the moment, so I can't test it. (Yes, we are still on 2000)


Live to Throw
Throw to Live
Will Summers
rubes
rubes
SSC-Addicted
SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)SSC-Addicted (414 reputation)

Group: General Forum Members
Points: 414 Visits: 398
Thanks for all the input!

Ellis, I've used the right click generate scripts method quite often. In this case I needed something automated.

Greg, the Transfer SQL Server Objects task in SSIS is exactly what I needed. I simply created a "schema" database to copy the objects to and scheduled a backup of it. I also transferred static lookup data to the schema database so that was backed up as well.

Will, I have not had much success for sp_MSForEachDB in sql 2005. It "appears" to work and doesn't throw in errors, but on instances with hundreds of database, I've seen it skip databases altogether. Very flaky behaviour so I stay away from it in 2005 and just create a cursor off of sysdatabases instead when I need to. I also don't know if the scptxfr.exe utility will work in 2005. I didn't find it in the sql server directories and did not try the 2000 copy in 2005. It may work, but I prefer Gregs method.

Thanks again!

Regards,
Rubes
Lisa Ward-361516
Lisa Ward-361516
Valued Member
Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)Valued Member (60 reputation)

Group: General Forum Members
Points: 60 Visits: 382
You might try this product which does not cost:
SQL Server Database Publishing Wizard

For further details on usage please see:

http://www.codeplex.com/Wiki/View.aspx?ProjectName=sqlhost&title=Database%20Publishing%20Wizard

It can be run via command line and schedule through SQL Server's job scheduler.

Biggest issue I found with it is that it does NOT script out users properly. It converts them to roles. However, if you are primarily interested in the schema, it may work for you.
MyTSQLRepl
MyTSQLRepl
SSC-Enthusiastic
SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)SSC-Enthusiastic (126 reputation)

Group: General Forum Members
Points: 126 Visits: 409
Hello All,

I have the same question. But we have quest software used for backup and restore.

Jeffrey, can the LiteSpeed backup only the stored procedures or do I have to backup all the database but the software could restore any object needed. I do not want to backup the whole database because of the size. I just wanted the stored procedures.

Please suggest if LiteSpeed could be used for this purpose?

Thanks,
James Horsley
James Horsley
Right there with Babe
Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)Right there with Babe (745 reputation)

Group: General Forum Members
Points: 745 Visits: 451
Where I have had a similar need I have done it the other way around by creating a "template" database that has all the schema in it but with all the tables empty - this empty database is backed up and restored with another name as the "reporting" database into which all the data is loaded. Whenever changes are made to the schema they are made to the empty template database.

I find this is more usable than a scripted copy of the schema as it allows changes to the schema to be easily applied to the template




James Horsley
Workflow Consulting Limited
himabindu.dindukurth
himabindu.dindukurth
Grasshopper
Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)Grasshopper (15 reputation)

Group: General Forum Members
Points: 15 Visits: 17
Hi Rubes,

Can you please tell us the steps you followed to automate only database schemas backup

Regards,
Hima
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search