Restore .BAK - SQL Server Forums

Please start any new threads on our new site at https://forums.sqlteam.com. We've got lots of great SQL Server experts to answer whatever question you can come up with.

All Forums

General SQL Server Forums

New to SQL Server Programming

Restore .BAK

Author

Topic

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-07 : 04:56:32

I tried to adapt this from a procedure that successfully restores .sqb (SQL Backup) files. What am I doing wrong to get it to restore BAK files?

IF @filename1 like '%.bak'--and @filequantity = '1'
BEGIN
set @restorecmd = 'exec master..sqlbackup N'' "RESTORE DATABASE' +@newdbname
+ ' FROM DISK = '''''
+ @filename1
+ ''''''
+ ' WITH RECOVERY,'
+ 'MOVE '''''+ @dblogicdat +''''' TO ''''F:\SQL2K5Data\'+ @dbdata + ''''','
+ 'MOVE '''''+ @dblogiclog +''''' TO ''''F:\SQL2K5Logs\'+ @dblog +''''','
+ 'REPLACE, PASSWORD = ''''password''''"'''''

Exec(@restorecmd)

END

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

Working until "the morning sun sets the midnight sky on fire"!

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-07 : 05:04:13

These are the errors I am seeing (line # is wrong):

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 or an xmlnamespaces clause, the previous statement must be terminated with a semicolon.
Msg 132, Level 15, State 1, Line 1
The label 'F' has already been declared. Label names must be unique within a query batch or stored procedure.
Msg 105, Level 15, State 1, Line 1
Unclosed quotation mark after the character string ''''.

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

Working until "the morning sun sets the midnight sky on fire"!

webfred
Master Smack Fu Yak Hacker

8781 Posts

Posted - 2009-01-07 : 07:19:38

Make a print @restorecmd to see what happens.

Webfred

No, you're never too old to Yak'n'Roll if you're too young to die.

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-08 : 02:07:34

RESTORE DATABASE database FROM DISK = ''\\backupserver\SQL_Backups\Database\server\folder\database_20090107_401.BAK'' WITH RECOVERY,MOVE ''database_dat '' TO ''F:\SQL2K5Data\database.mdf '',MOVE ''database_log '' TO ''F:\SQL2K5Logs\database.ldf '',REPLACE, PASSWORD = ''password''"''

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

Working until "the morning sun sets the midnight sky on fire"!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 02:16:26

Looks like you are using Red Gate as your backup software. It allows you to use square brackets instead of single quotes around everything. This is highly desirable as it eliminates the issue you are having. Try square brackets instead. This syntax is only support in Red Gate and not in the native backup command or in other backup programs such as Quest's Litespeed.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-08 : 02:39:01

I am trying to run a restore from a native backup...My problem is I don't know the changes I need to make to get this to run the restore.

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

Working until "the morning sun sets the midnight sky on fire"!

tkizer
Almighty SQL Goddess

38200 Posts

Posted - 2009-01-08 : 02:48:31

I'm confused by your mentioning of sqb and sqlbackup.

Look at the output from the PRINT command. You'll need to remove all of those extra quotes.

Tara Kizer
Microsoft MVP for Windows Server System - SQL Server
http://weblogs.sqlteam.com/tarad/

Subscribe to my blog

GRAYWOLF
Posting Yak Master

106 Posts

Posted - 2009-01-08 : 03:45:24

Sorry, this entire procedure is to test the backups. We are backing up with Red Gate as well as native. I am having to process both types at the same time. It looks like the quotes were the problem...thanks

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

Working until "the morning sun sets the midnight sky on fire"!

darkdusky
Aged Yak Warrior

591 Posts

Posted - 2009-04-08 : 06:32:37

There is an excellent procedure by Allan Hirt to generate backup commands - this automatically generates file paths from system tables for different scenarios such as Full Backup, or Full + Transactional etc.

I got it from apress as source-code zip-file (it's a free download), can't see it on web so here's source:

--starts here:
if exists (select * from dbo.sysobjects where id = object_id(N'[dba_GenerateBackupCommands]') and OBJECTPROPERTy(id, N'IsProcedure') = 1)
drop procedure [dba_GenerateBackupCommands]
GO

create procedure dba_GenerateBackupCommands
@backuptype int,
@dbname varchar(128)
AS
--
-- Procedure Name: dba_GenerateBackupCommands
-- Purpose:
-- This stored procedure will build your RESTORE commands using the SQL Server system tables and DMVs.
-- Author: Allan Hirt
-- Version: 1.0, 10/07
--
-- @dbname = the name of the database to generate the commands for; must be in single quotes
-- Valid values for @backuptype
-- 1, which will return only a full backup
-- 2, which will return the combination of full + transaction logs
-- 3, which will return the combination of full + differential backups
-- 4, which will return the combination of full, differential, and transaction log backups
--
-- Example execution
-- exec dba_GenerateBackupCommands
-- @backuptype = 4,
-- @dbname = 'TestDB'
--

-- Variables
declare @fullid int
declare @diffid int
declare @trnid int
declare @fullcount int
declare @diffcount int
declare @trncount int
declare @filepath nvarchar(1000)
declare @mediaset int
declare @loopno int

-- Get the number of full backups
set @fullcount = (select count(*)
from msdb..backupset
where type = 'D'
and database_name = @dbname)

-- Get the number of differential backups
If @fullcount > 0
begin
set @fullid = (select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)
end

set @diffcount = (select count(*)
from msdb..backupset
where type = 'I'
and database_name = @dbname)

-- Get the # of transaction logs
set @trncount = (select count(*)
from msdb..backupset
where type = 'L'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname))

-- Full Backup Only
If @backuptype = 1
begin
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id =
(select media_set_id
from msdb..backupset
where type = 'D'
and database_name = @dbname
and backup_set_id =
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)))

PRINT 'RESTORE DATABASE [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39)
end

-- Full + Transaction Logs
If @backuptype = 2
begin
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id =
(select media_set_id
from msdb..backupset
where type = 'D'
and database_name = @dbname
and backup_set_id =
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)))

PRINT 'RESTORE DATABASE [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39) + ' WITH NORECOVERY'

set @loopno = 1

DECLARE TLogCursor CURSOR FOR
select backup_set_id
from msdb..backupset
where type = 'L'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)

OPEN TLogCursor

FETCH NEXT FROM TLogCursor INTO @trnid

WHILE @@FETCH_STATUS = 0
BEGIN
IF @loopno = @trncount
BEGIN
set @mediaset = (select media_set_id
from msdb..backupset
where backup_set_id = @trnid)
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id = @mediaset)

PRINT 'RESTORE LOG [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39)
END

IF @loopno <> @trncount
BEGIN
set @mediaset = (select media_set_id
from msdb..backupset
where backup_set_id = @trnid)
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id = @mediaset)

PRINT 'RESTORE LOG [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39) + ' WITH NORECOVERY'
END

SET @loopno = @loopno + 1
FETCH NEXT FROM TLogCursor INTO @trnid
END

CLOSE TLogCursor
DEALLOCATE TLogCursor
end

-- Full, Differential
If @backuptype = 3
begin
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id =
(select media_set_id
from msdb..backupset
where type = 'D'
and database_name = @dbname
and backup_set_id =
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)))

PRINT 'RESTORE DATABASE [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39) + ' WITH NORECOVERY'

set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id =
(select media_set_id
from msdb..backupset
where type = 'I'
and database_name = @dbname
and backup_set_id =
(select max(backup_set_id)
from msdb..backupset
where type = 'I'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname))))
PRINT 'RESTORE DATABASE [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39)
END

-- Full, Differential, and TLogs
If @backuptype = 4
begin
-- get the # of transaction logs after the differential
set @trncount = (select count(*)
from msdb..backupset
where type = 'L'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'I'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)))

set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id =
(select media_set_id
from msdb..backupset
where type = 'D'
and database_name = @dbname
and backup_set_id =
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)))

PRINT 'RESTORE DATABASE [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39) + ' WITH NORECOVERY'

set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id =
(select media_set_id
from msdb..backupset
where type = 'I'
and database_name = @dbname
and backup_set_id =
(select max(backup_set_id)
from msdb..backupset
where type = 'I'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname))))
PRINT 'RESTORE DATABASE [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39) + ' WITH NORECOVERY'

set @loopno = 1

DECLARE TLogCursor CURSOR FOR
select backup_set_id
from msdb..backupset
where type = 'L'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname)
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'I'
and database_name = @dbname
and backup_set_id >
(select max(backup_set_id)
from msdb..backupset
where type = 'D'
and database_name = @dbname))

OPEN TLogCursor

FETCH NEXT FROM TLogCursor INTO @trnid

WHILE @@FETCH_STATUS = 0
BEGIN
IF @loopno = @trncount
BEGIN
set @mediaset = (select media_set_id
from msdb..backupset
where backup_set_id = @trnid)
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id = @mediaset)

PRINT 'RESTORE LOG [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39)
END

IF @loopno <> @trncount
BEGIN
set @mediaset = (select media_set_id
from msdb..backupset
where backup_set_id = @trnid)
set @filepath = (select physical_device_name
from msdb..backupmediafamily
where media_set_id = @mediaset)

PRINT 'RESTORE LOG [' + LTRIM(RTRIM(@dbname)) + '] FROM DISK = N' + CHAR(39) + LTRIM(RTRIM(@filepath)) + CHAR(39) + ' WITH NORECOVERY'
END

SET @loopno = @loopno + 1
FETCH NEXT FROM TLogCursor INTO @trnid
END

CLOSE TLogCursor
DEALLOCATE TLogCursor
end
go

Subscribe to SQLTeam.com

SQLTeam.com Articles via RSS

SQLTeam.com Weblog via RSS

- Advertisement -

Resources