October 31, 2018 at 2:24 am
Hi
Abit of background, we use a software provider for entering our data and each night they send over a backup of the data. Which is then used so we can fix any integrity issues working off yesterdays data the next day. So every morning our database restores automatically from an app we use using there backup they provide.
Recently the database im working on keeps getting stuck in recovery/restore mode.
I can normally get around this by using a very old back up to get it out of this mode. Then this will allow me to restore the database using the most recent backup in our app.
Is there any reasons why the database keeps going into restore/recovery when the database is being restored each morning? It seems to be happening more frequently.
Is there anything i can do to keep track of these problems? I've been chucked in the deep end with this so learning quickly.
Thanks
October 31, 2018 at 2:32 am
I have many questions!
- Are you restoring the database yourself each day, or is somebody doing it for you?
- Has the restore definitely finished when you go to use the database? (You can check this by looking at sys.dm_exec_requests in the percent_complete field.)
- Assuming the restore has finished, can you bring it online with restore log databasename?
October 31, 2018 at 2:54 am
Beatrix Kiddo - Wednesday, October 31, 2018 2:32 AMI have many questions!- Are you restoring the database yourself each day, or is somebody doing it for you? - No we use a custom app that does all this early hours of the morning.
- Has the restore definitely finished when you go to use the database? (You can check this by looking at sys.dm_exec_requests in the percent_complete field.)
- Assuming the restore has finished, can you bring it online with restore log databasename?
Are you restoring the database yourself each day, or is somebody doing it for you? - No we use a custom app that does all this early hours of the morning. It usually tells us when everything has completed.
- Has the restore definitely finished when you go to use the database? (You can check this by looking at sys.dm_exec_requests in the percent_complete field.) - I have run this and the only running status i believe is from the select query to run sys.dm_exec_requests everything else is sleeping or background. All percent_complete fields are zero.
- Assuming the restore has finished, can you bring it online with restore log databasename? - I have not tried this please could you explain abit more? I have tried to use the ALTER DATABASE (NAME) SET ONLINE statement but it returns with an error saying database is in single user mode.
October 31, 2018 at 3:08 am
SqlGeek89 - Wednesday, October 31, 2018 2:54 AMAre you restoring the database yourself each day, or is somebody doing it for you? - No we use a custom app that does all this early hours of the morning. It usually tells us when everything has completed.
Probably need to check the code behind the app. You can run a trace or Extended Events session if you don't have access to the source coe
Assuming the restore has has has finished, can you bring it online with restore log databasenamerestore log databasenamerestore log databasename? - I have not tried this please could you explain abit more? I have tried to use the ALTER DATABASE (NAME) SET ONLINE statement but it returns with an error saying database is in single user mode.
You can only set a database online if its current status is OFFLINE (or, trivially, ONLINE). Try RESTORE DATABASE myDatabase WITH RSECOVERY;
John
October 31, 2018 at 3:34 am
John Mitchell-245523 - Wednesday, October 31, 2018 3:08 AMSqlGeek89 - Wednesday, October 31, 2018 2:54 AMAre you restoring the database yourself each day, or is somebody doing it for you? - No we use a custom app that does all this early hours of the morning. It usually tells us when everything has completed.Probably need to check the code behind the app. You can run a trace or Extended Events session if you don't have access to the source coe
Assuming the restore has has has finished, can you bring it online with restore log databasenamerestore log databasenamerestore log databasename? - I have not tried this please could you explain abit more? I have tried to use the ALTER DATABASE (NAME) SET ONLINE statement but it returns with an error saying database is in single user mode.
You can only set a database online if its current status is OFFLINE (or, trivially, ONLINE). Try RESTORE DATABASE myDatabase WITH RSECOVERY;
John
If i try to run RESTORE DATABASE myDatabase WITH RECOVERY i get the following errors
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
October 31, 2018 at 3:41 am
Try switching to the master database and then running the restore code again. Or you could do alter database NAME set restricted_user with rollback immediate first, then try again.
October 31, 2018 at 3:50 am
SqlGeek89 - Wednesday, October 31, 2018 3:34 AMJohn Mitchell-245523 - Wednesday, October 31, 2018 3:08 AMSqlGeek89 - Wednesday, October 31, 2018 2:54 AMAre you restoring the database yourself each day, or is somebody doing it for you? - No we use a custom app that does all this early hours of the morning. It usually tells us when everything has completed.Probably need to check the code behind the app. You can run a trace or Extended Events session if you don't have access to the source coe
Assuming the restore has has has finished, can you bring it online with restore log databasenamerestore log databasenamerestore log databasename? - I have not tried this please could you explain abit more? I have tried to use the ALTER DATABASE (NAME) SET ONLINE statement but it returns with an error saying database is in single user mode.
You can only set a database online if its current status is OFFLINE (or, trivially, ONLINE). Try RESTORE DATABASE myDatabase WITH RSECOVERY;
John
If i try to run RESTORE DATABASE myDatabase WITH RECOVERY i get the following errors
Msg 3101, Level 16, State 1, Line 1
Exclusive access could not be obtained because the database is in use.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Check the existing session used for "myDatabase " and kill that make it single user mode and do a restore.
Muthukkumaran Kaliyamoorthy
https://www.sqlserverblogforum.com/
October 31, 2018 at 4:19 am
The earlier database backup restores fine but if i run the ALTER database in single user mode i get this error.
Msg 5052, Level 16, State 1, Line 1
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
However it clears the restoring state but as soon as i try to restore the most recent back up it gets stuck in restore mode again.
October 31, 2018 at 4:49 am
So i have tried to restore via the GUI and receive this error now.
The media family on device '**********************.Lts.bak' is incorrectly formed. SQL Server cannot process this media family.
RESTORE HEADERONLY is terminating abnormally. (Microsoft SQL Server, Error: 3241)
I believe this means the backup is from a newer version of sql and isn't compatible with the version of sql im using?
If this is the case is it likely that this app i use does something to this back up so it restores fine in normal circumstances?
Sorry for so many questions
October 31, 2018 at 5:15 am
No, you should still be able to run RESTORE HEADERONLY even if the backup is from a newer version. In any case, I'd be surprised if your app could do any magic to make the backup look as if it were from an older version. What's more likely is that your backup file isn't a backup file at all, or that it has some sort of corruption on it. What happens if you run the RESTORE HEADERONLY statement from a query window?
You might try querying the restorehistory table in msdb to find out which restores have actually taken place.
John
October 31, 2018 at 5:39 am
If i run the restore headeronly i get a similar error.
Msg 3241, Level 16, State 0, Line 1
The media family on device **************************.Lts.bak' is incorrectly formed. SQL Server cannot process this media family.
Msg 3013, Level 16, State 1, Line 1
RESTORE HEADERONLY is terminating abnormally.
Has the .Lts.Bak got anything to do with this? seems i cant do anything with a file ending like this in the GUI
I can run a really early backup we have from 2009 which is just .bak with no problems
October 31, 2018 at 5:48 am
No, that's nothing to do with it. .BAK is just a convention: you can give the file any extension you like and it will still work. This really does point to the backup files with which you are being supplied being either corrupt or not backups at all, especially given that you're able to restore an earlier backup. Try speaking to the people who supply the backup files.
John
October 31, 2018 at 6:41 am
After narrowing all this down it turns out our software provider hasn't informed us of an update on there extractor tool that we use in our app.
Thanks for you help everyone! I have learnt alot today!
Viewing 13 posts - 1 through 12 (of 12 total)
You must be logged in to reply to this topic. Login to reply