Backup 30 views without DB

Question

Backup 30 views without DB

a.borgeld

Ten Centuries

Points: 1395
More actions
July 7, 2010 at 11:54 am

#387293

I want to restore a production database to a test database.
1. Backup ProdDB
2. Copy to another server
3. Restore at TestDB
The problem is, it takes the views from the production database and kopies it.
But i want to keep the views from the TestDB.
And saving 30 views one by one is a lot of work it has to go easyer.
Doing the create database will take te option to create a database, but you don't see the view code.
Who's got a good idea? Thanks for helping!
Kind regards,
André

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

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

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

script all the views on your test database first (save to a file). Restore the database then run the scripts to re-create/alter the views to the ones you saved. shouldn't take more than an extra 30 seconds.

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

DBAgal Hall of Fame Points: 3185 More actions · Answer 2

If you are using SSMS 2008, you can View => View Object Details. Then navigate to the "Views" folder. Select the 30 Views you want by holding down the Ctrl key. Then, right click and script view as. Easy as pie.

Lowell SSC Guru Points: 323518 More actions · Answer 3

lets cover the basics, Andre, since this is your first post:

a VIEW is a saved SQL statement...so if you have other views in the test database, you will need to script them out:

this is a fast way to do it:

select definition + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)

from sys.sql_modules

left outer join sys.objects

on sys.sql_modules .object_id = sys.objects.object_id

where sys.objects.type='V'

and sys.objects.name IN('myFirstView','...','MyLastView')

those queries would then be available for you to create in the restored database. if they already exist, you'd have to change the statements to ALTER VIEW instead of CREATE VIEW.

now if you mean the DATA you saw in the view in test....well you'll have to save that data to a table and script the table and it's data out to a file..the SQL 2008 SSMS GUI has that option when you script a table.

then you'd have to run that script on the newly restored database.

see this article on it:

[urlhttp://www.kodyaz.com/articles/sql-server-script-data-with-generate-script-wizard.aspx[/url]

a.borgeld (7/7/2010)
I want to restore a production database to a test database.
1. Backup ProdDB
2. Copy to another server
3. Restore at TestDB
The problem is, it takes the views from the production database and kopies it.
But i want to keep the views from the TestDB.
And saving 30 views one by one is a lot of work it has to go easyer.
Doing the create database will take te option to create a database, but you don't see the view code.
Who's got a good idea? Thanks for helping!
Kind regards,
André

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!

a.borgeld Ten Centuries Points: 1395 More actions · Answer 4

But you have to script the views one by one? And then i think the easyest way is to clipboard and then paste it one by one in one ouput pane in the query editor.

And your right, althrough a few clicks its not such a problem.

I would think there would be a solution to export them all at ones.

a.borgeld Ten Centuries Points: 1395 More actions · Answer 5

Lol. I'm a forum newbie i see. I mostly search for solutions myself. But i see you can learn much from some solutions.

Thanks. I work in SQL2005 but i have SSMS 2008, so i will have a look.

Grant Fritchey SSC Guru Points: 398882 More actions · Answer 6

a.borgeld (7/7/2010)
But you have to script the views one by one? And then i think the easyest way is to clipboard and then paste it one by one in one ouput pane in the query editor.
And your right, althrough a few clicks its not such a problem.
I would think there would be a solution to export them all at ones.

I prefer using scripting tools & such, but if you want a quick way to do this with the Management STudio GUI, highlight the views folder in a database in the Object Explorer window. Make sure the Object Explorer Details window is open. Select the views with a shift-click or control-click. Right click and select Script Views To from the menu choice. Everything selected will go together.

"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore Roosevelt

Author of:
SQL Server Execution Plans
SQL Server Query Performance Tuning

a.borgeld Ten Centuries Points: 1395 More actions · Answer 7

Ok you guys, you helpt me a lot.

1.

select definition + CHAR(13) + CHAR(10) + 'GO' + CHAR(13) + CHAR(10)

from sys.sql_modules

left outer join sys.objects

on sys.sql_modules .object_id = sys.objects.object_id

where sys.objects.type='V'

2.

Export grid to *.txt

3.

Open with notepad and you see al the views in a nice ordent way.

4.

Copy to query editor and run.

Thanks y'all