September 5, 2011 at 9:45 am
Hi there,
I am trying to migrate some of SQL Server 2005 databases to SQL Server 2008 R2. I first used the backup script generated by SQL Server 2005 Management Studio to back up my databases; then used the restore script generated by SQL Server 2008 R2 Management Studio to back up my databases.
backup script example:
-------------------------------
BACKUP DATABASE [ADINA] TO DISK = N'F:\SQLbackup\UpgradeTo2008R2\ADINA.bak' WITH NOFORMAT, INIT, NAME = N'ADINA-Full Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10
GO
Restore script example:
-------------------------------
RESTORE DATABASE [ADINA] FROM DISK= N'\\192.168.100.100\Sqlbackup$\UpgradeTo2008R2\ADINA.bak' WITH FILE = 1, MOVE N'ADINA_dat' TO N'D:\SQLMSN\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ADINA.mdf', MOVE N'ADINA_log' TO N'D:\SQLMSN\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ADINA.ldf', NOUNLOAD, REPLACE, STATS = 10
GO
All the databases seemed to be restored fine. I could see the tables in each database. I also set up a maintenance plan to back up these database daily. However next day I found that the backup maintenance job failed for those databases that use "Full" recovery model with the following message:
Executing the query "BACKUP DATABASE [ADINA] TO DISK = N'F:\\SQLbackup\\..." failed with the following error: "Database 'ADINA' cannot be opened. It is in the middle of a restore.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database's status is 'Restoring' and I cannot see the tables. Why is that? I could see the tables immediately after restoring it.
The above approach worked for databases with "Simple" recovery model.
Anybody can point out what wrong I did? Your help is very much appreciated!
September 5, 2011 at 10:15 am
Your restore script does not have "WITH RECOVERY" option in it. Hence the database is in Recovering state. Please issue RESTORE DATABASE [your db name] WITH RECOVERY now. The database should be online.
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 5, 2011 at 10:23 am
Adiga (9/5/2011)
Your restore script does not have "WITH RECOVERY" option in it. Hence the database is in Recovering state.
Errr, RECOVERY is the default if nothing is specified. To get a DB into Restoring (not recovering) you have to explicitly state WITH NORECOVERY.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2011 at 10:23 am
I see your point. But that script was generated by SQL server studio management tool. I explicitly chose "Leave the database ready to use by rolling back uncommitted transactions. Additional transaction logs cannot be restored (RESTORE WITH RECOVERY)" in the "Options" step.... I could see the tables immediately the database was first restored....
September 5, 2011 at 10:25 am
GilaMonster (9/5/2011)
Adiga (9/5/2011)
Your restore script does not have "WITH RECOVERY" option in it. Hence the database is in Recovering state.Errr, RECOVERY is the default if nothing is specified. To get a DB into Restoring (not recovering) you have to explicitly state WITH NORECOVERY.
My bad, Gail. I responded little too quickly
Pradeep Adiga
Blog: sqldbadiaries.com
Twitter: @pradeepadiga
September 5, 2011 at 10:26 am
hl6a (9/5/2011)
All the databases seemed to be restored fine. I could see the tables in each database. I also set up a maintenance plan to back up these database daily. However next day I found that the backup maintenance job failed for those databases that use "Full" recovery model with the following message:Executing the query "BACKUP DATABASE [ADINA] TO DISK = N'F:\\SQLbackup\\..." failed with the following error: "Database 'ADINA' cannot be opened. It is in the middle of a restore.
BACKUP DATABASE is terminating abnormally.". Possible failure reasons: Problems with the query, "ResultSet" property not set correctly, parameters not set correctly, or connection not established correctly.
The database's status is 'Restoring' and I cannot see the tables. Why is that? I could see the tables immediately after restoring it
Something happened between the time you restored the database and when the backup ran. Someone either re-restored the database and specified NORECOVERY or someone ran a database or log backup and specified the NORECOVERY option
I've seen people select the 'backup the tail of the log and leave the database restoring' in the maintenance plans without understanding or reading up on what it does (no clue why someone would do something like that, but...). Check that someone didn't modify your maint plans (log backup ones) and do that.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
September 5, 2011 at 11:00 am
or the fact that its still actually restoring across the network location. Move the backup file locally and run the restore
-----------------------------------------------------------------------------------------------------------
"Ya can't make an omelette without breaking just a few eggs" 😉
September 5, 2011 at 11:20 am
Sir, you are absolutely right! I double-checked the log backup maintenance job, the option "Back up the tail of the log, and leave the database in the restoring state" was checked, I guess by default. Thank you so much! I will definitely learn from this mistake!
September 5, 2011 at 11:49 am
hl6a (9/5/2011)
Sir, you are absolutely right! I double-checked the log backup maintenance job, the option "Back up the tail of the log, and leave the database in the restoring state" was checked, I guess by default.
That is most certainly NOT set by default in a maintenance plan. Someone explicitly checked the option.
Gail Shaw
Microsoft Certified Master: SQL Server, MVP, M.Sc (Comp Sci)
SQL In The Wild: Discussions on DB performance with occasional diversions into recoverability
Viewing 9 posts - 1 through 8 (of 8 total)
You must be logged in to reply to this topic. Login to reply