Few Questions On Backups For Warehouse
Sep 1, 2004
I am starting to load a data warehouse for a retention period of 10 years. My database backup plan is as follows -
1. Perform full back on Sunday.
2. Perform differential backup everyday from Mon - Sat.
3. Perform transaction log back-ups every hour on all days.
My recovery mode is going to be BULK-LOGGED at all times. I had a few questions / comments on the Maintenance Plan that I would be creating for the back-ups. My database name is Warehouse.
1. Differential Backups cannot be created via a Maintenance Plan. Only a full-backup gets created. Am I correct?
2. I shall be running Optimizations and Integrity checks prior to full-backup. Is this ok?
3. Remove files (both .BAK and .TRN) older than - I am thinking of having 6 days. I want only one full back-up at a time in the server. What settings can I use? I think the old back-up gets deleted when the new one is successful. What settings in the Maintainence Plan do I have to use to overwrite the previous back-up with the current one?
System Databases -
Should the settings for System Databases be the same as my Warehouse database?
The Maintenance Plan takes care of full-back up and TLOG back-ups. For Differential Backups I have to use the All Tasks from EM and specify the Differential Backup job. Correct?
All kinds of back-ups can occur in the database when it is active. Meaning, I have a job that loads data in the warehouse when a back-up is occurring simultaneously. Am I correct?
I do not intend to shrink the Transaction Log at any time, since it gets backed up every hour I do not expect it to grow to a large size. If I do have to shrink it, then I change the recover mode to Simple, shrink the log and then immediately do a full-backup and after that set the mode back to Bulk-Logged. Is the sequence of steps correct?
Please let me know your inputs.
Thanks,
Vivek
View 1 Replies
ADVERTISEMENT
May 8, 2007
Hi,
Ive a few questions on backups im hoping someone can help me with.
1. Is it possible to create backups, with the db in a disconnected state, which can be reattached and will function exactly as the database was at the time of the backup?
2. In sql express I currently have one backup which is a full backup. I would like to have an other backup on a different disk, but when I add another one in the Destination box I get an error 'The volume of device 'first backup' is not of a multiple famliy media set'. Is it possible to have moree than one backup of the database?
3. What are the main things that third party software packages do that the built in backup cannot? What do they make easier?
4. If I delete my current backup, and then create a new one, will it include all transactions logs, and have multiple restore points, or will it just create a backup of the current time, with no transaction logs?
5. I have found a simple script that allows me to restore my backup to a new database :
RESTORE DATABASE test
FROM DISK = 'C:SQLSQLWLM.BAK'
WITH
MOVE 'wlm.com' TO 'C:SQL est_Data.mdf' ,
MOVE 'wlm.com_Log' TO 'C:SQL est_log.ldf', REPLACE
that works in restoring the database to a new database. However, it only restores to the earliest point, rather than the most recent. Can this be fixed easily?
Thanks,
C
View 2 Replies
View Related
Mar 12, 2008
hi all,
i have some questions about backing up mssql database, please, if you can help me with them.
1. what is main difference between Full Recovery and Bulk_Logged Recovery models? when to use one, and when to use the other?
2. why do i need marks in my backup? can you tell me in which case do i need marks? i don't understand, why wolud i ever wanted to recover database to some marked point? in which case i would like to recover only it's part and not full database?
3. if i do have backup of transaction log, it means nothing if i do not have backup of database structure? i need to have complete backup of transaction log to be able to recover all of the data?
4. i have database, and i did make no changes in database structure. in that case, only one backup of the system tables of the database is enough? there is no need to backup it all the time?
5. when i backup my transaction log file, do i backup only a differences between last backup of transaction log file and current, or i always backup complete transaction log file?
thank you in advance!
View 3 Replies
View Related
Nov 15, 2015
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies
View Related
Feb 19, 2008
This might not be the right place to post this but I'm going crazy on this....
Has anyone seen this error or know what this error is and how to fix it?
Error:
A connection cannot be made to the database.
Set and test the connection string.
Additional Information:
Either the user, (me), does not have access to the TFSWarehouse database, or the database does not exist. (Microsoft SQL Server 2005 Analysis Services)
Thanks,
Nici
View 2 Replies
View Related
Apr 8, 1999
hi, 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
View 3 Replies
View Related
Jan 15, 2008
Hello,
I'm here in my first attempt at using SSIS, and I'm creating a data warehouse for my company. I made a huge oversight as I was building my facts and dimensions, which is that while I created a new integer primary key for each table, I destroyed the referentiality between them. I hadn't planned that part out. When I went to go connect the tables at the end, I realized that even though I could create the FK's in each table, they wouldn't mean anything.
So I'm a little confused as to how to maintain the referentiality between the tables during the creation of the new warehouse tables. My only idea right now is to create a new table for each original entity in the relational system -- I'll call it a "LOOKUP" table -- that table would have 2 columns, their original GUID column, and a new integer id column. Then, when I create a new warehouse table, I can do a lookup to the proper LOOKUP table and assign the new PK or FK values so they'll continue to be consistent across the new warehouse tables, maintaining the references to each other.
Am I approaching this right?
Thanks,
Andy
View 1 Replies
View Related
Feb 5, 2008
Hi All,
I am facing a small problem while updating the warehouse using SSIS package.
Here is an sample scenario.
I have a staging table T1 with some important columns as stateid,ename etc ..
and target table as Trg with only two columns stateid and statecnt.
The Rule is, each state will be sending an excel file which basically contains state and employees belonging to the state information.
For the first time when i execute the SSIS package, i need to load statetid and count_of_employees for that particular state.
say for example.
SELECT stateid,
count(ename)
from T1
where stateid = ?
For the next time if the same state information is received then we need to update the count to the existing record in the Dataware house rather than creating a brand new record once again.
I am little bit confused of implementig this logic.
Can anyone help me out in accomplishing this task.
What can done or what steps is involved in achiving the task.
Thanks in advance.
View 3 Replies
View Related
May 7, 2003
Hi,
I am trying to learn the MDX tutorial(http://www.databasejournal.com/features/mssql/article.php/1550061) by william Pearson.It uses the example of "warehouse" cube.I am unable to proceed with the tutorial as I don't have this cube.Any links or articles on how to build this "warehouse" cube being used in the tutorial.
Thanks,
Copernicus
View 2 Replies
View Related
Dec 22, 2004
I 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!
View 4 Replies
View Related
Feb 27, 2008
Hi 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.
View 1 Replies
View Related
Feb 5, 2008
Hi,
I am facing a small problem while updating the warehouse using SSIS package.
Here is an sample scenario.
I have a staging table T1 with some important columns as stateid,ename etc ..
and target table as Trg with only two columns stateid and statecnt.
The Rule is, each state will be sending an excel file which basically contains state and employees belonging to the state information.
For the first time when i execute the SSIS package, i need to load statetid and count_of_employees for that particular state.
say for example.
SELECT stateid,
count(ename)
from T1
where stateid = ?
For the next time if the same state information is received then we need to update the count to the existing record in the Dataware house rather than creating a brand new record once again.
I am little bit confused of implementig this logic.
Can anyone help me out in accomplishing this task.
What can done or what steps is involved in achiving the task.
Thanks in advance.
View 2 Replies
View Related
Jul 23, 2005
Has 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 Related
Mar 15, 2006
Hello..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 Related
May 21, 2007
I€™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.
View 8 Replies
View Related
Jul 6, 2007
Hi, 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,
View 3 Replies
View Related
Apr 10, 2008
Is 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
View 2 Replies
View Related
Apr 25, 2008
Hi,
Can any one give some good articles and webcast links for Developing SSRS reports with Data WareHouse.
Regards
Sithender
View 1 Replies
View Related
Jun 26, 2003
Does 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.
View 4 Replies
View Related
Jun 9, 2004
Hello, 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
View 5 Replies
View Related
Feb 28, 2008
At 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.
View 7 Replies
View Related
Mar 12, 2008
Hi 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!
View 3 Replies
View Related
Jun 14, 2006
Regarding the code/db from the REAL project that just got released, I have no problem attaching the "REAL Sample V6" database, but the "REAL Warehouse Sample V6" database requires ENTERPRISE edition, because the default copy uses Partitioning (i.e. the PT version, although the document stated that the multi-table (MT) version is the default). I only have the STANDARD edition of SQL 2005, is there a workaround?
Appreciate any help.
View 7 Replies
View Related
Jun 1, 2004
I'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?
View 14 Replies
View Related
Jul 20, 2005
Hi,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 Related
Jul 20, 2005
Hello 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 Related
Jan 6, 2006
I 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
View 10 Replies
View Related
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?
View 7 Replies
View Related
Jan 19, 2007
Hi 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
View 4 Replies
View Related
May 4, 2007
We have a relational database (rd) and a data warehouse (dw). This dw has a table (tw) with all key fields (dimension keys) and metric related (measures) fields. This table is populated with monthly data each month. The tw is joined to various look up views present in the dw to obtain name fields from rd. The DBA wants me to remove the look up views. I now have following 2 options that I can think of –
1)Further de-normalize the tw and store the name fields as well. However, there are two issues with this option –
a.The size of tw will grow tremendously.
b.We are storing monthly data and the values in the name fields may change after some time. Then we will have to put in additional views/objects to obtain the latest name.
2)Using ETL, obtain the copy of rd tables overnight in dw. We will then join tw with these tables and there will no longer be cross database joins. However, this will be a burden on maintenance and support.
As of now these are the possible options I can come up with. Which one would you suggest and why? If you have another option, please let me know.
Thank you all in advance,
sajmera
View 1 Replies
View Related
Apr 1, 2004
I would like to buy the book "The Data Warehouse Toolkit by Ralph Kimball" but unfortunately only the 1st edition is currently available over here and they say that it will take at least 6 weeks if I want to order the 2nd edition.
Can someone please guide if there are MAJOR differences between the 1st and the 2nd edition of this book? If not, then what are the differences if any.
I'll appreciate your reply.
View 2 Replies
View Related
Jul 10, 2015
We have a master database (SQL 2014 Std) from which data are imported from XML files (send by en ERP system) using SSIS. Â There is about 12 other servers (SQL 2014 Express) located in remote warehouses. People will uses PocketPC to scan barcode of products in the warehouses and all operations must be forwarded to the master DB to be exported in a XML file for the ERP system.Now, each warehouses are independant. How can I setup the replication so only data belonging to a specific warehouse is replicated to its corresponding DB? I thought about creating views, one for each warehouses, and setup a replication for each warehouse, so there would be 12 merge replications configured. Is it fine?
View 2 Replies
View Related
Aug 21, 2014
OK, so I have:
- 500 GB DW
- 5 GB in smaller DBs
- 220 GB TempDB
- 350 GB in Log files.
My machine is Fujitsu Primergy 64 cores (with HT) and 192 GB RAM.
I have several IO locations:
- 540 GB in-server HDD 15k RAID10
- 1 TB HDD 15k RAID10 on SAN (separete controller)
- 2 TB HDD 15k RAID10 on SAN (same controlller as below)
- 800GB SSD RAID10 on SAN (same controller as above)
Data warehouse has 2 fact tables that are absolutely crucial and quite large.
Now i want to organize DB into several Filegroups and put them on different drives. Filegroups I'm thinking of:
- FILEGROUP1: for 1st crucial Fact Table
- FILEGROUP2: for 2nd crucial Fact Table
- FILEGROUP3: for tempDB
- FILEGROUP4: for dimensions data
- FILEGROUP5: for the rest of facts data
- FILEGROUP6: for dimensions indexes
- FILEGROUP7: for the rest of facts indexes
- FILEGROUP8: for 1 log file of one smaller DB (its in full-recovery and its quite large)
- FILEGROUP9: for the rest of log files
- FILEGROUP10: others
How should I organize them across available drives? I was thinking about sth like:
800 GB SSD: FILEGROUPS 1-3
2 TB RAID10: FILEGROUPS 5+7+8
1 TB RAID10: FILEGROUPS 4+6+10
540 GB in-server: FILEGROUP 9
I know that having multiple filegroups on the same drive is pointless regarding performance, but in future i could actually add some more drives, so i want to separate them now.
Also - how much files per filegroups should i create? Considering 1 or 2. Except TempDB where I am going for 4.
View 2 Replies
View Related
