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
LVL 1
marrowyungSenior Technical architecture (Data)Asked:
Who is Participating?
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
marrowyungSenior Technical architecture (Data)Author Commented:
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
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
Keep up with what's happening at Experts Exchange!

Sign up to receive Decoded, a new monthly digest with product updates, feature release info, continuing education opportunities, and more.

marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Are you obtaining any errors while trying to restore it using either Red Gate or native methods wizard approach instead of TSQL..
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
rrjegan17,

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

DBA100.
0
marrowyungSenior Technical architecture (Data)Author Commented:
I know this is just amazing to see this, I just surprise why changing simple statement give error but working..
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
"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
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
>> 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..
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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.
0
marrowyungSenior Technical architecture (Data)Author Commented:
then how can I solve this?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
marrowyungSenior Technical architecture (Data)Author Commented:
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.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
marrowyungSenior Technical architecture (Data)Author Commented:
Redgate site seems has no response for this.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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..
0
marrowyungSenior Technical architecture (Data)Author Commented:
what document ?
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
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
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.

Question has a verified solution.

Are you are experiencing a similar issue? Get a personalized answer when you ask a related question.

Have a better answer? Share it in a comment.