Sql-server – Restoring a DB issues

sql server

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

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

Related Question