Job to Back Up DB structures only

Question

Job to Back Up DB structures only

Michelle-138172

Hall of Fame

Points: 3234
More actions
August 23, 2016 at 9:43 am

#311395

I have a db that has huge data but simple tables. I want to set up a job to back up the db structure only without the data. What's the best way to do this? Thanks.

Viewing 9 posts - 1 through 8 (of 8 total)

You must be logged in to reply to this topic. Login to reply

Phil Parkin SSC Guru Points: 246960 More actions · Answer 1

If you tie the structure of your database to a VCS, this requirement should be unnecessary 🙂

I therefore assume that you do not have this in place (I suggest you seriously think about it if your database is important to your business).

A couple of ideas come to mind. One is to use sqlpackage.exe to extract a DACPAC from your database.

Another (which I have only read about) is to use DBCC CloneDatabase.

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Alan Burstein SSC Guru Points: 61141 More actions · Answer 2

As Phil eluded to, this is best handled via source control. Another way to get collect definitions is to use dbo.sp_GetDDL[/url]

This is a script I use to pull all your table definitions into a table:

SET NOCOUNT ON;

DECLARE @objects TABLE

(

objectTempID int identity,

objectType varchar(20),

objectName varchar(100),

objectDefinition varchar(max)

);

DECLARE @i int = 1;

DECLARE @objectDef TABLE(objectDefinition varchar(max));

INSERT @objects (objectType, objectName)

SELECT 'table', CONCAT(TABLE_CATALOG,'.',TABLE_SCHEMA,'.',TABLE_NAME)

FROM INFORMATION_SCHEMA.TABLES

WHERE TABLE_TYPE = 'BASE TABLE';

WHILE @i <= (SELECT COUNT(*) FROM @objects)

BEGIN

DECLARE @o varchar(100) = (SELECT objectName FROM @objects WHERE objectTempID = @i);

INSERT @objectDef EXEC dbo.sp_GetDDL @o;

PRINT @o

UPDATE o

SET o.objectDefinition = od.objectDefinition

FROM @objects o JOIN @objectDef od ON o.objectTempID = @i;

DELETE FROM @objectDef;

SET @i += 1;

END

-- Tables

SELECT objectType, objectName, objectDefinition

FROM @objects

"I cant stress enough the importance of switching from a sequential files mindset to set-based thinking. After you make the switch, you can spend your time tuning and optimizing your queries instead of maintaining lengthy, poor-performing code."

-- Itzik Ben-Gan 2001

Lowell SSC Guru Points: 323518 More actions · Answer 3

there's a number of powershell scripts that use SMO to script out all objects as well;

you could easily set something up to call that on a regular basis.

I'll see if i can find my version, it's on another computer.

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Eric M Russell SSC Guru Points: 125561 More actions · Answer 4

Michelle-138172 (8/23/2016)
I have a db that has huge data but simple tables. I want to set up a job to back up the db structure only without the data. What's the best way to do this? Thanks.

When you say "backup", do you mean: a SQL Server backup, clone to another database, extract to .sql files ?

Also, what is the purpose of this backup: disaster recovery, performance testing, baseline comparison, sampling for development ?

In addition to the link provided earlier on database cloning, here is an article on data sampling in case that's your goal.

https://www.mssqltips.com/sqlservertip/3157/different-ways-to-get-random-data-for-sql-server-data-sampling/

"Do not seek to follow in the footsteps of the wise. Instead, seek what they sought." - Matsuo Basho

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 5

Many thanks to everyone. Great ideas! I'll look into the options and pick one that best fits my purpose.

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 6

Thanks, Eric. I just wanted to extract the db structure to a .sql file and backup the file on a regular basis. This way I can rebuild the DB from the .sql file and reprocess the data in case of disaster recovery.

I could use "Generate Scripts" for a one-off deal, but want to set up a job for it.

Eric M Russell (8/23/2016)
When you say "backup", do you mean: a SQL Server backup, clone to another database, extract to .sql files ?
Also, what is the purpose of this backup: disaster recovery, performance testing, baseline comparison, sampling for development ?
In addition to the link provided earlier on database cloning, here is an article on data sampling in case that's your goal.
https://www.mssqltips.com/sqlservertip/3157/different-ways-to-get-random-data-for-sql-server-data-sampling/

Phil Parkin SSC Guru Points: 246960 More actions · Answer 7

This way I can rebuild the DB from the .sql file and reprocess the data in case of disaster recovery.

This sounds very much like the long-winded equivalent of restoring a backup! 🙂

The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin

Michelle-138172 Hall of Fame Points: 3234 More actions · Answer 8

Exactly so. Only that I don't need to back up and store the huge amount of data, which is refreshed constantly and is not worth saving.

Phil Parkin (8/24/2016)
This sounds very much like the long-winded equivalent of restoring a backup! 🙂