Creating a Test DB from a full backup of Prod

Question

Creating a Test DB from a full backup of Prod

Viewing 15 posts - 16 through 30 (of 30 total)

You must be logged in to reply to this topic. Login to reply

Beatrix Kiddo SSC-Dedicated Points: 32407 More actions · Answer 1

I do a restore with move, as suggested above, and rather than setting to single user mode, I set to restricted_user mode;

use master

go

alter database destinationdbname

set restricted_user with rollback immediate

(then do the restore with replace, move)

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 2

Also, you can use the sp_who2 proc to see IF another user has a connection to the DB.

You can either kill xxx (where xxx is the SPID of the user connected to the DB) OR I use the following script and un-comment the ALTER DATABASE statement to place the DB in single user mode to kick out any users then do the restore (you will have to fill in the correct paths/names etc...):

USE master

RESTORE FILELISTONLY

FROM DISK = '' --Disk Backup File

--sp_helpdb TEST

USE master

--ALTER DATABASE [TEST] SET SINGLE_USER WITH ROLLBACK IMMEDIATE

RESTORE DATABASE [TEST] -- DB Name

FROM DISK = '' --Disk Backup File

WITH STATS, REPLACE,

MOVE '' TO '.mdf',

MOVE '' TO '.ldf'

Perry Whittle SSC Guru Points: 234013 More actions · Answer 3

This is why you are getting the error

RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'

WITH REPLACE, RECOVERY,

MOVE 'PROD' TO 'E:\<path>\TEST.MDF';

You've only specified a move for the primary data file, you need to specify a move command for each log file and secondary data file too. If you do not specify a file it will try to use the source databases file and that will produce an error.

Don't set anything offline or single user or anything else, just use the restore command and specify a move command for each file in the database. If you want to get a list of the files run this first

RESTORE FILELISTONLY FROM DISK = 'E:\<path>\FULL.BAK'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 4

Perry Whittle (7/25/2014)
This is why you are getting the error
RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'
WITH REPLACE, RECOVERY,
MOVE 'PROD' TO 'E:\<path>\TEST.MDF';
You've only specified a move for the primary data file, you need to specify a move command for each log file and secondary data file too. If you do not specify a file it will try to use the source databases file and that will produce an error.
Don't set anything offline or single user or anything else, just use the restore command and specify a move command for each file in the database. If you want to get a list of the files run this first
RESTORE FILELISTONLY FROM DISK = 'E:\<path>\FULL.BAK'

And i can do this during business hours without affecting the Prod DB?

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 5

Paul Morris-1011726

SSCommitted

Points: 1879

July 25, 2014 at 6:41 am

#1732337

Also, do i use the logical name or physical name?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 6

Paul Morris-1011726 (7/25/2014)
And i can do this during business hours without affecting the Prod DB?

Should be perfectly safe as long as you set the move clauses correctly. The logical name is used to refer to the file within the database. For each logical filename you move the path and\or physical name.

Make sense?

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 7

So it should look like this?

RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'

WITH REPLACE, RECOVERY,

MOVE 'PROD' TO 'E:\<path>\TEST.MDF',

MOVE 'PROD_log' TO 'E:\<path>\TEST_log.LDF';

Do i need the RECOVERY?

Perry Whittle SSC Guru Points: 234013 More actions · Answer 8

Paul Morris-1011726 (7/25/2014)
So it should look like this?
RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'
WITH REPLACE, RECOVERY,
MOVE 'PROD' TO 'E:\<path>\TEST.MDF',
MOVE 'PROD_log' TO 'E:\<path>\TEST_log.LDF';
Do i need the RECOVERY?

You don't need REPLACE since the database doesn't exist, you don't need RECOVERY since the default action is to recover the database.

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 9

So this is what it should look like:

RESTORE DATABASE TEST FROM DISK = 'E:\<path>\FULL.BAK'

WITH

MOVE 'PROD' TO 'E:\<path>\TEST.MDF',

MOVE 'PROD_log' TO 'E:\<path>\TEST_log.LDF';

Sorry for asking a lot of questions, never created test DB's this way.

In the past, I would detach production, make a copy, rename, and reattached. Now I am learning that is not a good way of doing it.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 10

I have an example here which should help.

I have a Litespeed database called Litespeedlocal which i have a backup of, I want to restore it as bob.

The logical filenames are

LiteSpeedLocal

LiteSpeedLocal_log

I use the following query

RESTORE DATABASE [BOB] FROM DISK = N'P:\Backups\LiteSpeedLocal\LiteSpeed.BAK'

WITH MOVE 'LiteSpeedLocal' TO 'F:\MSSQL11.MSSQLSERVER\MSSQL\DATA\bobsmith_data.mdf',

MOVE 'LiteSpeedLocal_log' TO 'F:\MSSQL11.MSSQLSERVER\MSSQL\Logs\bobsmith_log.ldf'

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Perry Whittle SSC Guru Points: 234013 More actions · Answer 11

Once you restored the database as a new name i would also then change the logical filenames as shown below (and based on my example)

ALTER DATABASE [bob] MODIFY FILE (NAME = LiteSpeedLocal, NEWNAME = Bob_Data)

ALTER DATABASE [bob] MODIFY FILE (NAME = LiteSpeedLocal_log, NEWNAME = Bob_log)[/code]

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 12

Very cool, I will do this off hours just because i am paranoid. Let you know how it goes. Thanks for the help!

DeWayne_McCallie Hall of Fame Points: 3617 More actions · Answer 13

As long as you do not have the "REPLACE" parm in the restore command any current DB can not be overwritten as you will get an error message.

Perry Whittle SSC Guru Points: 234013 More actions · Answer 14

Paul Morris-1011726 (7/25/2014)
Very cool, I will do this off hours just because i am paranoid. Let you know how it goes. Thanks for the help!

no need to be paranoid, just ensure your script is correct before you run it.

Ensure there is a MOVE clause for every file returned in the RESTORE FILELISTONLY output 😎

-----------------------------------------------------------------------------------------------------------

"Ya can't make an omelette without breaking just a few eggs" 😉

Paul Morris-1011726 SSCommitted Points: 1879 More actions · Answer 15

Just wanted to follow up to let you know that it worked fine... thanks for all the info/help.