Geeks With Blogs
Justin Gardner .NET, T-SQL and Reporting Services Developer

Tonight I have to stay late at work to finish up some migrations and to handle a customer request that's a few days old.

This post is regarding the aging customer request. I need to create a reproduction of a report that was generated on Saturday. To do this, I've got to copy down some prod data to my local database and delete any data newer than Saturday. Next, I'll set my system time to 1/24/09, fire up Visual Studio, open my Reporting Services project and finally load the report in question.

With a few small tweaks to the SQL (changing getdate() to '01/24/09', for example), I'll be able to get a local snapshot of what the report would have looked like on Saturday.

The deliverable is an Excel version of the report that somehow was missing data (to be discussed in an upcoming blog). With my Back to the Future version of the report loaded, I'll simply export to Excel, set the file name as it would have been on the LAN, email the report to theĀ customer and create a request to have our server admins drop the modified version on top of the existing report.

Usually I'll set up my data using the SQL Server GUI to save a little time figuring out how to do this in straight up SQL. But, today I'm feeling adventurous so I did a few google searches.

The results: Set up a linked server since the production data is on a completely different server than the destination (localhost). I've never done this before but have an upcoming task that will throw me mercilessly into the fire of Linked Servers, so may as well get it figured out now.

After more searching, and a bit of trail and error, I've got the syntax down. Since I do not know the duration of the linked server (probably stays until deleted), and since I probably do not have permissions to create a linked server in Production anyway, I've opted to execute the following on my local machine.

EXEC sp_addlinkedserver
@server='server'
@srvproduct='product_name'
@provider='provider_name'
@datasrc='data_source'
@catalog='catalog'

Edit: somehow the above was clipped off after EXEC

Ok, so it seems pretty strait forward. Fill in the name of your particular server for the @server and @datasrc variables, and @catalog is set to your 'Initial Catalog', AKA the name of the database you're looking for. So, I'd modify the above as follows if my database was called Customers and lived on a server called PRODDB-01:

EXEC sp_addlinkedserver
@server='PRODDB-01',
@srvproduct='SQLServer',
@provider='SQLNCLI',
@datasrc='PRODDB-01',
@catalog='Customers'

Next, the query pattern. I now want to draw the data down to my local host. When calling the fictional Names table in the Customers database on the PRODDB-01 server, my syntax would be as follows (this is for a simple select *):

select * from PRODDB-01.Customers.<schema>.Names

Substitute the actual schema in use on your database where the <schema> tag is located.

And, to reference my local db, you'd do select * from <database>.<schema>.<table>, or:

select * from Customers.<schema>.Names

Note: If you are used to calling 'use <database>' so that you don't always have to include the database name in your queries, keep in mind that this will only work when referencing the local tables. It appears you have to keep the database name in there when referencing the linked server. So, with a database name of Customers, you could do the following for prod and local copies:

use Customers

select * from PRODDB-01.Customers.<schema>.NamesĀ  -- Production query

select * from <schema>.Names -- Local query

Now, to actually move the data, do your typical insert into / sub select. Something akin to:

INSERT INTO <schema>.Names (FirstName, LastName)
select FirstName, LastName from PRODDB-01.Customers.<schema>.Names

Now all first name and last name entries in the production database are imported into my local version. In real life, I'd be adding a where clause to limit the date range in order to only pull in the data I need for testing.

Your database may be set up so that you do not need to call the schema name. If that is the case you'll want to see if you can get away with excluding it from your queries to the production database (or any database not on the same server).

Now I'm going to finish working and get out of here.

px

Posted on Wednesday, January 28, 2009 6:41 PM SQL Server | Back to top


Comments on this post: SQL Server - Moving data across servers using a linked server

# re: SQL Server - Moving data across servers using a linked server
Requesting Gravatar...
Thanks for posting the (gory) details. Yuk.

What I did was go to the source server, add a database and populate it with copies of a table or two that I needed to move. Then I backed up the temp DB and deleted it.

I transferred the backup to my target server and restored it. Then I was able to do all my work in my target environment.

In my case, that was a test system, and I wanted to do some processing and comparison. It was nice to just have a copy of the production stuff.

If the source server DB gets backed up regularly, and if the backup is small enough and/or your link fast enough to just move the whole backup, then that is obviously a way to skin this cat with a couple less steps.
Left by Paul Rowe (paulrowe.biz) on May 28, 2009 12:59 PM

# re: SQL Server - Moving data across servers using a linked server
Requesting Gravatar...
Paul,

Thanks for stopping by and taking the time to comment. I'm glad you found an easier way.

Working in an environment where access is limited and red tape abounds creative engineering becomes a daily habit and, over time, second nature.

I'm sure this has resulted in my solving normal problems in my life with more complexity than is required.

Justin
Left by Justin on Jun 02, 2009 9:56 AM

Your comment:
 (will show your gravatar)


Copyright © Justin | Powered by: GeeksWithBlogs.net