2

I am trying to copy over the live database from one server and restore it on the dev server using SQL Server 2005. I have made a backup of the live db and copied the .bak file over to the dev server. I am trying to run this script and I keep getting errors:

ALTER DATABASE Portal SET SINGLE_USER WITH ROLLBACK IMMEDIATE
GO

ALTER DATABASE Portal SET OFFLINE WITH ROLLBACK IMMEDIATE 
GO

RESTORE DATABASE Portal 
FROM DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\Portal.bak' 
WITH FILE = 1, NOUNLOAD, REPLACE, STATS = 10
GO

exec dbo.sp_fixusers
GO 

ALTER DATABASE Portal SET MULTI_USER
GO

ALTER DATABASE Portal SET ONLINE WITH ROLLBACK IMMEDIATE
GO

Errors:

Msg 5052, Level 16, State 1, Line 2
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
Msg 5052, Level 16, State 1, Line 2
ALTER DATABASE is not permitted while a database is in the Restoring state.
Msg 5069, Level 16, State 1, Line 2
ALTER DATABASE statement failed.
10 percent processed.
20 percent processed.
30 percent processed.
40 percent processed.
50 percent processed.
60 percent processed.
70 percent processed.
80 percent processed.
90 percent processed.
100 percent processed.
Processed 307768 pages for database 'Portal', file 'Portal_Data' on file 1.
Processed 127 pages for database 'Portal', file 'Portal_Log' on file 1.
RESTORE DATABASE successfully processed 307895 pages in 130.538 seconds (19.322 MB/sec).
Msg 2812, Level 16, State 62, Line 1
Could not find stored procedure 'dbo.sp_fixusers'.

Improve this question
2
  • sp_fixusers is not one of the MS-supplied stored procedures found in master. If you google sp_fixusers you should find examples of what you are looking for, i.e. scripts that call the MS-supplied stored procedure sp_change_users_login.
    – Chris Kelly
    Mar 26, 2012 at 19:38
  • Where does sp_fixusers live? Just because you restore your database doesn't mean the database context will change to that database. So it's looking for sp_fixusers in your current database (actually, because of the sp_* it is initially looking in master).
    – Thomas Stringer
    Mar 28, 2012 at 15:52

1 Answer 1

Reset to default
2

You cannot set your database to single-user mode or offline because your database is already in a RESTORING state.

Second, your script tries to use the stored procedure sp_fixusers. This is a custom stored procedure to fix orphaned users. If you need to use this stored procedure, you need to re-create it first, using the script in the link above. But it is possible that your databases uses a different version and it will be better if you use the sp_fixusers from your server/database.

Improve this answer

Your Answer

By clicking “Post Your Answer”, you agree to our terms of service and acknowledge that you have read and understand our privacy policy and code of conduct.