July 23, 2014 at 6:37 am
I am attempting to create a Test db from a full backup of the production db. With 2012, I cannot do it the the way i had done it in previous versions (and now i understand why because of Logical names).
The Test db runs in the same instance as Prod db.
I attempted to run this but come up with errors. This is what i executed:
RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'
WITH REPLACE, RECOVERY,
MOVE 'PROD' TO 'E:\<path>\TEST.MDF';
The errors are all cannot execute due to PROD is in use.
Any assistance would be greatly appreciated.
July 23, 2014 at 7:52 am
Are you able to post the error message?
July 23, 2014 at 8:22 am
I would need to do it after hours but i can.
The errors where along the line of cannot move Prod because it is in use.
July 23, 2014 at 8:56 am
You need to end (kill) all other user sessions connected to the db TEST:
declare users cursor
for select spid
from master..sysprocesses
where db_name(dbid) = @dbname
declare @spid int,
@STR varchar(255)
open users
fetch next from users into @spid
while @@fetch_status <> -1
begin
if @@fetch_status = 0
begin
set @STR = 'kill ' + convert(varchar, @spid)
exec (@str)
end
fetch next from users into @spid
end
deallocate users
Then set it to single user mode to be sure:
ALTER DATABASE @dbname SET SINGLE_USER
EDIT: You will also have to move the Log. See here for a complete restore statement:
http://technet.microsoft.com/en-us/library/ms190447%28v=sql.105%29.aspx
July 23, 2014 at 9:17 am
It's simpler if you just drop the test database before starting the restore to prevent it from being in use.
Example:
use master;
-- Set offline to disconnect all users from the database
alter database [MyTestDB] set offline with rollback immediate;
-- Set back online so that when you execute the drop command,
-- all DB data files are deleted.
alter database [MyTestDB] set online with rollback immediate;
drop database [MyTestDB];
July 23, 2014 at 9:21 am
I don't drop it, because I want to keep the old db in case the restore fails 😉
Bringing it offline and online again to drop the connections is elegant.
July 23, 2014 at 9:26 am
J.Faehrmann (7/23/2014)
I don't drop it, because I want to keep the old db in case the restore fails 😉Bringing it offline and online again to drop the connections is elegant.
I doubt if you will have a usable database if the restore fails, so there is really no point in not dropping it.
FYI, if you set a database offline and online again just to disconnect users, it will be owned by the login that set it online.
if you are doing that, I suggest doing it this way:
execute as login = 'SA'; -- or the login you desire
use master;
alter database [MyTestDB] set offline with rollback immediate;
alter database [MyTestDB] set online with rollback immediate;
revert;
-- or you could do this after setting it online
use [MyTestDB];
exec sp_changedbowner 'sa'
July 23, 2014 at 9:31 am
the ALTER DATABASE....SINGLE_USER method as suggested earlier is a good suggestion. Remember to add the WITH ROLLBACK IMMEDIATE otherwise it will wait for the connection to be closed before completing.
Also as previously mentioned you need to move the log file as well as the data file
July 23, 2014 at 9:42 am
Thanks for the reponses....
So the above is correct but due to someone signed in probably caused the errors.
I just want to make sure that this will create a new Test DB from the Prod db.
July 23, 2014 at 9:45 am
MrG78 (7/23/2014)
the ALTER DATABASE....SINGLE_USER method as suggested earlier is a good suggestion. Remember to add the WITH ROLLBACK IMMEDIATE otherwise it will wait for the connection to be closed before completing.Also as previously mentioned you need to move the log file as well as the data file
If you set it to single user mode and some process connects to it before you start the restore, you will find it very difficult to disconnect that user.
That is why I prefer to drop the database before the restore. No one can connect to a database that does not exist. 😎
July 23, 2014 at 9:47 am
Ah, just picked up on this. If you're restoring to a brand new database, i.e. not currently there. You shouldn't be constrained by the database the restore is from being in use.
Maybe take the REPLACE option out of your statement and also add the move for the log file, like this...
RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'
WITH RECOVERY,
MOVE 'PROD' TO 'E:\<path>\TEST.MDF',
MOVE 'PROD_Log' TO 'E:\<path>\TEST.LDF';
*Sorry for formatting, not sure how to post this nicely like others have
July 23, 2014 at 10:05 am
If you set it to single user mode and some process connects to it before you start the restore, you will find it very difficult to disconnect that user.
That is why I run the restore from the same session/proc that set it to single mode.
I doubt if you will have a usable database if the restore fails, so there is really no point in not dropping it.
Keeping the old db only works if the backup files are missing, else a working backup is truely needed 🙂
July 23, 2014 at 10:10 am
This is a sample of a restore command to use after dropping the restore target database:
declare @BackupFile nvarchar(500)
set @BackupFile = '\\MyBackupServer\MyBackupShare\MyBackupFolder\MyTestDBBackup.bak'
restore database [MyTestDB]
from disk = @BackupFile
with
-- Logical names to match source database
-- File path and files names as you specify
move 'MyTestDB_Data'to 'E:\DBFile\MyTestDB_Data.mdf' ,
move 'MyTestDB_Log'to 'E:\DBFile\MyTestDB_Log.ldf',
replace, recovery
July 23, 2014 at 10:19 am
J.Faehrmann (7/23/2014)
If you set it to single user mode and some process connects to it before you start the restore, you will find it very difficult to disconnect that user.
That is why I run the restore from the same session/proc that set it to single mode.
I doubt if you will have a usable database if the restore fails, so there is really no point in not dropping it.
Keeping the old db only works if the backup files are missing, else a working backup is truely needed 🙂
"...That is why I run the restore from the same session/proc that set it to single mode..."
Since you cannot be connected to the database when you restore, that would not prevent someone else from connecting to the database you are trying to restore. It is really just a matter of how often connections are made to the database.
The same applies to dropping the database, but once the database is successfully dropped, no one can connect to it. If connections are made at a really high rate, it might be necessary to drop the database while it is offline and then delete the database files manually.
July 23, 2014 at 3:10 pm
1. Do the restore to a different database name
2. then rename/drop the old database
3. Rename the new database to the correct DB name
Viewing 15 posts - 1 through 15 (of 30 total)
You must be logged in to reply to this topic. Login to reply