Ramblings of a DBA

Tara Kizer
posts - 166, comments - 835, trackbacks - 75

My Links

Advertisement

News

Subscribe
Search this Blog

Archives

Post Categories

Work

RESTORE stored procedure

The below stored procedure performs a RESTORE of the newest BAK file in the specified directory (@bkpDir).  I recommend putting it into an Admin database (hey just name it Admin!) rather than in master. 

----------------------------------------------------------------------------------------------------
-- OBJECT NAME         : isp_Restore
-- AUTHOR                    : Tara Duggan
-- DATE                          : November 3, 2005
-- INPUTS                      : @dbName - name to use for the restored database
--                                       @bkpDir - full path to the directory where the BAK files exist
--                                       @newDataLocation - new location and file name for the MDF, can be NULL
--                                       @newLogLocation - new location and file name for the LDF, can be NULL
-- OUTPUTS                    : None
-- RETURN CODES        : 0 - success
--                                        1 - invalid path
--                                        2 - no BAK files exist in @bkpDir
--                                        3 - backup contains more than 2 D files which isn't supported by this sproc
--                                        4 - restore error
-- DEPENDENCIES        : None
-- DESCRIPTION            : This stored procedure performs a restore of the newest BAK file in a specified directory.
-- EXAMPLES (optional)  :
/*
  DECLARE @rc tinyint
  EXEC @rc = dbo.isp_Restore @dbName = 'Db1', @bkpDir = 'H:\MSSQL\BACKUP\Db1', @newDataLocation = 'F:\MSSQL\DATA\Db1_Data.MDF', @newLogLocation = 'G:\MSSQL\DATA\Db1_Log.LDF'
  PRINT @rc
*/
----------------------------------------------------------------------------------------------------
CREATE  PROC dbo.isp_Restore
(@dbName sysname, @bkpDir nvarchar(1000), @newDataLocation nvarchar(1000), @newLogLocation nvarchar(1000))
AS

SET NOCOUNT ON

DECLARE @cmd sysname, @bkpFile nvarchar(1000), @sql nvarchar(4000), @logicalDataName nvarchar(128), @logicalLogName nvarchar(128)

-- Add a backslash to the end of the path if one doesn't exist
IF REVERSE(SUBSTRING(@bkpDir, 1, 1)) <> '\'
 SET @bkpDir = @bkpDir + '\'

-- Get files sorted by date
SET @cmd = 'dir ' + @bkpDir + '*.BAK /OD'

CREATE TABLE #Dir(DirInfo VARCHAR(7000)) -- Stores the dir results
CREATE TABLE #BackupFiles(BackupDate varchar(10), BackupFileName nvarchar(1000)) -- Stores only the data we want from the dir

INSERT INTO #Dir
EXEC master.dbo.xp_cmdshell @cmd

IF EXISTS (SELECT * FROM #Dir WHERE DirInfo = 'The system cannot find the path specified.')
 RETURN 1

INSERT INTO #BackupFiles
SELECT SUBSTRING(DirInfo, 1, 10), SUBSTRING(DirInfo, LEN(DirInfo) -  PATINDEX('% %', REVERSE(DirInfo)) + 2, LEN(DirInfo))
FROM #Dir
WHERE ISDATE(SUBSTRING(DirInfo, 1, 10)) = 1 AND DirInfo NOT LIKE '%<DIR>%'

IF @@ROWCOUNT = 0
 RETURN 2

-- Get the newest file
SELECT TOP 1 @bkpFile = BackupFileName
FROM #BackupFiles
ORDER BY BackupDate DESC

DROP TABLE #Dir, #BackupFiles

SET @cmd = @bkpDir + @bkpFile

-- Stores RESTORE FILELISTONLY result set
CREATE TABLE #RestoreFileListOnly
(
 LogicalName nvarchar(128),
 PhysicalName nvarchar(260),
 Type char(1),
 FileGroupName nvarchar(128),
 [Size] numeric(20,0),
 [MaxSize] numeric(20,0)
)

INSERT INTO #RestoreFileListOnly
EXEC('RESTORE FILELISTONLY FROM DISK = ''' + @cmd + '''')

IF @@ROWCOUNT <> 2
 RETURN 3

SELECT @logicalDataName = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'D'

SELECT @logicalLogName = LogicalName
FROM #RestoreFileListOnly
WHERE Type = 'L'

DROP TABLE #RestoreFileListOnly

SET @sql = ''
SET @sql = @sql + 'RESTORE DATABASE ' + @dbName + CHAR(10)
SET @sql = @sql + 'FROM DISK = ''' + @cmd + '''' + CHAR(10)
SET @sql = @sql + 'WITH' + CHAR(10)
SET @sql = @sql + CHAR(9) + 'REPLACE'

IF @newDataLocation IS NOT NULL
BEGIN
 SET @sql = @sql + ',' + CHAR(10)
 SET @sql = @sql + CHAR(9) + 'MOVE ''' + @logicalDataName + ''' TO ''' + @newDataLocation + ''''
END

IF @newLogLocation IS NOT NULL
BEGIN
 SET @sql = @sql + ',' + CHAR(10)
 SET @sql = @sql + CHAR(9) + 'MOVE ''' + @logicalLogName + ''' TO ''' + @newLogLocation + ''''
END

PRINT @sql
EXEC(@sql)

IF @@ERROR <> 0
 RETURN 4
ELSE
 RETURN 0

GO

If you don't use BAK as the extension for your backups or perhaps you are restoring transaction logs instead, then just change the line directly below the comment “Get files sorted by date“ to whatever meets your requirements.

Print | posted on Tuesday, November 08, 2005 1:23 PM | Filed Under [ SQL Server - Database Administration ]

Feedback

Gravatar

# Database maintenance routines

11/8/2005 4:24 PM | Ramblings of a DBA
Gravatar

# re: RESTORE stored procedure

Return Codes?

tsk, tsk....

Thanks though for the code.....

11/9/2005 8:10 AM | Brett
Gravatar

# re: RESTORE stored procedure

So you use output parameters? How do you exit the stored procedure, with labels/gotos? I don't think output parameters are appropriate here as I am indicating a problem, just like a return code. But really, either way you go, it's only a preference thing and not a rule. MS even chimed in on this when Kristen asked Paul to see what their opinion was.
11/9/2005 9:33 AM | Tara
Gravatar

# re: RESTORE stored procedure

Good code! I've been wanting ideas on dragging info out of xp_cmdshell and filelist commands.

disclaimer - I have not spent a lot of time digging yet....
but it *seems* SQL 2005 backup files have added more columns into the FileList. So the current temp table as you have it did not work.
I've not yet dug into the properties to add those new columns.
1/5/2006 2:52 PM | Clint
Gravatar

# re: RESTORE stored procedure

Once I start using 2005 for purposes other than getting up to speed on the product, I'll be testing and updating all of the maintenance routines that I've blogged. Stay tuned!
1/5/2006 2:55 PM | Tara
Gravatar

# re: RESTORE stored procedure

Nice code - the collection of the BackupFileName could be simplified by using

SET @cmd = 'dir ' + @bkpDir + '*.BAK /OD/B'

but maybe that's just being picky :)
1/13/2006 1:20 AM | Paul
Gravatar

# re: RESTORE stored procedure

What is the license on your fantastic scripts? (for example, I've extended some of your scripts some and wanted to publish them to my blog)
5/4/2006 11:32 AM | Matthew Martin
Gravatar

# re: RESTORE stored procedure

Feel free to post it just make sure to reference where you got the original code from.
5/4/2006 11:37 AM | Tara
Gravatar

# re: RESTORE stored procedure

Hi

The code looks brilliant but I keep getting a return code of 1 in SQL Express and can't work out what's wrong - the command completes successfully but the database is not restored.
5/31/2006 7:30 AM | Andy
Gravatar

# re: RESTORE stored procedure

I would suggest looking at the statement returned by the PRINT @sql line to see if it is a valid RESTORE command.
5/31/2006 9:32 AM | Tara
Gravatar

# re: RESTORE stored procedure

I try use with vb.net but something is wrong i cant find.

no one exception but dont work see how i pass parameter...

And if i use sql console ( EXEC srv_Restore @dbName = 'msdb', @bkpDir = 'c:\MSSQL\BACKUP\MSDB', @newDataLocation = null , @newLogLocation = null ) this go

all parametrs the same up

Thanks

miComando.CommandType = CommandType.StoredProcedure
miComando.Parameters.Add("@dbName", OleDbType.VarChar, 100).Value = dbName
miComando.Parameters.Add("@bkpDir", OleDbType.VarChar, 100).Value = (carpeta & "\")
miComando.Parameters.Add("@newDataLocation", OleDbType.Empty).Value = newDataLocation
miComando.Parameters.Add("@newLogLocation", OleDbType.Empty).Value = newLogLocation
11/30/2006 3:18 AM | Marcio
Comments have been closed on this topic.

Powered by:
Powered By Subtext Powered By ASP.NET