Automate the backup of Views

[simian336]

I have a third party application in which I have created several Views. I would like to back each View up to an sql file.

This is easy to do if you right click on a View and choose

Script View as > Alter to > File

and give it a name. I was wondering if there is a way to do this from the command line so I could automate and do it on a regular basis?

Any Thoughts?

Thanks

Simi

 

[simian336]

I think I finally found something. You can use either:

EXEC sp_helptext 'ViewName';

or

SELECT OBJECT_DEFINITION (OBJECT_ID(N'ViewName))

to see the object definition. I will just need to work on getting it saved to a file from there.

Thanks

Simi

 

[gmmastros]

Sorry. I noticed this a couple hours ago and started writing up a reply, but then got side-tracked.

I suggest you use osql or SQLCMD from a command prompt. You'll need to play with it a bit to get it the way you want, but, to get you started....

SQLCMD -S[!]ServerName[/!] -d[!]DatabaseName[/!] -E -Q"sp_helpText '[!]ViewName[/!]'" -o"C:\[!]FolderName\ViewName.sql[/!]"

Obviously, change the parts in red. Also, I recommend you save this to a batch file. Open notepad, copy paste the code above and save it to your computer somewhere. Name the file something like "BackupViews.bat"

Then, start a command prompt, navigate to the correct folder, and type "BackupViews" and hit enter.

You could even automate this process using the built-in windows task scheduler.

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[markros]

That may be a good topic for a blog post.

 

[simian336]

This would also be helpful if you only had access to SQLCMD and you need to be able to see view definitions, I think it would also work with sp's.

This is what I wound up with...


USE mydatabase;
GO
declare @output as varchar(max)
declare @tablename as varchar(100)

DECLARE mycursor CURSOR FOR
--All the views that I want start with '_'
select name from sys.views where name like '[_]%';
OPEN mycursor;
FETCH NEXT FROM mycursor into @tablename
WHILE (@@FETCH_STATUS = 0)
begin
print '--************* ' + @tablename + ' **************'
set @output = (SELECT OBJECT_DEFINITION (OBJECT_ID(@tablename)) AS [Object Definition])
print @output
FETCH NEXT FROM mycursor into @tablename;
end

CLOSE mycursor;
DEALLOCATE mycursor;
GO

Simi

 

[gmmastros]

You don't really need a cursor here. This would work just as well.

SET NOCOUNT ON
Select '--*************  ' + name + ' **************' + Char(13) + Char(10) + 
       object_Definition(object_id)
From   sys.views
where name like '[_]%'

-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

Hey George,

I tried your solution but it cut off all my definitions after 5 or 6 lines. The longest was 10 lines.

Could I have a setting different from yours?

Thanks

Simi

 

[gmmastros]

Click Tools-> options
Expand "Query Results"
Expand "SQL Server"
Click "General"
Set "Default destination for results" to "results to text"
Click "Results to text" (on the left)
Change "Maximum number of characters displayed in each column" to 8000.

now run it again.

Understand that these are just the settings for SQL Server management Studio. When you run this form OSQL or SQLCMD, nothing would be cut off (because SQLCMD is not run through management studio).


-George

"The great things about standards is that there are so many to choose from." - Fortune Cookie Wisdom

 

[simian336]

George,

I made the change in SSMS and it works, but it is still cut off in SQLCMD.

Any ideas?

Thanks

MJ

 

[SQLSister]

Personally I prefer the appraoch of never writing a view except through a script that is under source control. Now you don't need to periodically rescript all of them because a new source control version is created every time someone chnages the view. Just becasue it is daatbase stuff should never exempt you from making all code changes through source control.

"NOTHING is more important in a database than integrity." ESquared

 

[simian336]

Good thought.

What program do you use for your souce control?

Simi

 

[SQLSister]

We use Subversion

"NOTHING is more important in a database than integrity." ESquared