Restoring multiple databases via script

Hi Guys,
Hope you are having fun with SQL as usual.
I have developed a script which I thought may be useful to the community least they need to restore huge number of databases from backups without using any enteprise restore software.

Script is broken into two major parts
1. Getting the names of the databases that needs to be restored.
2. Getting the restore script for the databases in question

Now let's delve into the Scripts.
1. Names of the Databases 
Here in this example I am restoring the databases in offline mode. Just a scenario we can use this to be any scenario.

DECLARE @db_row INT
SELECT @db_row=COUNT(name) FROM sys.databases where state_desc<>'online';
DECLARE @res_script varchar(max)

SELECT TOP (@db_row) b.database_name,physical_device_name,b.type,b.backup_start_date
INTO #backup_tab
FROM msdb..backupset b
join msdb..backupmediafamily f on f.media_set_id=b.media_set_id and type ='d'
AND database_name IN (select name from sys.databases where state_desc<>'online')
order by b.backup_start_date DESC

Now we have the information of the backup set of the databases in question. Now let's move on to the next part of the query.

2. Restore Script
Here we use a cursor. I personally do not like them but for this time I thought it will be easy to use a cursor. I will try in future to write it without a cursor.

DECLARE @dbname sysname, @backpath nvarchar(260)
DECLARE rest_cur CURSOR FOR
SELECT database_name FROM #backup_tab ORDER BY 1

OPEN rest_cur
FETCH NEXT FROM rest_cur INTO @dbname
WHILE @@FETCH_STATUS=0
BEGIN
SELECT @backpath=physical_device_name FROM #backup_tab WHERE database_name=@dbname
SELECT @res_script=''

SELECT @res_script= 'RESTORE database [' + @dbname + '] from disk ='+'''' + @backpath+''''

SELECT  @res_script=@res_script+ ' WITH '

SELECT @res_script=@res_script+' move '''+name+''' ' + 'to ''' +physical_name+''','
FROM sys.master_files
WHERE 1=1
AND database_id=DB_ID(@dbname)

SELECT @res_script=@res_script+ 'stats=1 ,replace'

print @res_script
--EXEC(@res_script)
FETCH NEXT FROM rest_cur INTO @dbname

END
CLOSE rest_cur
DEALLOCATE rest_cur

DROP TABLE #backup_tab

It's one of my hot favourite scripts in a disaster recovery scenario. Hope you guys liked it.

Here is the link to the script file


Thanks,
Tushar

Recovering Master Database with Master and Data log file

Hi ,

There are times when you will need to fix a corrupt database server and you will need to restore your master database from a good old backup.  This situation is fairly documented and does not need further elaboration with so many articles floating around from God's of SQL Server to help us. 

But I found another situation which was new and different from the other situations. 

I will start with giving you the configuration path so it will help to imagine the pain and progress. 

OS Installation - C:\ Drive 

SQL Installation - D:\ Drive 

SQL User and Data Log file - E:\ Drive (I know please do not shout it's our dev and I did not build it)

SQL Backup - J:\ Drive

SQL Server version - SQL Server 2016 upgraded from SQL Server 2012. 

Ok now that we have base line set let me show you the scenario. 

The OS crashed aka C drive and the backup crashed aka J:\ Drive. 

So we had to get the OS rebuilt to get something up and running. 

SQL was definitely not up because the OS had no clue there was any installation. 

So we had a discussion with the Infra Team that they will install SQL 2012 and then upgrade 2016 and bring it up to the same patch level as before the disaster and then we will try and re-attach the old system database files to bring up the system.

But as I have mentioned before things do not get fixed so easily once they are messed up in DB World. Specially when some one is blogging about it. 

So when I got back the system I saw that the super smart Infra Guy had installed 2016 with the latest patch directly rather than the upgrade path and he improvised on choosing the new path for the system databases to be E:\ drive rather than the default D:\ Drive and he was very proud that he had fixed a glitch with the original install plan. 

Only if he could understand my pain but let me help you understand by elaborating a little on this. 

1. The old system database files were in D Drive when the disaster happened so if we put them in the new path and start the server it will not come up with the missing address link from old (in master database ) to new path . 

2. If we put the old system databases in the old path and try to start the SQL Server then the resource database will happen address issues. 

So I devised an interim plan to first modify all the existing system databases to old database file path in D:\ Drive so the resource database will be updated. Second we will shutdown the database server and attach the old database files to bring up the system. 

Well after attaching the old database files and restarting the SQL Server the System resumed and all the databases and other objects came online. 

This is how we potentially avoided the need to redo all the login , packages and jobs from prod to dev and saved weeks of development effort. 

Thanks,

Tushar Kanti 

Removing SQL Databases from Recovery Pending Status

Hi Guys,

Today met with an unusual situation where a couple of drives in the Dev box crashed. Luckily the system databases were not affected. So cooked up a script to generate the restore of the user databases from the last good backup. Should be a piece of cake I assumed with the restore script to my rescue. 

Sample restore Script. 

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

DECLARE @dbname sysname ='dba'

SELECT 'RESTORE database [' + @dbname + '] from disk =' AS 'restore script'

UNION ALL 

SELECT  'WITH ' AS 'restore script'

UNION all

SELECT 

'move '''+name+''' ' + 'to ''' +physical_name+''',' AS 'restore script'

FROM sys.master_files

WHERE 1=1

AND database_id=DB_ID(@dbname)

UNION all

SELECT 'stats=1,replace ' AS 'restore script'

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

Well those of you thinking this was a happily ever after story you are almost wrong. Things do not turn good so easily when they mess up in DB world. 

To my surprise I was not able to a. restore with overwrite d. alter database to single user mode c. take db offline d. drop the database. And as you might have guessed the reason by now the database automatically starts when SQL Server restarts. Well I always loved this feature where you do not have to manually start 100s of database manually or through a script every time you restart SQL Server. But just for this time I was aching to have that feature where I could actually stop the recovery of the databases I do not want. I finally got to the answer through a lot of soul searching and blog posts (Amit Bansal's website ).

So here goes the list of my attempts 

1. Take the SQL Server in single user mode and try to take the user databases offline - Did not work 

2. Take the SQL Server in single user mode and try to drop the user databases - Did not work

3.  Take the SQL Server in minimal  mode and try to take the user databases offline - Did not work 

4. Take the SQL Server in minimal  mode and try to drop the user databases - Did not work

5. Set Trace 3607 in SQL Server Startup and try to take the user databases offline - Did not work 

6. Set Trace 3607 in SQL Server Startup and try to  drop the user databases - Did not work

Then finally I hit gold and tried to set the Trace 3608 in the SQL Server Startup and started the SQL Server. 

Most of the user databases showed online though some were in recovery or recovery pending. 

I dropped the databases which were in online status and restarted the SQL Server  with the trace 3608 in the the startup parameter to bring the other user databases online and then dropped them. 

I finally removed the trace flag 3608 and restarted the SQL Server and all the user databases were gone. 

So all I had to do now was to use the restore script to generate the restore database command for all databases. Well I swung a cursor around the script to get everything in one go but later on that in the next blog post. 

Thanks,

Tushar Kanti

Drop SQL Server databases in Recovery Mode

Hi Guys,

Today met with an unusual situation where a couple of drives in the Dev box crashed. Luckily the system databases were not affected. So cooked up a script to generate the restore of the user databases from the last good backup. Should be a piece of cake I assumed with the restore script to my rescue. 

Sample restore Script. 

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

DECLARE @dbname sysname ='dba'

SELECT 'RESTORE database [' + @dbname + '] from disk =' AS 'restore script'

UNION ALL 

SELECT  'WITH ' AS 'restore script'

UNION all

SELECT 

'move '''+name+''' ' + 'to ''' +physical_name+''',' AS 'restore script'

FROM sys.master_files

WHERE 1=1

AND database_id=DB_ID(@dbname)

UNION all

SELECT 'stats=1,replace ' AS 'restore script'

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

Well those of you thinking this was a happily ever after story you are almost wrong. Things do not turn good so easily when they mess up in DB world. 

To my surprise I was not able to a. restore with overwrite d. alter database to single user mode c. take db offline d. drop the database. And as you might have guessed the reason by now the database automatically starts when SQL Server restarts. Well I always loved this feature where you do not have to manually start 100s of database manually or through a script every time you restart SQL Server. But just for this time I was aching to have that feature where I could actually stop the recovery of the databases I do not want. I finally got to the answer through a lot of soul searching and blog posts (Amit Bansal's website ).

So here goes the list of my attempts 

1. Take the SQL Server in single user mode and try to take the user databases offline - Did not work 

2. Take the SQL Server in single user mode and try to drop the user databases - Did not work

3.  Take the SQL Server in minimal  mode and try to take the user databases offline - Did not work 

4. Take the SQL Server in minimal  mode and try to drop the user databases - Did not work

5. Set Trace 3607 in SQL Server Startup and try to take the user databases offline - Did not work 

6. Set Trace 3607 in SQL Server Startup and try to  drop the user databases - Did not work

Then finally I hit gold and tried to set the Trace 3608 in the SQL Server Startup and started the SQL Server. 

Most of the user databases showed online though some were in recovery or recovery pending. 

I dropped the databases which were in online status and restarted the SQL Server  with the trace 3608 in the the startup parameter to bring the other user databases online and then dropped them. 

I finally removed the trace flag 3608 and restarted the SQL Server and all the user databases were gone. 

So all I had to do now was to use the restore script to generate the restore database command for all databases. Well I swung a cursor around the script to get everything in one go but later on that in the next blog post. 

Thanks,

Tushar Kanti