Click here to monitor SSC
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Restore all Databases in a Directory


Restore all Databases in a Directory

Author
Message
Brian Knight
Brian Knight
SSCommitted
SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)SSCommitted (2K reputation)

Group: Moderators
Points: 1955 Visits: 235
Comments posted to this topic are about the item Restore all Databases in a Directory

Brian Knight
Free SQL Server Training Webinars
Jpotucek
Jpotucek
Right there with Babe
Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)Right there with Babe (790 reputation)

Group: General Forum Members
Points: 790 Visits: 1648
is there a '2005' version of this ?????



ramsvvss
ramsvvss
Forum Newbie
Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)Forum Newbie (4 reputation)

Group: General Forum Members
Points: 4 Visits: 358
Can someone give the 2005 version for this, please?
ahassan 82526
ahassan 82526
Forum Newbie
Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)Forum Newbie (1 reputation)

Group: General Forum Members
Points: 1 Visits: 8
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
RML51
Ten Centuries
Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)Ten Centuries (1.3K reputation)

Group: General Forum Members
Points: 1291 Visits: 1612
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
Steve Jones
SSC-Dedicated
SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)SSC-Dedicated (36K reputation)

Group: Administrators
Points: 36107 Visits: 18742
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

Follow me on Twitter: @way0utwest
Forum Etiquette: How to post data/code on a forum to get the best help
My Blog: www.voiceofthedba.com
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search