Solved

database backup

Posted on 2007-11-15
9
202 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
 
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
PRTG Network Monitor: Intuitive Network Monitoring

Network Monitoring is essential to ensure that computer systems and network devices are running. Use PRTG to monitor LANs, servers, websites, applications and devices, bandwidth, virtual environments, remote systems, IoT, and many more. PRTG is easy to set up & use.

 

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

Is Your Active Directory as Secure as You Think?

More than 75% of all records are compromised because of the loss or theft of a privileged credential. Experts have been exploring Active Directory infrastructure to identify key threats and establish best practices for keeping data safe. Attend this month’s webinar to learn more.

Question has a verified solution.

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

Why is this different from all of the other step by step guides?  Because I make a living as a DBA and not as a writer and I lived through this experience. Defining the name: When I talk to people they say different names on this subject stuff l…
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.
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
Via a live example, show how to setup several different housekeeping processes for a SQL Server.

937 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

Need Help in Real-Time?

Connect with top rated Experts

7 Experts available now in Live!

Get 1:1 Help Now