June 21, 2005 at 3:50 am
Hey guys.
I am trying to find a way to be able to Update and old database or Install a new database on a client's system without haveing to delete the customer's data and part of the system's data.
Meaning if we want to change the current database structure or completly replace it by a new one...how can I find a way to save the data entered by the user so that I can reload it afterwards...
I was thinking of maybe converting the data into excel files and then reloading them afterwards...is there such an option in SQL enterprise? If so, how do we do this? Ive been playing around the the DTS, but with no luck at all....Do we have to first create an excel file with column ready or is there a way for it to automatically create such tables on an excel file?
My other option was to backup my old database, and restore it into a new database so as to save a copy of it...Then when the updates on the old database is over, is there a way to copy a certain part of the data stored in the new database back into the old database? And when I restore the database, all the tables seem to be restored but the actual data within the tables are missing...
HELP!!!
Take Care
Sarah
June 21, 2005 at 5:09 am
Hi Sarah
If you are just changing the structure of an existing database, you should be able to script the required changes without losing data (but this depends on the extent of the changes - eg if you are changing the primary key of a table that contains data already, you might need to do some additional work to avoid duplicate key errors).
If you are creating a 'new' database, why would you be moving customer data into it? This also sounds like a change to a database to me.
Definitely no need to go near Excel here - it's safer and more efficient to keep this all in SQL Server, once you know how.
If you can give an example of the sorts of changes you are trying to make, we can point you in the right direction as far as scripting those changes. Of course, before you run any scripts that change a database's structure, you would perform a backup of the db.
Regards
Phil
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
June 21, 2005 at 6:33 am
Hey Phil!
thanks so much for you timely reply!!!
Ok, so the two most prominent updates that would be done in the database would be
a) adding new fields to existing tables
and/or
b) adding a new stored procedure(s)
....
So basically right now when these updates occur, all the data is lost and we have to start from scrath... I have created a backup of our database, but I need a way that I can select just a certain number of tables or procedures to put back into the new database...Is there a fexible way to do this?
I figured out how to convert my views and procedures into script files ( extention .sql), there is also option to convert the tables into script files... Should I convert it to, or do I only need the views and procedures to be able to reload the data of the tables into the updated database?
Now when I try to reload the script into the SQL Query analyzer and try to execute it, the debugger shows loads of errors such as:
/******************************************************
Server: Msg 2714, Level 16, State 5, Procedure OBJECT NAMES, Line 4
There is already an object named 'OBJECT NAMES' in the database.
Server: Msg 213, Level 16, State 4, Procedure Ancestors_copy_Ver_1, Line 13
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 213, Level 16, State 4, Procedure Ancestors_copy_Ver_2, Line 13
Insert Error: Column name or number of supplied values does not match table definition.
Server: Msg 207, Level 16, State 3, Procedure GetParenrRevision, Line 9
Invalid column name 'VersionNumber'.
Server: Msg 207, Level 16, State 1, Procedure GetParenrRevision, Line 9
Invalid column name 'ObjectID'.
Server: Msg 7601, Level 16, State 2, Procedure MatchFilterCriteriaFacet, Line 11
Cannot use a CONTAINS or FREETEXT predicate on table 'tblFacetValues' because it is not full-text indexed.
**********************************************************/
And this is a sample of the script file I copy pasted into the SQL Query Analyzer.
/*********************************************************
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddContentObject]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddContentObject]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddFilter]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddFilter]
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[AddLogEntry]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[AddLogEntry]
GO
....
SET QUOTED_IDENTIFIER ON
GO
SET ANSI_NULLS ON
GO
CREATE PROCEDURE dbo.Version_Add_Checkout
(
@ObjectID int,
@NewVer decimal(9,3),
@FullName nvarchar(50),
@status int,
@CheckedOut bit,
@ContentCheckedOut bit
)
AS
....
**********************************************************/
Do u know anything that I am doing wrong?
TAKE CARE!
Sarah
June 21, 2005 at 7:18 am
OK, this isn't so bad. If you execute a CREATE TABLE script after a DROP, it will do exactly that - delete the table and its data and then recreate the table with the new structure, minus the data. Not what you are after.
To retain the data while adding new columns to a table, you will need to execute an ALTER TABLE command - check out BOL (Books Online) for details.
Stored procedures are somewhat easier - as they contain no data you can always execute a conditional DROP and then reCREATE it with the new definition. The error messages that you are seeing in QA (Query Analyser) are appearing because you are trying to create something that already exists. As an example, here's a script (auto generated by QA), that can be run in the PUBS database to DROP and reCREATE the byroyalty stored procedure
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[byroyalty]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)
drop procedure [dbo].[byroyalty]
GO
CREATE PROCEDURE byroyalty @percentage int
AS
select au_id from titleauthor
where titleauthor.royaltyper = @percentage
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER OFF
GO
SET ANSI_NULLS ON
GO
Hope this helps. Good luck.
The absence of evidence is not evidence of absence
- Martin Rees
The absence of consumable DDL, sample data and desired results is, however, evidence of the absence of my response
- Phil Parkin
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply