Restore 1100+ DBs from Prod to QA

Question

Restore 1100+ DBs from Prod to QA

Chitown

Hall of Fame

Points: 3239
More actions
May 17, 2016 at 9:55 am

#326373

All, I need to refresh 1100+ DBs from Prod to QA. Is there any easy to accomplish this task?

Viewing 10 posts - 1 through 9 (of 9 total)

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

Sean Lange SSC Guru Points: 286590 More actions · Answer 1

Chitown (5/17/2016)
All, I need to refresh 1100+ DBs from Prod to QA. Is there any easy to accomplish this task?

You will become familiar with both the backup and restore commands. You could possibly leverage some dynamic sql to help you generate the sql but in the end you will have to get a backup and restore that backup.

_______________________________________________________________

Need help? Help us help you.

Read the article at http://www.sqlservercentral.com/articles/Best+Practices/61537/ for best practices on asking questions.

Need to split a string? Try Jeff Modens splitter http://www.sqlservercentral.com/articles/Tally+Table/72993/.

Cross Tabs and Pivots, Part 1 – Converting Rows to Columns - http://www.sqlservercentral.com/articles/T-SQL/63681/
Cross Tabs and Pivots, Part 2 - Dynamic Cross Tabs - http://www.sqlservercentral.com/articles/Crosstab/65048/
Understanding and Using APPLY (Part 1) - http://www.sqlservercentral.com/articles/APPLY/69953/
Understanding and Using APPLY (Part 2) - http://www.sqlservercentral.com/articles/APPLY/69954/

Chitown Hall of Fame Points: 3239 More actions · Answer 2

We already have a full backup in place from this weekend.

Select 'RESTORE DATABASE [' + name + '] FROM DISK = (Backup location);

'

FROM sys.databases

I figured out half of it but I still need to figure out to restore each and every DB using the appropriate backup file and I just don't know to do it?

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 3

Are these the same instance? different ones?

We have scripts here that might help, like this one: http://www.sqlservercentral.com/scripts/Backup+and+restore/31995/

Or this: http://www.sqlservercentral.com/scripts/Restore/113431/

Chitown Hall of Fame Points: 3239 More actions · Answer 4

I am actually getting close.

Select 'RESTORE DATABASE [' + name + '] FROM DISK = \\database\sql\prod\[' + name + ']\;

'

FROM sys.databases

;

Lowell SSC Guru Points: 323518 More actions · Answer 5

Sean Lange (5/17/2016)
Chitown (5/17/2016)
All, I need to refresh 1100+ DBs from Prod to QA. Is there any easy to accomplish this task?
You will become familiar with both the backup and restore commands. You could possibly leverage some dynamic sql to help you generate the sql but in the end you will have to get a backup and restore that backup.

you want to generate bot the backup command and the restore command together, so you know the name of the database.

here's a crappy example, that assumes you are not restoring a stack of differential and incrementals...if you need to do that, you have to read all the values from the msdb backup history.

Select

'BACKUP DATABASE ' + quotename(name) + ' TO DISK = N''\\GiantBackupServer\SQLBackup\' + name + '_MassMove.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10',

'RESTORE DATABASE ' + quotename(name) + ' FROM DISK = N''\\GiantBackupServer\SQLBackup\' + name + '_MassMove.bak'';

'

FROM sys.databases WHERe database_id > 4

Lowell

--help us help you! If you post a question, make sure you include a CREATE TABLE... statement and INSERT INTO... statement into that table to give the volunteers here representative data. with your description of the problem, we can provide a tested, verifiable solution to your question! asking the question the right way gets you a tested answer the fastest way possible!

Chitown Hall of Fame Points: 3239 More actions · Answer 6

Lowell (5/17/2016)
Sean Lange (5/17/2016)
Chitown (5/17/2016)
All, I need to refresh 1100+ DBs from Prod to QA. Is there any easy to accomplish this task?
You will become familiar with both the backup and restore commands. You could possibly leverage some dynamic sql to help you generate the sql but in the end you will have to get a backup and restore that backup.
you want to generate bot the backup command and the restore command together, so you know the name of the database.
here's a crappy example, that assumes you are not restoring a stack of differential and incrementals...if you need to do that, you have to read all the values from the msdb backup history.
Select
'BACKUP DATABASE ' + quotename(name) + ' TO DISK = N''\\GiantBackupServer\SQLBackup\' + name + '_MassMove.bak'' WITH NOFORMAT, NOINIT, NAME = N''' + name + '-Full Database Backup'', SKIP, NOREWIND, NOUNLOAD, STATS = 10',
'RESTORE DATABASE ' + quotename(name) + ' FROM DISK = N''\\GiantBackupServer\SQLBackup\' + name + '_MassMove.bak'';
'
FROM sys.databases WHERe database_id > 4

Thanks a lot. The script is working as expected however, when I run the restore command for all the DBs, almost 25% of the DBs were restored successfully but most of them failed. The error was "File 'Voxco_Project_1431263' cannot be restored to 'E:\MSSQL\MSSQL10_50.VOXCO\MSSQL\DATA\\Voxco_Project_1431263.mdf'. Use WITH MOVE to identify a valid location for the file."

There are many DBs which exist in prod doesn't exist in QA and that's why I am getting this error.

I don't know why there are 2 slashes and how do I add the script so I am able to move data file to D and log file to L

Chitown Hall of Fame Points: 3239 More actions · Answer 7

Maybe close but close enough

Select

'RESTORE DATABASE ' + QUOTENAME(name) + 'FROM DISK = N''\\dd-database\database\SQL\prod\Voxco_2008R2\SS-VX-SQL1' + name + '.bak''

WITH FILE = 1,

MOVE ' + name + ' to N'' D:\Program Files\Microsoft SQL Server\MSSQL10_50.VOXCO\MSSQL\DATA

MOVE ' + name + '_log to N'' E:\Program Files\Microsoft SQL Server\MSSQL10_50.VOXCO\MSSQL\Data

NOUNLOAD, REPLACE, STATS = 10

GO

'

FROM sys.databases

WHERE name like 'Voxco_Project%'

;

Steve Jones - SSC Editor SSC Guru Points: 736212 More actions · Answer 8

You'll need logic in there that looks for e:\ and replaces with d:\. You might need to create folders ahead or build in logic to do so.

For the two slashes, where? You need to be specific when you post or ask a question. We don't necessarily see what you see as this is something we pick up and look at quickly.

Chitown Hall of Fame Points: 3239 More actions · Answer 9

Finally resolved. Missing some double, triple quotations....

Select

'RESTORE DATABASE ['+name+'] FROM DISK = N''\\dd-database\database\SQL\prod\'+name+'.bak'' WITH FILE = 1, MOVE N'''+name+''' TO N''D:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\DATA\'+name+'.mdf'', MOVE N'''+name+'_log'' TO N''E:\Program Files\Microsoft SQL Server\MSSQL10_50\MSSQL\Data\'+name+'_log.ldf'', NOUNLOAD, REPLACE, STATS = 5'

FROM sys.databases

WHERE name like 'V_Project%'

;