Take the 2-minute tour ×
Database Administrators Stack Exchange is a question and answer site for database professionals who wish to improve their database skills and learn from others in the community. It's 100% free, no registration required.

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'.

share|improve this question
    
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 '12 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 '12 at 15:52
add comment

migrated from stackoverflow.com Mar 28 '12 at 15:47

This question came from our site for professional and enthusiast programmers.

1 Answer

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.

share|improve this answer
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.