TSQL moving data from server to another

[Mansi]

Hello,
I have this scenario ...
1- Open SQL SERVER Management.
2- Connect to local server and view Database (old ASPDOTNETSTOREFRONT) named [local Database]
3- connect to remote server and access my staging database succefully [Remote Database]
4- from Local Server trying to insert data into remote using TSQL
ex select *
into [remote server].[remote database].[dbo].[table name]
from [local server].[local database].[dbo].[table name]
is not working.... Also after I tried to add addlinkserver is still not working

any thought?

my work around for right now is create ODBC connection and use MS ACCESS QUERY via Linked tables..but it's very slow...


Thanks,
Mansi

[esedirect]

Possibly depends on the version of your remote SQL server. For example, if it's SQL Server Express, which is enough to use as a db on a web server, it won't accept remote connections.

[SWillis]

If this is a one-time job and you have access to both servers via SSMS, then use 'Generate Scripts.'

GenerateScripts1.jpg

You can choose to script everything or just select objects:
GenerateScripts2.jpg

Choose your save location (I usually script it to a new query window) and click on ADVANCED:
GenerateScripts3.jpg

Select your options here...in this example see that you can script just the schema or the schema and all the data. The script will also create all the indexes, keys, etc if you select those options.
GenerateScripts4.jpg

When the scripting is complete, paste it into a new query window on the target server and run it. Make sure you change the "USE [databasename]" if necessary to make sure you are scripting to the correct instance.

Happy SQL coding!

[esedirect]

I think "Types of data to script" only exists in SQL Studio 2008 onwards. Just in case anyone is looking for it in 2005.

[SWillis]

I think "Types of data to script" only exists in SQL Studio 2008 onwards. Just in case anyone is looking for it in 2005.

Thanks for pointing that out...it's been awhile since I used SSMS 2005.

BTW, this Generate Scripts task is a great "poor man's" backup method if your SQL Server is on a host that restricts your backup privileges and/or your access to the SQL Server backup location...which is usually the case on shared SQL providers (assuming you can connect to your desired db instance with SSMS).

*