Go Premium for a chance to win a PS4. Enter to Win

x
?
Solved

database backup

Posted on 2007-11-15
9
Medium Priority
?
217 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 1200 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 300 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
LVL 35

Assisted Solution

by:David Todd
David Todd earned 1200 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 1200 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 1200 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

Independent Software Vendors: We Want Your Opinion

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

Question has a verified solution.

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

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
A Stored Procedure in Microsoft SQL Server is a powerful feature that it can be used to execute the Data Manipulation Language (DML) or Data Definition Language (DDL). Depending on business requirements, a single Stored Procedure can return differe…
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…
This video shows how to set up a shell script to accept a positional parameter when called, pass that to a SQL script, accept the output from the statement back and then manipulate it in the Shell.

782 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