Hello faculties,
We've a production database that is being used by one of our clients
as a backend for his website. The database size is around 1GB.
Recently we added some tables to our local database which is a
replica of the production database. Now we need to apply the same
changes at the production one aslo.
I've no clue about what steps should i implement.
Please guide me!
Thanks in advance
Debian
*** Sent via Developersdex http://www.developersdex.com ***
Hello. I am not very strong with SQL server. But I know enough to get my way around. The reason I am asking forhelp is that I need to change a SQL server script that resides on one of the production database servers of thecompany I work for. I just need to add two new lines to the stored procedure.(See the lines that are bolded.) Thesevalues are [MO_FAX],[MO_EMAIL]. Can someone tell me if there is a best way of going about doing this? Can I just simply open up SQL server and quickly make the change? The big issue here is that this script is used in production. So I am just a little worriedabout screwing something up. Anyway, I would appreciate some good advice on this?
/* Returns all data given the region and country */ALTER PROCEDURE [dbo].[GetAllInfomration] AS SELECT [COUNTRY], [Company_NAME], [CompanyAddress], [CompanyPhone], [MO_FAX] [MO_EMAIL] [CompanyFax], FROM [InfoLookup].[dbo].[Company_Contact] RETURN
I am able to run the package successfuly in test database. but not in production database. It throughs up error saying
Description: Unable to load the package as XML because of package does not have a valid XML format. A specific XML parser error will be posted. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded c orrectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format. End Error Could not load package "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" because of error 0xC0011002. Description: Failed to open package file "D:\TAHOE\APPS\SSISPackages\Integration Services Packages\ArchiveMain.dtsx" due to error 0x80070015 "The device is not ready.". This happens when loading a package and the file cannot be opened or loaded corr ectly into the XML document. This can be the result of either providing an incorrect file name was specified when calling LoadPackage or the XML file was specified and has an incorrect format.
We often have to migrate changes to sql server 2000 databases fromdevelopment to production. Normally we dump the sql from Enterprise Managerfor production and development and do a diff (using CSDiff - downloadable forfree).From the diff information we create some scripts to add new tables or alterexisting tables as required. This is time consuming and error-prone.I am now trying the following:1. Backup up the Development database2. Delete the Development database3. Restore the Development database as Development_Backup3. Use the the sql script to create the new Development databasestructure from scratch4. Use Data Transformation Services to migrate the data fromDevelopment_BackupThe problem is that many of the 'sys' tables are empty. For example table'sysforeignkeys' is empty.Is there an easy way to rebuild the data in the 'sys' tables?Thanks
We are installing an application with MSSQL database at our users place.Now we have two instances of the same database:- developing database, on which we are working at- production database, which is at our user's place.That 2 databases would have to be syncronised.Users will of course enter some data into production database. How can wechange the production database do reflect changes we have made on thedeveloping database?D.--
I developed an asp.net application in visual web developer 2005 express edition and SQL sever 2005 express with Advanced services. The application has been deployed and iam wondering what tools are availabel to for backing up my data. Are there any tools i can use to back-up my database. Iam not talking of third party tools but tools a vailable in sql sever 2005 express with advanced services or visual web developer express. OR can write a vb.net Sub procedure that i run and have my database backed up. If so where can i start or what other options may i explorer.
Can anyone help me in this issue: How do we replicate database changes (like tables) from QA to production without losing Production data. We already tried using the DTS export but it is dropping the destination tables before export which will result in data loss in the destination database.
We have a database in production which has free space about 200 GB in Data files and Index files, I want to shrink Data files and Index files.If I do incremental shrink in daytime does it hurt the performance of the database or please advise what is the best practice.
I have a production database with a backup job that creates files with thenaming convention dbname_db_200503291800.bak. I want to schedule a restorejob that will retire yesterdays backup. How can I write my restore statementso that it will specify the backup file with yesterdays date.Thanks
we are having 2 prodction servers in our environment. and we are planning to perform database mirroring between them.i want to mirror server 1 databases to server2 and server 2 databases to server 1. is this possible??
if possible , can any one give me a link to document where i can find more info on this, or send me the steps what i need to done for this...
- I have one production database and one test database. - In the test database i have done a few changes on the structure - mostly adding new fields in the tables. - Now i want to apply the table changes to my production database. Does anyone have any suggestions on how i should do that?
I mean, of course i could just manually check every field and update the changes - but that could take a while - i guess there are some better ways this could be done, like: - backup data - delete tables - recreate tables using structure of test database - reinsert data
Is this the correct way? Are there any easy ways to do this?
The changes i made should not make problems regarding data that is already stored (like changing a string field to int)
I actually work in an organisation and we have to find a solution about the data consistancy in the database. our partners use to send details to the organisation and inserted directly in the database, so we want to create a new database as a buffer database to insert informations from the partners then make an update to the main database. is there a better solution instead of that?
Hi all, I have a site which used a SQL Express database. On my development machine, it works perfectly. As soon as I move it to the production machine, it doesn't work at all. I am pretty confused by what is going on and I really don't want to install Visual Studio on my production machine just to debug it. I am hoping someone might have an idea what could be happening. Oh, and I have tried setting Everyone to Full Control on the App_Data directory just to test. Still breaks. Below is my web.config connection string: <connectionStrings> <add name="database_sqlexpress" connectionString="Data Source=.SQLEXPRESS; AttachDbFilename=|DataDirectory|MyDatabase.mdf; integrated security=true; user instance=true"/></connectionStrings>
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 have been looking for some documentation that would support or rejectmy opinion on Production -vs- Development naming conventions. Ibelieve that each environment should be housed on separate servers withidentical names, access, users, stored procs....... If you eitheragree or disagree with this methodology, I would appreciate your input.TIA,Bill
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.
I am trying to refresh a test database with data from a production database. Both database structures are identical, e.g. constraints, stored procs, PK, etc. I am trying to create a package in SSIS that accomplishes this task and I am having extensive problems. The import export wizard is out of the question because the constaints are not carried over, plus when I try to refresh the data using the import export wizard, it fails on 1 specific table because of a column in that table named "Error code". I think "Error code" is a micrsoft keyword, so it fails on this column. Does anyone know a workaround that I can do to accomplish this simple task, that could be completed in minutes using DTS. I understand that SSIS is not as straight forward as DTS, but this task is something that DBA's do on a regular basis and therefore should not be this difficult.
I have question regarding SQL Transactional Replication methodology
1. Let's say successfully created SQL Transactional Replication and running / transferring data from publisher to subscriber
2. Now one day the source production / publisher SQL Server is down and the remaining DR SQL Server is up (subscriber)
3. Next day, we fixed and bring up the production / publisher SQL Transactional Replication server, then how can we sync back all existing data records from subscriber into publisher side ?
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?
I have a 20 GB SS2005 database that I would like to be replicated to a dev server for testing purposes. I might have some issues with the firewall blocking ports. I haven't read up on the specifics of how log shipping works yet, but I assume that is the best approach. A daily transfer would suffice.
A few options I am considering:
1. use the built in stuff, but I'm not sure it will work with our security settings, and if the trans logs are large it might be strained.
2. writing a small custom app to zip up the log file then ftp them down to the dev server. This could run nightly.
3. some third party util, such as FolderShare could transfer the log files
I have a windows forms application that I would like to put in production on several machines. Each machine will contain it's own data and does not need to be networked.
I can use my local connection right now to access the data, but I would like to change my connection so that it will work when installed on a client's machine.
My Current connection string looks like this:
Data Source=.SQLEXPRESS;AttachDbFilename="C:Documents and SettingsMYNAMEMy DocumentsVisual Studio 2005ProjectsInterimApplicationInterimApplicationInterim.mdf";Integrated Security=True;User Instance=True
I believe I could use something like this:
Dim connectionString as string = "Data Source=" & Environment.GetFolderPath(Environment.SpecialFolder.ProgramFiles) & "/InterimApplication/data/Interim.mdf;Integrated Security=True;User Instance=True"
However, I am not sure. Also, if the client does not install to the default directory, then this may cause the application to not work properly.
Lastly, I am not sure if the client machine needs any additional software on their computer in order for the application to be capable of connecting to the data source.
Any recommendations, ideas, comments, or general help is greatly appreciated.
I have searched hi and low for hours within the product and online - I cannot find how to have SQL 2005 produce a script to automatically repeat the creation of a database role I have tedious manually created.
I have tried "script object as, create to,<>" - single-liners are produced - no details. More single line results from "<Database>, All Tasks, Generate Scripts".
Am I missing something or is this a bug?
€¦also to note after review blogs and other sources the use of the SQL2005 methods related to security and new schema objects - I feel€¦ dreadfully wrong - what is claimed feels like hype, unsupported by examples and usage cases feels like we better just make all automated processes sysadmin; I know learning curves can be strong but I am not this green or that stupid - where are the DOCS/training materials?
System Configuration : OS : Windows 2003 latest SP SQL Server : Standard Edition, SP2 Microsoft SQL Server 2005 - 9.00.3042.00 (Intel X86) Feb 9 2007 22:47:07 Copyright (c) 1988-2005 Microsoft Corporation Standard Edition on Windows NT 5.2 (Build 3790: Service Pack 1)
DownTime : This is not a 24x7 kind of machine. It can have downtime
I have been discussing this Service Broker issues in this forum for quite sometimes and sorry to bother u all again€¦ To make things more clearer before implementing in production environment I have few doubts and it should be clarified..
As discussed in the first link we can clear sys.conversation_endpoints by just giving ALTER DATABASE GPx SET NEW_BROKER WITH ROLLBACK IMMEDIATE. But my apprehension is that if we run this command on production server and it truncate this table€¦ what will be the impact and overall overhead on the system€¦ Is it recommended to give this statement on Production Server daily at less traffic times, to clear this table ?€¦ will it have adverse effect. I repeat I have downtime , I can even shutdown this server daily€¦
I also, just want to know why Microsoft has not looked into this aspects€¦ why the system itself is clearing the expired messages.. What is the thought behind this architecture
I have the script to run in batch€¦ but in high-level meetings it is always difficult to convince this architecture/process€¦
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.
We have a production 2012 R2 SQL server that is not part of a domain. I have a database called CSSDC. I need to backup this database and make the BAK file available to a user on a different SQL server. Security is a concern. In order for this user to restore the database, they will have to be in the fixed server role sysadmins. I know I am going to have to use the Move with replace for the filegroups for the restore. Once the database is restored it will have to be altered to read-only. I would also like to remove the existing security and only have this new user access the read-only database.
I have package there i have multiple tasks,I have used one User definied varible into my package level,So here my condition is i want to change my variable name from the package level,Here that variable used in different places in my package level,it has used in some places as well in my package level.
I need to change my Variable name after development of my package so how it will changed.
I have to disable newly implemented database encryption. It's a necessity unfortunately. Can I do this during production hours without much of a hit? I know I have to restart the instance after it's done. Can I expect performance impacts or other issues?
All queries on our Production database are timing out. Viewing the error log file the following show up over and over again:
Autogrow of file 'tempdev' in database 'tempdb' was cancelled by user or timed out after 3937 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Autogrow of file 'Prod' in database 'Production' was cancelled by user or timed out after 33156 milliseconds. Use ALTER DATABASE to set a smaller FILEGROWTH value for this file or to explicitly set a new file size.
Our production database is about 1 gig in size with 3.5 million records. I tried setting the autogrow from 30% which it was before to 100MB, but no luck, still timing out and getting the errors above. Permission should be all good, nothing has changed.
There is about 50gigs of available disk space as well, so that's not the problem. Thanks for the help.
Can someone at Microsoft comment on this article, specifically, how it relates to SQL Replication? Will SQL Replication fall into the category of what this article describes, or only the mirroring feature?