January 25, 2008 at 1:32 pm
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
January 25, 2008 at 4:22 pm
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 Elliswww.gethynellis.com
January 27, 2008 at 11:29 am
I know you stated you did not want to use third party tools, but I am assuming that is because of cost. So, visit http://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
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”
― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs
January 28, 2008 at 9:58 am
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
January 29, 2008 at 6:28 am
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:
execmaster..sp_MSforeachdb "exec master..sp_ScriptDatabase ?"
Source code for the script:
CREATEproc 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)
January 29, 2008 at 8:29 am
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
January 31, 2008 at 6:11 pm
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.
March 15, 2009 at 9:26 pm
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,
March 16, 2009 at 4:29 am
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
January 13, 2014 at 1:19 am
Hi Rubes,
Can you please tell us the steps you followed to automate only database schemas backup
Regards,
Hima
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply