Microsoft Sql Server - How to replicate a table from Linked Server?Permission Error when creating Linked...
Wild Shape Centaur Into a Giant Elk: do their Charges stack?
How did the USSR manage to innovate in an environment characterized by government censorship and high bureaucracy?
Imbalanced dataset binary classification
What is the meaning of "of trouble" in the following sentence?
Is "plugging out" electronic devices an American expression?
Finding files for which a command fails
Mapping arrows in commutative diagrams
What is the command to reset a PC without deleting any files
Are objects structures and/or vice versa?
How to move the player while also allowing forces to affect it
Domain expired, GoDaddy holds it and is asking more money
Sort in WP_Query(), not filter? Is it possible?
Patience, young "Padovan"
Is every set a filtered colimit of finite sets?
How do you conduct xenoanthropology after first contact?
What is GPS' 19 year rollover and does it present a cybersecurity issue?
What to wear for invited talk in Canada
Crop image to path created in TikZ?
How many letters suffice to construct words with no repetition?
extract characters between two commas?
Weird behaviour when using querySelector
Does it makes sense to buy a cycle to learn riding?
Eliminate empty elements from a list with a specific pattern
Why do UK politicians seemingly ignore opinion polls on Brexit?
Microsoft Sql Server - How to replicate a table from Linked Server?
Permission Error when creating Linked Server from SQL Server to Access DBSQL Server Linked Server with Microsoft OLE DB Provider for Visual FoxPro?Extracting data from SQL Server linked serverTest connection for linked server fails on desktop but not on serverOLAP linked server fails connection testCopy whole database from SQL Server to SQL Server using linked serverSQL Server Linked Table in Access DBcreating server with Microsoft Server Management StudioReplicate data of some tables of a local sql server to a database in AzureOracle linked server shows all tables from all schemas in sql server management studio
.everyoneloves__top-leaderboard:empty,.everyoneloves__mid-leaderboard:empty,.everyoneloves__bot-mid-leaderboard:empty{ margin-bottom:0;
}
I have an Origin Database - #1 with very restrictive access due to security.
I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.
I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.
How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?
sql-server replication linked-server
asked 28 mins ago
Chim KanChim Kan
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
|
show 1 more comment
I have an Origin Database - #1 with very restrictive access due to security.
I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.
I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.
How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?
sql-server replication linked-server
asked 28 mins ago
Chim KanChim Kan
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something likeSelect * into tbl1 From Database1.server.dbo.tbl1
– Thomas D.
20 mins ago
I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
– Chim Kan
18 mins ago
@ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
– Chim Kan
16 mins ago
1
I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
– Thomas D.
14 mins ago
@ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
– Chim Kan
12 mins ago
|
show 1 more comment
I have an Origin Database - #1 with very restrictive access due to security.
I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.
I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.
How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?
sql-server replication linked-server
asked 28 mins ago
Chim KanChim Kan
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
I have an Origin Database - #1 with very restrictive access due to security.
I provisioned a new Windows desktop machine with Microsoft Sql Server Management where I created a local database with all privileges, so I can create a Linked Server to database #1.
I have access to the Database #1 via Linked Server but I need to replicate some tables in realtime; I tried using the Replication option in the Object Explorer, but it does not allow me to do anything with the Linked Server.
How can I replicate some of those tables via Linked Server? So, I can copy those tables and updates and store them in local database?
sql-server replication linked-server
sql-server replication linked-server
asked 28 mins ago
Chim KanChim Kan
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 28 mins ago
Chim KanChim Kan
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 28 mins ago
Chim KanChim Kan
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
asked 28 mins ago
Chim KanChim Kan
1061
asked 28 mins ago
Chim KanChim Kan
1061
1061
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
New contributor
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
Chim Kan is a new contributor to this site. Take care in asking for clarification, commenting, and answering.
Check out our Code of Conduct.
1
Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something likeSelect * into tbl1 From Database1.server.dbo.tbl1
– Thomas D.
20 mins ago
I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
– Chim Kan
18 mins ago
@ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
– Chim Kan
16 mins ago
1
I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
– Thomas D.
14 mins ago
@ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
– Chim Kan
12 mins ago
|
show 1 more comment
1
Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something likeSelect * into tbl1 From Database1.server.dbo.tbl1
– Thomas D.
20 mins ago
I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
– Chim Kan
18 mins ago
@ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
– Chim Kan
16 mins ago
1
I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
– Thomas D.
14 mins ago
@ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
– Chim Kan
12 mins ago
1
1
Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something like
Select * into tbl1 From Database1.server.dbo.tbl1– Thomas D.
20 mins ago
Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something like
Select * into tbl1 From Database1.server.dbo.tbl1– Thomas D.
20 mins ago
I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
– Chim Kan
18 mins ago
I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
– Chim Kan
18 mins ago
@ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
– Chim Kan
16 mins ago
@ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
– Chim Kan
16 mins ago
1
1
I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
– Thomas D.
14 mins ago
I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
– Thomas D.
14 mins ago
@ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
– Chim Kan
12 mins ago
@ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
– Chim Kan
12 mins ago
|
show 1 more comment
0
active
oldest
votes
Your Answer
StackExchange.ready(function() {
var channelOptions = {
tags: "".split(" "),
id: "182"
};
initTagRenderer("".split(" "), "".split(" "), channelOptions);
StackExchange.using("externalEditor", function() {
// Have to fire editor after snippets, if snippets enabled
if (StackExchange.settings.snippets.snippetsEnabled) {
StackExchange.using("snippets", function() {
createEditor();
});
}
else {
createEditor();
}
});
function createEditor() {
StackExchange.prepareEditor({
heartbeatType: 'answer',
autoActivateHeartbeat: false,
convertImagesToLinks: false,
noModals: true,
showLowRepImageUploadWarning: true,
reputationToPostImages: null,
bindNavPrevention: true,
postfix: "",
imageUploader: {
brandingHtml: "Powered by u003ca class="icon-imgur-white" href="https://imgur.com/"u003eu003c/au003e",
contentPolicyHtml: "User contributions licensed under u003ca href="https://creativecommons.org/licenses/by-sa/3.0/"u003ecc by-sa 3.0 with attribution requiredu003c/au003e u003ca href="https://stackoverflow.com/legal/content-policy"u003e(content policy)u003c/au003e",
allowUrls: true
},
onDemand: true,
discardSelector: ".discard-answer"
,immediatelyShowMarkdownHelp:true
});
}
});
Chim Kan is a new contributor. Be nice, and check out our Code of Conduct.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234231%2fmicrosoft-sql-server-how-to-replicate-a-table-from-linked-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
0
active
oldest
votes
0
active
oldest
votes
active
oldest
votes
active
oldest
votes
Chim Kan is a new contributor. Be nice, and check out our Code of Conduct.
Chim Kan is a new contributor. Be nice, and check out our Code of Conduct.
Chim Kan is a new contributor. Be nice, and check out our Code of Conduct.
Chim Kan is a new contributor. Be nice, and check out our Code of Conduct.
Thanks for contributing an answer to Database Administrators Stack Exchange!
- Please be sure to answer the question. Provide details and share your research!
But avoid …
- Asking for help, clarification, or responding to other answers.
- Making statements based on opinion; back them up with references or personal experience.
To learn more, see our tips on writing great answers.
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
StackExchange.ready(
function () {
StackExchange.openid.initPostLogin('.new-post-login', 'https%3a%2f%2fdba.stackexchange.com%2fquestions%2f234231%2fmicrosoft-sql-server-how-to-replicate-a-table-from-linked-server%23new-answer', 'question_page');
}
);
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Sign up or log in
StackExchange.ready(function () {
StackExchange.helpers.onClickDraftSave('#login-link');
});
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Sign up using Google
Sign up using Facebook
Sign up using Email and Password
Post as a guest
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
Required, but never shown
1
Are you looking to create a 'recent enough' backup to do testing on or to have a constantly updating backup that mirrors the production db? If you just want to pull what is currently up there, you could just drop the tables locally and run something like
Select * into tbl1 From Database1.server.dbo.tbl1– Thomas D.
20 mins ago
I think recent enough is good. But I'm not using as a backup, I'm using the replication database as read-only for other apps to access the data. There's some other restrictions to the database #1 so that's why it's cumbersome.
– Chim Kan
18 mins ago
@ThomasD. just saw your suggestion. Well, it cannot be select * into. My concept of recent enough is like every 15 minutes to 1 hour. So if we keep dropping and creating a new table into the database, I'm not sure if that's going to fit the requirements.
– Chim Kan
16 mins ago
1
I apologize, when I said "backup" I just meant "local copy of a table." if you're just looking for a one-off query to run so that the other apps have somewhat up-to-date info, then my first suggestion will work, if you're looking to automate this and have it run every x hours or something, then it becomes a lot more involved. Is there any reason that you couldn't create a user on Database1 with very low permissions such that they are effectively read-only? It seems that creating another database will not be the optimal solution.
– Thomas D.
14 mins ago
@ThomasD. there are concerns about Database #1 which is behind a VPN that may or may not connect due to constant security changes on that VPN channel. So the idea is to make a "copy of a copy", as badly it sounds unfortunately, so in case the VPN connection drops, we are still able to have the "latest version".
– Chim Kan
12 mins ago