SQL Server Forums
Profile | Register | Active Topics | Members | Search | Forum FAQ
 
Register Now and get your question answered!
Username:
Password:
Save Password
Forgot your Password?

 All Forums
 General SQL Server Forums
 New to SQL Server Administration
 Backing Up
 New Topic  Reply to Topic
 Printer Friendly
Author Previous Topic Topic Next Topic  

mlawrence
Starting Member

3 Posts

Posted - 08/26/2014 :  06:25:36  Show Profile  Reply with Quote
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

India
643 Posts

Posted - 08/26/2014 :  08:51:22  Show Profile  Send ahmeds08 a Yahoo! Message  Reply with Quote
you can use the Generate Scripts option in SSMS to backup only views

Javeed Ahmed
Go to Top of Page

tkizer
Almighty SQL Goddess

USA
36941 Posts

Posted - 08/26/2014 :  12:22:44  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Flowing Fount of Yak Knowledge

United Kingdom
2062 Posts

Posted - 08/27/2014 :  02:26:13  Show Profile  Visit jackv's Homepage  Reply with Quote
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

3 Posts

Posted - 08/27/2014 :  02:48:46  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 08/27/2014 :  12:35:10  Show Profile  Visit tkizer's Homepage  Reply with Quote
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

3 Posts

Posted - 08/28/2014 :  02:40:43  Show Profile  Reply with Quote
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

USA
36941 Posts

Posted - 08/28/2014 :  12:27:56  Show Profile  Visit tkizer's Homepage  Reply with Quote
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
Starting Member

31 Posts

Posted - 09/12/2014 :  06:04:25  Show Profile  Reply with Quote
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
  Previous Topic Topic Next Topic  
 New Topic  Reply to Topic
 Printer Friendly
Jump To:
SQL Server Forums © 2000-2009 SQLTeam Publishing, LLC Go To Top Of Page
This page was generated in 0.07 seconds. Powered By: Snitz Forums 2000