Backing Online Database Table Data To Local Machine
Jul 23, 2005
I have an online SQL Server database provided by an ISP. I do not have
permission to create a backup device and I understand this is normal
practice.
I am not using Enterprise Manager to administer the online database.
I know I can back up the structure of the database using SQL
scripts.
My question is:
How do I back up on my own machine the data contained in the online
database tables I have created? If I were using Enterprise Manager I
could do it by downloading tables using the DTS facility but how can I
do it without Enterprise Manager?
Is there some work around which I have missed eg creating a csv file
of the data?
Best wishes for 2005 to all those helpful people in this newsgroup!
John Morgan
Is there anyway to backup the current online log after complete loss of the current correponding datafile?
Example:
(2) Logical Disk Volumes
Disk 1 (D:) contains pubs_data.mdf Disk 2 (E:) contains pubs_log.ldf
Disk 1 becomes corrupt and goes offline leaving the the database pubs in a suspect state. Is backup of the current online log pubs_log.ldf possible? If a backup of the log is not possible are there any other restoration methods that can be used to bring this database back online, rescuing as much information as possible from the current online log pubs_log.ldf.
Hello All Just wondered if someone could help me with a bit of T-SQL, i have a application in ASP.NET/VB that allows the user to update a message board by clicking a button "update" this in turn triggers my Stored Procedure for inserting this data into a table, which works great. It inserts the data into its respective fields and also takes The Title, Line 1, Line 2 and so on and creates a XML file (Using FOR XML) which is stored in the same Table under a column call XML_Data. Which again works great. My problem now is how do i output this XML_Data to an actual XML file that is on my local machine, i.e. It be created in say C:Inetpubwwwrootxmlfiles("xml file name inserted here from another column that holds xml file name").xml Any help on this would be greatfully apreciated Thanks In Advance Neil
I have database on localhost and i want to show this data on my website. I want to create a database online and want to sync with Local Host. Can it be possible syncing data automatically after some interval?
I have Enterprise Manager on my local machine. For the last twelvemonths it has been connecting without problem to my online SQL Serverdatabase provided by my ISP.Three weeks ago the ISP applied some sort of extra securityarrangements to their SQL Server to allow access only through port1433. they have told me to configure an alias using Network Client andto register this alias in the usual way using my Enterprise Manager.My problem is that despite creating the alias in exactly the wayinstructed I just cannot connect to online database. I keep gettingthe SQL Server does not exist or access denied when I try to connect.As a result of advice obtained I have tried using the lite version ofthe 'littleADmin' tool available fromhttp://www.mylittletools.net/scripts/en/mla_sql/Using this useful looking software I find I cannot connect to theonline SQL database when it is installed on my own local machine butif I install it on my website it does connect without problem to myonline SQL. Server databaseHowever I need to use my more powerful Enterprise Manager to connect,my question (at last!) is:1. Is there likely to be a problem trying to connect to my onlinedatabase from Enterprise Manager on my local machine as a result ofthe new security arrangements. In other words, is there somethinganalagous to what is happening with the 'littleAdmin tool' wherebyusing Enterprise Manager I cannot connect from my local machine forsome reason obvious to everyone except myself??or2. Is it possible that there is some problem arising from Net Client?(the aliases appear in Enterprise Manager OK)I know my ISP should be answering this sort of question but they justdon't seem to know. I need to connect even if only to download mydatabase and take it to another ISP,Best wishes, John Morgan
I have my first small SQl Server 2005 database developed on my localserver and I have also its equivalent as an online database.I wish to update the local database (using and asp.net interface) andthen to upload the data (at least the amended data, but given thesmall size all data should be no trouble) to the online database.I think replication is the straight answer but I have no experience ofthis and I am wondering what else I might use which might be lesscomplicated. One solution is DTS (using SQL 2000 terms) but i am notsure if I can set this up (1) to overwrite existing tables and (2) notto seemingly remove identity attributes from fields set as identities.I know there are other possibilities but I would be glad of advice asto the likely best method for a small database updated perhaps onceweekly or at less frequent intervals,Best wishes, John Morgan
First of all let me explain that I am a complete newbie to this SQL stuff. I am in the process of reading a book, and followed the books suggestions for what I want to do, but it doesn't work. I know this has been covered several times in this forum, but no one post in particular covered exactly what I want to do.
I want to copy the database from the server to my local machine.
First, do I need to have SQL running on my machine in order to copy the database? I do have Enterprise Manager running, does that mean SQL Server is running locally?
Secoond, I tried using the Copy Wizard, but when I get to the target list, my local machine is not listed. How do I get it listed so I can copy the database?
I hope my questions are not too stupid... but then I'd be stupid if I didn't ask questions.
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.
I downloaded the SR2 - major mistake Now I cannot get into the sample databases I have on my machine. I can attach to the Servers, but they have SR1 and now I guess if I would make changes from my SR2, then they will be screwed up.
My error when I try to log into my machine (adventure works) is:
TITLE: Connect to Server ------------------------------
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) (Microsoft SQL Server, Error: 2)
For help, click: http://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&EvtSrc=MSSQLServer&EvtID=2&LinkId=20476
------------------------------ BUTTONS:
OK ------------------------------
Is there any way to reverse SR2? Should I reinstall SQL 2005?
I am trying to use a new Excel microsoft add-in using SQL server 2005. I installed the 180 day trial version of SQL Server 2005 on my local machine and according to the instructions it was suppose to be very easy to connect the excel add in to SQL. I am receiving an error message which I cannot find a resolution to using the readme file and wonder if you can help.
ERROR MESSAGE Unable to connect to server 'localhost'. Please make sure user 'ARTIMUS' has at least read permission to some database on the server.
I have done a bit of searching around and cant find a clear answer to this question.
Current Setup Desktop application (c#) that connects to a SQL Server 2005 express database on the same local network as the application (currently 3 users)
It is only a very small company and has just taken on their first remote worker, but expects to take on another 6-8 over the next few months. They have asked for the database to be moved online.
The application was written in such a way that everything has been done using no stored procs, or views, it is all native SQL.
This will be my first DB hosted online and before I go ahead and do anything I just wanted to make sure what I have to do is correct, sorry if this is a very basic question, although I have been programming for a long time, I have never had the chance to do any online databases before.
Will this work. 1.Find a SQL Server 2005 Hosting company. 2.Move the database to the server. 3.Setup the users permissions. 3.Alter the connection string in the application to point to the new location.
So the only thing that would change would be a new connection string in the application preferences?
Or am I living in a dream world, because nothing is ever that simple.
One thing I am worried about is the security/visiblity of the database and data as it travels from the server to the client and back.
I am on an XP Pro 32-bit laptop, running SQL Server 2005 Developer Edition. I want to import an Excel spreadsheet into one of my databases using (formerly known as) DTS. The Excel spreadsheet was created in 2003 (I have Office 2007 installed on this laptop). Whenever I try and do any OleDb import, I get the error:
TITLE: SQL Server Import and Export Wizard ------------------------------ The operation could not be completed. ------------------------------ ADDITIONAL INFORMATION: The 'Microsoft.Jet.OLEDB.4.0' provider is not registered on the local machine. (System.Data) ------------------------------ BUTTONS: OK ------------------------------
I cannot install/reinstall MDAC because I have the latest version. What else can I try?
As additional information, I also get the error when trying to import anything into or export anything out of Access 2007. I'm pretty sure MDAC is awful, but I still need OleDb providers for much of the work I am doing.
i want to take a backup a database which is on a different machine to my machine.Iam trying to give unc naming conventions i.e backup to //servername/sharename/file.bak but its not working.iam getting an error saying device error or device offline-access denied.
Anyone could tell me how to do it step by step plz.
I have a SQL db with a hosting company that appears to have gone bust, so am moving my site from them to a new provider. I am able to access the database on the SQL server but I need to transfer all the data and objects to my desktop PC. I have tried scripting all data/objects and DTS but these just generate errors and fail (I suspect due to permissions).
I did this once before and got a .bak file from the providers, which I was successfully able to restore elsewhere, so this is what I need to do again. However, now I'm getting no response to support requests so need to do this myself. The question, then is....
Is there a way to backup a SQL db to a .bak file on the server and then somehow transfer it to my own machine? Or is it possible to somehow run the backup to a different machine and get it off their server?
Any help would be appreciated, as I'm worried about how long I'll continue to have access to the server before they all disappear without a trace!
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 am trying to modify my database maintenance plan so I can perform backups to another machine (using a UNC path).
I have only just found out from the documentation that I "cannot" do this: I am sure there must be a way around it but haven't had any luck on the web. Can anyone help?
I've been working with SQL Server for a few years now and I've had quite a few cases where users cannot backup SQL to a network location or external hard drive. Why can SQL not do this? Is it some limitation of the speeds at which SQL needs to backup the information and the external hard drives/network drives do not meet the requirements?
i wanted to test routines using my local sql server engine and thought I could backup the mdf ldf file on the true network sql server in which I am listed as a dbowner with full permissions. In fact i can add users for the at db etc.The backup allows me to browse to a local folder
with the following being the default C:Program FilesMicrosoft SQL ServerMSSQL10_50.CMS_PROJECTMSSQLBackup
i name the file and it executes properly but when i try to restore that file to my local db that path is not even visible to me.In fact when i use exploreer I cannot even find the folders after Microsoft SQL Server!I search for .bak but nothing.How can I see that folder construction in the sql browser but never in explorer -
When you load the data into a new partition table, can it to done online without any downtime? because I have few tables that are around 250 gigs and more.
Hi,I wonder if anyone can help with the following on a fee paying basisfor the the design or development of some type of script or utility orpiece of code to do the following work.I wish to retrieve some data from an online database that is in thepublic domain. The online database has a search facility that matchesthe entry (name) in a search box then returns a screen stating that amatch has been found or not found.If a match is found there is a button to click that proceeds to thescreen containing the data which is simply two names. It is these twonames that I wish to retrieve and store them in something like a textfile where they are associated with the original entry (name) used inthe search box.I have a list of the entries for the search box that can be suppliedin sample format for testing as a columnar text file or commadelimited file or a spreadsheet.. I would need the procedure toprocess the list of search entries, retrieve the data then move on tothe next one in the list. Obviously, if a match was not found then theprocess would need to move on the the next entry in the list withperhaps a message saying "No Match" until the entire list wasprocessed.The PC I hope to run the process on is a Windows XP machine and if Ineed to purchase any particular software that is necessary for the jobthen I am quite willing to do so. Also, as I pointed out above I wouldpay for the work to be done.I hope that I have explained the above OK and that I have posted it tothe correct newsgroup(s). If it is not in the correct newsgroup Iwould be grateful if anyone could point me in the right direction.RegardsDave Gibson
See above. At first I thought it was a problem with SQL server, since I have just installed a new certificate. Now I am not so sure. Any suggestions???? Thanks, kreid
I have SQL Server Express 2005. I have it configured for remote connections, but I cannot even see my local dbase. I have the server setup for windows authentication. In VB 2005 database connection wizard I see the db server but on the database itself.
When I click test connection....without a dbase choosen it says ( i don't have any to choose from).
"An error has occured while establishing a connection to the server. When connection 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"
This doesn't make sense because i have changed the the surface area configuration to allow remote connections on both TCP/IP and Named pipes. It shouldn't matter though because this is a local database.
I am getting the "Error Locating Server/Instance Specified" message. The part I find most confusing is that I am connecting to my database locally. I have read the recommendations on the blog posted here:
and implemented them to no avail. The confusing part is that I only have this problem on one particular application, which I am not the original author of. When I build my connection to the very same server (different database) using the Data Source configuration wizard, it works just fine. Any help would be greatly appreciated.
Hi SQL folks, I've googled for this the whole yesterday and I couldn't find a complete solution. I'm having a sql2005 database in my development machine and I need to copy all the content "Tables, Diagrams, PK columns and other data" to the remote production machine. Using the Import/Export method didn't copied the relationships between tables, also it turned the PKs into just a standard columns Can anyone help?
I have had a new PC and have installed SQL Server. However, I do not seem to have anywhere to create databases on my PC. I did on my old one. How can i do this ?
Sql server type: database engine server name: authentication: sql server authentication login: sa password: xxxxx
I remember the server name being sqlexpress (I think that was the default name during the install).
OR are they asking for my computer name? because I tried that also and it gave me an error saying "the default installation doesn't allow remote connections"
Hi, i wondered if anyone could help me with my stored procedure. It updates a table with users messageboard and saves the Title, Line 1, Line 2 and so on into a XML thats stored in the column named XML_Data.
How can i then output this xml to a file on my local machine?
T-SQL :-
ALTER PROCEDURE [dbo].[prUpdateMessageBoardVetted]
