Hi, I'm trying to restore an incremental backup but it
fails. Here are the TSQL commands and the results. Any
idea?
RESTORE DATABASE [LWHKPROD] FROM DISK =
N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
RESTORE DATABASE [LWHKPROD] FROM DISK =
N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
Results:
...
100 percent restored.
Processed 20312 pages for database 'LWHKPROD',
file 'lwxpertData' on file 1.
Processed 1 pages for database 'LWHKPROD',
file 'lwxpert_log' on file 1.
RESTORE DATABASE successfully processed 20313 pages in
9.794 seconds (16.989 MB/sec).
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
Tue.bak' to database 'LWHKPROD'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Was the backup "LWHKPROD-Tue.bak" actually backed up using the WITH
DIFFERENTIAL clause? Show the T-SQL for the backups too.
Eric
PepitoDBA wrote:
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
|||Run the following to confirm that LWHKPROD-Tue.bak' is a differential
backup file:
RESTORE HEADERONLY FROM DISK = 'D:\ASIA\LWHKPROD-Tue.bak'
Look at the BackupType column to check if the value is 5 (a differential
backup).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:16f7601c448c7$b531fb60$a601280a@.phx.gbl...
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>
|||Yes, it is a type 5 (incremental)
>--Original Message--
>Was the backup "LWHKPROD-Tue.bak" actually backed up
using the WITH[vbcol=seagreen]
>DIFFERENTIAL clause? Show the T-SQL for the backups too.
>Eric
>
>PepitoDBA wrote:
N'lwxpertData'
>
>.
>
|||Was there a full backup made between 31 May and June 2 i.e. the date of the
differential backup you are trying to restore? You could look at the
backupset table e.g.
SELECT type, * FROM msdb..backupset WHERE name = 'LWHKPROD' AND
backup_start_date > '31-May-2004' ORDER BY backup_start_date
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0401c44942$1088da90$a001280a@.phx.gbl...[vbcol=seagreen]
> Yes, it is a type 5 (incremental)
> using the WITH
> N'lwxpertData'
Showing posts with label tsql. Show all posts
Showing posts with label tsql. Show all posts
Friday, March 9, 2012
restoring incremental backup fails
Hi, I'm trying to restore an incremental backup but it
fails. Here are the TSQL commands and the results. Any
idea?
RESTORE DATABASE [LWHKPROD] FROM DISK =
N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
RESTORE DATABASE [LWHKPROD] FROM DISK =
N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
Results:
...
100 percent restored.
Processed 20312 pages for database 'LWHKPROD',
file 'lwxpertData' on file 1.
Processed 1 pages for database 'LWHKPROD',
file 'lwxpert_log' on file 1.
RESTORE DATABASE successfully processed 20313 pages in
9.794 seconds (16.989 MB/sec).
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
Tue.bak' to database 'LWHKPROD'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Was the backup "LWHKPROD-Tue.bak" actually backed up using the WITH
DIFFERENTIAL clause? Show the T-SQL for the backups too.
Eric
PepitoDBA wrote:
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.|||Run the following to confirm that LWHKPROD-Tue.bak' is a differential
backup file:
RESTORE HEADERONLY FROM DISK = 'D:\ASIA\LWHKPROD-Tue.bak'
Look at the BackupType column to check if the value is 5 (a differential
backup).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:16f7601c448c7$b531fb60$a601280a@.phx
.gbl...
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>|||Yes, it is a type 5 (incremental)
>--Original Message--
>Was the backup "LWHKPROD-Tue.bak" actually backed up
using the WITH
>DIFFERENTIAL clause? Show the T-SQL for the backups too.
>Eric
>
>PepitoDBA wrote:
N'lwxpertData'[vbcol=seagreen]
>
>.
>|||Was there a full backup made between 31 May and June 2 i.e. the date of the
differential backup you are trying to restore? You could look at the
backupset table e.g.
SELECT type, * FROM msdb..backupset WHERE name = 'LWHKPROD' AND
backup_start_date > '31-May-2004' ORDER BY backup_start_date
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0401c44942$1088da90$a001280a@.phx
.gbl...[vbcol=seagreen]
> Yes, it is a type 5 (incremental)
> using the WITH
> N'lwxpertData'
fails. Here are the TSQL commands and the results. Any
idea?
RESTORE DATABASE [LWHKPROD] FROM DISK =
N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
RESTORE DATABASE [LWHKPROD] FROM DISK =
N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
Results:
...
100 percent restored.
Processed 20312 pages for database 'LWHKPROD',
file 'lwxpertData' on file 1.
Processed 1 pages for database 'LWHKPROD',
file 'lwxpert_log' on file 1.
RESTORE DATABASE successfully processed 20313 pages in
9.794 seconds (16.989 MB/sec).
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
Tue.bak' to database 'LWHKPROD'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Was the backup "LWHKPROD-Tue.bak" actually backed up using the WITH
DIFFERENTIAL clause? Show the T-SQL for the backups too.
Eric
PepitoDBA wrote:
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.|||Run the following to confirm that LWHKPROD-Tue.bak' is a differential
backup file:
RESTORE HEADERONLY FROM DISK = 'D:\ASIA\LWHKPROD-Tue.bak'
Look at the BackupType column to check if the value is 5 (a differential
backup).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:16f7601c448c7$b531fb60$a601280a@.phx
.gbl...
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK =
> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>|||Yes, it is a type 5 (incremental)
>--Original Message--
>Was the backup "LWHKPROD-Tue.bak" actually backed up
using the WITH
>DIFFERENTIAL clause? Show the T-SQL for the backups too.
>Eric
>
>PepitoDBA wrote:
N'lwxpertData'[vbcol=seagreen]
>
>.
>|||Was there a full backup made between 31 May and June 2 i.e. the date of the
differential backup you are trying to restore? You could look at the
backupset table e.g.
SELECT type, * FROM msdb..backupset WHERE name = 'LWHKPROD' AND
backup_start_date > '31-May-2004' ORDER BY backup_start_date
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0401c44942$1088da90$a001280a@.phx
.gbl...[vbcol=seagreen]
> Yes, it is a type 5 (incremental)
> using the WITH
> N'lwxpertData'
restoring incremental backup fails
Hi, I'm trying to restore an incremental backup but it
fails. Here are the TSQL commands and the results. Any
idea?
RESTORE DATABASE [LWHKPROD] FROM DISK = N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
RESTORE DATABASE [LWHKPROD] FROM DISK = N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
Results:
...
100 percent restored.
Processed 20312 pages for database 'LWHKPROD',
file 'lwxpertData' on file 1.
Processed 1 pages for database 'LWHKPROD',
file 'lwxpert_log' on file 1.
RESTORE DATABASE successfully processed 20313 pages in
9.794 seconds (16.989 MB/sec).
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
Tue.bak' to database 'LWHKPROD'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Was the backup "LWHKPROD-Tue.bak" actually backed up using the WITH
DIFFERENTIAL clause? Show the T-SQL for the backups too.
Eric
PepitoDBA wrote:
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.|||Run the following to confirm that LWHKPROD-Tue.bak' is a differential
backup file:
RESTORE HEADERONLY FROM DISK = 'D:\ASIA\LWHKPROD-Tue.bak'
Look at the BackupType column to check if the value is 5 (a differential
backup).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:16f7601c448c7$b531fb60$a601280a@.phx.gbl...
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>|||Yes, it is a type 5 (incremental)
>--Original Message--
>Was the backup "LWHKPROD-Tue.bak" actually backed up
using the WITH
>DIFFERENTIAL clause? Show the T-SQL for the backups too.
>Eric
>
>PepitoDBA wrote:
>> Hi, I'm trying to restore an incremental backup but it
>> fails. Here are the TSQL commands and the results. Any
>> idea?
>> RESTORE DATABASE [LWHKPROD] FROM DISK =>> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
>> NOUNLOAD , STATS = 10, NORECOVERY , MOVE
N'lwxpertData'
>> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
>> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
>> go
>> RESTORE DATABASE [LWHKPROD] FROM DISK =>> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
>> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
>> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
>> N'D:\sql2000\logs\LWHKPROD_log.ldf'
>> go
>> Results:
>> ...
>> 100 percent restored.
>> Processed 20312 pages for database 'LWHKPROD',
>> file 'lwxpertData' on file 1.
>> Processed 1 pages for database 'LWHKPROD',
>> file 'lwxpert_log' on file 1.
>> RESTORE DATABASE successfully processed 20313 pages in
>> 9.794 seconds (16.989 MB/sec).
>> Server: Msg 3136, Level 16, State 1, Line 1
>> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
>> Tue.bak' to database 'LWHKPROD'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>
>.
>|||Was there a full backup made between 31 May and June 2 i.e. the date of the
differential backup you are trying to restore? You could look at the
backupset table e.g.
SELECT type, * FROM msdb..backupset WHERE name = 'LWHKPROD' AND
backup_start_date > '31-May-2004' ORDER BY backup_start_date
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0401c44942$1088da90$a001280a@.phx.gbl...
> Yes, it is a type 5 (incremental)
> >--Original Message--
> >Was the backup "LWHKPROD-Tue.bak" actually backed up
> using the WITH
> >DIFFERENTIAL clause? Show the T-SQL for the backups too.
> >
> >Eric
> >
> >
> >
> >PepitoDBA wrote:
> >> Hi, I'm trying to restore an incremental backup but it
> >> fails. Here are the TSQL commands and the results. Any
> >> idea?
> >> RESTORE DATABASE [LWHKPROD] FROM DISK => >> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> >> NOUNLOAD , STATS = 10, NORECOVERY , MOVE
> N'lwxpertData'
> >> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> >> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> >> go
> >> RESTORE DATABASE [LWHKPROD] FROM DISK => >> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> >> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> >> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> >> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> >> go
> >>
> >> Results:
> >> ...
> >> 100 percent restored.
> >> Processed 20312 pages for database 'LWHKPROD',
> >> file 'lwxpertData' on file 1.
> >> Processed 1 pages for database 'LWHKPROD',
> >> file 'lwxpert_log' on file 1.
> >> RESTORE DATABASE successfully processed 20313 pages in
> >> 9.794 seconds (16.989 MB/sec).
> >> Server: Msg 3136, Level 16, State 1, Line 1
> >> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> >> Tue.bak' to database 'LWHKPROD'.
> >> Server: Msg 3013, Level 16, State 1, Line 1
> >> RESTORE DATABASE is terminating abnormally.
> >
> >
> >.
> >
fails. Here are the TSQL commands and the results. Any
idea?
RESTORE DATABASE [LWHKPROD] FROM DISK = N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
RESTORE DATABASE [LWHKPROD] FROM DISK = N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
N'D:\sql2000\logs\LWHKPROD_log.ldf'
go
Results:
...
100 percent restored.
Processed 20312 pages for database 'LWHKPROD',
file 'lwxpertData' on file 1.
Processed 1 pages for database 'LWHKPROD',
file 'lwxpert_log' on file 1.
RESTORE DATABASE successfully processed 20313 pages in
9.794 seconds (16.989 MB/sec).
Server: Msg 3136, Level 16, State 1, Line 1
Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
Tue.bak' to database 'LWHKPROD'.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.Was the backup "LWHKPROD-Tue.bak" actually backed up using the WITH
DIFFERENTIAL clause? Show the T-SQL for the backups too.
Eric
PepitoDBA wrote:
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.|||Run the following to confirm that LWHKPROD-Tue.bak' is a differential
backup file:
RESTORE HEADERONLY FROM DISK = 'D:\ASIA\LWHKPROD-Tue.bak'
Look at the BackupType column to check if the value is 5 (a differential
backup).
Peter Yeoh
http://www.yohz.com
Need smaller SQL2K backups? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:16f7601c448c7$b531fb60$a601280a@.phx.gbl...
> Hi, I'm trying to restore an incremental backup but it
> fails. Here are the TSQL commands and the results. Any
> idea?
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> NOUNLOAD , STATS = 10, NORECOVERY , MOVE N'lwxpertData'
> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> RESTORE DATABASE [LWHKPROD] FROM DISK => N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> go
> Results:
> ...
> 100 percent restored.
> Processed 20312 pages for database 'LWHKPROD',
> file 'lwxpertData' on file 1.
> Processed 1 pages for database 'LWHKPROD',
> file 'lwxpert_log' on file 1.
> RESTORE DATABASE successfully processed 20313 pages in
> 9.794 seconds (16.989 MB/sec).
> Server: Msg 3136, Level 16, State 1, Line 1
> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> Tue.bak' to database 'LWHKPROD'.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
>|||Yes, it is a type 5 (incremental)
>--Original Message--
>Was the backup "LWHKPROD-Tue.bak" actually backed up
using the WITH
>DIFFERENTIAL clause? Show the T-SQL for the backups too.
>Eric
>
>PepitoDBA wrote:
>> Hi, I'm trying to restore an incremental backup but it
>> fails. Here are the TSQL commands and the results. Any
>> idea?
>> RESTORE DATABASE [LWHKPROD] FROM DISK =>> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
>> NOUNLOAD , STATS = 10, NORECOVERY , MOVE
N'lwxpertData'
>> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
>> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
>> go
>> RESTORE DATABASE [LWHKPROD] FROM DISK =>> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
>> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
>> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
>> N'D:\sql2000\logs\LWHKPROD_log.ldf'
>> go
>> Results:
>> ...
>> 100 percent restored.
>> Processed 20312 pages for database 'LWHKPROD',
>> file 'lwxpertData' on file 1.
>> Processed 1 pages for database 'LWHKPROD',
>> file 'lwxpert_log' on file 1.
>> RESTORE DATABASE successfully processed 20313 pages in
>> 9.794 seconds (16.989 MB/sec).
>> Server: Msg 3136, Level 16, State 1, Line 1
>> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
>> Tue.bak' to database 'LWHKPROD'.
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>
>.
>|||Was there a full backup made between 31 May and June 2 i.e. the date of the
differential backup you are trying to restore? You could look at the
backupset table e.g.
SELECT type, * FROM msdb..backupset WHERE name = 'LWHKPROD' AND
backup_start_date > '31-May-2004' ORDER BY backup_start_date
Peter Yeoh
http://www.yohz.com
Need smaller backup files? Try MiniSQLBackup
"PepitoDBA" <anonymous@.discussions.microsoft.com> wrote in message
news:17b0401c44942$1088da90$a001280a@.phx.gbl...
> Yes, it is a type 5 (incremental)
> >--Original Message--
> >Was the backup "LWHKPROD-Tue.bak" actually backed up
> using the WITH
> >DIFFERENTIAL clause? Show the T-SQL for the backups too.
> >
> >Eric
> >
> >
> >
> >PepitoDBA wrote:
> >> Hi, I'm trying to restore an incremental backup but it
> >> fails. Here are the TSQL commands and the results. Any
> >> idea?
> >> RESTORE DATABASE [LWHKPROD] FROM DISK => >> N'D:\ASIA\LWHKPROD as of May 31.bak' WITH FILE = 1,
> >> NOUNLOAD , STATS = 10, NORECOVERY , MOVE
> N'lwxpertData'
> >> TO N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log'
> >> TO N'D:\sql2000\logs\LWHKPROD_log.ldf'
> >> go
> >> RESTORE DATABASE [LWHKPROD] FROM DISK => >> N'D:\ASIA\LWHKPROD-Tue.bak' WITH FILE = 1, NOUNLOAD ,
> >> STATS = 10, RECOVERY , MOVE N'lwxpertData' TO
> >> N'F:\sql2000\Data\LWHKPROD.mdf', MOVE N'lwxpert_log' TO
> >> N'D:\sql2000\logs\LWHKPROD_log.ldf'
> >> go
> >>
> >> Results:
> >> ...
> >> 100 percent restored.
> >> Processed 20312 pages for database 'LWHKPROD',
> >> file 'lwxpertData' on file 1.
> >> Processed 1 pages for database 'LWHKPROD',
> >> file 'lwxpert_log' on file 1.
> >> RESTORE DATABASE successfully processed 20313 pages in
> >> 9.794 seconds (16.989 MB/sec).
> >> Server: Msg 3136, Level 16, State 1, Line 1
> >> Cannot apply the backup on device 'D:\ASIA\LWHKPROD-
> >> Tue.bak' to database 'LWHKPROD'.
> >> Server: Msg 3013, Level 16, State 1, Line 1
> >> RESTORE DATABASE is terminating abnormally.
> >
> >
> >.
> >
Tuesday, February 21, 2012
Restoring Database in TSQL
Hi. I want to restore a database named Employee Training but when I restore it, I want to name it Training. I know to to restore it in TSQL I type
"RESTORE DATABASE Employee Training To (name of device). How do I rename it to Training? I'd appreciate any help. Thanks.Have you looked in BOL (books online)? There is an example under RESTORE DATABASE - How to restore a database with a new name (Transact-SQL) that restores a copy of an existing database to another database with a new name.
I refer you here because you have to use the with move parameter to rename your physical mdf and ldf (and maybe ndf) files. If you have any more questions after you have read this, ask again.|||Restore the database first then rename it.|||I think then the question would have been "How do I rename a database?"|||Just give it the New name in the RESTORE command?
RESTORE myNewDBName ...|||Which is true and I referred to in post #2 with the BOL article and example cited, since they will have to move the physical filenames to a new file name for the restored database!
Lindalog, are you getting anything out of this?|||Which is true and I referred to in post #2 with the BOL article and example cited, since they will have to move the physical filenames to a new file name for the restored database!
Lindalog, are you getting anything out of this?
I'm getting it, you're right about having to move the physical filenames. Thanks for your help|||Thanks everybody. Tom53, I went to BOL and found exactly what I needed. Thanks alot. You saved me from hours of frustration. I'm sure that I'll be posting another question soon. Take care.
"RESTORE DATABASE Employee Training To (name of device). How do I rename it to Training? I'd appreciate any help. Thanks.Have you looked in BOL (books online)? There is an example under RESTORE DATABASE - How to restore a database with a new name (Transact-SQL) that restores a copy of an existing database to another database with a new name.
I refer you here because you have to use the with move parameter to rename your physical mdf and ldf (and maybe ndf) files. If you have any more questions after you have read this, ask again.|||Restore the database first then rename it.|||I think then the question would have been "How do I rename a database?"|||Just give it the New name in the RESTORE command?
RESTORE myNewDBName ...|||Which is true and I referred to in post #2 with the BOL article and example cited, since they will have to move the physical filenames to a new file name for the restored database!
Lindalog, are you getting anything out of this?|||Which is true and I referred to in post #2 with the BOL article and example cited, since they will have to move the physical filenames to a new file name for the restored database!
Lindalog, are you getting anything out of this?
I'm getting it, you're right about having to move the physical filenames. Thanks for your help|||Thanks everybody. Tom53, I went to BOL and found exactly what I needed. Thanks alot. You saved me from hours of frustration. I'm sure that I'll be posting another question soon. Take care.
Subscribe to:
Posts (Atom)