Important Notice from AspDotNetStorefront
It is with dismay that we report that we have been forced, through the action of hackers, to shut off write-access to this forum. We are keen to leave the wealth of material available to you for research. We have opened a new forum from which our community of users can seek help, support and advice from us and from each other. To post a new question to our community, please visit: http://forums.vortx.com
Results 1 to 5 of 5

Thread: TSQL moving data from server to another

  1. 05-09-2012, 09:28 AM #1
    Mansi is offline Junior Member
    Join Date
    May 2012
    Posts
    7

    Default TSQL moving data from server to another

    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
    Last edited by Mansi; 05-10-2012 at 06:55 AM.
  2. 05-10-2012, 01:17 AM #2
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    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.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!
  3. 05-15-2012, 02:08 PM #3
    SWillis is offline Junior Member
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    9

    Default Moving data from one SQL serever to another

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

    Name: GenerateScripts1.jpg Views: 43 Size: 97.3 KB

    You can choose to script everything or just select objects:
    Name: GenerateScripts2.jpg Views: 43 Size: 95.6 KB

    Choose your save location (I usually script it to a new query window) and click on ADVANCED:
    Name: GenerateScripts3.jpg Views: 42 Size: 86.0 KB

    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.
    Name: GenerateScripts4.jpg Views: 42 Size: 112.3 KB

    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!
    Last edited by SWillis; 05-16-2012 at 09:18 AM.
  4. 05-16-2012, 01:05 AM #4
    esedirect is offline Senior Member
    Join Date
    Feb 2010
    Location
    Norfolk, UK
    Posts
    343

    Default

    I think "Types of data to script" only exists in SQL Studio 2008 onwards. Just in case anyone is looking for it in 2005.
    http://www.esedirect.co.uk
    --------------------------------------------------------------------------
    Using MS 9.2.0.0 with the following customisations:

    Lightbox/Fancybox enlarged images;
    Auto-suggest searchbox;
    Extra product information shown only to our IP Address (such as supplier info, costs, etc.);
    Failed transactions emailed via trigger;
    Custom app to show basket contents when customer online;
    Orders pushed through to accounting systems.

    All the above without source!
  5. 05-16-2012, 09:09 AM #5
    SWillis is offline Junior Member
    Join Date
    May 2012
    Location
    Charlotte, NC
    Posts
    9

    Default

    Quote Originally Posted by esedirect View Post
    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).

    *