marrowyung
asked on
regate restore error
Dear all,
I don't know why I can do only this command alone without any error message:
alter database <dbname> set single_user with rollback immediate
drop database <dbname>
But when I add the redgate restore command after that, it should be message like:
user account do not have permission to alter database, user database can't be set in single user mode.
At the end, the DB can be store succesfully, any idea why so strange?
DBA100
I don't know why I can do only this command alone without any error message:
alter database <dbname> set single_user with rollback immediate
drop database <dbname>
But when I add the redgate restore command after that, it should be message like:
user account do not have permission to alter database, user database can't be set in single user mode.
At the end, the DB can be store succesfully, any idea why so strange?
DBA100
ASKER
rrjegan17,
"Definitely a permission issue.
User account under which you run those scripts needs to have db_owner privilege on that database or sysadmin privilege to do these set of activities..
"
that user account is the sysadmin already but not dbowner, we try it using general DB backup in .bak format, it doesn't show this error.
DBA100
"Definitely a permission issue.
User account under which you run those scripts needs to have db_owner privilege on that database or sysadmin privilege to do these set of activities..
"
that user account is the sysadmin already but not dbowner, we try it using general DB backup in .bak format, it doesn't show this error.
DBA100
>> we try it using general DB backup in .bak format, it doesn't show this error.
Can you post the exact steps which you tried..
Can you post the exact steps which you tried..
ASKER
Step is hard to tell here.
Right now we have script to backup all files in BAK format, the format backup by SQL managment studio. We modify it to use redgate "sqlbackup" command and just after that this error show:
First version:
We use:
alter database SystemLog set single_user with rollback immediate
drop database SystemLog
Insert into #temp
exce master.sys.xp_dirtree 'd:\databasefiles\SystemLo g\'
If ((select count(*) from #temp)=0)
exec master.dbo.xp_create_subdi r 'd:\databasefiles\SystemLo g\'
truncate table #temp
restmore database [SystemLog] from disk=N'd:\SystemLog.bak with file=1,
Move N'Staging'
to N'D:\database files\SystemLog\Systemlog. mdf'
Move N'Data1' to N'D;\databasefiles\Systeml og\Systeml og_1.ndf'
....
Then we change to version 2:
alter database SystemLog set single_user with rollback immediate
drop database SystemLog
Insert into #temp
exce master.sys.xp_dirtree 'd:\databasefiles\SystemLo g\'
If ((select count(*) from #temp)=0)
exec master.dbo.xp_create_subdi r 'd:\databasefiles\SystemLo g\'
truncate table #temp
execute master..sqlbackup '-SQL "restore database [SystemLog] FROM DISK=''d:\SystemLog.sqb''
with recovery, disconnect_existing, move ''Staging'' to ''d:\databasefiles\SystemL og\SystemL og.mdf'',
move ''data1'' to ''d:\databasefiles\SystemL og\Systeml og_1.ndf'' ,
....
Any problem you can see? I just replace the restore part.
Right now we have script to backup all files in BAK format, the format backup by SQL managment studio. We modify it to use redgate "sqlbackup" command and just after that this error show:
First version:
We use:
alter database SystemLog set single_user with rollback immediate
drop database SystemLog
Insert into #temp
exce master.sys.xp_dirtree 'd:\databasefiles\SystemLo
If ((select count(*) from #temp)=0)
exec master.dbo.xp_create_subdi
truncate table #temp
restmore database [SystemLog] from disk=N'd:\SystemLog.bak with file=1,
Move N'Staging'
to N'D:\database files\SystemLog\Systemlog.
Move N'Data1' to N'D;\databasefiles\Systeml
....
Then we change to version 2:
alter database SystemLog set single_user with rollback immediate
drop database SystemLog
Insert into #temp
exce master.sys.xp_dirtree 'd:\databasefiles\SystemLo
If ((select count(*) from #temp)=0)
exec master.dbo.xp_create_subdi
truncate table #temp
execute master..sqlbackup '-SQL "restore database [SystemLog] FROM DISK=''d:\SystemLog.sqb''
with recovery, disconnect_existing, move ''Staging'' to ''d:\databasefiles\SystemL
move ''data1'' to ''d:\databasefiles\SystemL
....
Any problem you can see? I just replace the restore part.
Are you obtaining any errors while trying to restore it using either Red Gate or native methods wizard approach instead of TSQL..
ASKER
This is what my question is about, I get an error only when I change the native methods/format to redgate.
When I use the native backuop command, it say command execute successfully.
I just change to regate command it show me error message but the restore is ok. if the restore is not ok then the whoel thing will failed quickly ion less than 1 minutes but the whole thing run last 1 hour.
DBA100.
When I use the native backuop command, it say command execute successfully.
I just change to regate command it show me error message but the restore is ok. if the restore is not ok then the whoel thing will failed quickly ion less than 1 minutes but the whole thing run last 1 hour.
DBA100.
Ok.. In that case, try applying latest service packs for SQL Server 2005 and your Windows OS.
Also try contacting Red Gate directly regarding this as they provide supports for these kind of things.
Also try contacting Red Gate directly regarding this as they provide supports for these kind of things.
ASKER
rrjegan17,
Glad to hear the upgrade of SP and OS because of this. I will try.
DBA100.
Glad to hear the upgrade of SP and OS because of this. I will try.
DBA100.
ASKER
I know this is just amazing to see this, I just surprise why changing simple statement give error but working..
Your question is valid and no direct impact of that in your Restoration issue.
But asking you to try it since the actual method of troubleshooting didn't helped.
But asking you to try it since the actual method of troubleshooting didn't helped.
ASKER
"Your question is valid and no direct impact of that in your Restoration issue.
", I agee, let see. probably just remove the ALTER command, right? ahah
", I agee, let see. probably just remove the ALTER command, right? ahah
>> probably just remove the ALTER command, right? ahah
Regret for the delay in reply as I was in a short vacation..
And kindly explain what you meant by that..
Regret for the delay in reply as I was in a short vacation..
And kindly explain what you meant by that..
ASKER
ok, if removeing the ALTER comamnd in the script, which means I don't have to ALTER it anymore, then I will not get the error message.
Removing ALTER command can fail sometimes, if some other users are connected to that database..
Database needs to be in Single user mode for it to be restored from a backup, else you would be receiving errors too.
Database needs to be in Single user mode for it to be restored from a backup, else you would be receiving errors too.
ASKER
then how can I solve this?
Whatever method you try to restore a database, your database should be in single user mode at that time so that Restore works fine..
So you need to schedule or run Restore scripts only during non-peak hours or in other words no users are connected to your database..
So you need to schedule or run Restore scripts only during non-peak hours or in other words no users are connected to your database..
ASKER
rrjegan17,
This is not where the error encounter is about, the server we try to restore to, at this moment, we do not have access yet. So this is not the case.
As I said, this only show out error if I change the native backup command to redgate command, why the alter DB to single user mode command don't have problem when I am doing the backup using SQL natvice command instead of redgate command?
DBA100.
This is not where the error encounter is about, the server we try to restore to, at this moment, we do not have access yet. So this is not the case.
As I said, this only show out error if I change the native backup command to redgate command, why the alter DB to single user mode command don't have problem when I am doing the backup using SQL natvice command instead of redgate command?
DBA100.
Might probably a bug with Redgate backup..
Try contacting them for their support..
Kindly let me know whether I can contact them ( I am part of their community) and let you know about this..
Try contacting them for their support..
Kindly let me know whether I can contact them ( I am part of their community) and let you know about this..
ASKER
Redgate site seems has no response for this.
Let me check about it..
can you kindly consolidate it in a single document and send it to me so that I can check it out with high priority..
can you kindly consolidate it in a single document and send it to me so that I can check it out with high priority..
ASKER CERTIFIED SOLUTION
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Ok, It should have the following things:
* Your SQL Server version and Red Gate Backup version
* Are you obtaining the error for all databases or for a single database
* Are you obtaining error all over the time for this database
* Error obtained along with the scripts used
* Any other specific information which can help
* Your SQL Server version and Red Gate Backup version
* Are you obtaining the error for all databases or for a single database
* Are you obtaining error all over the time for this database
* Error obtained along with the scripts used
* Any other specific information which can help
Definitely a permission issue.
User account under which you run those scripts needs to have db_owner privilege on that database or sysadmin privilege to do these set of activities..
Grant either db_owner or sysadmin priviledge to the user and check it out..