[Last Call] Learn how to a build a cloud-first strategyRegister Now

x
?
Solved

data lost after restore database

Posted on 2005-04-25
9
Medium Priority
?
207 Views
Last Modified: 2012-05-05
I want to change the database name so I delete the database and add the database with other name
After that, I restore the data by using the function "Restore Database".
Then, I find that some data has lost. Why do it? Is it the database restore older data instead new?
If it is right, how can I recover the new data.
0
Comment
Question by:huang_ck
  • 3
  • 2
  • 2
  • +1
8 Comments
 
LVL 10

Accepted Solution

by:
AustinSeven earned 2000 total points
ID: 13856849
If you are only backing up your database using full backups (not transaction log backups as well), the data you restore can only be as 'new' as the time of your last full backup.   The backup can never including newer data than the database it was backed up from.   So, if you want a restore to contain the latest data, do a full backup immediately before you restore it.   That is how you will recover the new data.  

There is a more complicated answer that involves recover latest data from a full backup and transaction log backups but I don't think you want to get into this just yes.  Concentrate on the basics.

AustinSeven
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13857754
Alternatively use the DTS Import / Export Wizard to Import the data from the original database into a new dataabse with the name you want

regards
0
 
LVL 11

Expert Comment

by:pcsentinel
ID: 13857760
Also if you just want to change the database name you can use

sp_renamedb which will change the database name without a backup

regards
0
Transaction-level recovery for Oracle database

Veeam Explore for Oracle delivers low RTOs and RPOs with agentless transaction log backup and transaction-level recovery of Oracle databases. You can restore the database to a precise point in time, even to a specific transaction.

 

Author Comment

by:huang_ck
ID: 13857786
Thank you for your reply.

Now I would like to backup the sql server daily by using the SQL Server Agent.
I would like to create a new step but I don't know what do I choose the type and command to backup the hold database which name as "myDB" and I must backup all my table.

Another question is that when I just backup the file under the folder of c:/program file/microsoft sql server/sql. Is it work?
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 13857806
backup database mydb to disk = 'c:\backups\mydb.bak'

or create a device in Enterprise Manager and do...

backup database bydb to myDb_dump

-- where the myDb_dump points to a backup file placed anywhere, eg. C:\backups

Create a job and a step within the job with the above backup command in it.  Schedule the job and you're done.

AustinSeven
0
 
LVL 10

Expert Comment

by:AustinSeven
ID: 13857812
...or you can use a Maintenance Plan wizard to create your backup plan for you.

AustinSeven
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 13858393
>> Another question is that when I just backup the file under the folder of c:/program file/microsoft sql server/sql. Is it work?<<

For you to be able to backup the files under the c:/program file/microsoft sql server/sql folder, SQL Server has to be down because the data files will be in use and you cannot back it up.  Your best option is to use Maintenance Plan to back up your database.
0
 
LVL 28

Expert Comment

by:rafrancisco
ID: 14033192
huang_ck, were you able to solve this issue?
0

Featured Post

Granular recovery for Microsoft Exchange

With Veeam Explorer for Microsoft Exchange you can choose the Exchange Servers and restore points you’re interested in, and Veeam Explorer will present the contents of those mailbox stores for browsing, searching and exporting.

Question has a verified solution.

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

When trying to connect from SSMS v17.x to a SQL Server Integration Services 2016 instance or previous version, you get the error “Connecting to the Integration Services service on the computer failed with the following error: 'The specified service …
Ready to get certified? Check out some courses that help you prepare for third-party exams.
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed
Via a live example, show how to setup several different housekeeping processes for a SQL Server.
Suggested Courses

830 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