Can't Restore DB
-
hi everyone
I need to get a copy of a stored proc from backup b/c the one I am using in my current instance of the db isn't correct. I am not able to restore the db. What am I doing wrong? The name of the db is OptionsDB and I want to keep the current instance and call the restored one OptionsDBtemp. Once I get the stored proc from the temp instance of the DB then I will remove the temp db. I have attached a picture showing what I did:
Here is the full error message:
===================================
Restore of database 'OptionsDBtemp' failed. (Microsoft.SqlServer.Management.RelationalEngineTasks)
------------------------------
Program Location:
at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)
at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.Perform(ITaskExecutionContext context)
at Microsoft.SqlServer.Management.TaskForms.TaskExecutionManager.ExecuteTaskSequence(ISfcScriptCollector collector)
===================================
System.Data.SqlClient.SqlError: Exclusive access could not be obtained because the database is in use. (Microsoft.SqlServer.SmoExtended)
------------------------------
For help, click: https://go.microsoft.com/fwlink?ProdName=Microsoft+SQL+Server&ProdVer=16.100.47008.0&LinkId=20476
------------------------------
Program Location:
at Microsoft.SqlServer.Management.Smo.RestorePlan.Execute()
at Microsoft.SqlServer.Management.RelationalEngineTasks.RestoreDatabaseTaskFormComponent.PerformTask(ITaskExecutionContext context)Thank you!
-
Are you attempting to restore over the top of another database? (Did you change the name of the database you're restoring? You should have). Once you do that, you can import whatever objects you want, check that it works and then drop the restored backup db.
- pietlinden wrote:
Are you attempting to restore over the top of another database? (Did you change the name of the database you're restoring? You should have). Once you do that, you can import whatever objects you want, check that it works and then drop the restored backup db.
I am fairly new to this.
Ideally, I want there to be two instances...original and restored. go to restored and find the store proc and then remove restored.
I changed the destination to a different name and I thought that would work but it didn't. I provided screenshot of what I did. What am I doing wrong?
-
The database “OptionsDBtemp” already exists on your server AND there is an open session in that database.
As someone / something is using that database while you attempt to restore it, your failing as it will destroy that users session.
Find out who else is using that DB and close the session. Or set the DB to offline. Or use the additional option in the GUI to kill users connections prior to the restore.
-
You want to look up the WITH MOVE syntax. You can restore to a new database name, but only if you also move the storage location for the database.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
You also need to review how you got into this situation - and put controls in place to avoid having to restore a database to get a previous version of code.
If you happen to be using SQL Prompt - they include a SQL History tab that maintains all of the changes and you can quickly revert to a prior version of that code.
SSMS may also have the previous version available - you would have to search through the project backup files to see if there is a version that was autosaved. I have been able to recover previous versions that way, but it all depends on how you have SSMS set up and when that folder is cleaned up.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
Source control your database code. All of it.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
thanks for all the replies. much appreciated.
Some background...I have been making changes to a stored proc. I made a change (not sure exactly what tho) that resulted in incorrect query output. My first thought was to restore the backup DB and then find the sp I am looking. Once I have it then remove the restored db. Long process.
Ideally, it would be nice to have a version control so if I ever have to revert changes to a stored proc then I can do it easily. I use SSMS for creating the sp. Is there a way to create a history of previous version so if I have to revert then no problem? This would be the ideal solution.
-
3rd party solution (my employer), Redgate Software makes a tool, SQL Compare, that can actually look directly at the backup file and compare that to a database to find differences. It might be a good solution.
"The credit belongs to the man who is actually in the arena, whose face is marred by dust and sweat and blood"
- Theodore RooseveltAuthor of:
SQL Server Execution Plans
SQL Server Query Performance Tuning -
Some basics of version control can be found here:
https://www.sqlservercentral.com/books/sql-server-source-control-basics
Git is also worth looking at and integrates with Azure Data Sudio:
https://www.w3schools.com/git/
Even saving files on OneDrive can help as it keeps old versions for a limited time period.
Here is a template I use to generate a rough restore script - it can probably be improved.
Copy the code to a new Window, Ctrl Shift M, enter the db name - in this case OptionsDB. Run the code and copy the output to a new query window. Look at before running.
/* Restore2DateDB
Restores a backed up db to one with the current date.
Ignores log backups so get code separately if needed.
*/
DECLARE @db sysname = N'<Base_Database, sysname, Database>'
,@Today nchar(8) = CONVERT(nchar(8), CURRENT_TIMESTAMP, 112)
,@NewDb sysname
,@OldDb sysname
,@dbowner sysname
,@RecoveryModel sysname
,@StandbyFile nvarchar(255)
,@FullBackup nvarchar(255)
,@FullBackupTime datetime
,@DiffBackup nvarchar(255)
,@DiffBackupTime datetime
,@Moves nvarchar(MAX)
,@SQL nvarchar(MAX);
SELECT @db = [name]
,@NewDb = [name] + @Today
,@OldDb = [name] + N'_Old'
,@dbowner = SUSER_SNAME(owner_sid)
,@RecoveryModel = recovery_model_desc
FROM sys.databases
WHERE [name] = @db;
SELECT @StandbyFile =
REVERSE(SUBSTRING(R.physical_name, CHARINDEX('\', R.physical_name), 255))
+ @NewDb + N'.stb'
FROM sys.master_files M
CROSS APPLY
(
VALUES (REVERSE(M.physical_name))
) R (physical_name)
WHERE M.database_id = DB_ID(@db)
AND M.[type] = 1
AND M.data_space_id = 0;
SELECT TOP (1)
@FullBackup = M.physical_device_name
,@FullBackupTime = S.backup_finish_date
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
WHERE M.physical_device_name NOT LIKE '{%'
AND S.[database_name] = @db
AND S.[type] = 'D'
ORDER BY S.backup_finish_date DESC;
SELECT TOP (1)
@DiffBackup = M.physical_device_name
,@DiffBackupTime = S.backup_finish_date
FROM msdb.dbo.backupset S
JOIN msdb.dbo.backupmediafamily M
ON S.media_set_id = M.media_set_id
WHERE M.physical_device_name NOT LIKE '{%'
AND S.[database_name] = @db
AND S.[type] = 'I'
ORDER BY S.backup_finish_date DESC;
SELECT @Moves = STRING_AGG(NCHAR(9) + ',MOVE N''' + M.[name] + ''' TO N''' + REVERSE(R.physical_name) + '''', NCHAR(13) + NCHAR(10))
FROM sys.master_files M
CROSS APPLY
(
VALUES ( PATINDEX('%20[0-3][0-9][0-1][0-9][0-3][0-9]%', M.physical_name) )
) D (DatePos)
CROSS APPLY
(
VALUES
(
CASE
WHEN D.DatePos > 0
THEN REVERSE(LEFT(M.physical_name, D.DatePos - 1) + SUBSTRING(M.physical_name, D.DatePos + 8, 255))
ELSE REVERSE(M.physical_name)
END
,REVERSE(@Today)
)
) F (physical_name, Today)
CROSS APPLY
(
VALUES
(
CHARINDEX('.', F.physical_name)
,CHARINDEX('_', F.physical_name)
,CHARINDEX('\', F.physical_name)
)
) I (DotPos, UPos, BPos)
CROSS APPLY
(
VALUES
(
CASE
WHEN I.DotPos > I.UPos AND I.DotPos < I.BPos
THEN LEFT(F.physical_name, I.DotPos) + F.Today + SUBSTRING(F.physical_name, I.DotPos + 1, 255)
WHEN I.UPos > I.DotPos AND I.UPos < I.BPos
THEN LEFT(F.physical_name, I.UPos) + F.Today + SUBSTRING(F.physical_name, I.UPos + 1, 255)
ELSE F.Today + F.physical_name
END
)
) R (physical_name)
WHERE DB_NAME(M.database_id) = @db;
SET @SQL = N'USE [master];' + NCHAR(13) + NCHAR(10)
+ N'GO' + NCHAR(13) + NCHAR(10)
+ N'RESTORE DATABASE ' + @NewDb + NCHAR(13) + NCHAR(10)
+ N'FROM DISK = N''' + @FullBackup + N'''' + NCHAR(13) + NCHAR(10)
+ N'WITH NORECOVERY, CHECKSUM /*,REPLACE*/' + NCHAR(13) + NCHAR(10)
+ @Moves + N';' + NCHAR(13) + NCHAR(10)
+ N'GO' + NCHAR(13) + NCHAR(10)
+ CASE
WHEN @DiffBackupTime > @FullBackupTime
THEN N'RESTORE DATABASE ' + @NewDb + NCHAR(13) + NCHAR(10)
+ N'FROM DISK = N''' + @DiffBackup + N'''' + NCHAR(13) + NCHAR(10)
+ N'WITH NORECOVERY, CHECKSUM, FILE=1;' + NCHAR(13) + NCHAR(10)
+ N'GO' + NCHAR(13) + NCHAR(10)
ELSE
N''
END
+ N'RESTORE DATABASE ' + @NewDb + N' WITH RECOVERY;' + NCHAR(13) + NCHAR(10)
+ N'/* RESTORE DATABASE ' + @NewDb + N' WITH STANDBY=''' + @StandbyFile +'''; */' + NCHAR(13) + NCHAR(10)
+ N'GO' + NCHAR(13) + NCHAR(10)
+ N'ALTER DATABASE ' + @NewDb + N' SET MULTI_USER;' + NCHAR(13) + NCHAR(10)
+ N'ALTER AUTHORIZATION ON DATABASE::' + @NewDb + N' TO [' + @dbowner + N'];' + NCHAR(13) + NCHAR(10)
+ N'ALTER DATABASE ' + @NewDb + N' SET RECOVERY SIMPLE;' + NCHAR(13) + NCHAR(10)
+ N'GO' + NCHAR(13) + NCHAR(10)
+ N'USE ' + @NewDb + N';' + NCHAR(13) + NCHAR(10)
+ N'GO
DBCC CHECKDB WITH NO_INFOMSGS, ALL_ERRORMSGS;
GO
EXEC sp_updatestats;
GO
USE [master];
GO
' +
CASE
WHEN @RecoveryModel <> 'SIMPLE'
THEN N'ALTER DATABASE ' + @NewDb + N' SET RECOVERY ' + @RecoveryModel + N';' + NCHAR(13) + NCHAR(10)
+ N'GO' + NCHAR(13) + NCHAR(10)
ELSE ''
END +
N'/* To replace orignal db
USE master;
GO
ALTER DATABASE ' + @db + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE ' + @db + N' MODIFY NAME = ' + @OldDb + N';
GO
ALTER DATABASE ' + @OldDb + N' SET MULTI_USER;
GO
ALTER DATABASE ' + @NewDb + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
ALTER DATABASE ' + @NewDb + N' MODIFY NAME = ' + @db + N';
GO
ALTER DATABASE ' + @db + N' SET MULTI_USER;
GO
ALTER DATABASE ' + @OldDb + N' SET SINGLE_USER WITH ROLLBACK IMMEDIATE;
GO
DROP DATABASE ' + @OldDb + N';
*/';
PRINT @SQL; -
There should be a folder under one of the Visual Studio folders under you Documents folder. Example: C:\Users\{user}\Documents\Visual Studio 2017\Backup Files\Solution1
This should have a list of autorecover, recovered and original files.
It is possible that your changes were auto saved here and available.
Jeffrey Williams
“We are all faced with a series of great opportunities brilliantly disguised as impossible situations.”― Charles R. Swindoll
How to post questions to get better answers faster
Managing Transaction Logs -
At a minimum, before modifying a stored procedure, you can save the original, with a new name, such as appending the date on the end.
And add comments in the live version about what was changed and why,
Viewing 12 posts - 1 through 11 (of 11 total)
You must be logged in to reply to this topic. Login to reply