Generate DB full and trans log restore stmts without Mgt Studio

Last Post 15 Dec 2011 10:03 AM by sqladmin. 5 Replies.
AddThis - Bookmarking and Sharing Button Printer Friendly
  •  
  •  
  •  
  •  
  •  
Sort:
PrevPrev NextNext
You are not authorized to post a reply.
Author Messages
Markus_SQL
New Member
New Member

--
27 May 2011 07:29 AM
Does anyone have a script to build database restore statements. a full db plus the restore logs without using Management Studio?  The reason I ask is that we have a prod SQL Server and a disaster Recovery SQL Server.  On the Disaster SQL Server there are other databases that we want to keep intact.  In a disaster I need to restore specific databases so I cannot restore master and msdb from the Prod SQL Server.  My thought was to restore msdb from the prod SQL Server as another name and the script out the full and trans log restore statements from that not using the Restore Database GUI.  This is SQL 2008.
rm
New Member
New Member

--
27 May 2011 10:44 AM
MSDB doesn't have restore statement anyway.
timjohnstone
New Member
New Member

--
30 Nov 2011 01:00 AM
Probably far too late for you, but this is the script I wrote a while ago that I use. DECLARE @SQL VarChar(8000) SET @SQL = 'BACKUP DATABASE [' + DB_Name() + '] TO DISK = N''\\10.129.11.210\sqldumps\xxx\' + DB_Name() + '_Full_File1_' + REPLACE(CAST(GetDate() AS VarChar(11)), ' ', '_') + '.bak'', DISK = N''\\10.129.11.210\sqldumps\xxx\' + DB_Name() + '_Full_File2_' + REPLACE(CAST(GetDate() AS VarChar(11)), ' ', '_') + '.bak'' WITH INIT , NOUNLOAD , NAME = N''' + DB_NAME() + ' Backup'', NOSKIP , STATS = 10, NOFORMAT RESTORE DATABASE [' + DB_NAME() + '] FROM DISK = N''\\10.129.11.210\sqldumps\xxx\' + DB_Name() + '_Full_File1_' + REPLACE(CAST(GetDate() AS VarChar(11)), ' ', '_') + '.bak'', DISK = N''\\10.129.11.210\sqldumps\xxx\' + DB_Name() + '_Full_File2_' + REPLACE(CAST(GetDate() AS VarChar(11)), ' ', '_') + '.bak'' WITH FILE = 1, ' SELECT @SQL = @SQL + 'MOVE N''' + RTrim(name) + ''' TO N''' + Case When FileID = 1 Then 'D:\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\' Else Case When GroupID = 0 Then 'E:\TLogs\' Else 'K:\DATA\' End End + RTrim(name) + Case When GroupID = 0 Then '.LDF' Else '.MDF' End + ''', -- ' + Cast(Convert(Decimal(12,2),Round(( ( ( Size * 64.0 ) / 1024 ) / 8 ),2)) AS VarChar(10)) + 'Mb ' FROM sysFiles SET @SQL = @SQL + 'NOUNLOAD, STATS = 10' PRINT @SQL
sqladmin
New Member
New Member

--
30 Nov 2011 07:56 AM
i posted a script here years ago that would produce the restore output for each file per each db using full ( not tlog ) backups,
but could be modified to incorporate the tlog backups.

you would have to dig, but i'm sure it's out there still. search for 'restore' under sqladmin.

hope it's useful.
russellb
New Member
New Member

--
06 Dec 2011 06:14 AM
Mike! Been a while!
sqladmin
New Member
New Member

--
15 Dec 2011 10:03 AM
russellb... whatuuup

yeah it's been a while.   nice to see the whole crew on the site is still around 
You are not authorized to post a reply.

Acceptable Use Policy