December 30, 2009 at 4:01 am
Hi All,
I have encountered a strange issue on a production system of ours where all backups when restored to a new location are being restored as single_user. The database is not in single user mode when the backup is taken and it seems to affect all database backups on just one particular machine.
Has anyone encountered this before, or have any ideas what I can check to correct this behavior?
The server in question is Windows 2003 SP2 x64, SQL 2005 SP3 (9.0.4035). It does have Redgate backup installed but this happens for both native and redgate backups.
Any help would be much appreciated!
December 30, 2009 at 4:22 am
Is it definately single_user not restricted user? I know you can leave a database in restricted mode when preforming a restore but when i try to specify single_user SQL throws an error.
How are you performing the restore, GUI, Scripts, Agent Job etc?
Gethyn Elliswww.gethynellis.com
December 30, 2009 at 4:40 am
It is definitely single_user.
I have performed the restore using the GUI, a script and via a common stored procedure we use for redgate backups across all our servers.
At this point I am not convinced the issue is with the restore command, but something on the server where the backup is being taken...
December 30, 2009 at 4:49 am
So the job that takes the backup, there is nothing in there that pops the db in single user node, takes the backup, then pops it back into multi_user mode?
If you have users on when the backup is taken they will shout and scream if thats the case unless your backup is taken when no users are on - which is usually the case. So i suppose is feasable...
Gethyn Elliswww.gethynellis.com
December 30, 2009 at 6:16 am
No, I have taken a backup during the day using t-sql and it has definitely not put the db into single_user mode during the backup.
December 30, 2009 at 6:43 am
So when the backup is taken the DB is in multi-user mode but when restored onto another box the DB is in Single User mode.
Have you tried this on a different server? or with a different DB does the same thing happen?
I can't restore a database in Single user mode...SQL throws an error although I can restore it to restricted user.
Just trying to work out where the problem lies
Also is the restore good when you take the DB out of Single User mode?
Gethyn Elliswww.gethynellis.com
December 30, 2009 at 7:32 am
also check the SQL log for detailed messages around the restore and the steps that occur when the DB is brought online.
Cheers!
Craig Outcalt
December 30, 2009 at 9:15 am
I think I may have found a workaround - if I restore a full backup of the database over itself it should fix the issue happening with future backups. I need some downtime to test this theory, so I will update once tested.
Thanks for your help guys!
Viewing 8 posts - 1 through 7 (of 7 total)
You must be logged in to reply to this topic. Login to reply