msde restore problem

Hi guys - hope you can help me.
We had a support request from a user today who wanted to know why some of
her data was missing. Upon investigation, it turns out that due to various
things... we'd had to image back the server. Now, we took a copy of the
directory with all the database files in before we did that. So we tried to
restore that today, and it said it had worked, but the data is still missing
!
Question.. if we restored the FILES of the database over themselves, as the
database was running, would we need to restart the sql server agent thingy?
Or should we do that first, restore the files, and then restart MSDE?
Would it make any difference? Or to be more precise, would overwriting the
files (its an open file restore, but it said it worked?) require msde to be
restarted?!
Cheers
Danmusosdev wrote:
> Hi guys - hope you can help me.
> We had a support request from a user today who wanted to know why some of
> her data was missing. Upon investigation, it turns out that due to various
> things... we'd had to image back the server. Now, we took a copy of the
> directory with all the database files in before we did that. So we tried t
o
> restore that today, and it said it had worked, but the data is still missi
ng!
> Question.. if we restored the FILES of the database over themselves, as th
e
> database was running, would we need to restart the sql server agent thingy
?
> Or should we do that first, restore the files, and then restart MSDE?
> Would it make any difference? Or to be more precise, would overwriting the
> files (its an open file restore, but it said it worked?) require msde to b
e
> restarted?!
> Cheers
>
> Dan
Oh for crying out loud, did you actually use the word "thingy"? Sure
sign of a competent tech.
You should have made a proper SQL backup, instead of trying to copy the
raw database files. Those files were in use, unless you stopped the
MSSQLSERVER service. You've most likely lost data.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi, Dan,
Thanks for your post!
And Thanks Tracy for your kind participation and good suggestions.
Dan, my understanding of this issue is:
The database lost data that might be caused by many reasons. You had a copy
of that database data directory and decided to restore the files of the
database over themselves. You wanted to know whether you need restart the
SQL Server Agent at first and whether the MSDE need to be restarted.
If I have misunderstood, please let me know.
Do you mean that your backup files are raw files (.mdf,.ldf) of the
database and not real backup files (.bak) ?
If they are .bak files, you can directly use the RESTORE statement to
restore the database.
Otherwise, you can take the following steps to replace the files without
stop the MSDE:
1) Backup your database by "BACKUP DATABASE database_name TO
DISK='D:\temp_folder\test.bak' "
2) Run the statement "sp_detach_db [database_name]"
3) Remove the database files in the local directory.
4) Copy the backup files to the directory
5) Use the statement sp_attach_db like this:
EXEC sp_attach_db @.dbname = N'pubs',
@.filename1 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf',
@.filename2 = N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs_log.ldf'
You may refer to:
314546 HOW TO: Move Databases Between Computers That Are Running SQL Server
http://support.microsoft.com/?id=314546
224071 INF: Moving SQL Server Databases to a New Location with Detach/Attach
http://support.microsoft.com/?id=224071
Otherwise, are you sure that the backup files have no data loss? As Tracy
said, for time difference, they still possibly have lost data.
If you have any other concerns, please feel free to let me know. It's my
pleasure to be of assistance.
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.|||Hi Dan,
Just checking in to see if the suggestions were helpful. Please let us know
if you would like further assistance.
Have a great day!
+++++++++++++++++++++++++++
Charles Wang
Microsoft Online Partner Support
+++++++++++++++++++++++++++
When responding to posts, please "Reply to Group" via your newsreader so
that others may learn and benefit from your issue.
========================================
=============
Business-Critical Phone Support (BCPS) provides you with technical phone
support at no charge during critical LAN outages or "business down"
situations. This benefit is available 24 hours a day, 7 days a week to all
Microsoft technology partners in the United States and Canada.
This and other support options are available here:
BCPS:
https://partner.microsoft.com/US/te...erview/40010469
Others:
https://partner.microsoft.com/US/te...upportoverview/
If you are outside the United States, please visit our International
Support page:
http://support.microsoft.com/defaul...rnational.aspx.
========================================
=============
This posting is provided "AS IS" with no warranties, and confers no rights.