| 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 viewsJaveed Ahmed |
 |
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 |
|
|
|
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\nBut 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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 |
|
|
|
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 KizerSQL Server MVP since 2007http://weblogs.sqlteam.com/tarad/ |
|
|
|
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 StudioFrom 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 |
|
|
|
|
Backing Up