How do I copy a view from one server to another?

Question

I want to copy a view from server S1 --> Database D1 to Server S2 --> Database D2.

I generated the script by right clicking the database(D1)--> tasks--> generate script. However, when I execute this script in Database D2 it says that D1 doesn't exist. The error is on line1.

USE [D1]  -- Line 1
GO

Answer 1

Change the script so that you use the correct database before running it or remove the use statement completely either manually or by not scripting it out to begin with.

Answer 2

The USE statement changes the database context for the current connection. Since D1 does not exist on S2, executing this statement will result in an error.

Three options:

1. Remove the USE statements. Sometimes the scripting engine will generate multiples of these, so you may want to search/replace in the existing output in case there is more than one.

2. Similar to option #1, do a search/replace with the name of the database you know exists on S2.

3. Set the scripting options to not script the USE statements at all. This can be controlled through the Advanced scripting options in the wizard. Change the setting to False -- by default it's True, as shown below:

Answer 3

You can copy a View from one server to another server by using Export wizard..

1) Right Click On "DatabaseName" --> Choose Task --> Choose Export.

2) Export wizard will open now You have to fille the required detail as per the Destination server and the database login detail And the source server database login detail.

3) then select the tables/Views you want to move from and on what table you want to move the view.

By this way you can move your view.

You can also move your view by using the linkserver object of your destincation server connection.