Data Warehouse Backup/recover
Jun 9, 2004Hello, everyone:
Does any body have the experience to execute data warehouse backup/recover? What I want to know is how to backup/recover database in data warehouse and cubes.
Thanks.
ZYT
ADVERTISEMENT
Restore New Database And Recover Data From Backup
Jun 13, 2014I created a new database and want to recover data from a backup this script
use master
go
restore database new
from disk='D:
ew.bak'
go
but get an error
Message 3141, Level 16, State 0, Line 1
The database to be restored was named 'ats342'. Reissue the statement using the WITH REPLACE option to overwrite the 'new' database.
Message 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Database Backup And Recover
Jul 14, 2003Hi all:
In order to test database restore, I create a backup driver and puporse delete one table, then I try to restore my databse use backup, but the delete table donot show up.
Thank you
Recover The Backup Set With MSDB
Apr 13, 2007I am facing the Database recovery problems sicne I have lost my MSDB db , only i have the Complete db backup and transactional backup.
How do i can recover the database since i don't have the MSDB database
Recover Just One Table From Backup
Sep 6, 2007I work on sql server 2000.
Is it possble to recover just one table from the database backup ???
I am new to this.
Please let me know.
thanks
DB-Corruption: Backup, Restore And Recover
Oct 2, 2001Hi
I have a question concerning backup / restore / recover of a MS-SQL-Server-Database.
Following configuration exists:
MS-SQL-Server running on NT
OS, Server located on a mirrored Raid 0 Disk -> C:
Transactionlog-Files (.ldf) on the same physical mirrored Disk -> E:
Database-Files (.mdf) located on a separate pyhisical Disk (Disk-Set, Raid 5) -> D:
I take a full backup every night of all databases, no Transaction-Log backup is made during a day (never)
Assuming following situation:
During a day one Database became corrupt. The physical-file (.ldf) of corrupted database is still accessible
on Drive E:
1. Step: I make a copy of the .ldf-File and put it on a secure place or in an other directory
2. Step: I restore the DB from the last full backup
3. Step: ???? How is it now possible to recover from the physical available .ldf-File? I know it should be
possible but I don't now how.
Has anybody tips or suggestion or has someone ever had a similar situation? Feedback would be appreciated.
Kind regards
Gabi
How Do I Recover Database From A Detached Backup Device?
Nov 29, 2007I ran into a problem today where I had to pull a backup file from an older tape. The file was originally is a backup that was stored in a device like :
EXEC sp_addumpdevice
'disk', --type
'c2000_BackupDevice', --logical name
'\UNCPathackupFile.bak' --physical location
I put the file from the tape on a File Server and then tried to create a new backup device pointing to the file. That's easy enough, but when I try to restore from it I get an device error saying it's offline.
How can I get that device attached to my server and then extract the backup files stored inside it?
Please advise b/c this is an interesting problem I hadn't anticipated and it argues that I should change my backup strategy .
Thanks for any advice/experience you can provide,
alex8675
SQL Server Admin 2014 :: Change Data Capture(CDC) For Data Warehouse / Reporting?
Aug 12, 2015I have a requirement to implement CDC for 50+ tables to implement incremental data changes warehouse/reporting rather than exporting the whole table data. The largest table is having more than half a billion records.
The warehouse use a daily copy of OLTP db (daily DB refresh). How can I accomplish this. Is there a downside in implementing CDC just for the sake of taking incremental changes on the tables?
Is there any performance impact if we enable CDC on OLTP db?
Can we make use of the CDC tables on the environment we do daily db refresh so that the queries don't hit OLTP database?
What is the best way to implement CDC to take incremental changes for reporting.
Backup Failed In Loading Status. How To Restart Or Recover?
Jun 18, 2007I asked this question before but never received a decent answer. When a restore fails, what are the steps to re-start the restore? This should be a very common issue and for the life of me I have been unable to find it in BOL or the MS site! Please let me in on the secret!!
Thanks
Learning Data Warehouse/analysis Service/data Mining
May 25, 2006hi
I am new at MSSQL 2000 DBA thing. and trying to learn more about analysis service/data warehouse/data mining. so is any expert out there can Recommend some good books or web link article to read? Thanks
For New In SSIS. To Send Data From Operational D/base To Data Warehouse
Oct 6, 2006Hi Dear All!
I am Crystal Reports Developer and I am new in SSIS environment. I have started to read Professional SQL Server 2005 IS book. I am really confused by many tasks to choose.
I need to develop reports from data warehouse. But before I have to send the data from operational database (SQL Server 2000) to warehouse (SQL Server 2005) monthly - I have a script for retrieving the data. For my package, I chose Data Flow Task, Execute SQL Task, and OLE DB Destination, and it does not work.
Please help me if I can look similar packages performing?
Thank you!!
Data Warehouse Example?
Apr 8, 1999hi, Can anyone list the type of error that will make @@error =1
I created a procedure to update a table based on a cutomer id, Id 7 doest not exist in table A, and I am suppose to have Not valid id, but in this case nothing happen I always get table a updated
thanks
Ali
begin tran
update table a
set title =' manager '
where id =7
if (@error <> 0)
begin
rollback tran
print 'not valid id '
return
end
ELSE
begin
commit tran
print 'table A updated'
end
SQL 2012 :: Data Modeling Tool For A Data Warehouse?
Oct 19, 2015I need a recommendation on a data modeling tool that can be used with a data warehouse. My warehouse is running SQL 2012.
Here is my challenge: Most of the tables in the warehouse do not have primary keys and none of the tables have foreign keys on them. However, there are indexes and unique keys/indexes on the tables. I am looking for a tool that I can create virtual relationships on how the data is related, so it is visually easier for the ETL developers to write the code.
I have looked at both ER/Studio 11 and ERwin 9.6. Neither of them do it exactly the way I want it too. However, ER/Studio is pretty close.
Data Warehousing :: How To Represent Metadata In A Data Warehouse
Sep 24, 2015I am working on to create a data warehouse. I have made a database which will be the data warehouse and will consist of dimension and fact tables. I know that other than dimension and fact table a data warehouse should also consist of a meta data, now my question is what should be the structure of metadata and all the information it should have?
View 2 Replies View RelatedDatabase For Data Warehouse
Dec 22, 2004I am going to use Microsoft SQL Server to develop my data warehouse, but one thing makes me confused. Since Analysis Service can create a Star schema database, do I have to pre-set up a Star schema database for ETLed data? Basically, I am wondering what's the relationship between an ETLed database and the one created through the Analysis Services.
Can any one give me an explanation from the implementation perspective?
Many thanks!
Help With Restore Data Warehouse
Feb 27, 2008Hi all,
I am trying to restore my data warehouse from a January 2008 backup under a new name to recover a table that I accidentally deleted. It is taking a long time for the restore to get done. Here is the command I am running as sa in QA
---
RESTORE DATABASE Warehouse_new FROM DISK = 'H:MSSQLDataMSSQLBACKUPDBBackupsWarehouseWa rehouse_db_200801050600.BAK'
WITH
MOVE 'Warehouse_Data' TO 'G:MSSQLDataMSSQLDataWarehouse_New_Data.MDF',
MOVE 'Warehouse_Log' TO 'H:MSSQLDataMSSQLLogsWarehouse_New_Log.ldf'
----
There Warehouse_New_Data.MDF is 375 GB and the log is 12 GB.
There is still 169 GB of free space on the drive I am restoring to after the presence of Warehouse_Data.MDF and Warehouse_New_Data.MDF (each 375 GB).
Its been 4.5 hrs and the restore is still running. Backups take about 3.5 hrs to complete. Can I do any checks on the restore to see what point it is at? I stopped the restore using EM earlier after it took 8 hours and still no progress.
Please advise.
Thanks.
Has Anyone Seen A 25 TB Data Warehouse On SQL Server?
Jul 23, 2005Has anyone seen a 25 TB data warehouse on SQL Server? I do not know ofsuch a thing, but maybe it exists out there.
View 6 Replies View RelatedData Warehouse Nulls
Mar 15, 2006Hello..I was wondering if anyone out there could tell me how they deal withNULL values in a data warehouse? I am looking to implement a warehousein SQL 2005 and have some fields which will have NULL values and Iwould like some further ideas on how to deal with them. At my last jobin dealing with Oracle we were just going to leave the fields NULL, butin SQL how would you best recommend cleaning the data? I greatlyappreicate your help and look forward to your reponses.Thank you
View 2 Replies View RelatedREFRESH DATA WAREHOUSE
May 21, 2007I€™m making warehouse for our HMIS (healthcare management information system)by using SSIS. I€™m facing some problems now, could you please help me to solve my problem.
Brief idea about my Warehouse:
Source: oracle 9i
Destination: Sql server 2005
ETL tool: SSIS
Problems:
How to refresh or load the current data to data warehouse.(now i'm using truncate sql task for deleting old/entire data for each packages, i really dont want to use in the production) . For example: The patient admissions data is adding everyday so i want to load the current data into my warehouse.
Could you pls suggest me good solution for this?
Refresh Cycle timings: is there any task available in SSIS?
current status:
First Time load completed, i set one Execute Sql statement ctrl flow task for Truncate the existing loaded data in the sql server 2005. and then again i process one data flow task for loading the data from oracle to sql server.
Data Warehouse Forum
Jul 6, 2007Hi, all,
Thanks for your kind attention.
Just have some enquries about some issues of data warehouse design and would like to hear from any of you for any prestigious forum on data warehouse?
Thanks a lot in advance and I am looking forward to hearing from you shortly.
With best regards,
Yours sincerely,
Realtime Data Warehouse
Apr 10, 2008Is SSIS a tool for extracting realtime data from staging to data warehouse? Realtime in my case can be loading every 15 minutes but no more than 30 minutes. I've a data warehouse which data refresh once a day and it worked fine. The data that I extract into the warehouse is from a Staging database which is realtime replication of multiple production databases. Once a day, I've to have replication pauses on staging for a couple hours to refresh the data warehouse. That's the only way so SSIS can pull the data correctly; if I've replication on while SSIS pull data, it will always copy less rows than it supposed to.
I cannot afford to have replication pauses every 15 minutes just so I can refresh data warehouse. Does anyone every have this problem? or any best practice how to do this?
Thanks,
-Ash
Reg: Data WareHouse And SSRS
Apr 25, 2008
Hi,
Can any one give some good articles and webcast links for Developing SSRS reports with Data WareHouse.
Regards
Sithender
Sample RFP For A Data Warehouse Project
Jun 26, 2003Does anyone have a sample RFP for a Data Warehousing project?
My manager hired an outside consultant to draw up a proposal for our company. But it is getting stuck in details and we are way behind schedule.
It will help me greatly if there is an outline of a DW RFP.
Relative Time In A Data Warehouse
Feb 28, 2008At my office, we've been slowly working on putting together a data warehouse.
We're a financial services company and one of the services that we offer is debt collection. As far as reports go, our clients are interested in knowing how much money we collect over time. In particular, they want to know how many payments we've gotten 5, 10, and 15 months (and so on) after we receive a case. (Obviously, the 5-month payments are also included in the 10 and 15-month calculations).
When I wrote this report using our transactional database, I was completely new to SQL and the ever-resourceful Patron Saint took pity on me, so you can see a good description of the details at http://www.sqlteam.com/forums/topic.asp?TOPIC_ID=78510
Now that I'm no longer a total newbie at SQL, and having been through a relatively extensive seminar on data warehousing, I've been entrusted with researching certain aspects of data warehouse development (rest easy, though, folks - the real DWH work is not being done by the very inexperienced me, but by an actual professional :) ).
My question:
how would you model this kind of "relative time" in a data warehouse? How would you display the 5-month, 10-month, and 15-month payments in a DWH? I can't really imagine that the kinds of joins necessary to do this in a transactional database would be desirable in a data warehouse.
We have the following:
1.) FACT_Payment: A fact table showing each each payment to the most detailed granularity. One attribute of this table is the payment date. Another attribute is a foreign key to the case dimension described below.
2.) DIM_Case: A dimension table showing information on each case, including the case start date. DIM_Case
3.) DIM_Date: A date dimension table.
(For added clarification: The FACT_Payment payment date has to be 5, 10, 15 months etc... after the DIM_Case start date.)
Any ideas, comments, experience with something like this?
Thank you.
Column Names In Data Warehouse
Mar 12, 2008Hi all,
I'm working on my first data warehouse and I'm not sure how I should name the columns in the database.
The first phase of the data warehouse is to store a bunch of data from one third party source. The source contains over 100 pieces of data and the business user doesn't even know what some of the fields are but he wants to store everything. The third party refers to the each field with a somewhat cryptic short name and a longer description. The short name isn't always cryptic.
My question is am I better off naming my columns the same as the source system's short name so that I can easily debug problems later? Should I instead try to shorten their definition into something meaningful? On a side note, I'm 100% positive that we'll never populate the tables in questions with data from an additional source.
Thanks!
Data Warehouse And A Data Base? Difference?
Jun 19, 2007I need someone to give me a small briefieng on how a enterprise data warehouse (EDW)differs from a regular database?
Currently we have an appplication that accesses a database with about 18 tables. We also have a Data Warehouse. For some reason I was thinking that it would be possibly to migrate thedatabase into the data warehouse. The reason is that in looking at the schematic design forthe data warehouse there are some data tables that could also be used by our application that uses the DB.
I guess I am confused because I am not sure if a data warehouse is used in the same wayas a database?
How To Create A Data Warehouse Or Data Mart
Aug 31, 2006i want to create a data mart from an existing OLTP database. for example northwind or i will create an OLTP database. i dont know how i can create data mart from OLTP database. i want to learn that step by step. help me??? please!!
View 5 Replies View RelatedDate Table In Data Warehouse? Opinions...
Jun 1, 2004I'm reviewing a data warehouse design schema for a client that is following Kimball's data warehousing principles. One of the first things I noticed was a table of dates with expanded columns giving such information as the year, month, month name, fiscal year, quarter, etc for each date, They also have a surrogate key (int) for the date value. The fact tables store the surrogate key rather than the date value itself.
They were very surprised when I questioned the purpose of this table, assuring me that Kimball was very strong on the concept of having a date dimension for each table.
I don't see the purpose of a table containing nothing by derived date formats. I think they will get a bigger performance hit from having to link through the surrogate key than they would suffer from having to convert datevalues stored in the fact tables.
Has anybody else ever seen this before? Does Kimball really advise this?
Large Data Warehouse Handled Bei SQL Server?
Jul 20, 2005Hi,I would like to know if anyone out there really uses SQLServer 2000 (which edition?) to hold the data for a datawarehouse? How much data does it handle efficiently?TIAFrank
View 1 Replies View RelatedBulk Inserts To Data Warehouse - Best Practices?
Jul 20, 2005Hello all,I just started a new job this week and they complain about the length oftime it takes to load data into their data warehouse,which they do once a month.From what I can gather, they rebuild the indexes before the insert with an80% Fillfactor, then insert the data (with theindexes enabled), then rebuild the indexes with a 100% Fillfactor.Most of my RDBMS experience is with a different product. We would havedisabled the indexes and Foreign Keys, loaded the data, thenre-enabled them, moving any records that violated the constraints into anappropriate audit table to be checked after.Can someone share with me what the accepted "best practices" are for loadingdata efficiently into a data warehouse?Any thoughts would be deeply appreciated.Steve
View 2 Replies View RelatedGeneric Staging Design Of Data Warehouse
Jan 6, 2006I have a question about staging design using SSIS. Has anyone come up with an ETL design that would read table names from a generic table and dynamically create the ETL to stage the table.
1. Have a generic table which would have table name and description and whatever else that was required.
2. Have a master ETL that would enumerate through the table and stage all the table names found in the generic table.
This way I wouldn't have to create an ETL which would hardcode the names of 300-500 tables and have the appropriate 300-500 data sources and targets listed.
Not sure if I am making sense but I hope someone understands the attempt.
thanks
Loading Data Warehouse (Perf. Tuning)
Feb 25, 2008
Hi all,
I'm loading my data warehouse using several SCDs. Some of these SCDs need to occur in sequence, while others can be run at the same time. I'm wondering what the best option for me is in terms of performance. Here is what I was considering:
1) Create a single package. Create two sequence containers --- one that will contain SCD loads that occur in sequence; the other sequence container contains SCD loads that occur in parallel.
OR
2) Create a set of packages for each SCD load. Then create a "Master" package that will use "Execute Package Task" components to call these packages.
The othe reason I want to bring up these difference ways to design an DW Load is because the second option is a "cleaner" approach, or a more organizational approach, to the load. The first option can get quite messy and large if you have several SCDs and several sequence containers. However, I'm looking for the fastest performance. Any thoughts?
Indexing In SQL Server Star Scheme Data Warehouse
Jan 19, 2007Hi all,
Our star schema design has one fact table and 3 dimensions.
The FK's in the fact do not necessarily make up the primary key. So I have an identifier in the fact table as PK. Here is my index assignment:
Fact Table - Clustered Index on PK
Non Clustered Index 1 on FK1
Non Clustered Index 2 on FK2
Non Clustered Index 3 on FK3
Each Dimension Table - Clustered Index on PK
Non Clustered Index on Attribute. This is the attribute that will be used in reports / cubes.
Is the above design good to start with?
Thanks,
V