Hello-
I have a Sql Server 2000 database offsite that I would like to back up
to a local machine. I am using Enterprise Manager on a local machine to
administer the remote db.
Whats the best way to schedule backups so the remote db is backed up to
the local machine?
Can this be done in Enterprise Manager?
Would you recommend any 3rd party software?
Thanks!
MB
Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?
View 3 Replies View RelatedI am trying to add a linked server from a AMD x64 server (Windows 2003) with SQL Server 2005 64 bit to a Server running SQL 2000. These are not in the same domain.
I can create a linked server using the option "Be made using the login's current security context" but can not when trying to specify the security context, i.e. sa and the sa password. When I try I get the following message:
Msg 15185, Level 16, State 1, Procedure sp_addlinkedsrvlogin, Line 98
There is no remote user 'sa' mapped to local user '(null)' from the remote server 'DTS_FSERVER'.
I have several other x64 server that I have no problem creating a linked server and specifying sa and the sa password.
The problem with using "the login's current security context" option is that I get an error when trying to run any Jobs against the linked server. The job fails withe the following error:
Executed as user: NT AUTHORITYSYSTEM. Access to the remote server is denied because no login-mapping exists. [SQLSTATE 42000] (Error 7416). The step failed.
I'm sure the two errors are related. Any ideas what is going on?
I have a SQL Server 2005 instance which resides on a dedicated server hosted by a third party. I am able to connect to this server through my local SQL Management Studio, however, my local ASP.NET 2.0 web application cannot connect. I get the error below. The connection string I use is formatted as...
Connection String:
Server=thesever;uid=myuserid;pwd=mypassword;database=mydatabase
Error:
Failed to connect to database: An error has occurred while establishing a connection to the server. When connecting to SQL Server 2005, this failure may be caused by the fact that under the default settings SQL Server does not allow remote connections. (provider: Named Pipes Provider, error: 40 - Could not open a connection to SQL Server)
The hosting providr claims that the SQL Server configuration settings are as they should be as fars as TCP/IP and Named Pipes are concerned. Does anyone know what might be going on?
I know this is strickly not a website question, but dunno where else to post...To remotely admin and monitor some functions of the website, I wish to use a local application to connect to the MSSQL DB which is held on the remote webhosting serverI have the following code: Dim StrSQLUN As String = "[UN]"
Dim StrSQLPW As String = "[PW]"
Dim StrServer As String = "[IP][INSTANCE]"
Dim StrDB As String = "[DB]"
Dim strTimeOut As String = "Connection Timeout=0;"
Dim pStrSQLConn As String = "Server=" & StrServer & ";Database=" & StrDB & ";User Id=" & StrSQLUN & ";Password=" & StrSQLPW & ";" & strTimeOut
Dim sqlConn As New SqlClient.SqlConnection(pStrSQLConn)
If sqlConn.State = ConnectionState.Closed Then sqlConn.Open()
This has basically been take from the existing code on the website, but changing to the server details. I had just started dev'ing this app when the admins decided to move the SQL server over to a different server. It was working on the old one, but the new one doesn't. It just times out after whatever time you put in the timeout variable. 0=unlimited, and so just sits there.I am also using the MSSQL Server Management Studio locally to connect to the same database, and although slow, does connect after about a minute or so. I thought they would be using the same type of underlying connection to access the server and database? Is this correct?
Can they put restrictions in place for this specific sort of data access?Does anyone have any suggestions on how to resolve this issue??Thanks for any helpAdam.
Hi all, I have moved hosting companies and am trying to get my website and MSSQL database sorted on the new server. I am using an enterprise type tool and can connect to the database the new hosting company has created on their SQL server with no problem. I now want to upload the data I had in the database previously. All I have to do this from is .BAK and .TRN files on my local hard drive. Using the restore option from the menu, I tried to select the .BAK file I have saved to restore from it, and got the following error:
"EXECUTE permission denied on object 'xp_availablemedia', database 'master', owner 'dbo'."
I also tried the advice given in this post as the problem seems similar and follows the same pattern: http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=40841
but there are loads of users listed for sp_who - it seems to be the users for all the databases on the host's server so I didn't follow the next step to disconnect them!
Any advice as to what I need to do would be much appreciated.
Cheers
Lisa
What is the usual way to develop ASP.NET websites locally using a database and then migrate everything to the real webserver?
Here's what I'm doing now -- tell me what I should be doing instead (I'm a newbie developer).
I'm using Visual Studio 2005 Standard with a SQL Server 2005 database, to develop a small C# ASP.NET website. I'm the only developer.
Hello,
i've developed a website using VB.NET, and SQL SERVER 2K, in my dev environment works properly (SQL Server and WEB SERVER are in the same machine).
When i put everything in the target machine, where the webserver is but not the sql, i get the following error:
Sql SErver does not exist or access denied
I've tried so many ways to do this, ODBC, ADO.NET, OLEDB.....and everything ends in the same error.
Also if i try to connect with ODBC (from odbc manager) it seems to work (test connection succesfull), but in asp.net nothing works.
I've tried to access the server with:
INSTANCE_NAME
SERVER_NAMEINSTANCE_NAME
IP,PORT
I think (they are checking this), that the auth mode is set to MIXED MODE.
Any idea to save my life?? ;)
Thanks ppl
I have created a linked server (to an AS/400) via MSDASQL. It works fine when I execute queries locally (i.e. from Management Studio running on the SQL 2005 server).
However, when I execute a query from a remote machine I get an error (see below for the exact message).
I know the ODBC connection is between the SQL server and the AS/400, and that the remote client does not have connectivity to the AS/400. But shouldn't the SQL server be able to pass the query through to the linked server even if the query is initiated from a remote machine?
Does anyone have any suggestions on how I can resolve this problem so that queries against the linked server can be executed from remote machines?
Errors:
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[Microsoft][ODBC Driver Manager] Driver's SQLSetConnectAttr failed".
OLE DB provider "MSDASQL" for linked server "MERCURY" returned message "[IBM][iSeries Access ODBC Driver]Key value in connection string too long.".
Msg 7303, Level 16, State 1, Line 1
Cannot initialize the data source object of OLE DB provider "MSDASQL" for linked server "MERCURY".
Environment: SQL 2005, Standard Edition SP2
Thanks in advance for any suggestions.
David Rueter
drueter@assyst.com
Hi
I have an sqlserver setup locally to chew thru pricefiles from our suppliers. The pricefiles comes in various formats mostly txt-files and are inserted via bulk-inserts and separeted by GO-statements, in the end I end up with a table containing unique partnumbers, the average price for the part, and number in stock. What i would like is to have a transact- statement in the end of all thoose sql-statements that copies the final table to a remote sqlserver that contains the DB used on my website. How do i connect to a remote server thru a sql-statement? is it possible?
If i just get the connection part explained, i can figure out the rest by myself.
Thanks
/Jonas
Hi and thanks in advance for your help.
I have a dilemma and I need some expert help with this. I am trying to make a copy of a remote Database and then replicated the DB and it's contents on my local machine so I can build a test site to run data with. I tried to remot into the server machine but I guess that feature is not allowed by the honest of the remote machine. Since I build on my local machine and would like to be able to test data on my local machine.... I would like to copy the remote DB to my local computer and then I can run my test app on my local server. The version of SQL on the remote machine is SQL Express 2005 and I have SQL 2005 on my machine. Please give me how to accomplish this please.
Dollarjunkie
Is there any way to backup a remote SQL Server that is on a hosted account to a local drive? We have a web hosted account that has a SQL Server that we do not have full admin rights on, just dbo access to the data and structure. We would like to be able to do backups and restores to our local development server if possible. Perhaps vis DTS or some similar means? WE cannot use the normal backup/restore as we do not have right to these fucntions nor can we access the servers local drives directly.
View 2 Replies View RelatedHi all
I am about updating some fields with local date and time using blw API
Quote
Private Declare Sub GetLocalTime Lib "kernel32" (lpSystemTime As SYSTEMTIME)
Public Function LocalDateTime()
Dim MyTime As SYSTEMTIME
GetLocalTime MyTime
Debug.Print "The Local Date is:" & MyTime.wMonth & "-" & MyTime.wDay & "-" & MyTime.wYear
Debug.Print "The Local Time is:" & MyTime.wHour & ":" & MyTime.wMinute & ":" & MyTime.wSecond
End Function
Unquote
This works fine however due to it's not really easy for an administrator to keep checking every times wheter local date and time are correctly updated for each front end users computer i would that to reconsider my function and making it to read local date and time from remote SQL Server. my concern is to help administrator to save time as he should only need to ensure that Server is running with correct time...
thkx for any advise
I have a SQL Server 2014 installation on a server (CHRIS-PCCHRISSQL).I have SQL Server 2014 management studio installed on local server called Pootle.I have gone through the configuration on server (CHRIS-PCCHRISSQL) inc the following:
(1) I have set up a user on CHRIS-PCCHRISSQL called sqladminuser at server level with the Server Roles of 'Public' and 'SysAdmin'
(2) The computers are both on the same homegroup.
(3) On Chris-PC , I have opened up the firewall port 1433 as Inbound Rule
(4) On Chris-PC, Within SQL Server Configuration Manager,the 'SQL Server Network Configuration for Protocols for CHRISSQL' have been set up as follows:
- The TCP protocol is enabled
- I have set up IP2
as follows:
Active: Yes
Enabled: No
IP Addres: 192.168.0.3
However when I try to connect from SQL Server Management Studio 2014 on my local machine Pootle to Chris-PCCHRISSQL using SQL Server Authentication with the user sqladminuser
A network-related or instance-specific error occurred while establishing a connection to SQL Server. The server was not found or was not accessible. Verify that the instance name is correct and that SQL Server is configured to allow remote connections. (provider: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)how to set up a remote connection from SQL Server Management Studio
on my local machine Pootle to CHRIS-PCCHRISSQL
We currently have a PPTP connection set up for our developers toaccess our development SQL server through a VPN tunnel. When theyneed to copy tables up to the dev SQL from their local machine theysimply do a DTS copy.However, we are now moving to a thin client solution where they willbe working on a terminal server. They will have access to thedevelopment SQL servers and SQL tools such as EM and QA. However,they will not have access to their local SQL server and, therefore,will not be able to directly perform DTS copies. We have exploredseveral possibilities such as exporting tables to a .csv or .mdb fileand then importing them on the development SQL server but this is notideal because things are lost in that process (e.g. primary keys,field names, data types, etc.)My question is this: Is there a way to export and then import SQLtables without losing dependent objects such as primary keys and datatypes in the process? If any of you are working with a similarsituation I would really like to hear how your remote users copyobjects from their remote location to your SQL servers. Thanks!Ryan--Posted using the http://www.dbforumz.com interface, at author's requestArticles individually checked for conformance to usenet standardsTopic URL: http://www.dbforumz.com/General-Dis...pict211310.htmlVisit Topic URL to contact author (reg. req'd). Report abuse: http://www.dbforumz.com/eform.php?p=722630
View 4 Replies View Relatedhello all,
I am using vb.net in windows form. I have made a module which is connected to sql server 2000.
Now, I want to have a fresh copy of the remote database of SQL Server at my local computer and whenever there is change in remote database(insertions,deletion or updation), my local database copy may gets synchronized(i.e changes get reflected in this local copy).
Can any one help me? If it is possibel by using Vb.net code, it is very well, and if there is some Sql Server wizard that can help me do that, please reply to me.
Thanks in advance.
Hello,
I have searched every post and tried every connection string there but can't figure out how to connect to my database on my remote server. I am using Visual Developer 2005 Express Edition in C# and uploading it to a Windows server running asp.net 2.0. The domain has not resolved yet.
I am so new to this and have followed many tutorials step by step but none of them address this issue. They just show how to upload it to the server.
Do I need to use the SQL server provided by my host or can the database stay in the App_Data folder?
My local connection string works locally:
<add name="ConnectionString" connectionString="Data Source=.SQLEXPRESS;AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
When I uploaded to my server I changed SQLEXPRESS to (local) as advised in the forum.
<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
When I debug a page I get the <customErrors mode="Off"/>
error message even thought I have already set it in my remote web.config file which may be causing problems itself:
<configuration>
<appSettings/>
<connectionStrings>
<add name="ConnectionString" connectionString="Data Source=(local);AttachDbFilename=|DataDirectory|add_newSQL.mdf;Integrated Security=True;User Instance=True"
providerName="System.Data.SqlClient" />
</connectionStrings>
<system.web>
<customErrors mode="Off" />
<compilation debug="false" />
<authentication mode="Windows" />
</system.web>
</configuration>
Thanks for any help you can offer. I wish I could find someone to hire to do this part for me or teach me how over the phone. Any suggestions?
Hi, I have been creating a web store, which we will be hosting on our Windows Server 2003 system. I am also writing a windows application that will be running on our client systems on the same network/domain. I have no need to allow internet access to the database, just within our domain. Pretty much the windows application will be running and whenever a customer places an order, the program will automatically pop-up the needed information for our credit card processing, and shipping department. So far I have been able to establish a connection to our server by creating a data connection under the server explorer. However, it does not list any databases whatsoever, the web site has been published using Visual Studio 2005 Professional on the server, and is in 100% working order. I have searched and haven't found anything concrete on this, i'm sure its just something simple that im missing. It appears that the server is definetely connected to the database (since the website is fully functional), but it is not listing the information for other systems to access it. If anyone has any ideas, please let me know. Thank you
View 8 Replies View RelatedIs it possible for retriving data from a remote Sql server database and storing it in a local sqlserver database.
View 1 Replies View RelatedHi
I amtrying to register remote database server with my local machine Enterprise Manager. It says server doesnot exist or not available.
I am putting server name,Tcp/Ip, i.p,port in Sql client configuration utility
and then trying to register it with Enterprise Manager.
Pl help me out.
Thanks
Shaurav
My connection string (to a remote instance of SQL Server Exress 2005) is exactly the same when the web files are located on my local machine or my remote machine, and while it works fine when I use the local web files, I get the following error when I use the remote files:
"Named Pipes Provider: Could not open a connection to SQL Server [53]"
As you can see, since it works from the local version, I've setup SQL to accept remote connections via TCP/IP and Named Pipes. I'm using IIS7 on the local, and IIS6 on the remote, in case that makes a difference. I have other sites setup the same way, which work fine.
Any thoughts?
Hi Listers
I am using sql7 on both servers
i would like to run a stored proc(which has a distributed query) as a job from my local server on a remote server.This proc checks for info on the local server zzdb and remote server xxdb for updated fields.
This info is then inserted into yydb.The stored proc is defined on the yydb.
I have set up the linked server and login.The services is using the local account.The master and target server will not serve my purposes as this job is defined on the local machine and needs to be run from the local machine.
this job fails? any help will be appreciated
TIA
I try to copy database from remote SQL Server(6.5) to our local
SQL Server(7.0). I try this way: First, in Enterprice Manager,
I try "NEW SQL SERVER REGISTRATION" using "Register SQL
Server wizard". The remote server give me the IP, login name
and password. then, I try connect option by "login using
SQL Server". But I got message say:"specialted server not found,
Connection open,create file". some times say:"client server access
denied..." some times say:"timeout". What's going on here?
So, I can not get going on with "Import data wizard".
Does anyone know how to solve this problem? Any answer would be of great assistance!
Thanks
Hi,
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Thanks in advance
regards,
M.Y. Yap
We are attempting to restore one of our databases from a backup that went to a local drive on the server.
We see the backupset in the list but receive an error that it is not available when we try to use it. When we try to restore from device and select the files the drive letter is not available. When we attempt to enter the path to the file it can't locate it. We don't have space on our SAN to copy the backup there and we can't add the local drive to the cluster resources.
Is there a way to get this backup done?
I have been unable to use DMO to back up a DB anywhere but locally on the PC running the DMO code. Even if a network drive is mapped (eg. F:), the backup fails.
I'm trying to automate a backup process for server(s) that will run on a PC and store the backup file on the server that contains the DB. Has anyone done this? Any hints are appreciated.
Hi,everyone!
I`d encountered a problem about backup DB to remote server.
At first,I add a dump device to remote server in local server,
for example:
sp_addumpdevice `disk`,`netback`,`RemoteSvrsharedfolderetback.dat`
Return success!
But when I begin to backup,return the message"...device error or device off line..."
the detail in error log said:"RemoteSvrsharedfolderetbackup.dat failed to open ,operating system error=5(access is denied)"
Why?
Could you help me?
Thanks you very much!
steven.z/98.8.29
Hi, I am new on sql and very happy finding your forum!
Ive just installed mssql 2005 on my pc and I need your help on backing up a database that is on a remote server.
I ve connected to the remote server using the Management Studio. Im accessing the database and Im getting the backup, but unfortunately I see that the back up is being saved on the remote server.
How can I backup the database saving it on my machine?
Thanks in advance,
Martha
Hello Friends:
My problem relates to backing up my MS SQL 2005 database which is sitting on a shared server at a hosting company.
OVERVIEW:
- Hosting company is using MS SQL 2005
- I am using the SQL Server Management Studio that comes with SQL Server 2005 Standard (NOT Express), which is installed on MY PC.
- So, I am connecting to the SQL server over the internet
WHAT I WANT TO ACHIEVE:
- I would like to backup the data sitting on the Hosting company's MS SQL Server. I only have one database on this SQL Server. There are of course 100s of other databases on the same server which belong to other customers of the hosting company.
- I want to bring the backup to MY PC, from the SQL Server.
- As far as I can tell the following options within SQL Server Management Studio may be of help to me. I do not know which one I should use or which one is best or what is the proper method. 1) Select Backup option from the Tasks menu (but it only shows me drives/devices on the Hosting SQL Server, not my PC, so I can’t backup to my PC) 2) Export Data (it does not work, showing errors ‘…not a trusted connection…’ I have no clue what a trusted connection is. 3) Copy Database (which is supposed to copy the remote database on the Hosting company SQL server, to my local SQL Server running on my PC). I go through the wizard, on the last screen it just hangs i.e. shows- not responding)
MY QUESTION TO THE COMMUNITY:
How do I backup the database sitting on the hosting company SQL server? I of course need to bring the backup to my PC.
Hi
I want to take backup of rapport database.
and copy those .bak files from this local server to remote server automatically.
can any one help me plzzzzzzz.........
Thanks in advance
Madhu.......
Hi,
I have a database on a remote server, and want to take a back up of that to my local system. My objective is to take the back up of the db, and then restore it to another SQL Server. Can anyone please help me with this.
Thanks
Vivek
I would like to backup a database on a remote SQL 2005 server using T-SQL. The local server I want to issue the command from is also a SQL 2005 Server.
Do I need to use the openquery function?
I am doing this as a job step so I will be executing the query from a local server.
I do not want to use a SSIS package.
Thanks
I've a SQL server 2014 running on one of our server. We're in the process of implementing security steps for our databases. I've encrypted a column in one of the table in the database on the server. The issue is when I restore the backup on my local SQL server and run a query to decrypt the column data it gives me null values. On the other end when I decrypt the column data on the main server it works fine. I found a thread on this forum which states to do the following when restoring the encrypted database on different server.
USE [master];
GO
OPEN MASTER KEY DECRYPTION BY PASSWORD = 'StrongPassword';
ALTER MASTER KEY ADD ENCRYPTION BY SERVICE MASTER KEY;
GO
select File_Name
, CONVERT(nvarchar,DECRYPTBYKEY(File_Name))
from [test].[dbo].[Orders_Customer]
I tried doing above still no luck.