Solved

database backup

Posted on 2007-11-15
9
211 Views
Last Modified: 2016-08-29
I'm in the process of learning about database backup and recovry and have some questions on database and would if you can verify them
here are the text files for order and details:
https://filedb.experts-exchange.com/incoming/ee-stuff/5633-New-Folder.zip 

your help is appreciated.

Backup

Using T-SQL backup the database.
answer: backup database db1 to disk = 'c:\db1.bck' with init

Add 2 rows to the Orders and OrderDetails table.

Answer:
insert into Orders(salesproductID,OrderQty,status)
   values(' 1','5','6')

insert into orderdetails(salesproductID,OrderQty,ProductID)
   values('9','6','8')

Backup the log.
answer: backup log db1 to disk = 'c:\db1.bck' with init

Wait for at least 5 minutes and record time 1 using the GETDATE() function.
answer: execute GetDate()

Add 3 more rows to the two tables.

answer:
insert into Orders(salesproductID,OrderQty,status)
   values(' 2','3','5')
insert into Orders(salesproductID,OrderQty,status)
   values(' 4','2','9')
insert into Orders(salesproductID,OrderQty,status)
   values(' 3','2','1')

insert into orderdetails(salesproductID,OrderQty,ProductID)
   values('8','4','2')
insert into orderdetails(salesproductID,OrderQty,ProductID)
   values('6','3','2')
insert into orderdetails(salesproductID,OrderQty,ProductID)
   values('3','7','9')

Perform a differential database backup.
answer: backup database db1 to disk = 'c:\db1.bck' with differential, init

Wait for at least 5 minutes and record time: Time 2.
for this do i still use getdate() to get the date?

Drop the OrderDetails table.
drop table orderdetails

Wait for another 5 minutes minimum and record time: Time 3.
for this do i still use getdate() to get the date?

Add 3 rows to the Orders table.
insert into Orders(salesproductID,OrderQty,status)
   values('7','21','10')
insert into Orders(salesproductID,OrderQty,status)
   values('14','12','19')
insert into Orders(salesproductID,OrderQty,status)
   values(' 13','12','11')

Backup the log and record the time Time 4.
answer: backup log db1 to disk = 'c:\db1.bck' with init
getdate()

When trying to add rows to the OrderDetails table I figured that it does not exist.  I would like to get back the dropped table  OrderDetails.  At the same time I don't want to lose 3 rows added to the table Orders. a database expert asks whether table OrderDetails is dropped and i say that the table existed at time Time 2 and did not exist at time 3

Recovery

The system administrator needs to restore the database a minute early than Time 2  
how to create a procedure to restore? can you show me the steps and how it would look like in a file. what would the contents of the Orders table contain?

Also How to restore database to a point in time using Stop at
0
Comment
Question by:salesprod
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 4
  • 4
9 Comments
 
LVL 35

Accepted Solution

by:
David Todd earned 400 total points
ID: 20292283
Hi,

I've got to ask: Is this homework? EE rules forbid doing homework - its interlectually dishonest.

select getdate() shows the date and time, since in current releases of SQL there is not separate date and time it is one quantity - datetime.

Look at waitfor delay '0:05:00' to automate the 5min delay ...

Regards
  David
0
 

Author Comment

by:salesprod
ID: 20292360
Hi,
This is a self learning process trying to learn SQL.  Already finished University and workign now with a job related to SQL, but tryign get more in depth knowledge on the subject myself, in school they only taught how to query.  This question is from school questions im trying to work on my own want to have guidance on am i doing it correctly or not.

Hope this clarify any confusions, and thank you all for your assistance.

Salesprod
0
 
LVL 10

Assisted Solution

by:pai_prasad
pai_prasad earned 100 total points
ID: 20293010
i agree with DTodd..

"we" would rather help if you have read the concepts from BOL
http://msdn2.microsoft.com/en-us/library/ms191304.aspx , understand them and experiment ...
then ask..hey i tried backup but it failed with this error...or something like i used this option and my database is gone ,,what went wrong...why

tats how i learnt databases...

0
Best Practices: Disaster Recovery Testing

Besides backup, any IT division should have a disaster recovery plan. You will find a few tips below relating to the development of such a plan and to what issues one should pay special attention in the course of backup planning.

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 400 total points
ID: 20293434
Hi,

The big mistakes in your procedure is that you are overwriting the full backup with the differential backup and the log backup.

The restore process for dif backup is
restore the full backup that is just previous to the dif backup with norecovery
restore the dif backup.

The restore process for log backups is
restore the full backup that is just previous to the log backup with norecovery
restore the next log backup with norecovery
continue to the selected log backup
restore log backup with recovery

HTH
  David
0
 

Author Comment

by:salesprod
ID: 20295471
does this mean that my queries were incorrect?
please advise.
Thanks.
0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 400 total points
ID: 20295548
Hi,

Yes.

And you should be able to correct the queries and post them here ...

Regards
  David
0
 

Author Comment

by:salesprod
ID: 20295853
Hi there i am just following your procedure to gain a better understanding:
Can you let me know if I am on the right track?


The restore process for dif backup is
restore the full backup that is just previous to the dif backup with norecovery
restore the dif backup.

restore database db1 from disk = 'c:\db1.bck' with norecovery
restore database db1 from disk = 'c:\db1.bck' with differential

The restore process for log backups is
restore the full backup that is just previous to the log backup with norecovery
restore the next log backup with norecovery
continue to the selected log backup
restore log backup with recovery

restore database db1 from disk = 'c:\db1.bck' with norecovery
restore log db1 from disk = 'c:\db1.bck' with norecovery

Also to better understand backing up and how to write the queries, can you highlight and point out where I went wrong in the queries in the first post? and provide me with a few examples?
Thanks again.

0
 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 400 total points
ID: 20295943
Hi,

>>
restore database db1 from disk = 'c:\db1.bck' with norecovery
restore database db1 from disk = 'c:\db1.bck' with differential
<<

close but no - you will have to secify either a different file, or which backup in the media you want to use.

Suggestion: use .bak for the full backup, .dif for the differential, .log for the transaction log backup.

HTH
  David
0
 

Author Comment

by:salesprod
ID: 20296020
thanks
do you mean this?

restore database db1 from disk = 'c:\db1.bck' with norecovery
restore database db1 from disk = 'c:\db1.dif' with differential, file=1

also can you assist me with the queries one by one in the first post?
Thanks.


0

Featured Post

Back Up Your Microsoft Windows Server®

Back up all your Microsoft Windows Server – on-premises, in remote locations, in private and hybrid clouds. Your entire Windows Server will be backed up in one easy step with patented, block-level disk imaging. We achieve RTOs (recovery time objectives) as low as 15 seconds.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

In this article I will describe the Copy Database Wizard method as one possible migration process and I will add the extra tasks needed for an upgrade when and where is applied so it will cover all.
In this article we will learn how to fix  “Cannot install SQL Server 2014 Service Pack 2: Unable to install windows installer msi file” error ?
Via a live example, show how to extract insert data into a SQL Server database table using the Import/Export option and Bulk Insert.
Using examples as well as descriptions, and references to Books Online, show the documentation available for datatypes, explain the available data types and show how data can be passed into and out of variables.

751 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question