Link to home
Start Free TrialLog in
Avatar of marrowyung
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
Avatar of Raja Jegan R
Raja Jegan R
Flag of India image

>> user account do not have permission to alter database, user database can't be set in single user mode.

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..
Avatar of marrowyung
marrowyung

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
>> 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..
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\SystemLog\'
If ((select count(*) from #temp)=0)
exec master.dbo.xp_create_subdir 'd:\databasefiles\SystemLog\'
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\Systemlog\Systemlog_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\SystemLog\'
If ((select count(*) from #temp)=0)
exec master.dbo.xp_create_subdir 'd:\databasefiles\SystemLog\'
truncate table #temp

execute master..sqlbackup '-SQL "restore database [SystemLog] FROM DISK=''d:\SystemLog.sqb''
with recovery, disconnect_existing, move ''Staging'' to ''d:\databasefiles\SystemLog\SystemLog.mdf'',
move ''data1'' to ''d:\databasefiles\SystemLog\Systemlog_1.ndf'',
....

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..
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.
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.
rrjegan17,

Glad to hear the upgrade of SP and OS because of this. I will try.

DBA100.
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.
"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
>> 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..
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.
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..
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.
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..
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..
ASKER CERTIFIED SOLUTION
Avatar of marrowyung
marrowyung

Link to home
membership
This solution is only available to members.
To access this solution, you must be a member of Experts Exchange.
Start Free Trial
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