More Intelligent Backup and Restore for SQL Server 2005
Backing up and restoring databases is easy enough using Enterprise Manager or Management Studio, but sometimes you need to script these kinds of jobs. A while back I came across a More Intelligent Backup and Restore and it made life a lot easier. Unfortunately, the scripts aren't compatible on SQL Server 2005 due to some changes to schemas and a couple of system stored procedures. It wasn't difficult to modify the scripts to work on SQL Server 2005 so here you go.
Download the files here.
Download the files here.
2 Comments:
Phil, I donwloaded the scripts and the Backup part works like a charm. When I tried to run the Restore part I got the following error:
THIS IS WHAT I AM TRYING TO RESTORE
EXEC sp_ABRestoreDb 'dnndb', 'c:\Temp\dnndb.BAK'
THIS IS THE ERROR
RegQueryValueEx() returned error 2, 'The system cannot find the file specified.'
Msg 22001, Level 1, State 1
Msg 213, Level 16, State 7, Line 1
Insert Error: Column name or number of supplied values does not match table definition.
Msg 3013, Level 16, State 1, Line 1
RESTORE FILELIST is terminating abnormally.
Msg 50000, Level 16, State 1, Procedure sp_ABRestoreDb, Line 458
Couldn't restore database dnndb from file c:\Temp\dnndb.BAK since the number/type of logical devices do not match.
Any Ideas ????
I hope luis fixed this, but I got this same error and had to modify this part in sp_ABRestoreDb:
CREATE TABLE #FileListOutput (LogicalName nvarchar(128), -- Logical name of the file
PhysicalName nvarchar(260), -- Physical or operating-system name of the file
Type char(1), -- Data file (D) or a log file (L)
FileGroupName nvarchar(128) NULL, -- Name of the filegroup that contains the file
Size numeric(20,0) NULL, -- Current size in bytes
MaxSize numeric(20,0) NULL, -- Maximum allowed size in bytes
FileID bigint,
CreateLSN numeric (25,0),
DropLSN numeric (25,0),
UniqueID uniqueidentifier,
ReadOnlyLSN numeric (25,0),
ReadWriteLSN numeric (25,0),
BackupSizeInBytes bigint,
SourceBlockSize int,
FileGroupID int,
LogGroupGUID uniqueidentifier NULL,
DifferentialBaseLSN numeric(25,0) NULL,
DifferentialBaseGUID uniqueidentifier,
IsReadOnly bit,
IsPresent bit)
--RowNum int identity(1,1))
Post a Comment
<< Home