PAGE HEADER: backup

Showing posts with label backup. Show all posts

Monday, March 26, 2012

Problem with SQL Backup

Hello,
I having trouble restoring a full followed by a differential in SQL 2000.
I have a full file and one differential I am trying to restore.
I am using the full file to restore the full and then when I try to apply
the differential, I get an error saying Cannot apply the backup
My commands are
USE MASTER
RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
norecovery,
move 'acusydb' to 'F:\dataarea\fulldb.mdf',
move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
What I get is an error Msg3136 that says
Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
The question I have is why is this happening, and 2 how can I fix it. I was
able to get the full to restore properly, but not the diff.
Any help is appreciated.
--
Thanks,
DavidDavid
If these files are at the same device you need to specify a file option. If
they aren't ,make sure that you restore correct files.
"david" <david@.discussions.microsoft.com> wrote in message
news:06793D7B-CB71-4F9F-8E7C-903C3334FF58@.microsoft.com...
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with
> norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database
> dbname.
> The question I have is why is this happening, and 2 how can I fix it. I
> was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||Can you be a little more specific? An example will help. The data files are
on the same physical directory. how does this make a difference or is it the
way SQL needs to see it?
--
Thanks,
David
"Uri Dimant" wrote:
> David
> If these files are at the same device you need to specify a file option. If
> they aren't ,make sure that you restore correct files.
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:06793D7B-CB71-4F9F-8E7C-903C3334FF58@.microsoft.com...
> > Hello,
> >
> > I having trouble restoring a full followed by a differential in SQL 2000.
> >
> > I have a full file and one differential I am trying to restore.
> >
> > I am using the full file to restore the full and then when I try to apply
> > the differential, I get an error saying Cannot apply the backup
> >
> > My commands are
> >
> >
> >
> > USE MASTER
> > RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> > norecovery,
> > move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> > move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> > RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with
> > norecovery
> >
> > What I get is an error Msg3136 that says
> >
> > Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database
> > dbname.
> >
> > The question I have is why is this happening, and 2 how can I fix it. I
> > was
> > able to get the full to restore properly, but not the diff.
> >
> > Any help is appreciated.
> > --
> > Thanks,
> >
> > David
>
>|||Additionally, what specifies the file number? Is this something I need to
specify when I backup the file as well? Is there any way to verify what the
file contains similar to getting the logical database name out of the file?
Also, do I need to specify move for each file I restore because of the
logical name?
I am assuming I need to.
--
Thanks,
David
"david" wrote:
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||Does anyone have input for me' Any help is appreciated.
--
Thanks,
David
"david" wrote:
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||My guess is that you did something like below:
(a) BACKUP DATABASE
(b) BACKUP DATABASE
(c) BACKUP DATABASE WITH DIFFERENTIAL
RESTORE (a)
RESTORE (c)
In order to restore (c), you need to:
RESTORE (b)
RESTORE (c)
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"david" <david@.discussions.microsoft.com> wrote in message
news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
> Does anyone have input for me' Any help is appreciated.
> --
> Thanks,
> David
>
> "david" wrote:
>> Hello,
>> I having trouble restoring a full followed by a differential in SQL 2000.
>> I have a full file and one differential I am trying to restore.
>> I am using the full file to restore the full and then when I try to apply
>> the differential, I get an error saying Cannot apply the backup
>> My commands are
>>
>> USE MASTER
>> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> norecovery,
>> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> What I get is an error Msg3136 that says
>> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> The question I have is why is this happening, and 2 how can I fix it. I was
>> able to get the full to restore properly, but not the diff.
>> Any help is appreciated.
>> --
>> Thanks,
>> David|||Tibor:
There is a nightly full backup taken at 9:30 PM of the SQL database. We will
call this BACKUP (A)
Each morning, beginning at 8:00 AM, I am backing up the same database with
differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
If I have a failure, I want to take the full from the previous night (BACKUP
A)
and apply the appropriate differential (Backup 10AM) to bring the state of
the database to that point. There is no log backup.
I am copying these flat files to a file folder on another computer.
Through SQLQuery I am running the following restore script
This will allow me to move the full database to the new server and a new
location.
USE MASTER
RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
norecovery,
move 'acusydb' to 'F:\dataarea\fulldb.mdf',
move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
Then I try to restore the differential with the following command:
RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
Lastly, I should be able to add the following line to bring the database to
the final usable state.
RESTORE database dbname with recovery.
What is happening is I am getting the error as follows
What I get is an error Msg3136 that says
Cannot apply the backup on device F:\ftproot\fulldiff.bak to database dbname.
Prior people have stated I need to use the FILE flag, but I don't really
understand how to use it properly and I have asked for an example.
I am wondering if the path specified in the restore procedure
(F:\FTPRoot\fulldiff.bak') is the problem. Can I have two files in the same
directory?
I am assuming yes, but I am unsure how SQL handles the media.
Can you help?
Thanks,
David
"Tibor Karaszi" wrote:
> My guess is that you did something like below:
> (a) BACKUP DATABASE
> (b) BACKUP DATABASE
> (c) BACKUP DATABASE WITH DIFFERENTIAL
> RESTORE (a)
> RESTORE (c)
> In order to restore (c), you need to:
> RESTORE (b)
> RESTORE (c)
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
> > Does anyone have input for me' Any help is appreciated.
> > --
> > Thanks,
> >
> > David
> >
> >
> > "david" wrote:
> >
> >> Hello,
> >>
> >> I having trouble restoring a full followed by a differential in SQL 2000.
> >>
> >> I have a full file and one differential I am trying to restore.
> >>
> >> I am using the full file to restore the full and then when I try to apply
> >> the differential, I get an error saying Cannot apply the backup
> >>
> >> My commands are
> >>
> >>
> >>
> >> USE MASTER
> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> >> norecovery,
> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> >>
> >> What I get is an error Msg3136 that says
> >>
> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> >>
> >> The question I have is why is this happening, and 2 how can I fix it. I was
> >> able to get the full to restore properly, but not the diff.
> >>
> >> Any help is appreciated.
> >> --
> >> Thanks,
> >>
> >> David
>
>|||david wrote:
> Tibor:
> There is a nightly full backup taken at 9:30 PM of the SQL database. We will
> call this BACKUP (A)
> Each morning, beginning at 8:00 AM, I am backing up the same database with
> differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
> If I have a failure, I want to take the full from the previous night (BACKUP
> A)
> and apply the appropriate differential (Backup 10AM) to bring the state of
> the database to that point. There is no log backup.
> I am copying these flat files to a file folder on another computer.
> Through SQLQuery I am running the following restore script
> This will allow me to move the full database to the new server and a new
> location.
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>
> Then I try to restore the differential with the following command:
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>
If you have no transaction logs, why are you specifying norecovery? Try using recovery.|||If you are absolutely certain that you do restore the most recent db backup before the diff backup
(check against the backup history table in msdb, for instance), and you still get the error and you
don't find anything on KB i suspect a bug in SQL Server and you should open a case with MS Support.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"david" <david@.discussions.microsoft.com> wrote in message
news:9ED285B6-9549-49A5-BC0C-C01F15B2E4E6@.microsoft.com...
> Tibor:
> There is a nightly full backup taken at 9:30 PM of the SQL database. We will
> call this BACKUP (A)
> Each morning, beginning at 8:00 AM, I am backing up the same database with
> differential. These backups are Backups (8AM, 10AM, 12PM...throughout the day)
> If I have a failure, I want to take the full from the previous night (BACKUP
> A)
> and apply the appropriate differential (Backup 10AM) to bring the state of
> the database to that point. There is no log backup.
> I am copying these flat files to a file folder on another computer.
> Through SQLQuery I am running the following restore script
> This will allow me to move the full database to the new server and a new
> location.
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>
> Then I try to restore the differential with the following command:
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> Lastly, I should be able to add the following line to bring the database to
> the final usable state.
> RESTORE database dbname with recovery.
> What is happening is I am getting the error as follows
> What I get is an error Msg3136 that says
> Cannot apply the backup on device F:\ftproot\fulldiff.bak to database dbname.
> Prior people have stated I need to use the FILE flag, but I don't really
> understand how to use it properly and I have asked for an example.
> I am wondering if the path specified in the restore procedure
> (F:\FTPRoot\fulldiff.bak') is the problem. Can I have two files in the same
> directory?
> I am assuming yes, but I am unsure how SQL handles the media.
> Can you help?
>
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
>> My guess is that you did something like below:
>> (a) BACKUP DATABASE
>> (b) BACKUP DATABASE
>> (c) BACKUP DATABASE WITH DIFFERENTIAL
>> RESTORE (a)
>> RESTORE (c)
>> In order to restore (c), you need to:
>> RESTORE (b)
>> RESTORE (c)
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:BB13B21B-C21C-46F9-A7F4-5982ADF2B078@.microsoft.com...
>> > Does anyone have input for me' Any help is appreciated.
>> > --
>> > Thanks,
>> >
>> > David
>> >
>> >
>> > "david" wrote:
>> >
>> >> Hello,
>> >>
>> >> I having trouble restoring a full followed by a differential in SQL 2000.
>> >>
>> >> I have a full file and one differential I am trying to restore.
>> >>
>> >> I am using the full file to restore the full and then when I try to apply
>> >> the differential, I get an error saying Cannot apply the backup
>> >>
>> >> My commands are
>> >>
>> >>
>> >>
>> >> USE MASTER
>> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> >> norecovery,
>> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> >>
>> >> What I get is an error Msg3136 that says
>> >>
>> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> >>
>> >> The question I have is why is this happening, and 2 how can I fix it. I was
>> >> able to get the full to restore properly, but not the diff.
>> >>
>> >> Any help is appreciated.
>> >> --
>> >> Thanks,
>> >>
>> >> David
>>|||I have checked the files, but I am still not sure how to solve this issue. I
am using the data from the last full and the differential that I apply is the
one say for 2PM.
Why am I getting the error? And how do I correct it? Any help is appreciated?
Anyone?
--
Thanks,
David
"david" wrote:
> Hello,
> I having trouble restoring a full followed by a differential in SQL 2000.
> I have a full file and one differential I am trying to restore.
> I am using the full file to restore the full and then when I try to apply
> the differential, I get an error saying Cannot apply the backup
> My commands are
>
> USE MASTER
> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> norecovery,
> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> What I get is an error Msg3136 that says
> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> The question I have is why is this happening, and 2 how can I fix it. I was
> able to get the full to restore properly, but not the diff.
> Any help is appreciated.
> --
> Thanks,
> David|||The error message states that you did a database backup after the db backup you restore and before
the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to open a
case with MS Support for them to have a look at (unless you find something in KB that applies). See
my scripts for how to reproduce the error message:
--Work
CREATE DATABASE test
CREATE TABLE test..t(c1 int identity)
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
GO
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
--Doesn't work
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
INSERT INTO test..t DEFAULT VALUES
BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
GO
RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"david" <david@.discussions.microsoft.com> wrote in message
news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
>I have checked the files, but I am still not sure how to solve this issue. I
> am using the data from the last full and the differential that I apply is the
> one say for 2PM.
> Why am I getting the error? And how do I correct it? Any help is appreciated?
> Anyone?
> --
> Thanks,
> David
>
> "david" wrote:
>> Hello,
>> I having trouble restoring a full followed by a differential in SQL 2000.
>> I have a full file and one differential I am trying to restore.
>> I am using the full file to restore the full and then when I try to apply
>> the differential, I get an error saying Cannot apply the backup
>> My commands are
>>
>> USE MASTER
>> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> norecovery,
>> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> What I get is an error Msg3136 that says
>> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> The question I have is why is this happening, and 2 how can I fix it. I was
>> able to get the full to restore properly, but not the diff.
>> Any help is appreciated.
>> --
>> Thanks,
>> David|||I understand. I have made sure the database files are OK. I am seeing the
same results. Can you give me additional information on how SQL applies its
backups? Are there specific criteria it is expecting when applying a
differential?
--
Thanks,
David
"Tibor Karaszi" wrote:
> The error message states that you did a database backup after the db backup you restore and before
> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to open a
> case with MS Support for them to have a look at (unless you find something in KB that applies). See
> my scripts for how to reproduce the error message:
> --Work
> CREATE DATABASE test
> CREATE TABLE test..t(c1 int identity)
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> GO
> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> --Doesn't work
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> INSERT INTO test..t DEFAULT VALUES
> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> GO
> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> >I have checked the files, but I am still not sure how to solve this issue. I
> > am using the data from the last full and the differential that I apply is the
> > one say for 2PM.
> >
> > Why am I getting the error? And how do I correct it? Any help is appreciated?
> > Anyone?
> > --
> > Thanks,
> >
> > David
> >
> >
> > "david" wrote:
> >
> >> Hello,
> >>
> >> I having trouble restoring a full followed by a differential in SQL 2000.
> >>
> >> I have a full file and one differential I am trying to restore.
> >>
> >> I am using the full file to restore the full and then when I try to apply
> >> the differential, I get an error saying Cannot apply the backup
> >>
> >> My commands are
> >>
> >>
> >>
> >> USE MASTER
> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> >> norecovery,
> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> >>
> >> What I get is an error Msg3136 that says
> >>
> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> >>
> >> The question I have is why is this happening, and 2 how can I fix it. I was
> >> able to get the full to restore properly, but not the diff.
> >>
> >> Any help is appreciated.
> >> --
> >> Thanks,
> >>
> >> David
>|||I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
between the db backup and the diff backup you try to restore?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"david" <david@.discussions.microsoft.com> wrote in message
news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
>I understand. I have made sure the database files are OK. I am seeing the
> same results. Can you give me additional information on how SQL applies its
> backups? Are there specific criteria it is expecting when applying a
> differential?
> --
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
>> The error message states that you did a database backup after the db backup you restore and
>> before
>> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
>> open a
>> case with MS Support for them to have a look at (unless you find something in KB that applies).
>> See
>> my scripts for how to reproduce the error message:
>> --Work
>> CREATE DATABASE test
>> CREATE TABLE test..t(c1 int identity)
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
>> GO
>> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
>> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>> --Doesn't work
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
>> INSERT INTO test..t DEFAULT VALUES
>> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
>> GO
>> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
>> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
>>
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "david" <david@.discussions.microsoft.com> wrote in message
>> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
>> >I have checked the files, but I am still not sure how to solve this issue. I
>> > am using the data from the last full and the differential that I apply is the
>> > one say for 2PM.
>> >
>> > Why am I getting the error? And how do I correct it? Any help is appreciated?
>> > Anyone?
>> > --
>> > Thanks,
>> >
>> > David
>> >
>> >
>> > "david" wrote:
>> >
>> >> Hello,
>> >>
>> >> I having trouble restoring a full followed by a differential in SQL 2000.
>> >>
>> >> I have a full file and one differential I am trying to restore.
>> >>
>> >> I am using the full file to restore the full and then when I try to apply
>> >> the differential, I get an error saying Cannot apply the backup
>> >>
>> >> My commands are
>> >>
>> >>
>> >>
>> >> USE MASTER
>> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
>> >> norecovery,
>> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
>> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
>> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
>> >>
>> >> What I get is an error Msg3136 that says
>> >>
>> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
>> >>
>> >> The question I have is why is this happening, and 2 how can I fix it. I was
>> >> able to get the full to restore properly, but not the diff.
>> >>
>> >> Any help is appreciated.
>> >> --
>> >> Thanks,
>> >>
>> >> David
>>|||Good morning.
Had an interesting day yesterday that resulted in a better understanding of
the problem. I did a RESTORE HEADERONLY command on the two backup files and
noticed the date on the backup was two days earlier. I re-ran a full backup
and an another differential and was surprised the restore was successful.
I ensured the backup creation occured at the right time and now I have a
full backup that runs at 9PM each evening.
But I still have questions. This morning I took the full backup dated
11-04-2005 at 21:00. Differential files are dated 11-05-2005 at 10AM
(latest). When I tried to restore again, I got the 3136 error again. A big
disappointment.
So my questions are:
1. Can someone explain how the LSN markers are used in SQL headeronly data?
and does the differential need to match one of the lsn to function properly
on the restore.
Firstlsn, Lastlsn, Checkpointlsn, Differentiallsn
Any help as always is appreciated.
--
Thanks,
David
"Tibor Karaszi" wrote:
> I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
> between the db backup and the diff backup you try to restore?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "david" <david@.discussions.microsoft.com> wrote in message
> news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
> >I understand. I have made sure the database files are OK. I am seeing the
> > same results. Can you give me additional information on how SQL applies its
> > backups? Are there specific criteria it is expecting when applying a
> > differential?
> >
> > --
> > Thanks,
> >
> > David
> >
> >
> > "Tibor Karaszi" wrote:
> >
> >> The error message states that you did a database backup after the db backup you restore and
> >> before
> >> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
> >> open a
> >> case with MS Support for them to have a look at (unless you find something in KB that applies).
> >> See
> >> my scripts for how to reproduce the error message:
> >>
> >> --Work
> >> CREATE DATABASE test
> >> CREATE TABLE test..t(c1 int identity)
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> >> GO
> >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> >>
> >> --Doesn't work
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> >> INSERT INTO test..t DEFAULT VALUES
> >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> >> GO
> >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> >>
> >>
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "david" <david@.discussions.microsoft.com> wrote in message
> >> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> >> >I have checked the files, but I am still not sure how to solve this issue. I
> >> > am using the data from the last full and the differential that I apply is the
> >> > one say for 2PM.
> >> >
> >> > Why am I getting the error? And how do I correct it? Any help is appreciated?
> >> > Anyone?
> >> > --
> >> > Thanks,
> >> >
> >> > David
> >> >
> >> >
> >> > "david" wrote:
> >> >
> >> >> Hello,
> >> >>
> >> >> I having trouble restoring a full followed by a differential in SQL 2000.
> >> >>
> >> >> I have a full file and one differential I am trying to restore.
> >> >>
> >> >> I am using the full file to restore the full and then when I try to apply
> >> >> the differential, I get an error saying Cannot apply the backup
> >> >>
> >> >> My commands are
> >> >>
> >> >>
> >> >>
> >> >> USE MASTER
> >> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> >> >> norecovery,
> >> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> >> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> >> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> >> >>
> >> >> What I get is an error Msg3136 that says
> >> >>
> >> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> >> >>
> >> >> The question I have is why is this happening, and 2 how can I fix it. I was
> >> >> able to get the full to restore properly, but not the diff.
> >> >>
> >> >> Any help is appreciated.
> >> >> --
> >> >> Thanks,
> >> >>
> >> >> David
> >>
> >>
>|||Well, I learned some interesting things out of this process. Thanks Tibor for
the assistance with this. I did indeed seem to have a SQL backup in between.
After some diligent investigation, I realized there was an additional backup
occuring in a backup job (Veritas) that was a minute after my last full. I
removed the SQL Agent job and am relying solely on the flat file backup. I
was able to sucessfully restore both databases this morning using the full
and desired differential. I am going to continually test the process this
week to make sure I can restore a number of times to ensure the problem is
really solved.
I would like to understand the LSN numbers and how SQL uses these numbers in
the HEADER INFORMATION.
Can anyone elaborate?
--
Thanks,
David
"david" wrote:
> Good morning.
> Had an interesting day yesterday that resulted in a better understanding of
> the problem. I did a RESTORE HEADERONLY command on the two backup files and
> noticed the date on the backup was two days earlier. I re-ran a full backup
> and an another differential and was surprised the restore was successful.
> I ensured the backup creation occured at the right time and now I have a
> full backup that runs at 9PM each evening.
> But I still have questions. This morning I took the full backup dated
> 11-04-2005 at 21:00. Differential files are dated 11-05-2005 at 10AM
> (latest). When I tried to restore again, I got the 3136 error again. A big
> disappointment.
> So my questions are:
> 1. Can someone explain how the LSN markers are used in SQL headeronly data?
> and does the differential need to match one of the lsn to function properly
> on the restore.
> Firstlsn, Lastlsn, Checkpointlsn, Differentiallsn
>
> Any help as always is appreciated.
> --
> Thanks,
> David
>
> "Tibor Karaszi" wrote:
> > I'm not sure what you are asking. Are you saying that you indeed do have a database backup in
> > between the db backup and the diff backup you try to restore?
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> > http://www.solidqualitylearning.com/
> > Blog: http://solidqualitylearning.com/blogs/tibor/
> >
> >
> > "david" <david@.discussions.microsoft.com> wrote in message
> > news:63620D2B-31D1-46E3-9D80-20FA92FF3099@.microsoft.com...
> > >I understand. I have made sure the database files are OK. I am seeing the
> > > same results. Can you give me additional information on how SQL applies its
> > > backups? Are there specific criteria it is expecting when applying a
> > > differential?
> > >
> > > --
> > > Thanks,
> > >
> > > David
> > >
> > >
> > > "Tibor Karaszi" wrote:
> > >
> > >> The error message states that you did a database backup after the db backup you restore and
> > >> before
> > >> the diff backup you restore. If that isn't the case, you have a bug in SQL Server and need to
> > >> open a
> > >> case with MS Support for them to have a look at (unless you find something in KB that applies).
> > >> See
> > >> my scripts for how to reproduce the error message:
> > >>
> > >> --Work
> > >> CREATE DATABASE test
> > >> CREATE TABLE test..t(c1 int identity)
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> > >> GO
> > >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> > >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> > >>
> > >> --Doesn't work
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\db2.bak' WITH INIT
> > >> INSERT INTO test..t DEFAULT VALUES
> > >> BACKUP DATABASE test TO DISK = 'C:\diff.bak' WITH INIT, DIFFERENTIAL
> > >> GO
> > >> RESTORE DATABASE test FROM DISK = 'C:\db.bak' WITH NORECOVERY
> > >> RESTORE DATABASE test FROM DISK = 'C:\diff.bak' WITH RECOVERY
> > >>
> > >>
> > >>
> > >>
> > >> --
> > >> Tibor Karaszi, SQL Server MVP
> > >> http://www.karaszi.com/sqlserver/default.asp
> > >> http://www.solidqualitylearning.com/
> > >> Blog: http://solidqualitylearning.com/blogs/tibor/
> > >>
> > >>
> > >> "david" <david@.discussions.microsoft.com> wrote in message
> > >> news:8B7A9179-0B49-468B-A1F9-996D606C786D@.microsoft.com...
> > >> >I have checked the files, but I am still not sure how to solve this issue. I
> > >> > am using the data from the last full and the differential that I apply is the
> > >> > one say for 2PM.
> > >> >
> > >> > Why am I getting the error? And how do I correct it? Any help is appreciated?
> > >> > Anyone?
> > >> > --
> > >> > Thanks,
> > >> >
> > >> > David
> > >> >
> > >> >
> > >> > "david" wrote:
> > >> >
> > >> >> Hello,
> > >> >>
> > >> >> I having trouble restoring a full followed by a differential in SQL 2000.
> > >> >>
> > >> >> I have a full file and one differential I am trying to restore.
> > >> >>
> > >> >> I am using the full file to restore the full and then when I try to apply
> > >> >> the differential, I get an error saying Cannot apply the backup
> > >> >>
> > >> >> My commands are
> > >> >>
> > >> >>
> > >> >>
> > >> >> USE MASTER
> > >> >> RESTORE database dbname from disk=N'F:\FTProot\fullbackup.bak' with
> > >> >> norecovery,
> > >> >> move 'acusydb' to 'F:\dataarea\fulldb.mdf',
> > >> >> move 'acysylog to 'F:\dataarea\dbsyslog.ldf'
> > >> >> RESTORE database dbname from disk=N'F:\FTPRoot\fulldiff.bak with norecovery
> > >> >>
> > >> >> What I get is an error Msg3136 that says
> > >> >>
> > >> >> Cannot apply the backup on decvice F:\ftproot\fulldiff.bak to database dbname.
> > >> >>
> > >> >> The question I have is why is this happening, and 2 how can I fix it. I was
> > >> >> able to get the full to restore properly, but not the diff.
> > >> >>
> > >> >> Any help is appreciated.
> > >> >> --
> > >> >> Thanks,
> > >> >>
> > >> >> David
> > >>
> > >>
> >
> >

Monday, March 12, 2012

Problem with restoring sql server database

Hi
I need to restore a recent backup of sql server database. However, the
message I am getting is that database needs to be in exclusive mode. It is in
use. My question is how to know who is using the database and get them off
the database so that I can restore the database from a backup. Thanks in
advanceHave a look at the ALTER DATABASE command and the SET SINGLE_USER and
ROLLBACK IMEDIATE options.
--
Andrew J. Kelly SQL MVP
Solid Quality Mentors
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:C581FA7C-EC5A-4A97-AB02-790554E8ABB2@.microsoft.com...
> Hi
> I need to restore a recent backup of sql server database. However, the
> message I am getting is that database needs to be in exclusive mode. It is
> in
> use. My question is how to know who is using the database and get them off
> the database so that I can restore the database from a backup. Thanks in
> advance|||Thanks for your help Andrew. I appreciate it. Let me try this and see.
"Andrew J. Kelly" wrote:
> Have a look at the ALTER DATABASE command and the SET SINGLE_USER and
> ROLLBACK IMEDIATE options.
> --
> Andrew J. Kelly SQL MVP
> Solid Quality Mentors
>
> "Jack" <Jack@.discussions.microsoft.com> wrote in message
> news:C581FA7C-EC5A-4A97-AB02-790554E8ABB2@.microsoft.com...
> > Hi
> > I need to restore a recent backup of sql server database. However, the
> > message I am getting is that database needs to be in exclusive mode. It is
> > in
> > use. My question is how to know who is using the database and get them off
> > the database so that I can restore the database from a backup. Thanks in
> > advance
>|||Most of the time, it's "you," the DBA trying to restore the database that is
using the database. Whether using Enterprise Manager or Management Studio,
users tend to select the database in the UI when restoring. This has
already created a connection to the database. I'd rather do it using scripts
"Jack" <Jack@.discussions.microsoft.com> wrote in message
news:CEF6E906-04B9-4053-9E83-5CB7E89C78CD@.microsoft.com...
> Thanks for your help Andrew. I appreciate it. Let me try this and see.
> "Andrew J. Kelly" wrote:
>> Have a look at the ALTER DATABASE command and the SET SINGLE_USER and
>> ROLLBACK IMEDIATE options.
>> --
>> Andrew J. Kelly SQL MVP
>> Solid Quality Mentors
>>
>> "Jack" <Jack@.discussions.microsoft.com> wrote in message
>> news:C581FA7C-EC5A-4A97-AB02-790554E8ABB2@.microsoft.com...
>> > Hi
>> > I need to restore a recent backup of sql server database. However, the
>> > message I am getting is that database needs to be in exclusive mode. It
>> > is
>> > in
>> > use. My question is how to know who is using the database and get them
>> > off
>> > the database so that I can restore the database from a backup. Thanks
>> > in
>> > advance
>>|||Jack wrote:
> Hi
> I need to restore a recent backup of sql server database. However,
> the message I am getting is that database needs to be in exclusive
> mode. It is in use. My question is how to know who is using the
> database and get them off the database so that I can restore the
> database from a backup. Thanks in advance
Hi, Jack
You can use sp_who2 or look in Management / Activity Monitor (in
Management Studio).
It is possible that the connection using the database belongs to
yourself. Close any open windows that are accesing that database. If
you are using a query window to restore, make sure you the current
database is not that database (change it to master, for example).
--
Razvan Socol
SQL Server MVP

Problem with restoring an SQL 2K backup on to SQL 2005

I am currently having a problem with restoring a backup made on SQL 2000 XP machine onto a Windows server 2003 machine with SQL Express 2005. I am getting the following error

"System.Data.SqlClient.SqlError: The operating system returned the error '5(Access is denied.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'C:\Program Files\Avidian Technologies\Prophet\avidian.mdf'. (Microsoft.SqlServer.Express.Smo)"

I'm not sure what is going on becuase I have no issues installing the backup on my XP Pro machine that has SQL Express 2005 Dev edition. So what I did was restore the backup on to SQL Express 2005 on my dev machine and backup again. Unfortunately when I took the newly created backup and tried again to restore the database on to my window server 2003 machine it failed again. Exact same message was displayed.

Any suggestions or help would be greatful.

Thanks,
Rodney

Rodney, here is a thread on the engine forum regarding this issue. It looks like they solved it. Can you read through it and see if it works for you?

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=169145&SiteID=1

Thanks,
Sam Lester (MSFT)

Problem with restoring a database

Hello!
I use SQL Server 2005. I have a backup taken from a computer that had the
following definition when I started SQL Server
- Server type: Database Engine
- Server name: UHTDEMO5
-Authentication: Windows Authentication
-Username: UHTDEMO5\anvd
The backup file is called backup_2008_01_22.bak.
So I was sitting on server name UHTDEMO5 when the backup on database Utcas
was taken.
I was loggin in as user anvd. The backup was successfully created.
Now to my question I want to restore this backup on my local desktop server
that is called UHT-DEMO1.
I log in locally as the administrator.
I have tried several different things.
1. I have first create a new user database with name Utcas. This works fine.
2. Now I have an empy database with name Utcas.
3. Now I try Utcas->Tasks->Restore->Database.
4. In the field To database I select Utcas
5.In the From device I select and pick my backup set which is called
backup_2008_01_22.bak
6. In the field From device I can now see my backup file.
7. I the field Select the backup sets to restore I check the check box.
8. When I now klick the Ok button I get the following error message
"Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The media set has 2 media families but only
1 is provided. All
members must be provided. (Microsoft.SqlServer.Smo)"
What do I have to do to restore the backup on my server called UHT-DEMO1.
I thought it was easy to restore a backup but it seams to be difficult.
I'm not so used to SQL Server because I have worked with Oracle before.
//TonyIt appears that your original backup was done as a striped backup - i.e.
there was more than one file created at the time of the backup. If so, then
you need to specify all of the files in the backup when you do the restore.
Check out:
http://msdn2.microsoft.com/en-us/library/ms178062.aspx
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA, MCITP, MCTS
SQL Server MVP
Toronto, ON Canada
https://mvp.support.microsoft.com/profile/Tom.Moreau
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
Hello!
I use SQL Server 2005. I have a backup taken from a computer that had the
following definition when I started SQL Server
- Server type: Database Engine
- Server name: UHTDEMO5
-Authentication: Windows Authentication
-Username: UHTDEMO5\anvd
The backup file is called backup_2008_01_22.bak.
So I was sitting on server name UHTDEMO5 when the backup on database Utcas
was taken.
I was loggin in as user anvd. The backup was successfully created.
Now to my question I want to restore this backup on my local desktop server
that is called UHT-DEMO1.
I log in locally as the administrator.
I have tried several different things.
1. I have first create a new user database with name Utcas. This works fine.
2. Now I have an empy database with name Utcas.
3. Now I try Utcas->Tasks->Restore->Database.
4. In the field To database I select Utcas
5.In the From device I select and pick my backup set which is called
backup_2008_01_22.bak
6. In the field From device I can now see my backup file.
7. I the field Select the backup sets to restore I check the check box.
8. When I now klick the Ok button I get the following error message
"Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
Additional information:
System.Data.SqlClient.SqlError: The media set has 2 media families but only
1 is provided. All
members must be provided. (Microsoft.SqlServer.Smo)"
What do I have to do to restore the backup on my server called UHT-DEMO1.
I thought it was easy to restore a backup but it seams to be difficult.
I'm not so used to SQL Server because I have worked with Oracle before.
//Tony|||"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> Hello!
>
> 1. I have first create a new user database with name Utcas. This works
> fine.
> 2. Now I have an empy database with name Utcas.
Just so you know, the above is not necessary.
> 3. Now I try Utcas->Tasks->Restore->Database.
> 4. In the field To database I select Utcas
> 5.In the From device I select and pick my backup set which is called
> backup_2008_01_22.bak
> 6. In the field From device I can now see my backup file.
> 7. I the field Select the backup sets to restore I check the check box.
> 8. When I now klick the Ok button I get the following error message
> "Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
> Additional information:
> System.Data.SqlClient.SqlError: The media set has 2 media families but
> only
> 1 is provided. All
> members must be provided. (Microsoft.SqlServer.Smo)"
Yeah, as Tony said, it was a striped backup (it wrote to two files at once,
in some cases this can double backup and restore speeds.)
Look for somethign like UTCAS_db_20080121-1.bak and UTCAS_db_2008021-2.back
(i.e. most likely the files will include a -1 and -2... but honestly, could
be anything).
You can also check the originating server to see what it specified (if it's
an automatic job, it should list it, otherwise, I think it's
sys.backuphistory or sys.backupmediaset or something like that.)
> What do I have to do to restore the backup on my server called UHT-DEMO1.
> I thought it was easy to restore a backup but it seams to be difficult.
> I'm not so used to SQL Server because I have worked with Oracle before.
> //Tony
>
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html|||In addition to the other posts, you might find below useful:
RESTORE LABELONLY FROM ...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"TonyJ" <johansson.andersson@.telia.com> wrote in message
news:ulo3UJQXIHA.4868@.TK2MSFTNGP03.phx.gbl...
> Hello!
> I use SQL Server 2005. I have a backup taken from a computer that had the
> following definition when I started SQL Server
> - Server type: Database Engine
> - Server name: UHTDEMO5
> -Authentication: Windows Authentication
> -Username: UHTDEMO5\anvd
> The backup file is called backup_2008_01_22.bak.
> So I was sitting on server name UHTDEMO5 when the backup on database Utcas
> was taken.
> I was loggin in as user anvd. The backup was successfully created.
> Now to my question I want to restore this backup on my local desktop server
> that is called UHT-DEMO1.
> I log in locally as the administrator.
> I have tried several different things.
> 1. I have first create a new user database with name Utcas. This works fine.
> 2. Now I have an empy database with name Utcas.
> 3. Now I try Utcas->Tasks->Restore->Database.
> 4. In the field To database I select Utcas
> 5.In the From device I select and pick my backup set which is called
> backup_2008_01_22.bak
> 6. In the field From device I can now see my backup file.
> 7. I the field Select the backup sets to restore I check the check box.
> 8. When I now klick the Ok button I get the following error message
> "Restore failed for Server 'UHT-DEMO1'. (Microsoft.SqlServer.Smo)
> Additional information:
> System.Data.SqlClient.SqlError: The media set has 2 media families but only
> 1 is provided. All
> members must be provided. (Microsoft.SqlServer.Smo)"
> What do I have to do to restore the backup on my server called UHT-DEMO1.
> I thought it was easy to restore a backup but it seams to be difficult.
> I'm not so used to SQL Server because I have worked with Oracle before.
> //Tony
>

problem with restore database

I got a database backup file. By running RESTORE
FILELISTONLY, it shows 50GB mdf file and 200MB ldf file. I
try to restore it on the test server but the test server
doesn't have 50GB free space in one single drive. So, I
can't create one 50GB mdf file. I try to create the test
database with 20GB on one drive and another 30GB on
another drive. But I still can't restore the database.
Is it possible to restore this backup file on this test
server which has limited amount of disk space?
Thanks in advance for suggestion.
Jd.
You must recreate the exact file size and count for a restore. Files do not
have to be in the same location as the backup, but they cannot be split or
shrunk until the restore is complete. In short, if you have a database file
that is 50GB, you need 50GB to restore it. Plan your systems accordingly.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"JD" <anonymous@.discussions.microsoft.com> wrote in message
news:193fe01c4218f$284113d0$a601280a@.phx.gbl...
> I got a database backup file. By running RESTORE
> FILELISTONLY, it shows 50GB mdf file and 200MB ldf file. I
> try to restore it on the test server but the test server
> doesn't have 50GB free space in one single drive. So, I
> can't create one 50GB mdf file. I try to create the test
> database with 20GB on one drive and another 30GB on
> another drive. But I still can't restore the database.
> Is it possible to restore this backup file on this test
> server which has limited amount of disk space?
> Thanks in advance for suggestion.
> Jd.
|||> Is it possible to restore this backup file on this test
> server which has limited amount of disk space?
I'm afraid not. When doing restore, SQL Server will create the same database file layout, each file has the
same logical file name and at least the same size (this includes the transaction log file(s)). This fact is
one reason why you might want to split up the database into several files in the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JD" <anonymous@.discussions.microsoft.com> wrote in message news:193fe01c4218f$284113d0$a601280a@.phx.gbl...
> I got a database backup file. By running RESTORE
> FILELISTONLY, it shows 50GB mdf file and 200MB ldf file. I
> try to restore it on the test server but the test server
> doesn't have 50GB free space in one single drive. So, I
> can't create one 50GB mdf file. I try to create the test
> database with 20GB on one drive and another 30GB on
> another drive. But I still can't restore the database.
> Is it possible to restore this backup file on this test
> server which has limited amount of disk space?
> Thanks in advance for suggestion.
> Jd.

problem with RESTORE database

Hi Friends,
I have been facing a problem while restoring a database backup from
one SQL server on Win2003Server to another SQL Server on Win2000.
The error head is
ODBC SQL STATE : 42000
Please suggest me some way to overcome this.
Thanks,
Dutt.
How exactly are you restoring the database and can you give the full error
please?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1173694572.447609.52340@.h3g2000cwc.googlegrou ps.com...
> Hi Friends,
> I have been facing a problem while restoring a database backup from
> one SQL server on Win2003Server to another SQL Server on Win2000.
> The error head is
> ODBC SQL STATE : 42000
>
> Please suggest me some way to overcome this.
> Thanks,
> Dutt.
>
|||yeah,
Its saying........
The media family on device 'D:\Xpert_Full.bak' is incorrectly formed.
SQL Server cannot process this media family.
RESTORE is terminating abnormally.
Thanks Immi,
Dutt
|||and you are getting this error from simply backing up the database, copying
to the new server and then restoring?
Are they both SQLserver 2005?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:O7AVESJZHHA.3628@.TK2MSFTNGP02.phx.gbl...
> How exactly are you restoring the database and can you give the full error
> please?
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1173694572.447609.52340@.h3g2000cwc.googlegrou ps.com...
>
|||Dutt
Try
restore database ... from disk ='D:\Xpert_Full.bak' with file=1 ,recovery
I think you issued your backup with 'without init' option
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1173697414.376193.158530@.h3g2000cwc.googlegro ups.com...
> yeah,
> Its saying........
> The media family on device 'D:\Xpert_Full.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> RESTORE is terminating abnormally.
> Thanks Immi,
> Dutt
>
|||Hello,
Can you use RESTORE HEADERONLY and see if you have multiple backup files in
the same the same backup file. If yes then
try using FILE= option while restoring. If you get any errors then you may
need to contact microsoft product support to
investigate further or do a FULL database backup again from source using
BACKUP DATABASE WITH INIT option
and try restoring into destination again.
Thanks
Hari
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1173697414.376193.158530@.h3g2000cwc.googlegro ups.com...
> yeah,
> Its saying........
> The media family on device 'D:\Xpert_Full.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> RESTORE is terminating abnormally.
> Thanks Immi,
> Dutt
>
|||Hi,
Immy,I'm using SQL Server 2000 in both systems.
Dimant,Its showing some error.
Hari,There is only one backup with that name.
I stress again that the back up is in another server.
Thanks,
Dutt

problem with restore database

I got a database backup file. By running RESTORE
FILELISTONLY, it shows 50GB mdf file and 200MB ldf file. I
try to restore it on the test server but the test server
doesn't have 50GB free space in one single drive. So, I
can't create one 50GB mdf file. I try to create the test
database with 20GB on one drive and another 30GB on
another drive. But I still can't restore the database.
Is it possible to restore this backup file on this test
server which has limited amount of disk space?
Thanks in advance for suggestion.
Jd.You must recreate the exact file size and count for a restore. Files do not
have to be in the same location as the backup, but they cannot be split or
shrunk until the restore is complete. In short, if you have a database file
that is 50GB, you need 50GB to restore it. Plan your systems accordingly.
Geoff N. Hiten
Microsoft SQL Server MVP
Senior Database Administrator
Careerbuilder.com
I support the Professional Association for SQL Server
www.sqlpass.org
"JD" <anonymous@.discussions.microsoft.com> wrote in message
news:193fe01c4218f$284113d0$a601280a@.phx
.gbl...
> I got a database backup file. By running RESTORE
> FILELISTONLY, it shows 50GB mdf file and 200MB ldf file. I
> try to restore it on the test server but the test server
> doesn't have 50GB free space in one single drive. So, I
> can't create one 50GB mdf file. I try to create the test
> database with 20GB on one drive and another 30GB on
> another drive. But I still can't restore the database.
> Is it possible to restore this backup file on this test
> server which has limited amount of disk space?
> Thanks in advance for suggestion.
> Jd.|||> Is it possible to restore this backup file on this test
> server which has limited amount of disk space?
I'm afraid not. When doing restore, SQL Server will create the same database
file layout, each file has the
same logical file name and at least the same size (this includes the transac
tion log file(s)). This fact is
one reason why you might want to split up the database into several files in
the first place.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"JD" <anonymous@.discussions.microsoft.com> wrote in message news:193fe01c4218f$284113d0$a60
1280a@.phx.gbl...
> I got a database backup file. By running RESTORE
> FILELISTONLY, it shows 50GB mdf file and 200MB ldf file. I
> try to restore it on the test server but the test server
> doesn't have 50GB free space in one single drive. So, I
> can't create one 50GB mdf file. I try to create the test
> database with 20GB on one drive and another 30GB on
> another drive. But I still can't restore the database.
> Is it possible to restore this backup file on this test
> server which has limited amount of disk space?
> Thanks in advance for suggestion.
> Jd.

problem with RESTORE database

Hi Friends,
I have been facing a problem while restoring a database backup from
one SQL server on Win2003Server to another SQL Server on Win2000.
The error head is
ODBC SQL STATE : 42000
Please suggest me some way to overcome this.
Thanks,
Dutt.How exactly are you restoring the database and can you give the full error
please?
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1173694572.447609.52340@.h3g2000cwc.googlegroups.com...
> Hi Friends,
> I have been facing a problem while restoring a database backup from
> one SQL server on Win2003Server to another SQL Server on Win2000.
> The error head is
> ODBC SQL STATE : 42000
>
> Please suggest me some way to overcome this.
> Thanks,
> Dutt.
>|||yeah,
Its saying........
The media family on device 'D:\Xpert_Full.bak' is incorrectly formed.
SQL Server cannot process this media family.
RESTORE is terminating abnormally.
Thanks Immi,
Dutt|||and you are getting this error from simply backing up the database, copying
to the new server and then restoring?
Are they both SQLserver 2005?
"Immy" <therealasianbabe@.hotmail.com> wrote in message
news:O7AVESJZHHA.3628@.TK2MSFTNGP02.phx.gbl...
> How exactly are you restoring the database and can you give the full error
> please?
> "Dutt" <Mr.Dutt@.gmail.com> wrote in message
> news:1173694572.447609.52340@.h3g2000cwc.googlegroups.com...
>|||Dutt
Try
restore database ... from disk ='D:\Xpert_Full.bak' with file=1 ,recovery
I think you issued your backup with 'without init' option
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1173697414.376193.158530@.h3g2000cwc.googlegroups.com...
> yeah,
> Its saying........
> The media family on device 'D:\Xpert_Full.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> RESTORE is terminating abnormally.
> Thanks Immi,
> Dutt
>|||Hello,
Can you use RESTORE HEADERONLY and see if you have multiple backup files in
the same the same backup file. If yes then
try using FILE= option while restoring. If you get any errors then you may
need to contact microsoft product support to
investigate further or do a FULL database backup again from source using
BACKUP DATABASE WITH INIT option
and try restoring into destination again.
Thanks
Hari
"Dutt" <Mr.Dutt@.gmail.com> wrote in message
news:1173697414.376193.158530@.h3g2000cwc.googlegroups.com...
> yeah,
> Its saying........
> The media family on device 'D:\Xpert_Full.bak' is incorrectly formed.
> SQL Server cannot process this media family.
> RESTORE is terminating abnormally.
> Thanks Immi,
> Dutt
>|||Hi,
Immy,I'm using SQL Server 2000 in both systems.
Dimant,Its showing some error.
Hari,There is only one backup with that name.
I stress again that the back up is in another server.
Thanks,
Dutt|||Dutt wrote:
> Hi,
> Immy,I'm using SQL Server 2000 in both systems.
> Dimant,Its showing some error.
> Hari,There is only one backup with that name.
> I stress again that the back up is in another server.
> Thanks,
> Dutt
>
Could you show us the full statement you are running?
You could also try to look up the RESTORE FILELISTONLY and RESTORE
HEADERONLY commands in Books On Line to see what the reveal. An option
could also be to run the RESTORE VERIFYONLY command to check if the
backup file is corrupt or not.
Regards
Steen Schlter Persson
Database Administrator / System Administrator

Monday, March 26, 2012

Problem with SQL Backup

Problem with SQL Backup

Problem with SQL Backup

Monday, March 12, 2012

Problem with restoring sql server database

Problem with restoring an SQL 2K backup on to SQL 2005

Problem with restoring a database

problem with restore database

problem with RESTORE database

problem with restore database

problem with RESTORE database

PAGE HEADER

Blog Archive

About Me