Friday, February 17, 2012

Database will not restore from backup

have a production database running on SQL 2000 Enterprise with service pack
3, that until monday had a DTS package copying to another SQL Server for
development work. On monday a developer ran the package manually and it
failed with the error message "bulk copy transaction failed" error code
80045707. Since then it will not copy the database to the other SQL instance.
Tried to restore the databse on the same SQL instance to another database
name but the restore hangs with the message "loading".
I cannot put the database in into restricted access with SQL Enterprise
Management.
I have run DBCC DBCHECK and get no error reported.
Any suggestions?
Regards
Drew
I can do a restore of the database but still cannot run the DTS package to
copy the database even on the same server.
"drew" wrote:

> have a production database running on SQL 2000 Enterprise with service pack
> 3, that until monday had a DTS package copying to another SQL Server for
> development work. On monday a developer ran the package manually and it
> failed with the error message "bulk copy transaction failed" error code
> 80045707. Since then it will not copy the database to the other SQL instance.
> Tried to restore the databse on the same SQL instance to another database
> name but the restore hangs with the message "loading".
> I cannot put the database in into restricted access with SQL Enterprise
> Management.
> I have run DBCC DBCHECK and get no error reported.
>
> Any suggestions?
>
> Regards
> Drew
|||Hi,
Can you execute the Restore database using Query analyzer .
1. Using Restore filelistonly command identify the logical file names of the
database backup file
RESTORE FILELISTONLY from disk='c:\backup\dbname.bak'
2. With the output of the above query use RESTORE database
RESTORE DATABASE <newdbname> from disk='c:\backup\dbname.bak'
WITH move 'logical_mdf_filename' to 'new physical name with path',
move 'logical_ldf_filename' to 'new physical log name withpa
th',stats=10
Stats=10 will show the progress of restore.
Thanks
Hari
MCDBA
"drew" <drew@.discussions.microsoft.com> wrote in message
news:78023771-E516-4373-A1D3-BC922A4397D4@.microsoft.com...
> have a production database running on SQL 2000 Enterprise with service
> pack
> 3, that until monday had a DTS package copying to another SQL Server for
> development work. On monday a developer ran the package manually and it
> failed with the error message "bulk copy transaction failed" error code>
> 80045707. Since then it will not copy the database to the other SQL
> instance.
> Tried to restore the databse on the same SQL instance to another database
> name but the restore hangs with the message "loading".
> I cannot put the database in into restricted access with SQL Enterprise
> Management.
> I have run DBCC DBCHECK and get no error reported.
>
> Any suggestions?
>
> Regards
> Drew
|||I have managed to get the backup working. I am now trying to figure out why
the DTS package is failing. The paackage stops at the same table each time so
I will try and copy the database between servers less that table to see if
that clears the problem.
Thanks Drew
"Hari Prasad" wrote:

> Hi,
> Can you execute the Restore database using Query analyzer .
> 1. Using Restore filelistonly command identify the logical file names of the
> database backup file
> RESTORE FILELISTONLY from disk='c:\backup\dbname.bak'
> 2. With the output of the above query use RESTORE database
> RESTORE DATABASE <newdbname> from disk='c:\backup\dbname.bak'
> WITH move 'logical_mdf_filename' to 'new physical name with path',
> move 'logical_ldf_filename' to 'new physical log name withpa
> th',stats=10
> Stats=10 will show the progress of restore.
> Thanks
> Hari
> MCDBA
>
> "drew" <drew@.discussions.microsoft.com> wrote in message
> news:78023771-E516-4373-A1D3-BC922A4397D4@.microsoft.com...
>
>