March 15, 2006 at 2:58 pm
If you are moving a database with custom SQL views to another server, can you move the views to the other server without re-creating them manually?
March 15, 2006 at 3:06 pm
When you move your database. View will be automatically tranfers. Views are in database.
Amit Lohia
March 15, 2006 at 3:09 pm
Thanks for the response. Aren't views contained in the master database schema?
March 15, 2006 at 3:15 pm
No - all objects such as tables, views, stored procs, users (not logins), functions, constraints, etc are stored in the database...
Think along the lines of a backup / restore - if you had to restore your DB to a new server, everything that DB needs to operate is included in the backup... This is because they are in the DB.
Make sure that you look at the sp_change_users_login stored procedure - you may need to remap your database logins so that user X matches login X on your new server.
March 16, 2006 at 10:48 am
As Ian indicates, a view defintion is stored in the user database. The master database stores information on the databases themselves and the logins to SQL Server (whether SQL Server or Windows based). Object information is always stored in the individual database. That's why there are system tables in every database. In SQL Server 2000 you can see the view definition by querying syscomments in the database itself.
K. Brian Kelley
@kbriankelley
March 16, 2006 at 2:50 pm
Hi,
In the case that someone created views in another database and you need to move them between servers or databases, just views by themselves you may script them by Right-Clicking -> All Tasks ->Generate SQL Script. Visit all 3 tabs, specify objects you want to script, pay attention if you need to generate DROP statement or not, be very careful here.
I do know projects where views are in the separate database and each table or view are called by databasename.username.viewname (or tablename). It was a popular design 5 years ago.
Regards,Yelena Varsha
Viewing 6 posts - 1 through 5 (of 5 total)
You must be logged in to reply to this topic. Login to reply