Autoi Restore - Litespeed

  • 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'''

  • 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-2 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-2 = 'exec master..xp_cmdshell ''dir /B ' + @path + ''''

    insert into #filelist

    exec master..sp_executesql @s-2

    set @fname = (select min(fname) from #filelist where fname like '%.bak')

    while @fname is not null

    begin

    set @s-2 = 'EXEC master.dbo.xp_restore_filelistonly @filename = ''' + @path + @fname + ''''

    insert into #backuplist

    exec master..sp_executesql @s-2

    -- build restore command

    set @dbname = substring (@fname, 1 , charindex ( '.', @fname, 1)-1)

    set @s-2 = '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-2 = @s-2 + '"' + @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-2 = @s-2 + '"' + @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-2

    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

  • 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.

  • 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.

  • I don't really like the interface that LiteSpeed provides. I just never got comfortable with it. As a result I have a few stored procedures for backup automation and one stored procedure that I can use for restores. I also built a couple control tables that help drive everything. We do daily full backups every morning, differential backups every two hours, and transaction log backups every 15 minutes. Knowing this, and having my control tables that tell me where all the files are going, I was able to write a single restore procedure that can handle a restore of any type of backup I request. We do restores regularly to development boxes so this procedure gets used very often.

    When I first started this I wasn't very knowledgeable about SQL Server so some of my control structures are not the best. I created a table with my own db_id field that doesn't correspond to the db_id that SQL Server uses. I'm a little to busy these days to go back and change it but I'll include my procedures and table DDL.

    3 Tables. "Backups", "Backups_Info", and "Ident_Type". Backups just lists the databases I have and determines which types of backups are to be performed. Ident_Type is a definition of the type of data found in Backups_Info. In my Ident_Type table I have the following data:

    130ProdDB Intraday Backup Location NULLvarcharNULL

    131ProdDB Daily Backup Location NULLvarcharNULL

    133ProdDB Weekly Backup Location NULLvarcharNULL

    135Restore Intraday From Location NULLvarcharNULL

    136Restore Daily From Location NULLvarcharNULL

    154ProdDB Transaction Log Backup LocationNULLvarcharNULL

    155TLog Backup Increment Value NULLint NULL

    156Most Recent Differential NULLvarcharNULL

    157Restore Transaction Log From LocationNULLvarcharNULL

    I use some easily found strings in the Backups_Info table to allow the stored procedures to function.

    #Date#

    #Time#

    #log_increment#

    Here is a listing of the entries in Backups_Info for one DB

    14130\\proddb\y_drive\intraday\SRI_Claims_@#date#-#time#.bak

    14131\\proddb\y_drive\Daily\SRI_Claims_@#Date#.bak

    14133\\proddb\c_drive\sql_backups\SRI_Claims.bak

    14135\\backupdev\sql_backups\intraday\SRI_Claims_@#date#-#time#.bak

    14136\\backupdev\sql_backups\Daily\SRI_Claims_@#Date#.bak

    14154\\proddb\y_drive\Transaction_Log\SRI_Claims\SRI_Claims_@#date#-#time#-#log_increment#.bak

    141557

    1415608

    14157\\backupdev\SQL_Backups\Transaction_Log\SRI_Claims\SRI_Claims_@#date#-#time#-#log_increment#.bak

    I have some SSIS packages that actually move the backups off to a NAS device and do cleanup work to retain backups for as long as I decide I want to keep them. Usually that's a few weeks.

    Backups Table

    CREATE TABLE [dbo].[Backups](

    [db_id] [int] IDENTITY(1,1) NOT NULL,

    [db_name] [varchar](255) NOT NULL,

    [intraday] [bit] NOT NULL,

    [daily] [bit] NOT NULL,

    [weekly] [bit] NOT NULL,

    [TLog] [bit] NULL,

    CONSTRAINT [PK_Backups] PRIMARY KEY CLUSTERED

    (

    [db_id] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    Backups Info Table

    CREATE TABLE [dbo].[Backups_Info](

    [db_id] [int] NOT NULL,

    [ident_type_id] [int] NOT NULL,

    [ident_value] [varchar](1000) NULL

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    GO

    ALTER TABLE [dbo].[Backups_Info] WITH CHECK ADD CONSTRAINT [FK_Backups_Info_Backups] FOREIGN KEY([db_id])

    REFERENCES [dbo].[Backups] ([db_id])

    GO

    ALTER TABLE [dbo].[Backups_Info] CHECK CONSTRAINT [FK_Backups_Info_Backups]

    Ident Type Table

    CREATE TABLE [dbo].[Ident_Type](

    [Ident_Type_ID] [int] IDENTITY(1,1) NOT NULL,

    [Ident_Desc] [varchar](255) NOT NULL,

    [Col_Order] [int] NULL,

    [Data_Type] [varchar](20) NOT NULL,

    [Lookup_Table] [varchar](255) NULL,

    CONSTRAINT [PK_Ident_Type] PRIMARY KEY CLUSTERED

    (

    [Ident_Type_ID] ASC

    )WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = ON, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON, FILLFACTOR = 80) ON [PRIMARY]

    ) ON [PRIMARY]

    GO

    SET ANSI_PADDING OFF

    Full Backup Stored Procedure

    CREATEPROCEDURE [dbo].[sp_Daily_Backup]

    AS

    DECLARE @year AS VARCHAR(2)

    declare @date varchar(3)

    declare @backupfilename varchar(255)

    DECLARE @backupdestination VARCHAR(255)

    DECLARE @cmd AS NVARCHAR(4000)

    DECLARE @cmd2 AS NVARCHAR(4000)

    DECLARE @dbid AS int

    declare @dbname varchar(255)

    --Delete yesterday's full backups from the local machine

    SET @cmd2 = 'del y:\daily\*.bak'

    EXEC xp_cmdshell @cmd2

    --We use Julian Dates. yyddd where ddd = the number of the day of the year. You can find Julian calendars on the web.

    SET @year = CAST(right(DATEPART(yy, GETDATE()), 2) AS VARCHAR(10))

    SET @date = CAST(DATEDIFF(day, '01/01/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(10)), GETDATE())+1 AS VARCHAR(10))

    SET @date = REPLICATE('0', 3-LEN(@date)) + @date

    --run last TLog backup for previous differential

    --this isn't perfect but it's as close as we're going to get

    --the force bit tells the tlog backup to run even though it is an even-numbered hour.

    EXEC sri_master.dbo.sp_TLog_Backup

    @force = 1

    DECLARE crs_work CURSOR FOR

    SELECTa.db_name,

    b.ident_value,

    c.ident_value,

    a.[DB_ID]

    FROMsri_master.dbo.backups a

    INNER JOINsri_master.dbo.backups_info b ON a.db_id = b.db_id and b.ident_type_id = 131

    INNER JOINsri_master.dbo.backups_info c ON a.DB_ID = c.DB_ID AND c.ident_type_id = 136

    WHEREa.daily = 1

    OPEN crs_work

    FETCH NEXT FROM crs_work INTO

    @dbname,

    @backupfilename,

    @backupdestination,

    @dbid

    WHILE @@fetch_status = 0

    BEGIN

    --set the backup filename - specified by ident_type_id = 131 in sri_master.dbo.backups_info

    set @backupfilename = replace(@backupfilename, '#Date#', @year + @date)

    --set the command to copy the finished backup file to another location - specified by ident_type_id = 136 in sri_master.dbo.backups_info

    SET @cmd = 'copy /y ' + @backupfilename + ' ' + replace(@backupdestination, '#Date#', @year + @date)

    --The actual backup command

    exec master.dbo.xp_backup_database @database= @dbname , @filename = @backupfilename, @init=1

    --copy the backup file to a remote location

    --EXEC xp_cmdshell @cmd --this will not work unless the database has xp_cmdshell enabled. It is disabled by default.

    --set hour field for subsequent t-log backups

    --This is set to '06' because the daily backups happen at 6AM. If we change that schedule we need to change the value here.

    --This values tells us which backup file the transaction logs are subsequent to

    UPDATE [SRI_Master].dbo.[Backups_Info]

    SET[ident_value] = '06'

    WHERE[db_id] = @dbid

    AND ident_type_id = 156

    --reset t-log backup increment to 0

    --This should always be set to zero after a full or differential backup.

    --The tlog backup procedure increments this value by one every time it runs.

    UPDATE sri_master.dbo.backups_info

    SETident_value = 0

    WHERE[db_id] = @dbid

    AND ident_type_id = 155

    FETCH NEXT FROM crs_work INTO

    @dbname,

    @backupfilename,

    @backupdestination,

    @dbid

    END

    close crs_work

    deallocate crs_work

    Differential Backup Stored Procedure

    CREATE PROCEDURE [dbo].[sp_Intraday_Backup] AS

    declare @hour varchar(10)

    DECLARE @date VARCHAR(3)

    DECLARE @year VARCHAR(2)

    declare @backupfilename varchar(255)

    DECLARE @backupdestination VARCHAR(255)

    declare @dbname varchar(255)

    DECLARE @dbid AS int

    DECLARE @cmd AS NVARCHAR(4000)

    set @hour = datepart(hh,getdate())

    set @hour = REPLICATE('0', 2-LEN(@hour)) + @hour

    --set @date = replace(convert(char(10), getdate(), 101), '/','')

    SET @year = CAST(right(DATEPART(yy, GETDATE()), 2) AS VARCHAR(10))

    SET @date = CAST(DATEDIFF(day, '01/01/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(10)), GETDATE())+1 AS VARCHAR(10))

    SET @date = REPLICATE('0', 3-LEN(@date)) + @date

    IF @hour = 6

    GOTO skip_backup

    --run last TLog backup for previous differential

    EXEC sri_master.dbo.sp_TLog_Backup

    @force = 1

    DECLARE crs_work CURSOR FOR

    SELECTa.[db_name],

    b.ident_value,

    c.ident_value,

    a.[db_id]

    FROMsri_master.dbo.backups a

    INNER JOINsri_master.dbo.backups_info b ON a.[db_id] = b.[db_id] and b.ident_type_id = 130

    INNER JOINsri_master.dbo.backups_info c ON a.[db_id] = c.[db_id] AND c.[ident_type_id] = 135

    WHEREa.intraday = 1

    OPEN crs_work

    FETCH NEXT FROM crs_work INTO

    @dbname,

    @backupfilename,

    @backupdestination,

    @dbid

    WHILE @@fetch_status = 0

    BEGIN

    --if @dbname = 'sc_deleted_contacts'

    begin

    set @backupfilename = replace(@backupfilename, '#time#', @hour)

    SET @backupfilename = REPLACE(@backupfilename, '#date#', @year+@date)

    SET @cmd = 'copy /Y ' + @backupfilename + ' ' + REPLACE(replace(@backupdestination, '#time#', @hour), '#date#', @year+@date)

    --print @backupfilename

    exec master.dbo.xp_backup_database @database=@dbname, @filename = @backupfilename, @init=1, @with = 'DIFFERENTIAL'

    --EXEC xp_cmdshell @cmd

    --set hour field for subsequent t-log backups

    UPDATE [SRI_Master].dbo.[Backups_Info]

    SET[ident_value] = @hour

    WHERE[db_id] = @dbid

    AND ident_type_id = 156

    --reset t-log backup increment to 0

    UPDATE sri_master.dbo.backups_info

    SETident_value = 0

    WHERE[db_id] = @dbid

    AND ident_type_id = 155

    end

    FETCH NEXT FROM crs_work INTO

    @dbname,

    @backupfilename,

    @backupdestination,

    @dbid

    END

    close crs_work

    deallocate crs_work

    skip_backup:

    Transaction Log Backup Stored Procedure

    CREATE PROCEDURE [dbo].[sp_TLog_Backup](

    @force AS BIT = 0

    )

    AS

    declare @backupfilename varchar(255)

    DECLARE @backupdestination VARCHAR(255)

    DECLARE @dbname AS VARCHAR(255)

    DECLARE @dbid AS int

    DECLARE @date AS VARCHAR(10)

    DECLARE @year AS VARCHAR(2)

    DECLARE @hour AS VARCHAR(2)

    DECLARE @currenthour AS VARCHAR(2)

    DECLARE @backup_increment AS INT

    DECLARE @cmd AS NVARCHAR(4000)

    set @currenthour = datepart(hh,getdate())

    SET @year = CAST(right(DATEPART(yy, GETDATE()), 2) AS VARCHAR(10))

    SET @date = CAST(DATEDIFF(day, '01/01/' + CAST(DATEPART(YEAR, GETDATE()) AS VARCHAR(10)), GETDATE())+1 AS VARCHAR(10))

    SET @date = REPLICATE('0', 3-LEN(@date)) + @date

    DECLARE crs_work CURSOR FOR

    SELECTa.[db_name],

    b.ident_value,

    c.ident_value,

    d.ident_value,

    e.ident_value,

    a.[db_id]

    FROMsri_master.dbo.backups a

    INNER JOINsri_master.dbo.backups_info b ON a.[db_id] = b.[db_id] and b.ident_type_id = 154

    INNER JOINsri_master.dbo.backups_info c ON a.[db_id] = c.[db_id] AND c.[ident_type_id] = 157

    INNER JOINsri_master.dbo.backups_info d ON a.[db_id] = d.[db_id] AND d.ident_type_id = 156

    INNER JOINsri_master.dbo.backups_info e ON a.[db_id] = e.[db_id] AND e.ident_type_id = 155

    WHEREa.TLog = 1

    OPEN crs_work

    FETCH NEXT FROM crs_work INTO

    @dbname,

    @backupfilename,

    @backupdestination,

    @hour,

    @backup_increment,

    @dbid

    --adjust date for the midnight run

    IF @currenthour < @hour

    begin

    SET @date = REPLICATE('0', 3-LEN(@date-1)) + @date-1

    END

    WHILE @@fetch_status = 0

    BEGIN

    --skip backup if a differential should be running (every two hours)

    --the differential backup itself will call this procedure and feed in "@force = 1" to cause the backup to run.

    IF @currenthour%2 = 0 AND DATEPART(minute, GETDATE()) < 15 AND @force = 0

    BEGIN

    GOTO skip_backup

    END

    SET @backupfilename = REPLACE(@backupfilename, '#date#', @year + @date)

    SET @backupfilename = REPLACE(@backupfilename, '#time#', @hour)

    SET @backupfilename = REPLACE(@backupfilename, '#log_increment#', @backup_increment)

    SET @cmd = 'copy /Y ' + @backupfilename + ' ' + REPLACE(REPLACE(replace(@backupdestination, '#time#', @hour), '#date#', @year+@date), '#log_increment#', @backup_increment)

    --exec master.dbo.xp_backup_database @database=@dbname, @filename = @backupfilename, @init=1, @with = 'DIFFERENTIAL'

    EXEC MASTER.dbo.xp_backup_log

    @database = @dbname,

    @filename = @backupfilename,

    @init = 1

    --EXEC xp_cmdshell @cmd

    UPDATE sri_master.dbo.[Backups_Info]

    SETident_value = @backup_increment + 1

    WHERE[db_id] = @dbid

    AND [ident_type_id] = 155

    skip_backup:

    FETCH NEXT FROM crs_work INTO

    @dbname,

    @backupfilename,

    @backupdestination,

    @hour,

    @backup_increment,

    @dbid

    END

    close crs_work

    deallocate crs_work

    Restore Stored Procedure

    You'll see a check near the beginning of this that prevents this procedure from running on our production server. We're a small shop and I haven't convinced my boss to let me lock things down nearly as tightly as I'd like. I needed to make sure no one else did a restore onto the production server inadvertently.

    CREATE procedure [dbo].[usp_Restore_DB](

    @db_name as varchar(255),

    @backup_type as varchar(255) = 'daily',

    @backup_date as varchar(10) = null,

    @backup_hour as VARCHAR(2) = NULL,

    @backup_pit AS DATETIME = null

    )

    AS

    /******************************************************************************************************************/

    -- Author:Kent Kester

    -- Description:Restores data from backup files.

    --@db_name: name of database to restore

    --@backup_type: 'Daily', 'Intraday', 'PIT' - PIT = Point-In_Time

    --@backup_date:Date to restore to for daily and intraday. Required for daily and intraday. Not used for PIT restores. mm/dd/yyyy

    --@backup_hour:Hour to restore to for intraday. Required for intraday. 2-Digit numeric-only varchar. Not used for PIT restores. '02', '10', '22'.

    --If you put in an odd hour number it will subtract 1 and continue to perform a restore.

    --@backup_pit:Point-in-time to restore to from transaction log backups. Required for point-in-time restores. 'mm/dd/yyyy hh:mi:ss' 24-hour format.

    -- Notes:

    --1)This is written specifically to work with our backup scheme as it stands. That is: Full Backups daily at 6AM, Differential Backups every 2 hours (except at 6AM),

    --Transaction log backups every 15 minutes. Changes would have to be made to this script if anything changes in the backup strategy.

    --DateInitialsChange Note

    -------------------------------------------------------------------------------------------------------------------

    --11/25/2008KLKCreated

    /******************************************************************************************************************/

    IF@@SERVERNAME = 'proddb'

    BEGIN

    PRINT 'Not restoring: It is really a bad idea to restore a database onto ProdDB. If you really want to do this you need to change the stored procedure.'

    GOTOskip_restore

    END

    ELSE

    BEGIN

    print 'Restoring ' + @db_name + ' ' + @backup_type

    declare @db_id as int

    declare @backup_location_full as varchar(1000) --location of the full backup file

    declare @backup_location_diff as varchar(1000) --location of the differential backup file

    declare @backup_location_tlog as varchar(1000) --location of the transaction log backup file

    DECLARE @backup_date_full AS VARCHAR(5) --Julian date of the relevant full backup (yyddd)

    DECLARE @backup_date_diff AS VARCHAR(5) --Julian date of the relevant differential backup (yyddd)

    DECLARE @backup_date_tlog AS VARCHAR(5) --Julian date of the relevant transaction log backup (yyddd)

    DECLARE @tlog_base_hour AS DATETIME --2-digit hour that indicates the differential backup most immediately prior to the requested point-in-time restore

    DECLARE @backup_tlog_max_increment AS VARCHAR(2) --the last transaction log backup file increment to process

    DECLARE @current_tlog_increment AS VARCHAR(2) --the current transaction log backup file increment being processed

    DECLARE @current_tlog_location AS VARCHAR(1000) --the actual transaction log backup file currently being processed

    --skip the restore if the backup_type is not one of the acceptable options

    IF @backup_type NOT IN ('daily', 'intraday', 'pit')

    BEGIN

    PRINT 'Acceptable backup types are "DAILY", "INTRADAY", or "PIT". PIT = point-in-time.'

    GOTO skip_restore

    END

    --fix @backup_hour to be a 2-digit value if it isn't

    IF LEN(@backup_hour) < 2

    BEGIN

    SET @backup_hour = REPLICATE('0', 2-LEN(@backup_hour )) + CAST(CAST(@backup_hour AS INT) AS VARCHAR(2))

    PRINT '@backup_hour must be 2 characters. Changing your value to: ' + @backup_hour

    END

    --set @backup_hour and @backup_date if point-in-time restore is requested

    IF @backup_type = 'pit'

    BEGIN

    SET @backup_hour = DATEDIFF(hour, CONVERT(CHAR(10), @backup_pit, 101), @backup_pit)

    SET @backup_date = CONVERT(CHAR(10), @backup_pit, 101)

    end

    --skip the restore if the hour requested is invalid

    if @backup_type = 'intraday' AND @backup_hour > 23

    BEGIN

    PRINT 'This procedure uses a 24 hour clock. Please choose an hour bewteen 0 and 23.'

    PRINT 'Midnight is 0-hour of the new day.'

    GOTO skip_restore

    END

    --revert to a restore of a full backup if intraday restore of 6AM is requested

    IF @backup_type = 'intraday' AND @backup_hour = 6

    BEGIN

    PRINT 'The intraday backups do not run at 6AM. That is a full backup. Restoring full backup instead.'

    SET @backup_type = 'daily'

    set @backup_hour = NULL

    END

    --subtract 1 from @backup_hour if odd-numbered hour is requested. Warn about this if restore of a differential is requested.

    IF @backup_hour % 2 > 0

    BEGIN

    IF @backup_type = 'intraday'

    BEGIN

    PRINT 'Critical backups occur every two hours. Setting backup_hour one hour PRIOR TO the hour requested.'

    END

    SET @backup_hour = REPLICATE('0', 2-LEN(CAST(CAST(@backup_hour AS INT) - 1 AS VARCHAR(2)))) + CAST(CAST(@backup_hour AS INT) - 1 AS VARCHAR(2))

    end

    --Set dates for full, differential, and transaction log files to be restored.

    --change date of full backup to restore if backup_type is not 'Daily' and hour is less than 6.

    IF @backup_hour 'Daily'

    BEGIN

    --SET @backup_date_full = right(DATEPART(YEAR, @backup_date), 2) + CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date)-1 AS VARCHAR(5))

    SET @backup_date_full = right(DATEPART(YEAR, @backup_date), 2) + REPLICATE('0', 3-LEN(CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date-1) AS VARCHAR(5)))) + CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date-1) AS VARCHAR(5))

    SET @backup_date_diff = right(DATEPART(YEAR, @backup_date), 2) + REPLICATE('0', 3-LEN(CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date) AS VARCHAR(5)))) + CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date) AS VARCHAR(5))

    SET @backup_date_tlog = @backup_date_diff

    END

    ELSE

    BEGIN

    SET @backup_date_full = right(DATEPART(YEAR, @backup_date), 2) + REPLICATE('0', 3-LEN(CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date) AS VARCHAR(5)))) + CAST(DATEDIFF(DAY, '12/31/' + right(DATEPART(YEAR, @backup_date)-1, 2), @backup_date) AS VARCHAR(5))

    SET @backup_date_diff = @backup_date_full

    SET @backup_date_tlog = @backup_date_full

    END

    PRINT @backup_date_full

    --skip restore if intraday restore is requested but no hour is given

    IF @backup_type = 'intraday' AND @backup_hour IS NOT NULL AND @backup_date IS NULL

    BEGIN

    PRINT 'You must specify a date if a time is specified.'

    GOTO skip_restore

    END

    --skip restore if point-in-time restore is requested but no value given for @backup_pit

    IF @backup_type = 'pit' AND @backup_pit IS NULL

    BEGIN

    PRINT 'You must specify a point in time if a point-in-time restore is requested.'

    GOTO skip_restore

    END

    --get db_id. This is not the system's db_id. It is the db_id stored in sri_master.dbo.backups.

    SELECT @db_id = db_id

    FROMsri_master.dbo.backups

    WHEREdb_name = @db_name

    --get the template for full_backup_restore_location

    SELECT@backup_location_full = ident_value

    FROMsri_master.dbo.backups_info

    WHEREdb_id = @db_id

    and ident_type_id = 136

    --get the template for differential_backup_restore_location

    SELECT@backup_location_diff = ident_value

    FROMsri_master.dbo.backups_info

    WHEREdb_id = @db_id

    and ident_type_id = 135

    --get the template for transaction_log_backup_restore_location

    SELECT@backup_location_tlog = ident_value

    FROMsri_master.dbo.backups_info

    WHEREdb_id = @db_id

    and ident_type_id = 157

    --set the actual file names of the full and differential backup files

    --set the template for the transaction log backup files

    --set the base hour for a point-in-time restore

    SET @backup_location_full = REPLACE(@backup_location_full, '#DATE#', @backup_date_full)

    SET @backup_location_diff = REPLACE(@backup_location_diff, '#DATE#', @backup_date_diff)

    SET @backup_location_diff = REPLACE(@backup_location_diff, '#TIME#', @backup_hour)

    SET @backup_location_tlog = REPLACE(@backup_location_tlog, '#DATE#', @backup_date_tlog)

    SET @backup_location_tlog = REPLACE(@backup_location_tlog, '#TIME#', @backup_hour)

    SET @tlog_base_hour = DATEADD(hour, CAST(@backup_hour AS INT), CONVERT(CHAR(10), @backup_pit, 101))

    --RESTORE A DAILY BACKUP FILE

    IF @backup_type = 'daily'

    BEGIN

    if @backup_location_full is not null

    exec master.dbo.xp_restore_database

    @database = @db_name,

    @filename = @backup_location_full,

    @filenumber = 1,

    @with = 'RECOVERY',

    @with = 'NOUNLOAD',

    @with = 'STATS = 10',

    @with = 'REPLACE'

    ELSE

    begin

    print 'Could not find file - Please make sure you set the needed parameters'

    GOTO skip_restore

    end

    END

    --RESTORE AN INTRADAY BACKUP FILE

    IF @backup_type = 'intraday'

    BEGIN

    if @backup_location_full is not NULL AND @backup_location_diff IS NOT NULL

    BEGIN

    --restore full backup

    exec master.dbo.xp_restore_database

    @database = @db_name,

    @filename = @backup_location_full,

    @filenumber = 1,

    @with = 'NORECOVERY',

    @with = 'NOUNLOAD',

    @with = 'STATS = 10',

    @with = 'REPLACE'

    --restore differential backup

    exec master.dbo.xp_restore_database

    @database = @db_name,

    @filename = @backup_location_diff,

    @filenumber = 1,

    @with = 'RECOVERY',

    @with = 'NOUNLOAD',

    @with = 'STATS = 10'

    END

    ELSE

    BEGIN

    print 'Could not find file - Please make sure you set the needed parameters'

    GOTO skip_restore

    END

    END

    --RESTORE TO A POINT IN TIME

    IF @backup_type = 'pit'

    BEGIN

    if @backup_location_full is not NULL AND @backup_location_diff IS NOT NULL AND @backup_pit IS NOT NULL

    BEGIN

    SET @current_tlog_increment = 0

    SET @backup_tlog_max_increment = CASE

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) < 15 THEN 0

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) >= 15 AND DATEDIFF(minute, @tlog_base_hour, @backup_pit) <= 29 THEN 1

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) >= 30 AND DATEDIFF(minute, @tlog_base_hour, @backup_pit) <= 44 THEN 2

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) >= 45 AND DATEDIFF(minute, @tlog_base_hour, @backup_pit) <= 59 THEN 3

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) >= 60 AND DATEDIFF(minute, @tlog_base_hour, @backup_pit) <= 74 THEN 4

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) >= 75 AND DATEDIFF(minute, @tlog_base_hour, @backup_pit) <= 89 THEN 5

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) >= 90 AND DATEDIFF(minute, @tlog_base_hour, @backup_pit) <= 104 THEN 6

    WHEN DATEDIFF(minute, @tlog_base_hour, @backup_pit) > 104 THEN 7

    END

    END

    ELSE

    BEGIN

    print 'Could not find file - Please make sure you set the needed parameters'

    GOTO skip_restore

    END

    --restore full backup

    exec master.dbo.xp_restore_database

    @database = @db_name,

    @filename = @backup_location_full,

    @filenumber = 1,

    @with = 'NORECOVERY',

    @with = 'NOUNLOAD',

    @with = 'STATS = 10',

    @with = 'REPLACE'

    --restore differential backup

    exec master.dbo.xp_restore_database

    @database = @db_name,

    @filename = @backup_location_diff,

    @filenumber = 1,

    @with = 'NORECOVERY',

    @with = 'NOUNLOAD',

    @with = 'STATS = 10'

    WHILE @current_tlog_increment < @backup_tlog_max_increment

    BEGIN

    SET @current_tlog_location = REPLACE(@backup_location_tlog, '#log_increment#', @current_tlog_increment)

    EXEC MASTER.dbo.xp_restore_log

    @database = @db_name,

    @filename = @current_tlog_location,

    @filenumber = 1,

    @with = 'NORECOVERY',

    @WITH = 'NOUNLOAD',

    @WITH = 'STATS = 10'

    SET @current_tlog_increment = @current_tlog_increment + 1

    END

    DECLARE @stopat AS VARCHAR(100)

    SET @stopat = 'STOPAT = N''' + CAST(@backup_pit AS VARCHAR(25)) + ''''

    SET @current_tlog_location = REPLACE(@backup_location_tlog, '#log_increment#', @current_tlog_increment)

    EXEC MASTER.dbo.xp_restore_log

    @database = @db_name,

    @filename = @current_tlog_location,

    @filenumber = 1,

    @with = 'RECOVERY',

    @WITH = 'NOUNLOAD',

    @WITH = 'STATS = 10',

    @WITH = @stopat

    END

    END

    skip_restore:

    This is far from perfect but it works for us for now. It hasn't failed us yet. Once we move to SQL 2008 I plan to ditch LiteSpeed since 2008 can do compressed backups.

  • 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

  • I have a development server that is really the only target for my restores and its drives are configured to be nearly identical to my production server. I let my web developers run restores on their own as needed - on dev only. I don't think I've ever run a restore on our production server except when we got new hardware and I was migrating.

    Knowing that I've got people that aren't really interested in learning all sorts of new things about the database, I tried to keep the restore procedure simple to execute. They can choose to restore the daily full backup, any of the differentials, or they can do a point-in-time restore. The procedure is written to handle several different sets of parameters with the easiest being:

    EXEC sri_master.dbo.[usp_Restore_DB]

    @db_name = 'db_name',

    @backup_date = '03/16/2009'

    I can't make it any easier for a web developer to do a restore. Well, I suppose I could default in a date...

    I have yet to deal with partitioned DB's. I don't rely on SQL or LiteSpeed to tell me what files are in a backup. I know I have a single .mdf and .ldf file in each backup. I know where the backup file is based on my control tables.

    I 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. 😉

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • 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.

  • Ok, your command is very long and does not fit into the string variable.

    Try changing

    Declare @s-2 nvarchar(1024)

    to

    Declare @s-2 nvarchar(4000)

  • 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 21 total)

You must be logged in to reply to this topic. Login to reply