Restore all Databases in a Directory

  • Brian Knight

    One Orange Chip

    Points: 27249

    Comments posted to this topic are about the item Restore all Databases in a Directory

  • Jpotucek

    SSCoach

    Points: 18583

    is there a '2005' version of this ?????

  • ramsvvss

    SSC Enthusiast

    Points: 146

    Can someone give the 2005 version for this, please?

  • ahassan 82526

    SSC Journeyman

    Points: 83

    This is a great query that helps move and restore dbs very bulk very fast, however, it was made to work with sql 2000 and couldn't believe that it was not updated for any of the newer versions so we have modified the script to run on SQL Servers 2008, 2008r2, and 2012 (match the number of columns for the mentioned versions). we will also include a bulk back query that backs up all dbs in an instance but the system dbs.

    the modifications were (we believe that Server 2005 should use the same code included in the script):

    SQL Server 2008

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    SQL Server 2008r2

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    SQL Serevr 2012

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    the complete scrip is

    Original with modification

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CSS_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[sp_CSS_RestoreDir]

    GO

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

    -- Procedure Name: sp_CSS_RestoreDir

    -- Purpose: Restore one or many database backups from a single directory. This script reads all

    -- database backups that are found in the @restoreFromDir parameter.

    -- Any database backup that matches the form %_db_% will be restored to

    -- the file locations specified in the RestoreTo... parameter(s). The database

    -- will be restored to a database name that is based on the database backup

    -- file name. For example Insurance_db_200305212302.BAK will be restored to

    -- a database named Insurance. The characters preceeding the '_db_' text determines

    -- the name.

    --

    -- Input Parameters: @restoreFromDir - The directory where the database backups are located

    -- @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to

    -- @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If

    -- this parameter is not provided then the log files are restored to @restoreToDataDir.

    -- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,

    -- also allows for secondary data files 'ndf' to to be in a different dir than mdf files

    -- @DBName - restore just this one database - selects the latest bak file

    --

    -- Output Parameters: None

    --

    -- Return Values:

    --

    -- Written By: Chris Gallelli -- 8/22/03

    -- Modified By:

    -- Modifications: Bruce Canaday -- 10/20/2003

    -- Added optional parameters @MatchFileList and @DBName

    -- Bruce Canaday -- 10/24/2003

    -- Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame

    -- This is to handle databases such as ALIS_DB

    -- Bruce Canaday -- 10/28/2003

    -- When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist

    -- Bruce Canaday -- 11/04/2003

    -- Allow spaces in the @restoreFromDir directory name

    -- paul Wegmann -- 07/11/2012

    -- Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012

    -- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    -- FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    -- FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    -- Paul Wegmann -- 07/11/2012 changed from stored proc to set

    -- declare @restoreFromDir varchar(255),

    -- @restoreToDataDir varchar(255),

    -- @restoreToLogDir varchar(255) ,

    -- @MatchFileList char(1) ,

    -- @OneDBName varchar(255)

    --

    -- set @restoreFromDir = 'location of directory where your backup exist'

    -- set @restoreToDataDir = 'location where your data files will be restored too'

    -- set @restoreToLogDir = 'location of LDF files needs to be restored too'

    -- set @MatchFileList = 'N'

    -- set @OneDBName = null

    --

    -- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log' (if you use declare/set option then you don't have to use this command to restore)

    --

    -- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore)

    --

    -- Reviewed By: Anoar Hassan

    --

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

    CREATE proc sp_CSS_RestoreDir

    @restoreFromDir varchar(255),

    @restoreToDataDir varchar(255)= null,

    @restoreToLogDir varchar(255) = null,

    @MatchFileList char(1) = 'N',

    @OneDBName varchar(255) = null

    as

    -- to use delare/set option, use the following code and commond -- the create proc SP_CSS_RestoreDir

    -- declare @restoreFromDir varchar(255),

    -- @restoreToDataDir varchar(255),

    -- @restoreToLogDir varchar(255) ,

    -- @MatchFileList char(1) ,

    -- @OneDBName varchar(255)

    --

    -- set @restoreFromDir = 'M:\WEBQA2008R2'

    -- set @restoreToDataDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\DATA'

    -- set @restoreToLogDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\Log'

    -- set @MatchFileList = 'N'

    -- set @OneDBName = null

    --If a directory for the Log file is not supplied then use the data directory

    If @restoreToLogDir is null

    set @restoreToLogDir = @restoreToDataDir

    set nocount on

    declare @filename varchar(40),

    @cmd varchar(500),

    @cmd2 varchar(500),

    @DataName varchar (255),

    @LogName varchar (255),

    @LogicalName varchar(255),

    @PhysicalName varchar(255),

    @Type varchar(20),

    @FileGroupName varchar(255),

    @Size varchar(20),

    @MaxSize varchar(20),

    @restoreToDir varchar(255),

    @searchName varchar(255),

    @DBName varchar(255),

    @PhysicalFileName varchar(255)

    create table #dirList (filename varchar(100))

    --edited by Anoar

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    --Get the list of database backups that are in the restoreFromDir directory

    if @OneDBName is null

    select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'

    else

    select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

    insert #dirList exec master..xp_cmdshell @cmd

    select * from #dirList where filename like '%_db_%' --order by filename

    if @OneDBName is null

    declare BakFile_csr cursor for

    select * from #dirList where filename like '%_db_%bak' order by filename

    else

    begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above

    select @searchName = @OneDBName + '_db_%bak'

    declare BakFile_csr cursor for

    select top 1 * from #dirList where filename like @searchName

    end

    open BakFile_csr

    fetch BakFile_csr into @filename

    while @@fetch_status = 0

    begin

    select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"

    insert #filelist exec ( @cmd )

    if @OneDBName is null

    select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)

    else

    select @dbName = @OneDBName

    select @cmd = "RESTORE DATABASE " + @dbName +

    " FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "

    PRINT ''

    PRINT 'RESTORING DATABASE ' + @dbName

    declare DataFileCursor cursor for

    select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize

    from #filelist

    open DataFileCursor

    fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

    while @@fetch_status = 0

    begin

    if @MatchFileList != 'Y'

    begin -- RESTORE with MOVE option

    select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))

    if @Type = 'L'

    select @restoreToDir = @restoreToLogDir

    else

    select @restoreToDir = @restoreToDataDir

    select @cmd = @cmd +

    " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "

    end

    else

    begin -- Match the file list, attempt to create any missing directory

    select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )

    select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir

    exec master..xp_cmdshell @cmd2

    end

    fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

    end -- DataFileCursor loop

    close DataFileCursor

    deallocate DataFileCursor

    select @cmd = @cmd + ' REPLACE'

    --select @cmd 'command'

    EXEC (@CMD)

    truncate table #filelist

    fetch BakFile_csr into @filename

    end -- BakFile_csr loop

    close BakFile_csr

    deallocate BakFile_csr

    drop table #dirList

    return

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    to bulk backup dbs in a single instance:

    DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'O:\your backup location\'

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    DECLARE db_cursor CURSOR FOR

    SELECT name

    FROM master.dbo.sysdatabases

    WHERE name NOT IN ('master','model','msdb','tempdb')

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @fileName = @path + @name + '_db_' + @fileDate + '.BAK'

    BACKUP DATABASE @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

  • RML51

    SSCarpal Tunnel

    Points: 4410

    ahassan 82526 (7/12/2012)


    This is a great query that helps move and restore dbs very bulk very fast, however, it was made to work with sql 2000 and couldn't believe that it was not updated for any of the newer versions so we have modified the script to run on SQL Servers 2008, 2008r2, and 2012 (match the number of columns for the mentioned versions). we will also include a bulk back query that backs up all dbs in an instance but the system dbs.

    the modifications were (we believe that Server 2005 should use the same code included in the script):

    SQL Server 2008

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    SQL Server 2008r2

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    SQL Serevr 2012

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    the complete scrip is

    Original with modification

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    if exists (select * from dbo.sysobjects where id = object_id(N'[dbo].[sp_CSS_RestoreDir]') and OBJECTPROPERTY(id, N'IsProcedure') = 1)

    drop procedure [dbo].[sp_CSS_RestoreDir]

    GO

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

    -- Procedure Name: sp_CSS_RestoreDir

    -- Purpose: Restore one or many database backups from a single directory. This script reads all

    -- database backups that are found in the @restoreFromDir parameter.

    -- Any database backup that matches the form %_db_% will be restored to

    -- the file locations specified in the RestoreTo... parameter(s). The database

    -- will be restored to a database name that is based on the database backup

    -- file name. For example Insurance_db_200305212302.BAK will be restored to

    -- a database named Insurance. The characters preceeding the '_db_' text determines

    -- the name.

    --

    -- Input Parameters: @restoreFromDir - The directory where the database backups are located

    -- @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to

    -- @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If

    -- this parameter is not provided then the log files are restored to @restoreToDataDir.

    -- @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,

    -- also allows for secondary data files 'ndf' to to be in a different dir than mdf files

    -- @DBName - restore just this one database - selects the latest bak file

    --

    -- Output Parameters: None

    --

    -- Return Values:

    --

    -- Written By: Chris Gallelli -- 8/22/03

    -- Modified By:

    -- Modifications: Bruce Canaday -- 10/20/2003

    -- Added optional parameters @MatchFileList and @DBName

    -- Bruce Canaday -- 10/24/2003

    -- Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame

    -- This is to handle databases such as ALIS_DB

    -- Bruce Canaday -- 10/28/2003

    -- When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist

    -- Bruce Canaday -- 11/04/2003

    -- Allow spaces in the @restoreFromDir directory name

    -- paul Wegmann -- 07/11/2012

    -- Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012

    -- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    -- FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    -- FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    -- Paul Wegmann -- 07/11/2012 changed from stored proc to set

    -- declare @restoreFromDir varchar(255),

    -- @restoreToDataDir varchar(255),

    -- @restoreToLogDir varchar(255) ,

    -- @MatchFileList char(1) ,

    -- @OneDBName varchar(255)

    --

    -- set @restoreFromDir = 'location of directory where your backup exist'

    -- set @restoreToDataDir = 'location where your data files will be restored too'

    -- set @restoreToLogDir = 'location of LDF files needs to be restored too'

    -- set @MatchFileList = 'N'

    -- set @OneDBName = null

    --

    -- Sample Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log' (if you use declare/set option then you don't have to use this command to restore)

    --

    -- Alternate Execution: exec sp_CSS_RestoreDir 'C:\sqldb\sql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore)

    --

    -- Reviewed By: Anoar Hassan

    --

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

    CREATE proc sp_CSS_RestoreDir

    @restoreFromDir varchar(255),

    @restoreToDataDir varchar(255)= null,

    @restoreToLogDir varchar(255) = null,

    @MatchFileList char(1) = 'N',

    @OneDBName varchar(255) = null

    as

    -- to use delare/set option, use the following code and commond -- the create proc SP_CSS_RestoreDir

    -- declare @restoreFromDir varchar(255),

    -- @restoreToDataDir varchar(255),

    -- @restoreToLogDir varchar(255) ,

    -- @MatchFileList char(1) ,

    -- @OneDBName varchar(255)

    --

    -- set @restoreFromDir = 'M:\WEBQA2008R2'

    -- set @restoreToDataDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\DATA'

    -- set @restoreToLogDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\Log'

    -- set @MatchFileList = 'N'

    -- set @OneDBName = null

    --If a directory for the Log file is not supplied then use the data directory

    If @restoreToLogDir is null

    set @restoreToLogDir = @restoreToDataDir

    set nocount on

    declare @filename varchar(40),

    @cmd varchar(500),

    @cmd2 varchar(500),

    @DataName varchar (255),

    @LogName varchar (255),

    @LogicalName varchar(255),

    @PhysicalName varchar(255),

    @Type varchar(20),

    @FileGroupName varchar(255),

    @Size varchar(20),

    @MaxSize varchar(20),

    @restoreToDir varchar(255),

    @searchName varchar(255),

    @DBName varchar(255),

    @PhysicalFileName varchar(255)

    create table #dirList (filename varchar(100))

    --edited by Anoar

    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),

    FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,

    FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    --Get the list of database backups that are in the restoreFromDir directory

    if @OneDBName is null

    select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'

    else

    select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

    insert #dirList exec master..xp_cmdshell @cmd

    select * from #dirList where filename like '%_db_%' --order by filename

    if @OneDBName is null

    declare BakFile_csr cursor for

    select * from #dirList where filename like '%_db_%bak' order by filename

    else

    begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above

    select @searchName = @OneDBName + '_db_%bak'

    declare BakFile_csr cursor for

    select top 1 * from #dirList where filename like @searchName

    end

    open BakFile_csr

    fetch BakFile_csr into @filename

    while @@fetch_status = 0

    begin

    select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"

    insert #filelist exec ( @cmd )

    if @OneDBName is null

    select @dbName = left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)

    else

    select @dbName = @OneDBName

    select @cmd = "RESTORE DATABASE " + @dbName +

    " FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "

    PRINT ''

    PRINT 'RESTORING DATABASE ' + @dbName

    declare DataFileCursor cursor for

    select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize

    from #filelist

    open DataFileCursor

    fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

    while @@fetch_status = 0

    begin

    if @MatchFileList != 'Y'

    begin -- RESTORE with MOVE option

    select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))

    if @Type = 'L'

    select @restoreToDir = @restoreToLogDir

    else

    select @restoreToDir = @restoreToDataDir

    select @cmd = @cmd +

    " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "

    end

    else

    begin -- Match the file list, attempt to create any missing directory

    select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )

    select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir

    exec master..xp_cmdshell @cmd2

    end

    fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

    end -- DataFileCursor loop

    close DataFileCursor

    deallocate DataFileCursor

    select @cmd = @cmd + ' REPLACE'

    --select @cmd 'command'

    EXEC (@CMD)

    truncate table #filelist

    fetch BakFile_csr into @filename

    end -- BakFile_csr loop

    close BakFile_csr

    deallocate BakFile_csr

    drop table #dirList

    return

    GO

    SET QUOTED_IDENTIFIER OFF

    GO

    SET ANSI_NULLS ON

    GO

    to bulk backup dbs in a single instance:

    DECLARE @name VARCHAR(50) -- database name

    DECLARE @path VARCHAR(256) -- path for backup files

    DECLARE @fileName VARCHAR(256) -- filename for backup

    DECLARE @fileDate VARCHAR(20) -- used for file name

    SET @path = 'O:\your backup location\'

    SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)

    DECLARE db_cursor CURSOR FOR

    SELECT name

    FROM master.dbo.sysdatabases

    WHERE name NOT IN ('master','model','msdb','tempdb')

    OPEN db_cursor

    FETCH NEXT FROM db_cursor INTO @name

    WHILE @@FETCH_STATUS = 0

    BEGIN

    SET @fileName = @path + @name + '_db_' + @fileDate + '.BAK'

    BACKUP DATABASE @name TO DISK = @fileName

    FETCH NEXT FROM db_cursor INTO @name

    END

    CLOSE db_cursor

    DEALLOCATE db_cursor

    I could really use this but I get the following error after I create and try to run this SP. I've tried it using with and without the set/declare option.

    RESTORING DATABASE MY2008_db_Data12_From_backup_2012_11_01_180010_0528432.

    Msg 102, Level 15, State 1, Line 1

    Incorrect syntax near '.'.

    Msg 319, Level 15, State 1, Line 1

    Incorrect syntax near the keyword 'with'. If this statement is a common table expression, an xmlnamespaces clause or a change tracking context clause, the previous statement must be terminated with a semicolon.

    Any ideas?

  • Steve Jones - SSC Editor

    SSC Guru

    Points: 714078

    The error is what is says. The CTE needs a semi colon before it runs.

    If you have a CTE using the WITH statement, you can't have this.

    select col1 from mytable

    with mycte(xx, yy) ...

    you need this:

    select col1 from mytable

    ;

    with mycte(xx, yy) ...

    Find the line from the error, and add a semi colon.l

  • nitin.varshney

    SSC Eights!

    Points: 888

    Modified Script with some changes......
    Add dbname and filename from bak file, Add secondary file location if there is different folder, add with recovery or norecovery flag.
    Will be helpful in db migration..... Running successful for SQL 2012.
    *********************************************************************

    Script
    **********************************************************************************************************************
    USE [master]
    GO

    /****** Object: StoredProcedure [dbo].[sp_CSS_RestoreDir_ndf_Final]  Script Date: 31.07.2017 14:17:08 ******/
    SET ANSI_NULLS ON
    GO

    SET QUOTED_IDENTIFIER OFF
    GO

    /***************************************************************************************/
    -- Procedure Name:  sp_CSS_RestoreDir
    -- Purpose:    Restore one or many database backups from a single directory. This script reads all
    --   database backups that are found in the @restoreFromDir parameter.
    --   Any database backup that matches the form %_db_% will be restored to
    --   the file locations specified in the RestoreTo... parameter(s). The database
    --   will be restored to a database name that is based on the database backup
    --   file name. For example Insurance_db_200305212302.BAK will be restored to
    --   a database named Insurance. The characters preceeding the '_db_' text determines
    --   the name.
    --
    -- Input Parameters: @restoreFromDir - The directory where the database backups are located
    --   @restoreToDataDir - The directory where the data files (i.e. MDF) will be restored to
    --   @restoreToLogDir - The directory where the log files (i.e. LDF) will be restored to. If
    --    this parameter is not provided then the log files are restored to @restoreToDataDir.
    --       @MatchFileList - set to 'Y' to restore to same directory structure contained in the backup,
    --         also allows for secondary data files 'ndf' to to be in a different dir than mdf files
    --       @DBName - restore just this one database - selects the latest bak file
    --    
    -- Output Parameters: None
    --
    -- Return Values: 
    --
    -- Written By:   Chris Gallelli -- 8/22/03
    -- Modified By:  
    -- Modifications:  Bruce Canaday -- 10/20/2003
    --        Added optional parameters @MatchFileList and @DBName
    --       Bruce Canaday -- 10/24/2003
    --        Get the db name as the characters LEFT of the right most '_db_' in the bak filenaame
    --        This is to handle databases such as ALIS_DB
    --       Bruce Canaday -- 10/28/2003
    --        When using @MatchFileList = 'Y' attempt to create any directory that doesn't exist
    --       Bruce Canaday -- 11/04/2003
    --        Allow spaces in the @restoreFromDir directory name
    --      paul Wegmann -- 07/11/2012
    --       Chnaged the create table to allow more feilds to support SQL Server 2008r2 and SQl Server 2012
    -- create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
    --            FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
    --            FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )
    --      Paul Wegmann -- 07/11/2012 changed from stored proc to set
    --           declare  @restoreFromDir varchar(255), 
    --              @restoreToDataDir varchar(255),
    --             @restoreToLogDir varchar(255) ,
    --              @MatchFileList char(1) ,
    --             @OneDBName varchar(255)
    --
    --             set @restoreFromDir = 'location of directory where your backup exist'
    --             set @restoreToDataDir = 'location where your data files will be restored too'
    --             set @restoreToLogDir = 'location of LDF files needs to be restored too'
    --                                         set @restoreToSecondaryDataDir = 'Location of secondary data file for all databases'
    --                                         set @recovery = 0 -- default is 0 (without recovery)    ; 1 for with recovery                    
    --             set @MatchFileList = 'N'
    --             set @OneDBName = null
    --                    Nitin Varshney - 07/01/2017 1. changes for picking db name from backup file,
    --                                                2. Added secondary File Location
    --                                                3. With Recovery or without recovery option
    --                                                4. All bak file will be select from the folder
    --                                                5. Print all restore command.
    --                            Sample Execution : exec sp_CSS_RestoreDir_ndf_Final 'G:\SQLBACKUP_File', 'G:\Data1', 'G:\Log','G:\Data2',1
    --
    -- Sample Execution: exec sp_CSS_RestoreDir_ndf_Final 'C:\sqldb\sql_backup', 'C:\sqldb\sql_data', 'C:\sqldb\sql_log' (if you use declare/set option then you don't have to use this command to restore)
    --
    -- Alternate Execution: exec sp_CSS_RestoreDir_ndf_Final 'C:\sqldb\sql_backup', @MatchFileList = 'Y' (if you use declare/set option then you don't have to use this command to restore)
    --
    -- Reviewed By: Anoar Hassan
    --
    /***************************************************************************************/

    Alter proc sp_CSS_RestoreDir_ndf_Final
      @restoreFromDir varchar(255),
     @restoreToDataDir varchar(255)= null,
     @restoreToLogDir varchar(255) = null,
     @restoreToSecondaryDataDir varchar(255) = null,
     @recovery int = 0,
      @MatchFileList char(1) = 'N',
      @OneDBName varchar(255) = null
    as

    -- to use delare/set option, use the following code and commond -- the create proc SP_CSS_RestoreDir
    --  declare  @restoreFromDir varchar(255), 
    --      @restoreToDataDir varchar(255),
    --     @restoreToLogDir varchar(255) ,
    --      @MatchFileList char(1) ,
    --      @OneDBName varchar(255)
    --
    --     set @restoreFromDir = 'M:\WEBQA2008R2'
    --     set @restoreToDataDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\DATA'
    --     set @restoreToLogDir = 'J:\MSSQL10_50.WEBQASQL2008R2\MSSQL\Log'
    --     set @MatchFileList = 'N'
    --     set @OneDBName = null

    --If a directory for the Log file is not supplied then use the data directory
    If @restoreToLogDir is null
     set @restoreToLogDir = @restoreToDataDir

    If @restoreToSecondaryDataDir is null
     set @restoreToSecondaryDataDir = @restoreToDataDir

    set nocount on

    declare @filename   varchar(255),
      @cmd     varchar(8000),
      @cmd2     varchar(500),
        @cmd3             varchar(255),
      @DataName   varchar (255),
      @LogName    varchar (255),
      @LogicalName  varchar(255),
      @PhysicalName  varchar(255),
      @Type     varchar(20),
      @FileGroupName  varchar(255),
      @Size     varchar(20),
      @MaxSize    varchar(20),
      @restoreToDir  varchar(255),
       @searchName   varchar(255),
      @DBName    varchar(255),
       @PhysicalFileName varchar(255)

    create table #dirList (filename varchar(100))
    --edited by Anoar
    create table #filelist (LogicalName varchar(255), PhysicalName varchar(255), Type varchar(20), FileGroupName varchar(255), Size varchar(20), MaxSize varchar(20),
                FileId int,CreateLSN bit, DropLSN bit, UniqueID varchar(255),ReadOnlyLSn bit, ReadWriteLSN bit, backupSizeInBytes varchar(50), SourceBlockSize int,
                FileGroupid Int, LogGroupGUID varchar(255),DifferentialBaseLSN varchar(255),DifferentialBaseGUID varchar(255),isReadOnly bit, IsPresent bit,TDEThumbprint varchar(255) )

    Create table #Dbnameheaders
    (
      BackupName varchar(256),  BackupDescription varchar(256),  BackupType varchar(256),    ExpirationDate varchar(256),  Compressed varchar(256),  Position varchar(256), 
        DeviceType varchar(256),    UserName varchar(256),  ServerName varchar(256),  DatabaseName varchar(256),  DatabaseVersion varchar(256),    DatabaseCreationDate varchar(256),
      BackupSize varchar(256),  FirstLSN varchar(256),  LastLSN varchar(256),    CheckpointLSN varchar(256),  DatabaseBackupLSN varchar(256),  BackupStartDate varchar(256),
      BackupFinishDate varchar(256),    SortOrder varchar(256),  CodePage varchar(256),  UnicodeLocaleId varchar(256),  UnicodeComparisonStyle varchar(256),   CompatibilityLevel varchar(256),
      SoftwareVendorId varchar(256),  SoftwareVersionMajor varchar(256),    SoftwareVersionMinor varchar(256),  SoftwareVersionBuild varchar(256),  MachineName varchar(256),  Flags varchar(256),  
      BindingID varchar(256),  RecoveryForkID varchar(256),  Collation varchar(256),  FamilyGUID varchar(256),    HasBulkLoggedData varchar(256),  IsSnapshot varchar(256),  IsReadOnly varchar(256),
      IsSingleUser varchar(256),    HasBackupChecksums varchar(256),  IsDamaged varchar(256),  BeginsLogChain varchar(256),  HasIncompleteMetaData varchar(256),    IsForceOffline varchar(256),
      IsCopyOnly varchar(256),  FirstRecoveryForkID varchar(256),  ForkPointLSN varchar(256),    RecoveryModel varchar(256),  DifferentialBaseLSN varchar(256),  DifferentialBaseGUID varchar(256),  
      BackupTypeDescription varchar(256),  BackupSetGUID varchar(256),  CompressedBackupSize varchar(256),    Containment varchar(256)
    );

    --Get the list of database backups that are in the restoreFromDir directory
    if @OneDBName is null
     select @cmd = 'dir /b /on "' +@restoreFromDir+ '"'
    else
     select @cmd = 'dir /b /o-d /o-g "' +@restoreFromDir+ '"'

    --select @cmd,'AllFiles' -- Give All Files in Backup Folder

    insert #dirList exec master..xp_cmdshell @cmd

    --select * from #dirList where filename like '%_db_%' --order by filename -- List all the files in backup folder
    select * from #dirList where filename like '%.bak' --order by filename -- List all the files in backup folder

    if @OneDBName is null
     declare BakFile_csr cursor for
      select * from #dirList where filename like '%.bak' order by filename
    else
     begin -- single db, don't order by filename, take default latest date /o-d parm in dir command above
      select @searchName = @OneDBName + '_db_%bak'
         select @searchName = @OneDBName + '%.bak'
      declare BakFile_csr cursor for
      select top 1 * from #dirList where filename like @searchName
     end

    open BakFile_csr
    fetch BakFile_csr into @filename

    while @@fetch_status = 0
     begin
       select @cmd = "RESTORE FILELISTONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
       insert #filelist exec ( @cmd )

    --identify the db name from backup file

    select @cmd3 = "RESTORE HEADERONLY FROM disk = '" + @restoreFromDir + "\" + @filename + "'"
    insert #Dbnameheaders exec (@cmd3)

    Select @dbName = DatabaseName from #Dbnameheaders
     
    --identify the db name from backup file [END Here]     

         
       if @OneDBName is null
        select @dbName = @dbName --left(@filename,datalength(@filename) - patindex('%_bd_%',reverse(@filename))-3)
       else
      select @dbName = @OneDBName

       select @cmd = "RESTORE DATABASE " + @dbName +
      " FROM DISK = '" + @restoreFromDir + "\" + @filename + "' WITH "

       PRINT ''
       PRINT 'RESTORING DATABASE ' + @dbName

         --Select * from #filelist ---List of files in backupfile all mdf,ndf,ldf

       declare DataFileCursor cursor for
      select LogicalName, PhysicalName, Type, FileGroupName, Size, MaxSize
      from #filelist

     open DataFileCursor
       fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

       while @@fetch_status = 0
        begin
         if @MatchFileList != 'Y'
          begin -- RESTORE with MOVE option
         select @PhysicalFileName = reverse(substring(reverse(rtrim(@PhysicalName)),1,patindex('%\%',reverse(rtrim(@PhysicalName)))-1 ))

    /*
         if @Type = 'L'
          select @restoreToDir = @restoreToLogDir
         else
          select @restoreToDir = @restoreToDataDir
    */

         if @Type = 'L'
          select @restoreToDir = @restoreToLogDir
         else if @PhysicalFileName like '%.ndf'
                    select @restoreToDir = @restoreToSecondaryDataDir
                 else
          select @restoreToDir = @restoreToDataDir

      
         select @cmd = @cmd +
          " MOVE '" + @LogicalName + "' TO '" + @restoreToDir + "\" + @PhysicalFileName + "', "
          end
         else
          begin -- Match the file list, attempt to create any missing directory
           select @restoreToDir = left(@PhysicalName,datalength(@PhysicalName) - patindex('%\%',reverse(@PhysicalName)) )
           select @cmd2 = "if not exist " +@restoreToDir+ " md " +@restoreToDir
           exec master..xp_cmdshell @cmd2
          end

         fetch DataFileCursor into @LogicalName, @PhysicalName, @Type, @FileGroupName, @Size, @MaxSize

        end -- DataFileCursor loop

     close DataFileCursor
       deallocate DataFileCursor

         if @recovery = 0
       select @cmd = @cmd + ' NORECOVERY, STATS = 5'
         else
         select @cmd = @cmd + ' RECOVERY, STATS = 5'

       select @cmd 'command'
         --select 'test1'
       --EXEC (@CMD)

       truncate table #filelist
         truncate table #Dbnameheaders

       fetch BakFile_csr into @filename

     end -- BakFile_csr loop

    close BakFile_csr
    deallocate BakFile_csr

    drop table #dirList

    return

Viewing 7 posts - 1 through 7 (of 7 total)

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