Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backing Up

Author  Topic 

mlawrence
Starting Member

5 Posts

Posted - 2014-08-26 : 06:25:36
Hi, I am new to SQL Server Admin and am hoping to get some help? I have a database that only contains views of data from another database. It has no tables at all.
Is there a way to backup the data in the views?
Thanks in advance.
Mat

ahmeds08
Aged Yak Warrior

737 Posts

Posted - 2014-08-26 : 08:51:22
you can use the Generate Scripts option in SSMS to backup only views

Javeed Ahmed
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-26 : 12:22:44
The Generate Scripts wizard will only allow you to script the DDL of the views and other objects, not the data. Since the data is in another database, you can simply backup that database or harder you can export the data from the views using SSIS/bcp/etc.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

jackv
Master Smack Fu Yak Hacker

2179 Posts

Posted - 2014-08-27 : 02:26:13
Backup both databases i.e the one with the views and the db with data. Ensuring view logic and data are kept in synch.

Jack Vamvas
--------------------
http://www.sqlserver-dba.com
Go to Top of Page

mlawrence
Starting Member

5 Posts

Posted - 2014-08-27 : 02:48:46
Thanks for the replies. Just to explain a little further my challenge...I have a main database with all the tables and a second database with just views. This is so that I can restrict the data available to the user and write some custom queries for the main tables.
I then need to extract the data from the restricted views and send to the user as a SQL backup file. Perhaps I am going about this the wrong way and would welcome alternative suggestions. For example how could I backup selected data by writing a query?
Mat
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-27 : 12:35:10
You can extract data from views via bcp.

bcp ViewDbName.dbo.ViewName out c:\temp\somefile1.txt -T -Sserver2\instance1 -c -t, -r\r\n

But you don't need a separate database to restrict access. Create the views in the source database and grant access to the views only. The users will not be able to query the tables directly. Alternatively, you can write stored procedures and only grant access to those.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

mlawrence
Starting Member

5 Posts

Posted - 2014-08-28 : 02:40:43
Thanks again for the feedback. I did look at bcp but it doesn't appear to allow backup to native SQL server format which is what I was looking for. I am unable to grant access in the main DB as the purpose for my approach is to provide a regular dump of just certain queried tables which is why views seemed logical.
I guess I will have to think again on the whole approach.
Mat
Go to Top of Page

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2014-08-28 : 12:27:56
Yeah I would rethink this design as it's not needed. SQL Server supports the security that you need all in one database.

Tara Kizer
SQL Server MVP since 2007
http://weblogs.sqlteam.com/tarad/
Go to Top of Page

Lincolnburrows
Yak Posting Veteran

52 Posts

Posted - 2014-09-12 : 06:04:25
Once you have taken steps to backup the other database that contains the real data, you could also backup the views from your views only database, by scripting them out using SQL Server Management Studio

From SQL Server Management Studio Via Object Explorer

  • Expand the database you wish to backup views for

  • Right click on the database and select Tasks -> Generate Scripts

  • Choose "Select specific database objects"

  • Check views or just the views you want to backup

  • Select Next

  • Choose Save to new query window

  • Choose next and then next again

  • Click on finish and the view or views you selected will be scripted to a new window

Hopefully in way you can backup your database
Go to Top of Page
   

- Advertisement -