Solved

database backup

Posted on 2007-11-15
9
199 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
Comment Utility
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
Comment Utility
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
Comment Utility
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
Comment Utility
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
How to run any project with ease

Manage projects of all sizes how you want. Great for personal to-do lists, project milestones, team priorities and launch plans.
- Combine task lists, docs, spreadsheets, and chat in one
- View and edit from mobile/offline
- Cut down on emails

 

Author Comment

by:salesprod
Comment Utility
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
Comment Utility
Hi,

Yes.

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

Regards
  David
0
 

Author Comment

by:salesprod
Comment Utility
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
Comment Utility
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
Comment Utility
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

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

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.
Ever needed a SQL 2008 Database replicated/mirrored/log shipped on another server but you can't take the downtime inflicted by initial snapshot or disconnect while T-logs are restored or mirror applied? You can use SQL Server Initialize from Backup…
Familiarize people with the process of retrieving data from SQL Server using an Access pass-thru query. Microsoft Access is a very powerful client/server development tool. One of the ways that you can retrieve data from a SQL Server is by using a pa…
Via a live example combined with referencing Books Online, show some of the information that can be extracted from the Catalog Views in SQL Server.

743 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

15 Experts available now in Live!

Get 1:1 Help Now