Copying view from remote to local SQL Server with SSMS

20 pts.
Microsoft SQL Server 2000
SQL Server 2000 migration
SQL Server 2008
SQL Server 2008 administration
SQL Server Enterprise Manager
SQL Server Management Studio
SQL Server Management Studio 2008
SQL Server migration
SQL Server views
SSMS 2008
Hi I'm currently trying to get to grips with an upgrade from sql server 2000 to 2008, forced by an upgrade at my ISP. I'm used to using Enterprise Manager to export data from my ISP to my local machine for backup, and this process copying tables as tables, views as views and so on. But using Export in SSMS 2008 doesn't seem to do that. Instead it takes a query, runs it to create a table and then exports the table with data. So instead of ending up with a query with its permissions, I end up with a table which is no use to me. Obviously I can open the query, copy its sql, open a new local query, paste the sql and then set all the permissions, but this is a very long winded way and can't be automated. I'm sure there's something really obvious I am doing wrong, but I just can't find it! There are lots of differences between 2000 and 2005/8, but I can't find any helpful web resource to give me a quick guide to what to do differently with 2008. Books online gives you chapter and verse on the new product, but not the idiot's guide to what's changed. If anyone knows of a good web resource, I'd love to hear about it. Thanks in advance for any help. TP

Answer Wiki

Thanks. We'll let you know when a new response is added.

There isn’t really an easy way to script out the views automatically. Unless you are changing them all that often you shouldn’t need to back them up all that often. Also your ISP should be backing up the database for you so that it can be restored from tape in case of a problem.

If you want to get creative you can use the system stored procedure sp_helptext to script out the views, then use SSIS to capture that output and run the output against your local database.

Discuss This Question: 3  Replies

There was an error processing your information. Please try again later.
Thanks. We'll let you know when a new response is added.
Send me notifications when members answer or reply to this question.
  • Denny Cherry
    Check out my SQL Server blog "SQL Server with Mr Denny" for more SQL Server information.
    69,110 pointsBadges:
  • Tsp1965
    Thanks for that - I had a nasty feeling about that. It seems very sad that Microsoft have disimproved a system that worked well on ss2k. Another gripe I have with ss2008 and SSMS is the nightmare it has made of permissions. When I set up a new table on my development server with ss2k I could set its permissions, get it all working and then upload it, preserving all its permissions. As long as you had the same logins on both servers, it worked fine. As far as I can see SSMS doesn't do this, so each time I back up my remote db onto my local one, I 'lose' all the permissions and have to then go and set every permission on the local server again. And given the number of different restricted access logins I have for different web pages, that's a big job. Is there any way of preserving permissions on tables to stop this happening? Thanks again for your help.
    20 pointsBadges:
  • Denny Cherry
    Any time you drop the table (be it SQL 2000, SQL 2005, SQL 2008, SQL 6.5, etc) the permissions are removed. If this is a problem, in your development environment you can grant the rights to the schema instead of the tables (granted this will give you rights to all objects which may not be what you need). Another option would be don't remove the tables. Only remove the data and leave the tables alone. Since I assume you do all your development work in dev, then move the changes into production the schemas should be identical. All you should need to do is download the data for the tables that your application writes to. All the read only tables can stay the same. If you need to drop and recreate all the tables each time you can use the scripts found here to get the rights from production as a T/SQL script you can then run against the dev database. If the usernames aren't the same a quick search and replace will take care of that.
    69,110 pointsBadges:

Forgot Password

No problem! Submit your e-mail address below. We'll send you an e-mail containing your password.

Your password has been sent to:

To follow this tag...

There was an error processing your information. Please try again later.

Thanks! We'll email you when relevant content is added and updated.


Share this item with your network: