how to take backup databaseobjects(tables scripts,storeprocedure and functions etc) in sqlserver using sql scripts

Question

how to take backup databaseobjects(tables scripts,storeprocedure and functions etc) in sqlserver using sql scripts

asrinu13

Say Hey Kid

Points: 709
More actions
October 26, 2017 at 8:08 am

#329390

Hi All,
I have one doubt in sql server .
how to take Script database objects automaticaly using sql script and keep into specificfoleder(c:\backup\)
Create monthly
tables structure( create without data),views,procedures,functions and triggers backups with unique names and all are in one file and keep it into
specifice folder(current system: (c:\backup\))in SQL Server.
I have tried like below using ssms manualy
: right click on database and click task...>generate scripts..>choose objects...>select entire database
and all datbase objects(tables,view,function and triggers..etc) ....>
set scripting option choos the required dirctoryname((c:\backup\))and save file
please tell me how to achive this task in sql server .

Viewing 6 posts - 1 through 5 (of 5 total)

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

SQLPirate SSCrazy Points: 2880 More actions · Answer 1

Just schema?

You can extract a dacpac of your database without much powershell code or from the command line. Either of those methods can be set into a SQL Agent job for regular runs.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 2

SQLPirate - Thursday, October 26, 2017 4:05 PM
Just schema?
You can extract a dacpac of your database without much powershell code or from the command line. Either of those methods can be set into a SQL Agent job for regular runs.

Where do you find how to do that? Got a link?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

SQLPirate SSCrazy Points: 2880 More actions · Answer 3

Sure thing.

Here's Microsoft's extract page and example... I don't actually like this one, but it is what it is.
SQLStudies.com actually just posted this yesterday.

My starting point for approaching this would be to build on this very small test...
I created a folder at C:\DACBackups and in that folder I created DACPACBuild.ps1 with the following code.

add-type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll" $d = new-object Microsoft.SqlServer.Dac.DacServices "server=localhost" $d.extract("C:\DACBackups\dac01.dacpac", "test", "dacpac extraction", "1.0.0.0")

(Info about Microsoft.SqlServer.Dac.DacServices can be found here.)

I then created a job on my instance with an Operating system (CmdExec) step that just calls the following:

PowerShell.exe "C:\DACBackups\DACPACBuild.ps1" -NonInteractive

Running the job creates the specified dac01.dacpac file which contains the schema backup for my database [Test]

A few small modifications would allow the target file name, description, and version to be set automatically.

Jeff Moden SSC Guru Points: 1003872 More actions · Answer 4

SQLPirate - Thursday, October 26, 2017 6:42 PM
Sure thing.
Here's Microsoft's extract page and example... I don't actually like this one, but it is what it is.
SQLStudies.com actually just posted this yesterday.
My starting point for approaching this would be to build on this very small test...
I created a folder at C:\DACBackups and in that folder I created DACPACBuild.ps1 with the following code.
add-type -path "C:\Program Files (x86)\Microsoft SQL Server\110\DAC\bin\Microsoft.SqlServer.Dac.dll" $d = new-object Microsoft.SqlServer.Dac.DacServices "server=localhost" $d.extract("C:\DACBackups\dac01.dacpac", "test", "dacpac extraction", "1.0.0.0")
(Info about Microsoft.SqlServer.Dac.DacServices can be found here.)
I then created a job on my instance with an Operating system (CmdExec) step that just calls the following:
PowerShell.exe "C:\DACBackups\DACPACBuild.ps1" -NonInteractive
Running the job creates the specified dac01.dacpac file which contains the schema backup for my database [Test]
A few small modifications would allow the target file name, description, and version to be set automatically.

Thanks for taking the time. That does the full monty with FKs, Partitionion Functions/Schemas, indexes, constraints, etc, etc, etc?

--Jeff Moden

RBAR is pronounced "ree-bar" and is a "Modenism" for Row-By-Agonizing-Row.
First step towards the paradigm shift of writing Set Based code:
________Stop thinking about what you want to do to a ROW... think, instead, of what you want to do to a COLUMN.

Change is inevitable... Change for the better is not.

Helpful Links:
How to post code problems
How to Post Performance Problems
Create a Tally Function (fnTally)

sturner SSC-Insane Points: 22411 More actions · Answer 5

It uses the .net framework functionality for scripting that are in the Microsoft.SqlServer.Management.Smo namespace. I wrote a .net command line utility in C# that lets me access these handy scripting features and use it in all sorts of batch scripts I run. The most handy thing I use it for is scripting out all the SQL Agent Jobs on each of the production database servers every night so they can get pushed out to the DR site along with the back up jobs.

The probability of survival is inversely proportional to the angle of arrival.