Post reply

Backup a table using SQL Code and Linked Servers

  • January 23, 2009 at 2:50 pm

    #205728

    With SQL Server 2000, with is a quick and simple way to backup a sql table using SQL Code. Say, I have a prodserver with TableA and I want to make a copy of that table on the testserver. I was thinking somethin like:

    SELECT * INTO TableA

    FROM TableB

    However, I need to specify that the tables are on different servers. Also, how may this be done with an Insert Statement? I guess I would have to add and configure a linked server first. If you have some straigth forward code please share.

    Thanks in advance, Kevin

  • January 26, 2009 at 5:15 am

    #932529

    Hi,

    There are lost of ways to do this.

    This will depend on table size/usage etc.

    Initially you could try..

    Set up a linked server on your Test Server B looking at Production Server A

    On your Test server

    SELECT *

    INTO TableB

    FROM ProductionA.DataBaseA.dbo.TableA

    WHERE 1 = 0

    This creates the table

    Then

    INSERT INTO TableB

    SELECT *

    FROM ProductionA.DataBaseA.dbo.TableA

    This populates the table.

    This way you are not adding anything to your production server plus you are only selecting from the production server so you can be tight on permissions.

    Any good ?

    Graeme

  • January 26, 2009 at 3:02 pm

    #932840

    Thanks for the reply. I also had a related question that I mistakenly ask under the Administration Forum:

    We have SQL Server 2000. I would like to run a query on ServerB (Test) that reads a table on ServerA (Prod) and loads the data into a table on ServerB. The table is about 8 Million Rows (600 byte records). I would like to know if this query (I guess it is a distributed query) would affect performance on ServerA (Prod)? Where would the processing (creating/loading a temp table) be performed (ServerA or ServerB)? I would like to run the following queries on ServerB (Test):

    --Create Table on ServerB (Test Server)

    SELECT * INTO ServerB.TestDb.dbo.Employee

    FROM ServerA.ProdDb.dbo.Employee

    WHERE 1 = 0

    --Populate Table on ServerB (Test Server) from ServerA (Prod Server)

    INSERT INTO ServerB.TestDb.dbo.Employee

    SELECT *

    FROM ServerA.ProdDb.dbo.Employee

    Thanks in advance, Kevin

Viewing 3 posts - 1 through 2 (of 2 total)

You must be logged in to reply to this topic. Login to reply