Solved

database backup

Posted on 2007-11-15
9
205 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
  • 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
NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

 
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

NAS Cloud Backup Strategies

This article explains backup scenarios when using network storage. We review the so-called “3-2-1 strategy” and summarize the methods you can use to send NAS data to the cloud

Question has a verified solution.

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

In this article I will describe the Backup & Restore 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.
International Data Corporation (IDC) prognosticates that before the current the year gets over disbursing on IT framework products to be sent in cloud environs will be $37.1B.
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

803 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