Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

How to copy the database from external server to my local system server through Stored Procedure Expand / Collapse
Author
Message
Posted Tuesday, August 28, 2012 1:01 PM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 06, 2012 4:58 AM
Points: 2, Visits: 3
Hi,

I tried using the stored procedure to connect to the external server bu am unable to connect by entering the correct server name, user name and password. I don't have network. I think it is possible without network or through IP address.

I tried to connect external server by using
sp_addlinkedserver 'externalserver', 'SQL Server'

and also I tried this
sp_addlinkedsvrlogins 'externalserver', 'windowsusername', 'sa', 'sa'

Select * From [externalserver].[mydatabase].[dbo].[mytable]

But it is not connected. How to do it?

How to connect to the external server to access to the external database to view the data and migrate the data into another identical database in my local system.

Your valuable suggestion and sample stored procedure will be helpful. It is very urgent.

Thanks
Post #1351197
Posted Tuesday, August 28, 2012 2:25 PM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 12,031, Visits: 11,061
kamasat (8/28/2012)
Hi,

I tried using the stored procedure to connect to the external server bu am unable to connect by entering the correct server name, user name and password. I don't have network. I think it is possible without network or through IP address.

I tried to connect external server by using
sp_addlinkedserver 'externalserver', 'SQL Server'

and also I tried this
sp_addlinkedsvrlogins 'externalserver', 'windowsusername', 'sa', 'sa'

Select * From [externalserver].[mydatabase].[dbo].[mytable]

But it is not connected. How to do it?

How to connect to the external server to access to the external database to view the data and migrate the data into another identical database in my local system.

Your valuable suggestion and sample stored procedure will be helpful. It is very urgent.

Thanks


Do you get an error message? What do you mean by " I don't have network."? Can you ping the other sql box? What does "But it is not connected." mean?

Then I have to ask why do you need a link server for this? You stated you want to migrate the data from the server to your local? Can't you just import whatever data? That would be far easier than doing it through a link server.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1351255
Posted Wednesday, August 29, 2012 7:10 AM
Forum Newbie

Forum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum NewbieForum Newbie

Group: General Forum Members
Last Login: Thursday, September 06, 2012 4:58 AM
Points: 2, Visits: 3
Hi Sean Lange,

Thank you for your reply.

In my client place, to take backup of the database, in SQL server, there is an option 'Generate Scripts' which is not allowed at the client system. That is why I am using the stored procedure to create the backup of the client database in the client system and the same will be stored into my system through external server. In the client place, there is no network.

I tried the scripts to connect the remote server through linked server but it is not connected.

Is there any other way to get the backup of the database to be imported into my SQL server system or is there other options to move/copy the client database from the external server to my local server.

Please help
Post #1351592
Posted Wednesday, August 29, 2012 7:29 AM


SSChampion

SSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampionSSChampion

Group: General Forum Members
Last Login: Today @ 2:59 PM
Points: 12,031, Visits: 11,061
kamasat (8/29/2012)
Hi Sean Lange,

Thank you for your reply.

In my client place, to take backup of the database, in SQL server, there is an option 'Generate Scripts' which is not allowed at the client system. That is why I am using the stored procedure to create the backup of the client database in the client system and the same will be stored into my system through external server. In the client place, there is no network.

I tried the scripts to connect the remote server through linked server but it is not connected.

Is there any other way to get the backup of the database to be imported into my SQL server system or is there other options to move/copy the client database from the external server to my local server.

Please help


You mean to say that your client does not have a network? Or that you are not on that network. Either way, scripting the database using the generate script feature is not the most efficient method of copying a database. If you are not on the clients network there really is not a lot you can do. The best option would be to generate a backup. An actual backup not a scripted t-sql batch and have the client send you the .bak file.


_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Moden's splitter.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs
Understanding and Using APPLY (Part 1)
Understanding and Using APPLY (Part 2)
Post #1351607
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse