March 12, 2009 at 8:37 pm
Here is my script for litespeed restore , how can i do it with some cursors or any other technique so that i dont need to manually give all file names as i gave here for ndf files . I am looking for something that looks for all database files available in the .BAK file and restore accordingly. I want to run this script for all the databases in the sever like AUTO_RESTORE
exec master.dbo.xp_restore_database @database = N'DCC_REV',
@filename = N'D:\Backup\PA_REV.bak', @filenumber = 1,
@with = N'RECOVERY', @with = N'NOUNLOAD', @with = N'STATS = 10',
@with = N'REPLACE',
@with = N'MOVE N''DCC_REV'' TO N''D:\DATA\DCC_REV.mdf''',
@with = N'MOVE N''2007_01_REV'' TO N''D:\DATA\2007_01_REV.ndf''',
@with = N'MOVE N''2007_02_REV'' TO N''D:\DATA\2007_02_REV.ndf''',
@with = N'MOVE N''2007_03_REV'' TO N''D:\DATA\2007_03_REV.ndf''',
@with = N'MOVE N''2007_04_REV'' TO N''D:\DATA\2007_04_REV.ndf''',
@with = N'MOVE N''2007_05_REV'' TO N''D:\DATA\2007_05_REV.ndf''',
@with = N'MOVE N''2007_06_REV'' TO N''D:\DATA\2007_06_REV.ndf''',
@with = N'MOVE N''2007_07_REV'' TO N''D:\DATA\2007_07_REV.ndf''',
@with = N'MOVE N''2007_08_REV'' TO N''D:\DATA\2007_08_REV.ndf''',
@with = N'MOVE N''2007_09_REV'' TO N''D:\DATA\2007_09_REV.ndf''',
@with = N'MOVE N''2007_10_REV'' TO N''D:\DATA\2007_10_REV.ndf''',
@with = N'MOVE N''2007_11_REV'' TO N''D:\DATA\2007_11_REV.ndf''',
@with = N'MOVE N''2007_12_REV'' TO N''D:\DATA\2007_12_REV.ndf''',
@with = N'MOVE N''2008_01_REV'' TO N''D:\DATA\2008_01_REV.ndf''',
@with = N'MOVE N''2008_02_REV'' TO N''D:\DATA\2008_02_REV.ndf''',
@with = N'MOVE N''2008_03_REV'' TO N''D:\DATA\2008_03_REV.ndf''',
@with = N'MOVE N''2008_04_REV'' TO N''D:\DATA\2008_04_REV.ndf''',
@with = N'MOVE N''2008_05_REV'' TO N''D:\DATA\2008_05_REV.ndf''',
@with = N'MOVE N''2008_06_REV'' TO N''D:\DATA\2008_06_REV.ndf''',
@with = N'MOVE N''2008_07_REV'' TO N''D:\DATA\2008_07_REV.ndf''',
@with = N'MOVE N''2008_08_REV'' TO N''D:\DATA\2008_08_REV.ndf''',
@with = N'MOVE N''2008_09_REV'' TO N''D:\DATA\2008_09_REV.ndf''',
@with = N'MOVE N''2008_10_REV'' TO N''D:\DATA\2008_10_REV.ndf''',
@with = N'MOVE N''2008_11_REV'' TO N''D:\DATA\2008_11_REV.ndf''',
@with = N'MOVE N''2008_12_REV'' TO N''D:\DATA\2008_12_REV.ndf''',
@with = N'MOVE N''2009_01_REV'' TO N''D:\DATA\2009_01_REV.ndf''',
@with = N'MOVE N''2009_02_REV'' TO N''D:\DATA\2009_02_REV.ndf''',
@with = N'MOVE N''2009_03_REV'' TO N''D:\DATA\2009_03_REV.ndf''',
@with = N'MOVE N''2009_04_REV'' TO N''D:\DATA\2009_04_REV.ndf''',
@with = N'MOVE N''2009_05_REV'' TO N''D:\DATA\2009_05_REV.ndf''',
@with = N'MOVE N''2009_06_REV'' TO N''D:\DATA\2009_06_REV.ndf''',
@with = N'MOVE N''2009_07_REV'' TO N''D:\DATA\2009_07_REV.ndf''',
@with = N'MOVE N''2009_08_REV'' TO N''D:\DATA\2009_08_REV.ndf''',
@with = N'MOVE N''2009_09_REV'' TO N''D:\DATA\2009_09_REV.ndf''',
@with = N'MOVE N''2009_10_REV'' TO N''D:\DATA\2009_10_REV.ndf''',
@with = N'MOVE N''2009_11_REV'' TO N''D:\DATA\2009_11_REV.ndf''',
@with = N'MOVE N''2009_12_REV'' TO N''D:\DATA\2009_12_REV.ndf''',
@with = N'MOVE N''2010_01_REV'' TO N''D:\DATA\2010_01_REV.ndf''',
@with = N'MOVE N''2010_02_REV'' TO N''D:\DATA\2010_02_REV.ndf''',
@with = N'MOVE N''2010_03_REV'' TO N''D:\DATA\2010_03_REV.ndf''',
@with = N'MOVE N''2010_04_REV'' TO N''D:\DATA\2010_04_REV.ndf''',
@with = N'MOVE N''2010_05_REV'' TO N''D:\DATA\2010_05_REV.ndf''',
@with = N'MOVE N''2010_06_REV'' TO N''D:\DATA\2010_06_REV.ndf''',
@with = N'MOVE N''2010_07_REV'' TO N''D:\DATA\2010_07_REV.ndf''',
@with = N'MOVE N''2010_08_REV'' TO N''D:\DATA\2010_08_REV.ndf''',
@with = N'MOVE N''2010_09_REV'' TO N''D:\DATA\2010_09_REV.ndf''',
@with = N'MOVE N''2010_10_REV'' TO N''D:\DATA\2010_10_REV.ndf''',
@with = N'MOVE N''2010_11_REV'' TO N''D:\DATA\2010_11_REV.ndf''',
@with = N'MOVE N''2010_12_REV'' TO N''D:\DATA\2010_12_REV.ndf''',
@with = N'MOVE N''2011_01_REV'' TO N''D:\DATA\2011_01_REV.ndf''',
@with = N'MOVE N''2011_02_REV'' TO N''D:\DATA\2011_02_REV.ndf''',
@with = N'MOVE N''2011_03_REV'' TO N''D:\DATA\2011_03_REV.ndf''',
@with = N'MOVE N''2011_04_REV'' TO N''D:\DATA\2011_04_REV.ndf''',
@with = N'MOVE N''2011_05_REV'' TO N''D:\DATA\2011_05_REV.ndf''',
@with = N'MOVE N''2011_06_REV'' TO N''D:\DATA\2011_06_REV.ndf''',
@with = N'MOVE N''2011_07_REV'' TO N''D:\DATA\2011_07_REV.ndf''',
@with = N'MOVE N''2011_08_REV'' TO N''D:\DATA\2011_08_REV.ndf''',
@with = N'MOVE N''2011_09_REV'' TO N''D:\DATA\2011_09_REV.ndf''',
@with = N'MOVE N''2011_10_REV'' TO N''D:\DATA\2011_10_REV.ndf''',
@with = N'MOVE N''2011_11_REV'' TO N''D:\DATA\2011_11_REV.ndf''',
@with = N'MOVE N''2011_12_REV'' TO N''D:\DATA\2011_12_REV.ndf''',
@with = N'MOVE N''2012_01_REV'' TO N''D:\DATA\2012_01_REV.ndf''',
@with = N'MOVE N''2012_02_REV'' TO N''D:\DATA\2012_02_REV.ndf''',
@with = N'MOVE N''2012_03_REV'' TO N''D:\DATA\2012_03_REV.ndf''',
@with = N'MOVE N''2012_04_REV'' TO N''D:\DATA\2012_04_REV.ndf''',
@with = N'MOVE N''2012_05_REV'' TO N''D:\DATA\2012_05_REV.ndf''',
@with = N'MOVE N''2012_06_REV'' TO N''D:\DATA\2012_06_REV.ndf''',
@with = N'MOVE N''2012_07_REV'' TO N''D:\DATA\2012_07_REV.ndf''',
@with = N'MOVE N''2012_08_REV'' TO N''D:\DATA\2012_08_REV.ndf''',
@with = N'MOVE N''2012_09_REV'' TO N''D:\DATA\2012_09_REV.ndf''',
@with = N'MOVE N''2012_10_REV'' TO N''D:\DATA\2012_10_REV.ndf''',
@with = N'MOVE N''2012_11_REV'' TO N''D:\DATA\2012_11_REV.ndf''',
@with = N'MOVE N''2012_12_REV'' TO N''D:\DATA\2012_12_REV.ndf''',
@with = N'MOVE N''2013_01_REV'' TO N''D:\DATA\2013_01_REV.ndf''',
@with = N'MOVE N''2013_02_REV'' TO N''D:\DATA\2013_02_REV.ndf''',
@with = N'MOVE N''2013_03_REV'' TO N''D:\DATA\2013_03_REV.ndf''',
@with = N'MOVE N''2013_04_REV'' TO N''D:\DATA\2013_04_REV.ndf''',
@with = N'MOVE N''2013_05_REV'' TO N''D:\DATA\2013_05_REV.ndf''',
@with = N'MOVE N''2013_06_REV'' TO N''D:\DATA\2013_06_REV.ndf''',
@with = N'MOVE N''2013_07_REV'' TO N''D:\DATA\2013_07_REV.ndf''',
@with = N'MOVE N''2013_08_REV'' TO N''D:\DATA\2013_08_REV.ndf''',
@with = N'MOVE N''2013_09_REV'' TO N''D:\DATA\2013_09_REV.ndf''',
@with = N'MOVE N''2013_10_REV'' TO N''D:\DATA\2013_10_REV.ndf''',
@with = N'MOVE N''2013_11_REV'' TO N''D:\DATA\2013_11_REV.ndf''',
@with = N'MOVE N''2013_12_REV'' TO N''D:\DATA\2013_12_REV.ndf''',
@with = N'MOVE N''DCC_REV_log'' TO N''D:\DATA\DCC_REV_log.ldf'''
March 12, 2009 at 8:50 pm
This is what I use. The SP would take all backups from a particular folder and then use xp_restore_filelistonly to extract the list of the files. It will then loop through the list and build the restore commad.
CREATE PROCEDURE [dbo].[usp_RestoreDBsFromFolder]
(
@path varchar(100),
@newdatapath varchar(100),
@newindexpath varchar(100),
@newfulltextpath varchar(100),
@newlogpath varchar(100)
)
AS
-- !!! REMEMBER TO INCLUDE THE BACK SLASH INTO THE PATH !!!
--Declare @path varchar(100)
--Declare @newdatapath varchar(100)
--Declare @newindexpath varchar(100)
--Declare @newfulltextpath varchar(100)
--Declare @newlogpath varchar(100)
Declare @s nvarchar(1024)
Declare @fname varchar (100)
Declare @dfname varchar (100)
Declare @lfname varchar (100)
Declare @dbname varchar (100)
Declare @ext char (4)
Declare @FileID tinyint
set nocount on
create table #filelist (fname varchar(200))
create table #backuplist (FileId INT IDENTITY(1,1),
LogicalNamenvarchar(128), PhysicalNamenvarchar(128), Typechar(1), FileGroupNamenvarchar(128),
Size bigint, MaxSize bigint)
--set @path = '\\172.16.120.39\Backups\ODS\'
--set @newdatapath = 'D:\SQLData\'
--set @newindexpath = 'D:\SQLData\'
--set @newfulltextpath = 'D:\SQLData\'
--set @newlogpath = 'E:\SQLLogs\'
set @s = 'exec master..xp_cmdshell ''dir /B ' + @path + ''''
insert into #filelist
exec master..sp_executesql @s
set @fname = (select min(fname) from #filelist where fname like '%.bak')
while @fname is not null
begin
set @s = 'EXEC master.dbo.xp_restore_filelistonly @filename = ''' + @path + @fname + ''''
insert into #backuplist
exec master..sp_executesql @s
-- build restore command
set @dbname = substring (@fname, 1 , charindex ( '.', @fname, 1)-1)
set @s = 'EXEC master.dbo.xp_restore_database @Database = ''' + @dbname + ''', @Filename = "' + @path + @fname + '",' + ' @WITH = ''move '
set @FileID = (select MIN(FileId) from #backuplist where Type = 'D')
set @dfname = (select LogicalName from #backuplist where Type = 'D' and FileID= @FileID)
set @ext = (select substring(PhysicalName, charindex ( '.', PhysicalName, 18), 5) from #backuplist where LogicalName = @dfname)
while @dfname is not null
begin
set @s = @s + '"' + @dfname + '" to "' + @newdatapath + @dbname + '_' + @dfname + '_data' + @ext + '"'', @WITH = ''move '
set @FileID = (select MIN(FileId) from #backuplist where FileID > @FileID and Type = 'D')
set @dfname = (select LogicalName from #backuplist where Type = 'D' and FileID= @FileID)
set @ext = (select substring(PhysicalName, charindex ( '.', PhysicalName, 18), 5) from #backuplist where LogicalName = @dfname)
end
set @lfname = (select min(LogicalName) from #backuplist where Type = 'L')
set @ext = (select substring(PhysicalName, charindex ( '.', PhysicalName, 18), 5) from #backuplist where LogicalName = @lfname)
while @lfname is not null
begin
set @s = @s + '"' + @lfname + '" to "' + @newlogpath + @dbname + '_' + @lfname + '_log' + @ext + '"'''
set @lfname = (select min(LogicalName) from #backuplist where LogicalName > @lfname and Type = 'L')
end
exec master..sp_executesql @s
delete from #backuplist
DBCC CHECKIDENT (#backuplist, RESEED, 0)
--------------------------------------
set @fname = (select min(fname) from #filelist where fname > @fname and fname like '%.bak')
end
drop table #filelist
drop table #backuplist
set nocount off
GO
March 13, 2009 at 6:55 am
I tried this and get the follwing error:
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'move '.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move '.
Checking identity information: current identity value 'NULL', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
March 15, 2009 at 12:48 pm
Now i get this error...
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'move"2004_12_REV" to "E:\SQL'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move"2004_12_REV" to "E:\SQL'.
Checking identity information: current identity value '122', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
March 16, 2009 at 7:39 am
This was removed by the editor as SPAM
March 16, 2009 at 12:08 pm
Kent
I am working on this script now ..
http://www.sqlservercentral.com/Forums/Topic648516-146-1.aspx#bm674855
Can you put some idea on this, actually i am trying to modify my script which is used for LiteSpeed so that i can integrate the same in my new database structure where we have partitoned databases.
Thanks
March 17, 2009 at 6:56 am
This was removed by the editor as SPAM
March 17, 2009 at 11:09 am
guess I don't entirely understand why you would need to worry about the file list. I suppose if you wanted the ability to restore a single file from the list it would be handy or if you wanted the ability to move those files to a different physical location. Personally, I spent a long time determining where my files would exist on my drives in order to get the best performance I could. That's starting on a different topic though. 😉
Kent
do you mean that i need not wory about filelist when restoring a backup having multiple ndf files?? that means i dont need to do a restore using " @WITH= " option.
March 17, 2009 at 4:26 pm
Roust_m
I still get this error again and again, is there any thing worng in the syntax or anything else..
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'move '.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move '.
Checking identity information: current identity value 'NULL', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
Msg 0, Level 11, State 0, Line 0
A severe error occurred on the current command. The results, if any, should be discarded.
March 17, 2009 at 5:20 pm
Mike,
Sorry for the delayed response. Try changing this line:
set @ext = (select substring(PhysicalName, charindex ( '.', PhysicalName, 18), 5) from #backuplist where LogicalName = @dfname)
to
set @ext = (select substring(PhysicalName, charindex ( '.', PhysicalName, 1), 5) from #backuplist where LogicalName = @dfname)
I had very long paths and some weird characters for some file names, so I had to move the starting point of search for a dot character forward and forgot about it.
If this does not help, just put
"PRINT @s-2"
before
"exec master..sp_executesql @s-2
delete from #backuplist"
and post the result of the print command.
March 17, 2009 at 6:36 pm
still i get the same error:
here is the print..
EXEC master.dbo.xp_restore_database @Database = 'RevBudget', @Filename = "\\OBBEAVER\PA\RevBudget.BAKRevBudget.BAK", @WITH = 'move
yeah..i think my bak file name is duplicated.
March 17, 2009 at 6:58 pm
I see, you probably run it like this:
EXEC usp_RestoreDBsFromFolder
@path = '\\OBBEAVER\PA\RevBudget.BAK',
@newdatapath = 'D:\SQLData\',
@newindexpath = 'D:\SQLData\',
@newfulltextpath = 'D:\SQLData\',
@newlogpath = 'E:\SQLLogs\'
You should run it like this instead:
EXEC usp_RestoreDBsFromFolder
@path = '\\OBBEAVER\PA\',
@newdatapath = 'D:\SQLData\',
@newindexpath = 'D:\SQLData\',
@newfulltextpath = 'D:\SQLData\',
@newlogpath = 'E:\SQLLogs\'
The SP will pick all *.bak files in the folder and restore them on the server. The files have to have the same name as the databases. E.g. RevBudget.BAK will restore as RevBudget database. If you don't want other databases to be restored, then it should be the only file in the folder. You can also change the SP to take the filenames, not the folder names if you like.
March 17, 2009 at 7:14 pm
Now i have this from my print mesg
EXEC master.dbo.xp_restore_database @Database = 'RevBudget', @Filename = "\\OBBEAVER\PA\RevBudget.BAK", @WITH = 'move "2004_01_PA" to "F:\SQL_DATA\RevBudget_2004_01_PA_data.ndf"', @WITH = 'move "2004_02_PA" to "F:\SQL_DATA\RevBudget_2004_02_PA_data.ndf"', @WITH = 'move "2004_03_PA" to "F:\SQL_DATA\RevBudget_2004_03_PA_data.ndf"', @WITH = 'move "2004_04_PA" to "F:\SQL_DATA\RevBudget_2004_04_PA_data.ndf"', @WITH = 'move "2004_05_PA" to "F:\SQL_DATA\RevBudget_2004_05_PA_data.ndf"', @WITH = 'move "2004_06_PA" to "F:\SQL_DATA\RevBudget_2004_06_PA_data.ndf"', @WITH = 'move "2004_07_PA" to "F:\SQL_DATA\RevBudget_2004_07_PA_data.ndf"', @WITH = 'move "2004_08_PA" to "F:\SQL_DATA\RevBudget_2004_08_PA_data.ndf"', @WITH = 'move "2004_09_PA" to "F:\SQL_DATA\RevBudget_2004_09_PA_data.ndf"', @WITH = 'move "2004_10_PA" to "F:\SQL_DATA\RevBudget_2004_10_PA_data.ndf"', @WITH = 'move "2004_11_PA" to "F:\SQL_DATA\RevBudget_2004_11_PA_data.ndf"', @WITH = 'move "
Here is the error message
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string 'move "'.
Msg 102, Level 15, State 1, Line 1
Incorrect syntax near 'move "'.
Checking identity information: current identity value '122', current column value '0'.
DBCC execution completed. If DBCC printed error messages, contact your system administrator.
March 17, 2009 at 7:43 pm
I think still it will not work because i have nearly 140 ndf files and it will be more than 4000 strings.
is there any other way where it can loop through all the files listed in a restorefilelistonly and build a restore command.
thanks
Viewing 15 posts - 1 through 15 (of 22 total)
You must be logged in to reply to this topic. Login to reply