Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

need to backup database schema Expand / Collapse
Author
Message
Posted Friday, January 25, 2008 1:32 PM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:27 AM
Points: 136, 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
Post #447788
Posted Friday, January 25, 2008 4:22 PM
Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Tuesday, August 19, 2014 7:04 AM
Points: 1,030, Visits: 2,795
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 Ellis

gethynellis.com
Post #447870
Posted Sunday, January 27, 2008 11:29 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Wednesday, September 17, 2014 7:26 AM
Points: 4,358, Visits: 9,536
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 opportunites brilliantly disguised as insurmountable obstacles.

How to post questions to get better answers faster
Managing Transaction Logs
Post #448077
Posted Monday, January 28, 2008 9:58 AM
SSCarpal Tunnel

SSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal TunnelSSCarpal Tunnel

Group: General Forum Members
Last Login: Today @ 5:14 PM
Points: 4,064, Visits: 5,319
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
Post #448441
Posted Tuesday, January 29, 2008 6:28 AM


Say Hey Kid

Say Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey KidSay Hey Kid

Group: General Forum Members
Last Login: Tuesday, July 30, 2013 9:53 AM
Points: 679, Visits: 456
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
Post #448803
Posted Tuesday, January 29, 2008 8:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Tuesday, April 30, 2013 10:27 AM
Points: 136, 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
Post #448896
Posted Thursday, January 31, 2008 6:11 PM
SSC Rookie

SSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC RookieSSC Rookie

Group: General Forum Members
Last Login: Today @ 4:20 PM
Points: 49, Visits: 329
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.

Post #450256
Posted Sunday, March 15, 2009 9:26 PM
Grasshopper

GrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopperGrasshopper

Group: General Forum Members
Last Login: Wednesday, June 4, 2014 12:53 PM
Points: 23, Visits: 384
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,
Post #676236
Posted Monday, March 16, 2009 4:29 AM
SSC-Enthusiastic

SSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-EnthusiasticSSC-Enthusiastic

Group: General Forum Members
Last Login: Monday, September 8, 2014 6:39 AM
Points: 159, Visits: 430
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
Post #676349
Posted Monday, January 13, 2014 1:19 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Wednesday, June 11, 2014 5:35 AM
Points: 1, Visits: 17
Hi Rubes,

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

Regards,
Hima
Post #1530199
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse