I am in the process of restoring many SQL databases (1000+). Many of them restore with no issue, however some get stuck in the “Restoring” state. I have read countless articles on the internet regarding this and can’t find something that works. I figure I would share this with the EE community to see if anyone has any ideas.
Below is an example of the error message that I receive on some of these databases:
Msg 3183, Level 16, State 2, Line 2
RESTORE detected an error on page (0:0) in database "_db_200508070216" as read from the backup set.
Msg 3013, Level 16, State 1, Line 2
RESTORE DATABASE is terminating abnormally.
Msg 5011, Level 14, State 7, Line 1
User does not have permission to alter database '_db_200508070216', or the database does not exist.
Msg 5069, Level 16, State 1, Line 1
ALTER DATABASE statement failed.
I created the stored procedure “RestoreDatabase “to restore the SQL databases. I have the code for this at the end of the posting.
Below I have an example of what the call to “RestoreDatabase” looks like:
EXEC [dbo].[RestoreDatabase] '_db_200508070216' , '\\Vboxsvr\x_drive\_db_200508070216.BAK'
Code for RestoreDatabase
/****** Object: StoredProcedure [dbo].[RestoreDatabase] Script Date: 11/10/2014 13:07:13 ******/
SET ANSI_NULLS ON
SET QUOTED_IDENTIFIER ON
ALTER PROCEDURE [dbo].[RestoreDatabase]
-- Add the parameters for the stored procedure here
@dataBaseNameVarchar as varchar(250),
@bakPathVarchar as varchar(max)
-- SET NOCOUNT ON added to prevent extra result sets from
-- interfering with SELECT statements.
SET NOCOUNT ON;
'create database [' + @dataBaseNameVarchar + ']')
ALTER DATABASE [' + @dataBaseNameVarchar + ']
SET SINGLE_USER WITH
RESTORE DATABASE [' + @dataBaseNameVarchar + ']
FROM DISK = ''' + @bakPathVarchar + '''
WITH REPLACE, RECOVERY')
execute('ALTER DATABASE [' + @dataBaseNameVarchar + '] SET MULTI_USER')