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
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.
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
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.
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
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
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