Copy Local Sql 05 DB To Production Machine

Dec 13, 2007

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?

View 1 Replies


ADVERTISEMENT

Copy A Database From A Remote Server And Using It On My Local Machine

Sep 14, 2007

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

View 1 Replies View Related

How To Copy Tables From Local Machine To Remote SQL Server

Jul 23, 2005

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 Related

How To Copy Db From Test Server To Local Machine Running MSSQL2000 Per.

Jul 23, 2005

I am in a situation where I need to get a copy of test database that ison production server running MSSQL 2000 Standard to my local machinerunning MSSQL 2000 personel. I tried to use the copy wizard where itappears I get connected to the source server OK but when I try toindicate the destination server which is my local machine I get errorspoping up about cannot connect to (local) etc.I am NOT a DBA just a programmer trying to get a local test environmentup to be more productive.Lsumnler

View 2 Replies View Related

Help Needed - How Can I Set Up A Backup SQL Server Machine As An Exact Copy Of My Production SQL Server

Jul 20, 2005

Any help would be greatly appreciated.My problem is that I need to set up a backup SQL Server 2000 machinewhich can be used in case of a failure to my primary. All databases(30 as of now) must be an up to the minute exact copy of productionand include most recent changes in data as well as any structurechanges (Tables, Views, SP's, Triggers, Users . . etc).When I tried this using Transactional Replication, the replicationprocess gets fouled up once I introduce any kind of structure changesto the DB. I've considered the idea of doing periodic backups andrestoring it to my backup SQL server, but this does not give me theconcurrency needed with 0 latency.I've seen articles that recommend using Transaction Replication with'Scheduled Table Refresh', and also doing database dumps to restore onthe backup machine, but I have not been able to find any documentationregarding this to try out. How can I implement this type of backupstrategy in SQL 2000?

View 2 Replies View Related

Connection Pool Leak In Production Machine (in Other Machine It Runs Well)

Nov 16, 2006

Hi to everyone,
I'm facing a problem that drives me crazy. I've a web application that has the following problem. When I test the application on my developement machine all runs fine, but when I put into production server there is a problem in connection pooling. Look at the following image, the blue line is the number of connection in the pool and the violet line is the number of connection reclaimed. From the image it is clear that connection are returned on the pool only in block, maybe when the garbage collection pass and reclaim the object. The strange thing is that on my developement machine all is good, and also I'm using Enterprise Library and the connection are managed internally.- Thanks to everyone for any help.   

View 2 Replies View Related

The Remote Copy Of Database X Has Not Been Rolled Forward To A Point In Time That Is Encompassed In The Local Copy

May 11, 2006

Hi,

I set up DB mirror between a primary (SQL1) and a mirror (SQL2); no witness. I have a problem when I issue command:


alter database DBmirrorTest
Set Partner = N'TCP://SQL2.mycom.com:5022';
go


The error message is:

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy of the database log.

I have the steps below prior to the command. (Note that both servers' service accounts use the same domain account. The domain account I login to do db mirror setup is a member of the local admin group.)

1. backup database DBmirrorTest on SQL1

2. backup database log

3. copy db and log backup files to SQL2

4. restore db with norecovery

5. restore log with norecovery

6. create endpoints on both SQL1 and SQL2

CREATE ENDPOINT [Mirroring]

STATE=STARTED

AS TCP (LISTENER_PORT = 5022, LISTENER_IP = ALL)

FOR DATA_MIRRORING (ROLE = PARTNER)

7. enable mirror on mirror server SQL2

:connect SQL2

alter database DBmirrorTest

Set Partner = N'TCP://SQL1.mycom.com:5022';

go

8. Enable mirror on primary server SQL1

:connect SQL1

alter database DBmirrorTest

Set Partner = N'TCP://SQL2.mycom.com:5022';

go

This is where I got the error.

The remote copy of database "DBmirrorTest" has not been rolled forward to a point in time that is encompassed in the local copy



Thanks for any help,

KT

View 8 Replies View Related

Can Aspnetdb.mdf Be Moved From Development To Production Machine?

May 2, 2007

1.) Can an aspnetdb.mdf database be configured and setup on one server and then be moved to a production server or is there something machine specific that keeps this from being possible?  2.) Is putting this file in the app_data folder something that is used only for SQL 2005 or SQL express?  I had to set up a connection string in my SQL 2000 installation to get the connection to work.
Thanks for any input!
Colelaus

View 4 Replies View Related

Deployment - Cannot Acquire Connection In Production Machine

Nov 22, 2007



Hi
i can install and run my package in my development system work fine.

The same package when i was installing in the production system error

error: the acquire connection method call to the connection manager "con name" failed with error code
0xc0202009

I used sql server authentication and

tried the protection level
both encrypted password protected and sensitive with user key


Ezhil

View 4 Replies View Related

Packege Fails After Moving To Production Machine...

Oct 2, 2007

Hi everybody.
I have such problem:
After I move my integration services packege to another machine it failed. I tried to recreate connection manager, but result is the same. If I create a simple SQL task in that package and try to execute it - it fails with info:

SSIS package "Main_Package.dtsx" starting.

Error: 0xC00291EC at Execute SQL Task, Execute SQL Task: Failed to acquire connection "SRV-DSA3.TIA_EXTRACTS_OriginalData.sa". Connection may not be configured correctly or you may not have the right permissions on this connection.

Task failed: Execute SQL Task

SSIS package "Main_Package.dtsx" finished: Success.


But when I create it(SQL task) in the new package - it works perfectly. Maybe somebody could help solving this problem.

Thanks for any information.

View 4 Replies View Related

Copy Production Db To Another Server

Jan 31, 2007

I have a brand new database server with system databases.
I need to copy like four production database from another server to this new server. Can i do restore of the last production backups and restore them on the new server without creating the empty databases on the new server.If any one has better approach i will appreciate

View 3 Replies View Related

Cannot Login To Web App From Local Machine But Can Over RDP

Oct 29, 2007

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 

View 3 Replies View Related

Get Database To Local Machine

Jan 29, 2008

What is the fastest way to get a copy of a SQL server database on a server to my local Windows XP Professional system?

View 4 Replies View Related

Want To Change The Local Machine Name

Aug 28, 2000

Hi

I need to change the Local Machine Name. But if I do that, my SQL server will stop working. Is there any way to fix the SQL Server?

Thanks in advance

Manish

View 2 Replies View Related

Cannot Find DB On Local Machine In VB.Net

May 13, 2007





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.



Thanks



Kramer

View 4 Replies View Related

Error 26 On A Local Machine

Aug 13, 2007

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:


http://blogs.msdn.com/sql_protocols/archive/2007/05/13/sql-network-interfaces-error-26-error-locating-server-instance-specified.aspx

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.

View 4 Replies View Related

Copy Data From Staging Table To Production?

Apr 12, 2015

I am trying to insert data from staging table to production table. In the staging table I only have period or date but no primary key.

This is my staging table

Create stagingtable(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)

Create Production(
[Period] [char](7) NOT NULL,
[CompanyCode] [varchar](100) NOT NULL,
[total] [int] NULL,
[status] [varchar](50) NULL
)

I get this every month. What can I do to make sure only unique record are loaded into production table with no duplicate from previous month.

View 5 Replies View Related

How To Copy A Database From Production To Training Environment

Oct 21, 2015

Our DBA is out for about 6 weeks. One of his regular jobs is to take a Database that is in our Prod environment and copy it to our Training environment.

I have backed up and restored to different servers before, however the Database name in Prod is different than in the Train environment in this case.

In other words I have a PROD database named DATABASE-XX that I want to copy to another SQL server and restore it to DATABASE-XX-TRAIN.

Is there anything special I need to do, other than backup on PROD, copy to TRAIN and restore to the corresponding DB in TRAIN?

View 11 Replies View Related

Sql Server On Local Machine RESOLVED

Jun 15, 2007

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 ?

View 2 Replies View Related

Can't Connect To Sql2005 On My Local Machine

Nov 22, 2007

hello,

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"

View 6 Replies View Related

SQL Output A XML File To My Local Machine

Mar 17, 2008

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]

(PARAMS GO HERE)

UPDATE CustomerData
SET Title = @Title,

Line1 = @Line1,
Line2 = @Line2,

Line3 = @Line3,
Line4 = @Line4,

Line5 = @Line5,
Line6 = @Line6,

XmlFileName = @XmlFileName,
FlashFileName = @FlashFileName,

HtmlFileName = @HtmlFileName,
Vetted = @Vetted,

ClientName = @ClientName
WHERE

(UserRef = @original_UserRef)

END

BEGIN

IF @Vetted = 1

DECLARE @xmlData AS XML

SET @xmlData = (SELECT
UserRef,

Title,

Line1,

Line2,

Line3,

Line4,

Line5,

Line6

FROM dbo.CustomerData
WHERE (UserRef = @original_UserRef) FOR XML RAW('Message'), ELEMENTS)

SELECT @xmlData


UPDATE [dbo].[CustomerData]
SET

XML_Data = @xmlData
WHERE

(UserRef = @original_UserRef)



(THEN HERE DO SOME SORT PROCEDURE TO OUTPUT @xmlData to a XML FILE ON MY LOCAL MACHINE,

BUT ALSO NAME THE XML FILE VIA @XmlFileName BY DOING SOME SORT OF CONCANTENATION)

any ideas?

Thanks in advance !



Neil

View 1 Replies View Related

Can't Connect In Sql Express On Local Machine

Aug 7, 2007

I have SQL Express running on my laptop but are unable to connect to the default database paulm-laptopsqlexpress

When I try to connect I get the error message
TITLE: Microsoft SQL Server Management Studio Express
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: SQL Network Interfaces, error: 26 - Error Locating Server/Instance Specified) (Microsoft SQL Server, Error: -1)

Everything is on my laptop whcih is running Vista Business as an administrator

Any ideas why,
Thanks Paul


View 2 Replies View Related

Connecting To SQL Server (Local Machine)

Feb 7, 2007

Hi all,

Apologies if this is a dumb question, but I'm tearing my hair out over the basics when I should be spending time learning ASP.NET 2.0 and C#. I've searched the archives and a lot of people seem to be getting the same error as me, but when trying to connect from remote machines.

I'm getting what seems to be a standard message ...

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)

I'm not trying to connect remotely, I'm on my developer machine. The error comes up when trying to connect from within VS2005 (Tools, Connect to Database). It also comes up if I create a new web-site and go to the ASP.NET configuration tool (this is, I guess, trying to create the necessary database behind the scenes but is unable to connect to the database).

I suspect the error might be related to one or more of the following ...

September last year I installed VS2005 Express and SQL*SERVER Express. Both were fully un-installed when I bought VS2005 Professional before installing the new product.
When I set up SQL*SERVER 2005 Developer I remember choosing an option to have a separate user on my PC with administrative rights. Actually I don't recall much about what I chose but I can't find out where those permissions are managed from.
I've followed various instructions to check that remote access is enabled (despite the fact that I'm local, not remote). TCP and Named Pipes are both enabled (and I stopped then restarted SQL Server). I've checked that the service is started.
The odd thing is that I have had an application connect with the following string ...

SSLCon = new SqlConnection(@"Server=(local)SSLMJ;Integrated Security = True;" + "Database=SSLTESTRESULTS");


Though the application connection seems to work ok, I think I'm missing out lots of developer functionality because I can't get VS2005 to see the database or server.

Help ?

View 7 Replies View Related

Get Local Machine Name In MSDE Setup .bat File

Nov 5, 2003

I have the following command in a .bat file

osql -E -S localhost sp_grantlogin 'GREGASPNET'"

What I would like to do is replace GREG with the name of the local computer this .bat file is being run on.

Is there any string for this... i.e [LOCALMACHINE]ASPNET or something like that?

Greg

View 1 Replies View Related

How Deploying A Local Database To Target Machine?

May 9, 2005

how deploying a local database to target machine?

View 1 Replies View Related

Copying Database From Server To My Local Machine.

Feb 27, 2006

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.

Thanks in advance for your help...
David

View 2 Replies View Related

Connection Problems SQL2000 On Local Machine

Jun 19, 2006

This may be a stupid newbie question, but being that I'm not a DBA I'm going to ask it anyway.

We discovered a problem with a clients server (Windows 2003 SBS) when we tried to install Veritas Backup Exec. Backup Exec requires an instance of either SQLMSDE or full SQL. This server has full SQL 2000 sp3a installed on it with a working account application. When backup exec trys to create it's instance it fails.

Investigating further...when I go into Enterprise Manager (logged in as Domain Admin) I get an error when I select the local server.

I.E.
"A connection could not be established to (LOCAL).
Reason: Data source name not found and no default driver specified.
Please verify SQL Server is running and check your SQL Server registration properties. blah blah blah.

Okay the server is running. The accounting application (client/server) is still working. I right click on the server and select "New Server Registration" for the field "server" I click the browse button and the "Active Servers" box is empty..:shocked:

I went to check the "Data Sources" under drivers, that field is blank. (no drivers installed). I downloaded MS's component checker and it says "MDAC 2.8 on Windows server 2003 sp1" is installed.

At this point I have exhausted my knowledge of MS-SQL server. My goal is to get BackupExec installed and NOT break the existing accounting application.

Any help is greatly appreciated.

Thanks
rogerb

View 3 Replies View Related

ADO Connection String Not Working When SQL On Local Machine

Apr 3, 2007

Here is the connect tring from table properties:ODBC;DRIVER=SQL Server;SERVER=VICRAUCHSRVRVGSMISC;APP=Microsoft Data Access Components;WSID=VICRAUCH;DATABASE=vgs_prod;TABLE=d bo.USysCandidatesHere is the connect string from the ADO .Open connect string:"ODBC;DRIVER=SQL Server;SERVER=VICRAUCHSRVRVGSMISC;APP=Microsoft Data Access Components;UID=sa;PWD=xxxXX99X;WSID=VICRAUCH;DATAB ASE=vgs_prod"Here is the ADO .Open code. Set CNN = New ADODB.ConnectionDim strDEFConn As StringstrDEFConn = FixConnStr(DEFCONN)CNN.Open strDEFConn The last line fails with the CNN.Open strDEFConn with this message:Run-time error '-2147467259 (80004005)';[Microsoft][ODBC Driver Manager] Data source name not found and no default driver specified This code works when the SQL Server is on it's own server, but for testing at my own office, I have SQL Server on the same machine as the Access application. I'm getting the above error where SQL Server and the Access app are on the same machine. I can open a linked SQL table from the user interface, and VBA code that deals with the tables as Access tables works. It is the ADO .Open statement where the error happens. Thanks for any help you can give me on getting this to work.

View 5 Replies View Related

SQL 2012 :: How To Deploy Web Services On Local Machine

Apr 10, 2014

how to deploy web services on local machine. I created self certificate in IIS and then build the solution in the visual studio. After I build the solution, I found that automatically it created the virtual directories for the web services under the default website in the IIS Manager. When I click and select the browse it is not working.

View 9 Replies View Related

Local Storage In Client's Machine In .sdf File

Apr 2, 2008



How to create a db in clients local machine with sql server ce and .net?Is any sample applications available?so,that later i can sync the data to the sql server db.
Please help.I am new to sql server ce.

View 3 Replies View Related

Error When Loading SSIS To Local Machine

Mar 5, 2008

I was trying to load SSIS shipped from my colleague. It has configuration file with his SSIS. I added this one to my local project. I removed configuration file from my project. In case anything goes wrong, I also delete OLE DB Connection String, and recreated one. Modified all data flow and control flow for any connection to point to my new one.

After the modification, it still fails on login 'sa'. I cannot figure out the problem. Is there any tricky inside of SSIS configuration file?

View 12 Replies View Related

Database On Server But SQLEXPRESS On Local Machine

Jun 14, 2006

This is what I sometimes want:

I have installed on a localmachine sqlexpress. Also my application is installed on the local machine.

But for the night-backup of the database I want to put the database on the company server.

Let's say the database is on: X:datamydatabase.mdf

Why cant't I attach this database with the manager of sqlexpress



Second:

I have installed on local machine sqlexpress AND also the database

Why can't I backup the file to the server like:

BACKUP DATABASE [mydatabase] to DISK=x:datamydatabase.bak WITH FORMAT

It looks I can only backup to the localmachine.



thanks a lot

Klaas

View 5 Replies View Related

'OraOLEDB.Oracle.1' Is Not Registered On Local Machine

Jun 7, 2007

Hi all of you,

Primary platform is Framework 2.0 running over XP.



I've got an issue with a SSIS package which uses Oracle Provider for OleDB:



Test connection failed because of an error in initializing provider 'OraOLEDB.Oracle.1' is not registered on local machine



Nevertheless, MS Ole DB Provider for Oracle works fine. Let me know where am I failing.



Thanks in advance,

View 10 Replies View Related







Copyrights 2005-15 www.BigResource.com, All rights reserved