Moving Just Data Between A Production Server & Test Server
Feb 28, 2000
We have both a production SQL 7 server, QA, and Development. From time to time, I want to move just the data from the production server to the other 2 servers without modifing the objects that may have been changed such as stored procedures and rights. Is there a way using the SQL tools provided that we can just move the data. Becuase also what happens is that the rights to the objects change which means my developers no longer have access to the tables for selects in QA since the changes where overwritten by production where they do not have the rights.
Is there any tool available to migrate the data from the SQL Server test database to SQL Server production database. Data Migration should be based on a condition which can be given as an input for a table by the user. The dependant tables also should be migrated based on the given condition. i.e data subsetting based on the matching conditions.
Ex : Salary > 2000
The rows of the table which matches the condition alone need to be migrated for the corresponding table. Also its dependant table's rows should be migrated based on the given condition. Please help me with a tool which can automate this.
Howdy; I've tried this in the 'tools' area, but that didn't work too well. I suspect, I will have to generate a T-SQL code then schedule it as a job. Why I can't just drag and drop with basic desires, is beyond me, but THAT probably does exist.
anyway here is the problem [this server has many databases, on SQL 2000 sp2] 1. User only wants me to use Monday morning's full backup, which is good in that it doesn't include transaction logs. 2. Restore that data overtop/into Developement db. = good, no data to worry about damaging. 3. User does NOT want me to do this by hand, but schedule it.
ok, a. must do a RESTORE WITH FILELISTONLY from [?] what ?, master? and if I user the *.bak of the production, it has a coded date field in the name entry SO, I would, I guess, have to generate all sorts of wonderful code to find the date and build a file name. Why, because using the FROM DISK = 'F:MSSQLBACKUPDBPRODUCTION_yyyyddmm.BAK' is not going to work with a wild card. Can I do a file lookup using a 'PRODUCTION' prefix into a variable, then use that or should I look for latest file date [remember there are several database backups here], or ????
then. How does one schedule such a T-SQL. Do I save it to some text file, and invoke it using a job scheduler.
Is there any tool available to migrate the data from the SQL Server test database to SQL Server production database. Data Migration should be based on a condition which can be given as an input for a table by the user. The dependant tables also should be migrated based on the condition. i.e data subsetting based on the matching conditions.
Ex : Salary > 2000
The rows of the table which matches the condition alone need to be migrated for the corresponding table. Also its dependant table's rows should be migrated based on the given condition. Please help me with a tool which can automate this.
ok ok, stop laughing. for real, is there any programatic way of doing this? whom ever created this database i inherited (SQL 2000) created the LDF and DATA files on the same drive and in the same folder for that matter. just trying to do a little disaster magament.
There is a production database which has ever increasing data. For testing purposes though, I would like to build a test database with exactly the same schema but only a subset of data copied from the production database . I'll specify the criteria (something like a where clause in select query) for copying the data from the production database.
Is there a tool that anyone has come across to do this job ?
I am debugging one of our programs and ran the fix in Test. I would liketo compare table 1 between Production and Test. I want the query to outputcolumn 1 if Production <> Test output.What is the best way to achieve this?jeff--Message posted via http://www.sqlmonster.com
We are setting up a new Reporting Services 2005 enterprise reporting tier that will support multiple developers, applications, and end users. We will have mirrored environments including development, test, and production each with their own database cluster, and reporting server.
We have multiple report developers who share a single Visual Studio solution which is saved in SourceSafe and is setup to have separate report projects for each business unit in the orgainzation. Each report project is mapped to a specific deployment folder matching the business unit. Using the Visual Studio Configuration Manager, we can simply flip to the envirnoment we want to deploy to and the reports are published to the correct environment and folder structure.
My problem lies with the common data sources. We are using a single master Common Data Sources folder to hold all of the data sources. The trick is that each and every reporting folder seems to have to have it's own copy of the data source in visual studio. There does not seem to be an easy way to change the data sources for the reports when you publish to various environment, i.e. development, test, production etc.
Ideally, we would have a single project for the common data sources that all reporting projects and associated folders would map to, and we would have a way to associate the appropriate data source for each environment when we deploy.
I'm looling for best practices on how to setup data sources for development and deployment in an enterprise environment that uses Visual Studio to develop and publish reports. We have 3 environments, and 6 data sources per environment and about 20 reporting folder / project in Visual Studio. That's 360 changes that have to be manged when deploying reports. Is there a best practices way to do this?
There has got to be a better way? Can anyone give me some insite into how to set this up?
Production and development servers are on different domains and they do not trust each other. How do I import data from the table t1 from a database db1 in production and load it into table t1 inside database db1 in development?
Hi all, I have a asp .net 1.1 application running on the intranet which uses SQL Server 2000. The application is in production and everytime I want to do some changes, i do the changes on my development machine then I copy the application dll on the server. The problem is that I'm using Stored Procedures for all my Select, Insert and Delete statements. These stored procedures are live on the server so I can't do the modifications locally and test them then copy to the server.
How can I do modifications without affecting the production server and the users ??? thanks.
Sould one has a seperated environment for production and test system? How do you do it on a same server? Install two instance? How do you seperate test DBs from the production DBs? Please advise...Thank you
I have just finished upsizing an Access database to SQL Server 2k. Now the SQL Server need to be run on a test basis to determine if i need to make more changes to the front-end (Access). The problem I am facing is how to keep the two databases in sync while I am testing. Any suggestions?
Also any suggestion or comments on how to run a test setup like this (in parrallel) are also welcome since this is my first time attempting a project like this.
We're using SQL Server 2000 as back end in our web project. The problem is we've 3 different copies of same database - one each for Development, Test and Production sitting in 2 different machines.
My question is - is there any tool for comparing the objects (tables, stored procedures, etc) ?
I use SQL Server 2005 Dev Edition and am not new to making databases (then again, I've had enough experience and my dad does the same thing).
I am (unfortunately) a university student and for my dissertation I am going to produce a SQL Server database with a strong emphasis on data mining.
Obviously, for the data mining to be useful at all I need to produce loads and loads of test data.
Fair enough, and there are applications which do this, such as EMS Data Gen, but can anyone recommend me any other data gen utilities? EMS Data Gen has poor handling of unique attributes, and as I am doing a car manufacturer this will give me problems when I come to the registration number attribute.
Also, why are utilities for SQL Server (and Oracle at that) so expensive? This makes it out of my reach and makes it difficult to build a truly good database that will net me good marks, and demotivates me. :(
Lastly, please feel free to recommend to me any utilities for SQL Server - such as performance monitors, backup utilities. Anything. But if they are priced utilities, they have to be sensibly priced (<£100), because I cannot yet afford to pay >£1k on such utiltiies.
We will be implementing our first SQL cluster in December. Our current plan calls for a shared development/test database server with one physical server, but two SQL Server instances. Our production environment will be a SQL cluster. Is it necessary to create a clustered test environment for testing patches, hot-fixes, etc...?
How do I change application code to easily switch between the application working against a test database versus working with a production database?
My thought is to change the connection string to work with a test DB, and when ready to Publish, change the connection string back to the production DB. After Publish is successful, change the connection string back to the test DB.
At first, it appears it will work. Will it? Whether it will or won't?
Hello I have a production database that i need to refresh to our test environment daily. The database size is 700 MB. I do not need to transfer the stored procedures and triggers , users and logins. Would a DTS package that runs every night be the best and the easiest solution to implement or should i look into log shipping and snapshot replication.
I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot do it the the way i had done it in previous versions (and now i understand why because of Logical names).
The Test db runs in the same instance as Prod db.
I attempted to run this but come up with errors. This is what i executed:
RESTORE DATABASE TEST FROM DISK = 'E:<path>FULL.BAK' WITH REPLACE, RECOVERY, MOVE 'PROD' TO 'E:<path>TEST.MDF';
The errors are all cannot execute due to PROD is in use.
Setting up Transaction Replication in test environment. I am willing to bet that most of you take a production backup (if so, how, and using what?), restoring the database to your test environment, then running a snapshot to your subscriber and away you go.
But perhaps you take a backup of your publisher and subscriber, if so, how do you know there are no inconsistences because there were transactions sitting on the distributor?
What do you do if you have additional indexes on the subscriber for reporting, that are not on the publisher?
Here at work we are having issues with getting consistent databases set up with T Rep, missing rows, duplicate keys at subscriber etc. How to avoid these issues.
I've been tasked to generate some test data (a few thousand rows) into a new table in a new database. This database is a whole new idea, so I can't write a query to pull pieces of data from other databases. I cannot consider any third party tools, such as what Redgate or Idera has to offer. I can't consider free tools such as what I've found on GitHub. I've been instructed to restrict myself to Visual Studio 2013 and whatever I can get that works within that.
I built a SSIS(writing out to a flat file ) in 32 bit machine and it woks fine . But however when I deploy to the produciton server(64 bit) the SSIS writes out garbage data . After some research I found out that the problem with the 32 bit OS and 64 bit OS problem.What is my next step. Am I out of luck that now I will have to redesing the SSIS in 64 bit?
I have to install MDS on a production server without testing on test server (there is none test/dev server) On the production server each day are rendering SSRS reports which cannot be interrupted.
What risk is by installing MDS on a production server, (the SSRS, SSIS and engine may not go down,well can for some hours) SQL2012Enterprise.
What do I have to do first, steps taken, to install as save as possible for the current running BI environment?
I'm building a proc to generate fake stock portfolios for testing. I have a list of thousands of symbols, and I want the tester to be able to select how many symbols they want in their fake portfolio, and then give each symbol a random weighting (i.e. percentage held in that security) which, across all the symbols, sums to 100%. The securities here are not the part I care about, it's the weightings summing to 100 that's important.
So test data would look something like this:
/* --This is the repository of potential symbols I can add to a fake portfolio. -- So the simple part is basically select top (@symbolCt) from #PossibleSymbols, plus some magic I have yet to determine if object_id('tempdb.dbo.#PossibleSymbols') is not null drop table #PossibleSymbols create table #PossibleSymbols ( SymbolID int ) insert into #PossibleSymbols (SymbolID)
I've built my SQL Server Express database with SQL Serevr Management Studio Express, and now I want to enter some seed data to assist in building tha app around it. I cannot find an option to manage the data in SQL SMSX, like I used to with Enterprise Manager. I don't want to have to write an app just to get test data in. Seems like this should be a common need. Am I missing something obvious here? Can't find any reference to this in a search of the forums. Please help.
Our current sql server 7 is overloaded and I have been asked to move some data to a new box which is to be acquired. My query is as follows:
1what should be the ideal spec of a sql server to allow for windows 2000 in future. And how do I go about moving data from one to the other. I am new to sql and this is my first assignment.
I have a server that will be going off-line in a couple of weeks, and in the meantime, I need to backup everything and move it to a new location. This involves a lot of data and databases.
What I have is 33 databases in MS SQL Server 2005. The target machine is MS SQL Server 2008. Each of these databases has a myriad of stored procedures and compiled functions that will need to be moved as well. Essentially the task is to completely replicate the entire MS SQL environment.
Is there a shortcut to making this happen or am I relegated to backing up each database individually and then restoring them individually?
Access 2k -> SQL Server 2kMy client has an app that is A2k FE with A2k BE. They have asked meto move the BE to SQL Server.I have a bit of experience with SQL Server, and I'm happy withscripting the database etc.However, when it comes time to move the data itself, I have a teensylittle concern.Let's take our typical Customers -> Orders relation, where CustomerIDis the Foreign Key in the Orders table.Let's say I have 4 customers, but I used to have 6. The CustomerID isan AutoNum column, and they are 1, 2, 4, 6. However, when I insertthese records into the matching SQL Server table with an Identitycolumn, they will (presumably) be 1, 2, 3, 4.So what can I do about the matching orders? Is there any alternativeto this, which seeems very long winded.1. Insert the records as above, but copy the OLD Access AutoNumcolumn (called OldCustomerID) into a temporary column in the new SQLServer table for both the Customers and Orders tables.2. Insert the Orders records into the new SQL Server table.3. Run an update query thus:UPDATE OSET CustomerID = C.CustomerIDFROM Orders OINNER JOIN Customers C ON O.OldCustomerID = C.OldCustomerIDCan I do this any easier? For example, is there any way in SQL Serverto maintain the values of the AutoNum field in the Insert but havethem still be Identity fields? I know that I can import the AutoNumdata into a simple int column, and once the import is complete, turnON the identity, but is this reliable? Might I lose any dataintegrity?TIAEdward--The reading group's reading group:http://www.bookgroup.org.uk
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.
I have a closed polygon that coincidently is in the shape of Iowa :) I have a point that is within the state and a point WELL outside it, but I get weird results that I don't expect when I try to get it to tell me that the point is within the polygon. Here is some basic code, with long coordinates data.
(1 row(s) affected)As I read that there is a distance of about 7864 meters, this is close to what I would expect, so that's ok. The point outside I would expect a distance as well so that is confusing.. Then we have the intersects, it says that the point inside does NOT intersect but the one outside DOES, this is backed up by the intersection values.
