Hi,
I have a problem during the recovery of the sql db dump.
A user had sql server installed in driver c, my application backed up a db
using 'backup database'. Later on the user reinstalled sql server to driver
d, and ran my application to do the recovery. The restore db was successful
but after restart my app it complained that cannot connect to the db. I foun
d
the db was restored in driver c but not in driver d.
How can I fix this problem?
Thanks
WilliamThe new installation of SQL server isn't aware of the restored database. Use
the sp_attach_ db stored procedure (or wizards in Enterprise Manager) to add
the database to the server.
"William" wrote:
> Hi,
> I have a problem during the recovery of the sql db dump.
> A user had sql server installed in driver c, my application backed up a db
> using 'backup database'. Later on the user reinstalled sql server to drive
r
> d, and ran my application to do the recovery. The restore db was successfu
l
> but after restart my app it complained that cannot connect to the db. I fo
und
> the db was restored in driver c but not in driver d.
> How can I fix this problem?
> Thanks
> Williamsql
Showing posts with label recovery. Show all posts
Showing posts with label recovery. Show all posts
Wednesday, March 28, 2012
Monday, March 26, 2012
Restore Database problem - LOADING forever
I had to restore a database late this afternoon. I have the database set to FULL recovery. Database backups are performed nightly and transaction log backups are performed every other hour. I decided to perform a point-in-time restore. When I restored this way everything seems to go ok and it finishes. Then the database is grayed and says "Loading". Although I tried 4 separate times, one time allowing over an hour, the grayed out database and "Loading" never goes away.
Freaking out I deleted the "Loading" database (didn't delete logs and backup files) and tried a manual restore from the previous night's backup file. It attached and restored properly and was ready to go in 2 minutes.
Of course I wanted to get the transaction log files restored too, since it had work from earlier today. So I tried another restore via point-in-time and got the same old messages. Currently, the database is running with the previous night's backup restored but the users aren't too keen on having to do 5 hours worth of work to catch up to the previous transaction log backup come Monday morning.
Any suggestions?
Thanks,
JBHow are you doing the restore? And why do you want to restore to a point in time?
Do it via query analyser with standby - this will enable you to look at the database between restores.
Do the full restore - look at it.
Now do the tr logs in turn - checking the database between each one.
Now you can do the last one with the point in time recovery.
If that fails then go back and redo up to the last one that restored successfully. At least you will lose at the most 2 hours.|||The poster probably did not click on Refresh menu item on Databases folder in EM.sql
Freaking out I deleted the "Loading" database (didn't delete logs and backup files) and tried a manual restore from the previous night's backup file. It attached and restored properly and was ready to go in 2 minutes.
Of course I wanted to get the transaction log files restored too, since it had work from earlier today. So I tried another restore via point-in-time and got the same old messages. Currently, the database is running with the previous night's backup restored but the users aren't too keen on having to do 5 hours worth of work to catch up to the previous transaction log backup come Monday morning.
Any suggestions?
Thanks,
JBHow are you doing the restore? And why do you want to restore to a point in time?
Do it via query analyser with standby - this will enable you to look at the database between restores.
Do the full restore - look at it.
Now do the tr logs in turn - checking the database between each one.
Now you can do the last one with the point in time recovery.
If that fails then go back and redo up to the last one that restored successfully. At least you will lose at the most 2 hours.|||The poster probably did not click on Refresh menu item on Databases folder in EM.sql
Friday, March 23, 2012
RESTORE DATABASE is terminating abnormally
Hi DBA there,
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
HawleyPermission thing, perhaps? Does the service account have permissions to access the backup file?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
HawleyPermission thing, perhaps? Does the service account have permissions to access the backup file?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
RESTORE DATABASE is terminating abnormally
Hi DBA there,
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
Hawley
Permission thing, perhaps? Does the service account have permissions to access the backup file?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
Hawley
Permission thing, perhaps? Does the service account have permissions to access the backup file?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>
RESTORE DATABASE is terminating abnormally
Hi DBA there,
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
HawleyPermission thing, perhaps? Does the service account have permissions to acce
ss the backup file?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>sql
I am restoring a bakup from CD with SQL Statement:
RESTORE DATABASE WITE
FROM disk ='e:\wite.bak'
WITH RECOVERY,
MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\wite_mdf.mdf',
MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
Server\MSSQL\log\wite_log.ldf'
i have also use RESTORE FILELISTONLY to ensure the logical name
Unfortunately, the restore is unsuccessful with the result:
Server: Msg 3203, Level 16, State 1, Line 1
Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
more details.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Both physical files wite_data.mdf and wite_log.ldf are in the respective
folders already. On Enterprise manager, the databse WITE status shows
'Loading'.
Please help...
HawleyPermission thing, perhaps? Does the service account have permissions to acce
ss the backup file?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>|||Copy the back file from CD to hard disk and then try restoring.
Thanks
Hari
"HawleyBeach" <HawleyBeach@.discussions.microsoft.com> wrote in message
news:A07CD252-F19A-4CED-82D7-A5D7185DC181@.microsoft.com...
> Hi DBA there,
> I am restoring a bakup from CD with SQL Statement:
> RESTORE DATABASE WITE
> FROM disk ='e:\wite.bak'
> WITH RECOVERY,
> MOVE 'wfield_dat' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\Data\wite_mdf.mdf',
> MOVE 'wfield_log' TO 'c:\Program Files\Microsoft SQL
> Server\MSSQL\log\wite_log.ldf'
> i have also use RESTORE FILELISTONLY to ensure the logical name
> Unfortunately, the restore is unsuccessful with the result:
> Server: Msg 3203, Level 16, State 1, Line 1
> Read on 'e:\wite.bak' failed, status = 1. See the SQL Server error log for
> more details.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Both physical files wite_data.mdf and wite_log.ldf are in the respective
> folders already. On Enterprise manager, the databse WITE status shows
> 'Loading'.
> Please help...
> Hawley
>sql
Tuesday, March 20, 2012
Restore database and trx log
Hi:
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoonHi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they ap
pended to the backup
file (?) and then restored one of the older backups on that file? Use RESTOR
E HEADERONLY to
investigate, and the FILE option of the restore command to specify which to
restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon|||Atenza:
This make sense.
Thanks.
JCVoon|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegroups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoonHi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they ap
pended to the backup
file (?) and then restored one of the older backups on that file? Use RESTOR
E HEADERONLY to
investigate, and the FILE option of the restore command to specify which to
restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon|||Atenza:
This make sense.
Thanks.
JCVoon|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegroups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>
Restore database and trx log
Hi:
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoonHi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they appended to the backup
file (?) and then restored one of the older backups on that file? Use RESTORE HEADERONLY to
investigate, and the FILE option of the restore command to specify which to restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon|||Atenza:
This make sense.
Thanks.
JCVoon|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegroups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>|||Correct.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegroups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoonHi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
--
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegroups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they appended to the backup
file (?) and then restored one of the older backups on that file? Use RESTORE HEADERONLY to
investigate, and the FILE option of the restore command to specify which to restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegroups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon|||Atenza:
This make sense.
Thanks.
JCVoon|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegroups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>|||Correct.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegroups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>
Restore database and trx log
Hi:
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoon
Hi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegr oups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>
|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegr oups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>
|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon
|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they appended to the backup
file (?) and then restored one of the older backups on that file? Use RESTORE HEADERONLY to
investigate, and the FILE option of the restore command to specify which to restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon
|||Atenza:
This make sense.
Thanks.
JCVoon
|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegr oups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>
My db is set to full recovery mode. Do i need to restore the latest
transaction log after restore a latest complete db backup ?
eg:
1.00pm Trx log backup
2.00pm Complete db backup
3.00pm Trx log backup
There is no activity after 2.00pm, when i restore the 2.00pm db backup
to another server, do i need to restore 3.00pm trx log backup ? Will
the complete db backup include the data between 1.00pm and 2.00pm ?
Please help.
Thanks
JCVoon
Hi
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
No. Even though you restore full backup from ther previous week and
applied all log files including this one made on 1PM you will not have the
data between 1PM and 2PM.
If it is crtical for you so consider backu log file every 15 minute for
exampe
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegr oups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>
|||The database backup made a 2pm will include all transactions up through 2
pm. If you restore the backup with recovery, you should have the database as
it was as of 2 pm.
HTH
Kalen Delaney, SQL Server MVP
www.solidqualitylearning.com
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142835431.892296.151550@.z34g2000cwc.googlegr oups.com...
> Hi:
> My db is set to full recovery mode. Do i need to restore the latest
> transaction log after restore a latest complete db backup ?
> eg:
> 1.00pm Trx log backup
> 2.00pm Complete db backup
> 3.00pm Trx log backup
> There is no activity after 2.00pm, when i restore the 2.00pm db backup
> to another server, do i need to restore 3.00pm trx log backup ? Will
> the complete db backup include the data between 1.00pm and 2.00pm ?
> Please help.
>
> Thanks
> JCVoon
>
|||Uri Dimant, Kalen Delaney:
Thanks for the quick reply.
I did a small test, i create a new db and pump in some data, then i
perform a trxlog backup, after the log backup, i pump in another chunk
of data, then i perform full db backup, i restore the full db backup to
another db and i notice that the data that i insert after the 1st
trxlog backup is exist in the new db. But my customer complain that
after he restore the full db backup, data enterd before full db backup
is not exist ! Can u please explain what is going wrong ?
Thanks
JCVoon
|||maybe when you perform full db backup, the transaction is not yet committed
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||Can you provide a script yopu tested on?
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||> But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist !
That does not happen. Most likely they restored incorrectly. Perhaps they appended to the backup
file (?) and then restored one of the older backups on that file? Use RESTORE HEADERONLY to
investigate, and the FILE option of the restore command to specify which to restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142839424.157319.177960@.i40g2000cwc.googlegr oups.com...
> Uri Dimant, Kalen Delaney:
> Thanks for the quick reply.
> I did a small test, i create a new db and pump in some data, then i
> perform a trxlog backup, after the log backup, i pump in another chunk
> of data, then i perform full db backup, i restore the full db backup to
> another db and i notice that the data that i insert after the 1st
> trxlog backup is exist in the new db. But my customer complain that
> after he restore the full db backup, data enterd before full db backup
> is not exist ! Can u please explain what is going wrong ?
> Thanks
> JCVoon
>
|||Tibor Karaszi:
Thanks for your reply.
So i can confirm that restoring the full db backup will bring the db to
the state that i perform the full db backup right ?
Any for the uncommitted transaction during the full db backup, i have
to backup the trxlog after perform the full db backup and apply it to
the new db right ?
Thanks
JCVoon
|||Atenza:
This make sense.
Thanks.
JCVoon
|||Hi
When database is restoring CHECKPOINT occures. It checks what transactions
to undo or redo
"jcvoon" <jcvoon@.maximas.com.my> wrote in message
news:1142844757.254805.129640@.i39g2000cwa.googlegr oups.com...
> Tibor Karaszi:
> Thanks for your reply.
> So i can confirm that restoring the full db backup will bring the db to
> the state that i perform the full db backup right ?
> Any for the uncommitted transaction during the full db backup, i have
> to backup the trxlog after perform the full db backup and apply it to
> the new db right ?
> Thanks
> JCVoon
>
Wednesday, March 7, 2012
restore a sql server 6.5 db to sql server 2000
for disastor recovery purposes i have a user request to restore a sql server 6.5 base backup(.dat file) into sql server 7.0 or 2000. can this be done in a restore on a different server?? all servers are windows 2000Simple answer is NO.
http://www.mssqlcity.com/FAQ/BackRest/Res65Back.htm
But try this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx#EQFAC|||where can i find this upgrade wizard in sql 2000??|||Information is all in the same page.
Note: To run the SQL Server Upgrade Wizard, you must have an instance of SQL Server 2000 already installed on your computer.
1. On the Start menu, point to Programs/Microsoft SQL Server-Switch, click SQL Server Upgrade Wizard, and then click Next.
2. Select Named pipe; then click Next.
3. In Export server (6.5), in the Server name box, enter the name of the local or remote computer on which SQL Server 6.5 resides.
4. In the Administrator password ('sa') box, enter the sa password for SQL Server 6.5, and then click Next.
Note: Unless you have changed it, the system administrator (sa) password for SQL Server 2000 is blank.
5. Include the databases to upgrade. Move any database not to include in the disk space estimation to the Exclude list, and then click Next.
6. Select Use the default configuration or edit the default; then click Edit.
The SQL Server Upgrade Wizard layout utility appears, showing the proposed layout of the SQL Server 2000 data files.
7. Click Advanced.
8. Click an object in the Proposed database layout box to view details in the Object details box.
9. The Drive summary box shows the estimated size of all SQL Server 2000 data files and the free disk space left on all of the local fixed disks. On the Options menu, select Freespace includes 6.5 files to view the free space that would exist if the SQL Server 6.5 data files were deleted.
10. Click Accept to return to the Database Creation dialog box.
11. Click Cancel to quit the SQL Server Upgrade Wizard.
http://www.mssqlcity.com/FAQ/BackRest/Res65Back.htm
But try this:
http://www.microsoft.com/technet/prodtechnol/sql/2000/deploy/sqlugrd.mspx#EQFAC|||where can i find this upgrade wizard in sql 2000??|||Information is all in the same page.
Note: To run the SQL Server Upgrade Wizard, you must have an instance of SQL Server 2000 already installed on your computer.
1. On the Start menu, point to Programs/Microsoft SQL Server-Switch, click SQL Server Upgrade Wizard, and then click Next.
2. Select Named pipe; then click Next.
3. In Export server (6.5), in the Server name box, enter the name of the local or remote computer on which SQL Server 6.5 resides.
4. In the Administrator password ('sa') box, enter the sa password for SQL Server 6.5, and then click Next.
Note: Unless you have changed it, the system administrator (sa) password for SQL Server 2000 is blank.
5. Include the databases to upgrade. Move any database not to include in the disk space estimation to the Exclude list, and then click Next.
6. Select Use the default configuration or edit the default; then click Edit.
The SQL Server Upgrade Wizard layout utility appears, showing the proposed layout of the SQL Server 2000 data files.
7. Click Advanced.
8. Click an object in the Proposed database layout box to view details in the Object details box.
9. The Drive summary box shows the estimated size of all SQL Server 2000 data files and the free disk space left on all of the local fixed disks. On the Options menu, select Freespace includes 6.5 files to view the free space that would exist if the SQL Server 6.5 data files were deleted.
10. Click Accept to return to the Database Creation dialog box.
11. Click Cancel to quit the SQL Server Upgrade Wizard.
Saturday, February 25, 2012
restore a database to point in time
Should I need to restore a database to a specific point in
time, assuming full recovery model, are these steps
correct?
1. Back up Transactin Log
2. Restore Last Full backup and any differential backups
before the desired time.
3. Restore Transaction Log to specific point in time.
I am under the assumption that only tranasaction log
backups can be restored to a specific point in time, not
database backups, is this correct?Dan
You are correct that only transaction logs can restore to
a point in time.
If you are using differential backups, you only need to
restore the most recent one.
Your plan should work fine.
Regards
John|||Yes, you can only do point-in-time with transaction log restores.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6d01c39e35$08002b70$a501280a@.phx.gbl...
Should I need to restore a database to a specific point in
time, assuming full recovery model, are these steps
correct?
1. Back up Transactin Log
2. Restore Last Full backup and any differential backups
before the desired time.
3. Restore Transaction Log to specific point in time.
I am under the assumption that only tranasaction log
backups can be restored to a specific point in time, not
database backups, is this correct?
time, assuming full recovery model, are these steps
correct?
1. Back up Transactin Log
2. Restore Last Full backup and any differential backups
before the desired time.
3. Restore Transaction Log to specific point in time.
I am under the assumption that only tranasaction log
backups can be restored to a specific point in time, not
database backups, is this correct?Dan
You are correct that only transaction logs can restore to
a point in time.
If you are using differential backups, you only need to
restore the most recent one.
Your plan should work fine.
Regards
John|||Yes, you can only do point-in-time with transaction log restores.
--
HTH,
Vyas, MVP (SQL Server)
http://vyaskn.tripod.com/
What hardware is your SQL Server running on?
http://vyaskn.tripod.com/poll.htm
"Dan" <anonymous@.discussions.microsoft.com> wrote in message
news:0d6d01c39e35$08002b70$a501280a@.phx.gbl...
Should I need to restore a database to a specific point in
time, assuming full recovery model, are these steps
correct?
1. Back up Transactin Log
2. Restore Last Full backup and any differential backups
before the desired time.
3. Restore Transaction Log to specific point in time.
I am under the assumption that only tranasaction log
backups can be restored to a specific point in time, not
database backups, is this correct?
Subscribe to:
Posts (Atom)