SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore database with all necessary files


Restore database with all necessary files

Author
Message
Josep
Josep
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 4675
Comments posted to this topic are about the item Restore database with all necessary files
henrik staun poulsen
henrik staun poulsen
SSCrazy
SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)SSCrazy (2.2K reputation)

Group: General Forum Members
Points: 2211 Visits: 1225
Josep,

A Very nice script. Very useful.
I've added 2 features:
a) to have a MOVE xx to yy clause, so that the files can be restored to different drives/directories.

b) Stats=1, so that I can see progress.

Best regards
Henrik Staun Poulsen


/******************************************************************
from
http://www.sqlservercentral.com/scripts/Restore/61810/

This script creates the script to restore your database with the information existing in [msdb] database.
It helps you by finding the last FULL backup, the last DIFFERENTIAL backup and all the TRANSACTION LOG backups needed.
It's quite comfortable when you are doing so many differential or log backups.
I hope you enjoy it!!!

Script done by Josep Martínez based on the script done by [jtshyman] named "List SQL backups"
Of course, there's no warranty, etc ...

The variable @DBName should be set to the name of the database you want to query on.
It is not case sensitive unless your collation is.

The variable @Days should be set to how many days back in the records you want to list backups for. By default set to 14 (old enought I think)
*/
-- Important because we're going to 'print' the sql code for the restore
SET NOCOUNT ON
DECLARE @DBName sysname
DECLARE @Days INT, @WithMove INT, @WithStats INT, @Move VARCHAR(MAX), @Stats VARCHAR(MAX)

-- These are the only parameters that needs to be configured
SET @DBName='mydatabase'
SET @Days=14 -- I think that's old enough
SET @WithMove = 1 -- 1 or 0; 1=include a "move xx to yy" statement. "1" requires that dbname is the current database
SET @WithStats = 1 -- 1 or 0; 1=include a "STATS=1" statement

SET @Move=''
IF @WithMove = 1 BEGIN
SELECT @Move = @Move + 'MOVE ''' + NAME + ''' TO ''' + Physical_Name + ''', ' + CHAR(13)
FROM sys.database_files
IF LEN(@Move) > 2 SELECT @Move = LEFT(@Move, LEN(@Move)-2)
END
SET @Stats=''
IF @WithStats=1 BEGIN
SELECT @Stats = 'STATS=1,'
END

CREATE TABLE #BackupsHistory
(
id INT IDENTITY(1,1),
backup_start_date DATETIME,
backup_type CHAR(1),
physical_device_name VARCHAR(2000)
)

INSERT INTO #BackupsHistory (backup_start_date, backup_type, physical_device_name)
SELECT S.backup_start_date,
S.type,
M.physical_device_name
FROM msdb..backupset S
JOIN msdb..backupmediafamily M ON M.media_set_id=S.media_set_id
WHERE S.database_name = @DBName
AND DATEDIFF(DAY,S.backup_start_date,GETDATE()) < @Days
ORDER by backup_start_date

DECLARE @lastFullBackup INT, @lastFullBackupPath VARCHAR(2000), @lastDifferentialBackup INT, @lastDifferentialBackupPath VARCHAR(2000)

-- We get the last Full backup done. That where we are going to start the restore process
SET @lastFullBackup = (SELECT TOP 1 id FROM #BackupsHistory WHERE backup_type='D' ORDER BY backup_start_date DESC)
SET @lastFullBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@lastFullBackup)

-- Restoring the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastFullBackupPath + ''''
PRINT 'WITH '

IF @WithMove =1 BEGIN
PRINT @Move
END
IF @WithStats=1 BEGIN
PRINT @Stats
END

-- IF it's there's no backup (differential or log) after it, we set to recovery
IF (@lastFullBackup = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY'
ELSE PRINT 'NORECOVERY'

PRINT 'GO'
PRINT ''


-- We get the last Differential backup (it must be done after the last Full backup)
SET @lastDifferentialBackup = (SELECT TOP 1 id FROM #BackupsHistory WHERE backup_type='I' AND id>@lastFullBackup ORDER BY backup_start_date DESC)
SET @lastDifferentialBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@lastDifferentialBackup)

-- IF there's a differential backup done after the full backup we script it
IF (@lastDifferentialBackup IS NOT NULL)
BEGIN
-- Restoring the Full backup
PRINT 'RESTORE DATABASE ' + @DBName
PRINT 'FROM DISK=''' + @lastDifferentialBackupPath + ''''
PRINT 'WITH '
IF @WithStats=1 BEGIN
PRINT @Stats
END
-- IF it's there's no backup (differential or log) after it, we set to recovery
IF (@lastDifferentialBackup = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY'
ELSE PRINT 'NORECOVERY'

PRINT 'GO'
PRINT ''
END


-- For TRANSACTION LOGs
DECLARE @i INT, @logBackupPath VARCHAR(2000)
IF (@lastDifferentialBackup IS NULL)
SET @i = @lastFullBackup + 1
ELSE SET @i = @lastDifferentialBackup + 1

-- Here whe are scripting the restores for the necessary logs
WHILE (@i <= (SELECT MAX(id) FROM #BackupsHistory))
BEGIN
SET @logBackupPath = (SELECT physical_device_name FROM #BackupsHistory WHERE id=@i)
PRINT 'RESTORE LOG ' + @DBName
PRINT 'FROM DISK=''' + @logBackupPath + ''''
PRINT 'WITH '
IF @WithStats=1 BEGIN
PRINT @Stats
END
-- IF it's the last transaction log, we'll say it to recover
IF (@i = (SELECT MAX(id) FROM #BackupsHistory))
PRINT 'RECOVERY'
ELSE PRINT 'NORECOVERY'

PRINT 'GO'
PRINT ''

SET @i = @i + 1
END


DROP TABLE #BackupsHistory



Josep
Josep
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 4675
Hi Henrik,

Thank you for the compliment.
I like your modifications. Thanks for them and I'm going to add them in the script. Probably making a stored procedure...


Josep.
vvkp
vvkp
Grasshopper
Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)Grasshopper (20 reputation)

Group: General Forum Members
Points: 20 Visits: 164
Hi Joseph,
Really good work...I like it really.
Why don't you extend this to point of time restoration? that is if some one wants a database to restore at a point of time for a given date (your right now works for current date) then it should do .... what I mean is suppose if some one wants to rollback the database to some one month or 20 days back to some point of time...then it should work....may be by adding another parameter datetime...Is it possible? I am novice in this area

So if we give database name and point of time for particular date then it should give the TSQL like you are giving now with an additional Stopat the given datetime in the last step. Hope I am clear what I am requesting you.

Please let me know if you want more details.
Thanks,
Krishna.



Josep
Josep
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 4675
I like that you find it useful.

I've made the changes proposed by Henrik Staun Poulsen in a previous post and used it in a stored procedure because now there are more parameters. I'm testing it in some places and after I'm going to update it.

There's no problem to add a stopAt option. I have never used it, but for what I've read about it, it looks like that the time you want needs to me in the transaction log backup, so you would need old transaction logs to get that and apply an older full backup (no the last one) if you want to restore it to a date prior to it.
So, to support this feature could be done easily if the stopAt date is somewhere after the last differential backup or would be more difficult if it's before it or before the last full backup.

I don't know if I've explained myself...

And I'm curious for what reason you would need to use the stopAt, if it can be explained...


Regards,

Josep.
bitBIG
bitBIG
Grasshopper
Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)Grasshopper (21 reputation)

Group: General Forum Members
Points: 21 Visits: 189
Many thanks to Josep for the effort in creating the script, also to Henrik Staun Poulsen for augmenting the script with "move".

To get Henrik's script working on my SQL 2000 server, I had to change the following line from this:
SELECT @Move = @Move + 'MOVE ''' + NAME + ''' TO ''' + Physical_Name + ''', ' + CHAR(13)
FROM sys.database_files



to this:
SELECT @Move = @Move + 'MOVE ''' + RTRIM(NAME) + ''' TO ''' + RTRIM(FileName) + ''', ' + CHAR(13)
FROM dbo.sysfiles



I also found that VARCHAR(MAX) was introduced for SQL 2005, so I adapted it to VARCHAR(8000) which is an assumption on my part as to length required, but it works for me as my path/filenames aren't overly long.
DECLARE @Days INT, @WithMove INT, @WithStats INT, @Move VARCHAR(8000), @Stats VARCHAR(8000)



Regards,
bitBIG
logicinside22
logicinside22
Ten Centuries
Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)Ten Centuries (1K reputation)

Group: General Forum Members
Points: 1012 Visits: 1408
Hi Josep
this is the best script ever found ,i was looking for fromlong time. just few concersn like to make sure with you if you don't mind.
- I have backup files on shared location( with different folders like \\Server\Full Backups\Fulbackup.bak ) so does this script works for that kind of situation?
-I think Script find automatically latest backup files from list of old backup files right?

Aim to inspire rather than to teach.
SQL Server DBA
Josep
Josep
SSC Veteran
SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)SSC Veteran (290 reputation)

Group: General Forum Members
Points: 290 Visits: 4675
Hi!

I'm sorry to haven't answer, neither updated the script for so long. My appologises.

First of all, I've made a new version of the script, creating a stored procedure and adding the options With move and with stats written by Henrik. Thank you Henrik.

logicinside22, thank you for the compliment! The answer to your questions:
- You shouldn't have any problem using shared locations. But I haven't tested it.
- The script gets the backup information from msdb (I copy the query under these lines). The only think you should be carefull is that in your maintenance plan you should clean the history after the last backup date. If you do a full backup every weekend, the history retention in msdb should be higher, for example 15 days, to allow
the query to get the full backup information.

bitBIG, I've added the RTRIM to the script, but I prefered to keep the VARCHAR(MAX)

vvkp, I'm planning to allow point in time restore, but I don't know the stopAt time at the moment of scripting the restore. I'm planning to write a Powershell script that will write the restore script based on the files found, rather than the information on msdb. I'll post it here.

Regards,

Josep
Iwas Bornready
Iwas Bornready
SSCoach
SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)SSCoach (16K reputation)

Group: General Forum Members
Points: 16346 Visits: 885
Thanks for the script.
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search