Problem restoring database to a compressed drive.

I am learning how to use SMO and I have a test application that allows me to restore a database backup to a new database. On my system I have one physical drive but two logical drives on separate partitions. Drive C: is on a partition that is a FAT32 partition and drive D: is on a partition that is a NTFS partition and is a compressed partition. The database backup file is located on drive D:. When I try to restore the backup to a new database and provide a path on the D: drive in the RelocateFile object I get the following error message.

"The file 'D:\NPS_Test.mdf' is compressed but does not reside in a read-only database or filegroup. The file must be decompressed.

When I do a restore and indicate that the new database file will be located on the C: drive the restore works. I assume the problem has to do with the fact that drive D: is set up as a compressed drive, but I am not sure.

Does this mean that I cannot store a database file on a compressed drive or is there something I need to do to make it work?

Hi,

http://blogs.msdn.com/sqlblog/archive/2006/10/02/SQL-Server-databases-are-not-supported-on-compressed-volumes.aspx

HTH, Jens K. Suessmeyer.

http://www.sqlserver2005.de

Problem Restoring Database

I have a backup of a database from SQL Server. I try to restore that backup file to SQL Server Express and get the following error:

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TEST.BAK'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

When restoring other files, I would get this same error message, copy it to another folder and it would work. With this particular one, no matter what folder I copy it to I get the same error message. From what I can find online is that its a permission thing, but I cant find anywhere to change the permissions.

Any help is appreciated.

That can be based on serveral problems which either depends on the way you authenticate to the database server (you did not mention that) and the service account (running SQL Server authentication) or you own account (running Windows authenticaiton) does not have the appropiate rights to access the folder or you are running Vista, which needs elevated rights to access the special folder opn your system.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I connect using mixed authentication. I am running windows XP.|||

I have hit this issue, detaching a database then copying the .mdf file then attempting to attach on another server.

The 'copied-to' server in my case is running on Vista. I eventually bludgeoned it into working by opening up all the permissions on the containing folder and the .mdf file itself; so I guess this tends to confirm it's a permissions issue, but 'just give everyone access to everything' isn't really a satisfactory answer! Can anyone point us in the direction of more detail on how permissions and SQL Express interact - I gather it's particularly tricky with Vista and UAC etc. Jens - could you expand on the bit about Vista and the 'special folder' - I take it you mean the folder containing the .mdf?

TIA

pg

|||What I ended up doing was setting the mssql folder proprty to full access and took read only off and it worked. Thanks.|||There's some more about this issue here: http://msdn2.microsoft.com/en-us/library/ms189128.aspx

pg

Problem Restoring Database

I have a backup of a database from SQL Server. I try to restore that backup file to SQL Server Express and get the following error:

Cannot open backup device 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\TEST.BAK'. Operating system error 5(Access is denied.).
RESTORE HEADERONLY is terminating abnormally. (.Net SqlClient Data Provider)

When restoring other files, I would get this same error message, copy it to another folder and it would work. With this particular one, no matter what folder I copy it to I get the same error message. From what I can find online is that its a permission thing, but I cant find anywhere to change the permissions.

Any help is appreciated.

That can be based on serveral problems which either depends on the way you authenticate to the database server (you did not mention that) and the service account (running SQL Server authentication) or you own account (running Windows authenticaiton) does not have the appropiate rights to access the folder or you are running Vista, which needs elevated rights to access the special folder opn your system.

Jens K. Suessmeyer.

http://www.sqlserver2005.de

|||I connect using mixed authentication. I am running windows XP.|||

I have hit this issue, detaching a database then copying the .mdf file then attempting to attach on another server.

The 'copied-to' server in my case is running on Vista. I eventually bludgeoned it into working by opening up all the permissions on the containing folder and the .mdf file itself; so I guess this tends to confirm it's a permissions issue, but 'just give everyone access to everything' isn't really a satisfactory answer! Can anyone point us in the direction of more detail on how permissions and SQL Express interact - I gather it's particularly tricky with Vista and UAC etc. Jens - could you expand on the bit about Vista and the 'special folder' - I take it you mean the folder containing the .mdf?

TIA

pg

|||What I ended up doing was setting the mssql folder proprty to full access and took read only off and it worked. Thanks.|||There's some more about this issue here: http://msdn2.microsoft.com/en-us/library/ms189128.aspx

pg

problem restoring backup of DB

Hello,
I copied a backup file of a production DB on a production server to another
location on a Development server. When I try to restore this backup on the
Dev server - from Enterprise Manager, I am getting an error message that
states I need to use "With Move" to identify a valid location for the restor
e
file.
On the Restore Database Dialog I enter the name of the mdf file (which is
not in the dropdown list) in the "Restore as database" textbox. Then I
select the device - "Restore from" Disk, click on Add - select the bak file
,
click OK from that dialog, goto the Options tab and enter the physical path
in the "Move to physical file name" window. Then click OK on the Restore
Database dialog and get the error message above - "use With Move..." How do
I use "With Move"?
I also copied the actual mdf file from the production server and moved it to
the Dev server and tried to attach it to the Dev serve. But the mdf is
trying to reference the old Log file. I thought (hoped) it would create its
own log file. Is there a way to get around this?
Thanks,
Rich> I also copied the actual mdf file from the production server and moved it tod">
> the Dev server and tried to attach it to the Dev serve. But the mdf is
> trying to reference the old Log file. I thought (hoped) it would create i
ts
> own log file. Is there a way to get around this?
>
Did you detach the database first, before copying the mdf file?
If not, try detaching the DB, then copying the file(s), then re-attaching to
the other server.|||The file is a replication DB and I did not want to fiddle with
sp_removereplication. Otherwise I would have used the Copy Database wizard.
But since it is in Replication mode, I can't detach it or use the Copy
Database wizard.
What I did was to stop the production server for a few minutes, copied the
file to the dev server - restart the prod server. I did not copy the ldf
because it was kind of large. I was hoping the copy DB would create its own
log file on the Dev server. Is there a way to force that to happen?
Or am I stuck with using sp_removeReplication and then doing the copy?
"Mark Williams" wrote:

> Did you detach the database first, before copying the mdf file?
> If not, try detaching the DB, then copying the file(s), then re-attaching
to
> the other server.|||You will need the ldf file along with your mdf file. What you did is the
same as detach method except you shut MS SQL server down for a few minutes.
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:625F97FE-6DC8-4DB3-95AE-392A7CCAB396@.microsoft.com...
> The file is a replication DB and I did not want to fiddle with
> sp_removereplication. Otherwise I would have used the Copy Database
> wizard.
> But since it is in Replication mode, I can't detach it or use the Copy
> Database wizard.
> What I did was to stop the production server for a few minutes, copied the
> file to the dev server - restart the prod server. I did not copy the ldf
> because it was kind of large. I was hoping the copy DB would create its
> own
> log file on the Dev server. Is there a way to force that to happen?
> Or am I stuck with using sp_removeReplication and then doing the copy?
> "Mark Williams" wrote:
>|||Yeah, I figured I would have to do that. the ldf is like 3 gigs +. So once
I get the DB attached, what is the best way to shrink down the ldf? Can I
delete it once the mdf is attached and it will create a new one?
"Grant" wrote:

> You will need the ldf file along with your mdf file. What you did is the
> same as detach method except you shut MS SQL server down for a few minutes
.
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:625F97FE-6DC8-4DB3-95AE-392A7CCAB396@.microsoft.com...
>
>|||> I get the DB attached, what is the best way to shrink down the ldf?
You shrink if *before* you detach. Or after you have attached. Use DBCC SHRI
NKFILE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:D1264111-609E-4190-922C-A88BB233D0CB@.microsoft.com...
> Yeah, I figured I would have to do that. the ldf is like 3 gigs +. So on
ce
> I get the DB attached, what is the best way to shrink down the ldf? Can I
> delete it once the mdf is attached and it will create a new one?
> "Grant" wrote:
>|||Following or explaining GUI in text is incredibly hard. I suggest you invest
igate the file structure
of your backup using RESTORE FILELISTONLY. And based on that construct a RES
TORE command with the
proper commands. If restore from that doesn't work, you can post the output
from RESTORE
FILELISTONLY, your RESTORE command you tried and the error message.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
> Hello,
> I copied a backup file of a production DB on a production server to anothe
r
> location on a Development server. When I try to restore this backup on th
e
> Dev server - from Enterprise Manager, I am getting an error message that
> states I need to use "With Move" to identify a valid location for the rest
ore
> file.
> On the Restore Database Dialog I enter the name of the mdf file (which is
> not in the dropdown list) in the "Restore as database" textbox. Then I
> select the device - "Restore from" Disk, click on Add - select the bak fi
le,
> click OK from that dialog, goto the Options tab and enter the physical pa
th
> in the "Move to physical file name" window. Then click OK on the Restore
> Database dialog and get the error message above - "use With Move..." How
do
> I use "With Move"?
> I also copied the actual mdf file from the production server and moved it
to
> the Dev server and tried to attach it to the Dev serve. But the mdf is
> trying to reference the old Log file. I thought (hoped) it would create i
ts
> own log file. Is there a way to get around this?
> Thanks,
> Rich|||Thank you for your suggestion. Here is what Restore Filelist returned:
Subscriber_Data D:\MSSQLDATA\Subscriber_
Data.MDF D PRIMARY 654573568 3518437
2080640
Subscriber_Log E:\MSSQLLOGS\Subscriber_l
og.LDF L NULL 2818572288 35184372080
640
And here is what I tried with Restore Database - which is currently running
RESTORE DATABASE Subscriber
FROM DISK = 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber.bak'
WITH MOVE 'Subscriber' TO
'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Data.mdf',
MOVE 'Subscriber_log' TO
'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Log.ldf'
I will post the results of this restore effort - still running - probably
for an hour or so.
"Tibor Karaszi" wrote:

> Following or explaining GUI in text is incredibly hard. I suggest you inve
stigate the file structure
> of your backup using RESTORE FILELISTONLY. And based on that construct a R
ESTORE command with the
> proper commands. If restore from that doesn't work, you can post the outpu
t from RESTORE
> FILELISTONLY, your RESTORE command you tried and the error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
>|||Success!!! Thank you very much for your help. The restore DB works
perfectly!
"Tibor Karaszi" wrote:

> Following or explaining GUI in text is incredibly hard. I suggest you inve
stigate the file structure
> of your backup using RESTORE FILELISTONLY. And based on that construct a R
ESTORE command with the
> proper commands. If restore from that doesn't work, you can post the outpu
t from RESTORE
> FILELISTONLY, your RESTORE command you tried and the error message.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Rich" <Rich@.discussions.microsoft.com> wrote in message
> news:665B37F8-7FBD-4F62-9939-BFE283A242A7@.microsoft.com...
>|||The restore command looks fine with one comment, and I also noticed the othe
r post where you said it
was successful.
The comment is that you specified the location for the database files to be
created by your restore
process to be in the Backup directory. Not that it is harmful, but perhaps a
little bit unusual.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Rich" <Rich@.discussions.microsoft.com> wrote in message
news:C2E59AEB-3FF8-4930-B0E6-DDACEE2B9104@.microsoft.com...
> Thank you for your suggestion. Here is what Restore Filelist returned:
> Subscriber_Data D:\MSSQLDATA\Subscriber_Data.MDF D PRIMARY 654573568 35184
372080640
> Subscriber_Log E:\MSSQLLOGS\Subscriber_log.LDF L NULL 2818572288 351843720
80640
>
> And here is what I tried with Restore Database - which is currently runnin
g
> RESTORE DATABASE Subscriber
> FROM DISK = 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber.bak'
> WITH MOVE 'Subscriber' TO
> 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Data.mdf',
> MOVE 'Subscriber_log' TO
> 'G:\MSSQL\DevData\MSSQL$Dev\Backup\Subsc
riber_Log.ldf'
> I will post the results of this restore effort - still running - probably
> for an hour or so.
>
> "Tibor Karaszi" wrote:
>sql

Problem restoring a SQL 2005 database from a backup device

I am trying to restore a database from a backup device and when I try
to add the backup device in the restore database wizard I receive the
following error message:
An exception occurred while executing a transact-sql statement or
batch.
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.
The backup device was created using SQL Server 2005 and I am trying to
restore the database to an Express version of 2005.
I can restore the database successfully if I restore it to the same
server that I created the backup device on.
For a little background, I am trying to backup a database on a server
to a laptop for business continuity purposes. The database is under 1
gig in total size.
Any insight is greatly appreciated.Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
John
"wolf.emissar@.gmail.com" wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> John
> "wolf.emissar@.gmail.com" wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> >|||Hi
Both posters were trying to restore a SQL 2005 database onto a SQL 2000
instance and they have the error message you get. Are you sure that there are
no SQL 2000 instances on the machine you have? Database restores are
compatible between all versions of SQL 2005 therefore you should not have any
problems. Is this a beta or CTP version?
John
"wolf.emissar@.gmail.com" wrote:
> John, thanks for the response. I am trying to restore to a 2005
> instance but it is an Express version, I don't know if this is the
> source of the problem or not.
> In the second link you attached Tibor mentions logging into a 2000
> instance. This sounds very much like the error I am receiving, do you
> know what he is referring to? Can express 2005 be configured to run as
> a 2000 instance?
> John Bell wrote:
> > Hi
> >
> > Other posts indicate this error message occurs if you try and restore from
> > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> > >
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Steen, here is the SQL that I am running for the restore:
restore database wolf_sonar
from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
and I recieve the following error message:
Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are
allowed.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
One thing that I did notice that is I don't have a compatibility level
of 90 on the local server on the laptop that the wolf_sonar database
resides on. I only have 70 and 80 as available choices.
John to answer your question, I do not have any 2000 instances on the
machine that I am trying to restore the database to. This is a fairly
new machine and I actually just installed 2005 Express on this box a
couple of days. As a further chack I only see sql server 2005 in add /
remove programs.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > I am trying to restore a database from a backup device and when I try
> > to add the backup device in the restore database wizard I receive the
> > following error message:
> >
> > An exception occurred while executing a transact-sql statement or
> > batch.
> >
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed. RESTORE HEADER ONLY is terminating abnormally.
> >
> > The backup device was created using SQL Server 2005 and I am trying to
> > restore the database to an Express version of 2005.
> >
> > I can restore the database successfully if I restore it to the same
> > server that I created the backup device on.
> >
> > For a little background, I am trying to backup a database on a server
> > to a laptop for business continuity purposes. The database is under 1
> > gig in total size.
> >
> > Any insight is greatly appreciated.
> >
> Could you try to post the sql statement you are running?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||John, I have a tried a few different version of 2005 express. The
latest one that I have installed is Version 9.00.2047.00. I do not
believe that this is a beta version.
John Bell wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
If you ran SELECT @.@.VERSION to get the version number in your other post,
then you are on SQL 2005 Service Pack 1. If you obtained it otherwise run the
command in Management Studio or SQLCMD.
I am not sure where you are getting the compatibility level from but if you
ran EXEC sp_helpdb 'MyDatabase' it will tell you at the end column of the
first resultset.
Try running:
RESTORE FILELISTONLY
FROM disk = 'C:\sqlbackups\sonar_wolf_new.bak'
John
"wolf.emissar@.gmail.com" wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > I am trying to restore a database from a backup device and when I try
> > > to add the backup device in the restore database wizard I receive the
> > > following error message:
> > >
> > > An exception occurred while executing a transact-sql statement or
> > > batch.
> > >
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > >
> > > The backup device was created using SQL Server 2005 and I am trying to
> > > restore the database to an Express version of 2005.
> > >
> > > I can restore the database successfully if I restore it to the same
> > > server that I created the backup device on.
> > >
> > > For a little background, I am trying to backup a database on a server
> > > to a laptop for business continuity purposes. The database is under 1
> > > gig in total size.
> > >
> > > Any insight is greatly appreciated.
> > >
> >
> > Could you try to post the sql statement you are running?
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
>|||wolf.emissar@.gmail.com wrote:
> Steen, here is the SQL that I am running for the restore:
> restore database wolf_sonar
> from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> and I recieve the following error message:
> Msg 3205, Level 16, State 2, Line 1
> Too many backup devices specified for backup or restore; only 64 are
> allowed.
> Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> One thing that I did notice that is I don't have a compatibility level
> of 90 on the local server on the laptop that the wolf_sonar database
> resides on. I only have 70 and 80 as available choices.
> John to answer your question, I do not have any 2000 instances on the
> machine that I am trying to restore the database to. This is a fairly
> new machine and I actually just installed 2005 Express on this box a
> couple of days. As a further chack I only see sql server 2005 in add /
> remove programs.
>
It sounds like you haven't got a SQL2005 instance on the PC.
Could you try to run SELECT @.@.VERSION on the laptop to see which version
it reports to be running.
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator|||Thats it, I was logging into the 2000 server. I didn't realize that
2005 SQL Express installed a 2000 version and a 2005 version of the
database server on my local machine, and for some reason it defaults to
the 2000 version of the server. When I select and connect to the 2005
version everything works as expected.
Thanks for the help, I really appreciate it.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> > Steen, here is the SQL that I am running for the restore:
> >
> > restore database wolf_sonar
> > from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
> >
> > and I recieve the following error message:
> >
> > Msg 3205, Level 16, State 2, Line 1
> > Too many backup devices specified for backup or restore; only 64 are
> > allowed.
> > Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > One thing that I did notice that is I don't have a compatibility level
> > of 90 on the local server on the laptop that the wolf_sonar database
> > resides on. I only have 70 and 80 as available choices.
> >
> > John to answer your question, I do not have any 2000 instances on the
> > machine that I am trying to restore the database to. This is a fairly
> > new machine and I actually just installed 2005 Express on this box a
> > couple of days. As a further chack I only see sql server 2005 in add /
> > remove programs.
> >
> It sounds like you haven't got a SQL2005 instance on the PC.
> Could you try to run SELECT @.@.VERSION on the laptop to see which version
> it reports to be running.
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||wolf.emissar@.gmail.com wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
>
Good to hear that you got the problem solved. A SQL2005 install will NOT
install a SQL2000 instance in anyway. It's more likely that you already
had a SQL2000 instance installed on the PC. When you then installed the
SQL2005 instance it would only be able to install as a named instance
because the SQL2000 was the default instance.
Regards
Steen Schlüter Persson
Database Administrator / System Administrator|||Hi
It sounds like SQL 2000 is your default instance, in which case if you don't
specify a specific instance to log into it will default to that!
John
"wolf.emissar@.gmail.com" wrote:
> Thats it, I was logging into the 2000 server. I didn't realize that
> 2005 SQL Express installed a 2000 version and a 2005 version of the
> database server on my local machine, and for some reason it defaults to
> the 2000 version of the server. When I select and connect to the 2005
> version everything works as expected.
> Thanks for the help, I really appreciate it.
> Steen Persson (DK) wrote:
> > wolf.emissar@.gmail.com wrote:
> > > Steen, here is the SQL that I am running for the restore:
> > >
> > > restore database wolf_sonar
> > > from disk = 'C:\sqlbackups\sonar_wolf_new.bak'
> > >
> > > and I recieve the following error message:
> > >
> > > Msg 3205, Level 16, State 2, Line 1
> > > Too many backup devices specified for backup or restore; only 64 are
> > > allowed.
> > > Msg 3013, Level 16, State 1, Line 1
> > > RESTORE DATABASE is terminating abnormally.
> > >
> > > One thing that I did notice that is I don't have a compatibility level
> > > of 90 on the local server on the laptop that the wolf_sonar database
> > > resides on. I only have 70 and 80 as available choices.
> > >
> > > John to answer your question, I do not have any 2000 instances on the
> > > machine that I am trying to restore the database to. This is a fairly
> > > new machine and I actually just installed 2005 Express on this box a
> > > couple of days. As a further chack I only see sql server 2005 in add /
> > > remove programs.
> > >
> >
> > It sounds like you haven't got a SQL2005 instance on the PC.
> > Could you try to run SELECT @.@.VERSION on the laptop to see which version
> > it reports to be running.
> >
> >
> > --
> > Regards
> > Steen Schlüter Persson
> > Databaseadministrator / Systemadministrator
>|||Hi John -
Can you point me to a document confirming backups are compatible across SQL
Server 2005 versions? This has been a subject of debate here.
Thanks!
dj
"John Bell" wrote:
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
> > John, thanks for the response. I am trying to restore to a 2005
> > instance but it is an Express version, I don't know if this is the
> > source of the problem or not.
> >
> > In the second link you attached Tibor mentions logging into a 2000
> > instance. This sounds very much like the error I am receiving, do you
> > know what he is referring to? Can express 2005 be configured to run as
> > a 2000 instance?
> >
> > John Bell wrote:
> > > Hi
> > >
> > > Other posts indicate this error message occurs if you try and restore from
> > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > I am trying to restore a database from a backup device and when I try
> > > > to add the backup device in the restore database wizard I receive the
> > > > following error message:
> > > >
> > > > An exception occurred while executing a transact-sql statement or
> > > > batch.
> > > >
> > > > Too many backup devices specified for backup or restore; only 64 are
> > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > >
> > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > restore the database to an Express version of 2005.
> > > >
> > > > I can restore the database successfully if I restore it to the same
> > > > server that I created the backup device on.
> > > >
> > > > For a little background, I am trying to backup a database on a server
> > > > to a laptop for business continuity purposes. The database is under 1
> > > > gig in total size.
> > > >
> > > > Any insight is greatly appreciated.
> > > >
> > > >
> >
> >|||Hi
I don't think there is a single document! It is basically the same database
engine so why would you not think it is compatible?
Assuming that both instances are at the same service pack/hotfix level, then
if there are issues restoring from one version to another it should not be
because of the version which is running e.g. an unsupported collations on the
destination instance.
If you are planning a DR strategy, you should always test the recovery
scenarios and plans regardless!
John
"dj" wrote:
> Hi John -
> Can you point me to a document confirming backups are compatible across SQL
> Server 2005 versions? This has been a subject of debate here.
> Thanks!
> dj
> "John Bell" wrote:
> > Hi
> >
> > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > instance and they have the error message you get. Are you sure that there are
> > no SQL 2000 instances on the machine you have? Database restores are
> > compatible between all versions of SQL 2005 therefore you should not have any
> > problems. Is this a beta or CTP version?
> >
> > John
> >
> > "wolf.emissar@.gmail.com" wrote:
> >
> > > John, thanks for the response. I am trying to restore to a 2005
> > > instance but it is an Express version, I don't know if this is the
> > > source of the problem or not.
> > >
> > > In the second link you attached Tibor mentions logging into a 2000
> > > instance. This sounds very much like the error I am receiving, do you
> > > know what he is referring to? Can express 2005 be configured to run as
> > > a 2000 instance?
> > >
> > > John Bell wrote:
> > > > Hi
> > > >
> > > > Other posts indicate this error message occurs if you try and restore from
> > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > I am trying to restore a database from a backup device and when I try
> > > > > to add the backup device in the restore database wizard I receive the
> > > > > following error message:
> > > > >
> > > > > An exception occurred while executing a transact-sql statement or
> > > > > batch.
> > > > >
> > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > >
> > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > restore the database to an Express version of 2005.
> > > > >
> > > > > I can restore the database successfully if I restore it to the same
> > > > > server that I created the backup device on.
> > > > >
> > > > > For a little background, I am trying to backup a database on a server
> > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > gig in total size.
> > > > >
> > > > > Any insight is greatly appreciated.
> > > > >
> > > > >
> > >
> > >|||Call me cautious by nature. :)
Are they compatible between 32-bit and 64-bit editions?
"John Bell" wrote:
> Hi
> I don't think there is a single document! It is basically the same database
> engine so why would you not think it is compatible?
> Assuming that both instances are at the same service pack/hotfix level, then
> if there are issues restoring from one version to another it should not be
> because of the version which is running e.g. an unsupported collations on the
> destination instance.
> If you are planning a DR strategy, you should always test the recovery
> scenarios and plans regardless!
> John
> "dj" wrote:
> > Hi John -
> >
> > Can you point me to a document confirming backups are compatible across SQL
> > Server 2005 versions? This has been a subject of debate here.
> >
> > Thanks!
> > dj
> >
> > "John Bell" wrote:
> >
> > > Hi
> > >
> > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > instance and they have the error message you get. Are you sure that there are
> > > no SQL 2000 instances on the machine you have? Database restores are
> > > compatible between all versions of SQL 2005 therefore you should not have any
> > > problems. Is this a beta or CTP version?
> > >
> > > John
> > >
> > > "wolf.emissar@.gmail.com" wrote:
> > >
> > > > John, thanks for the response. I am trying to restore to a 2005
> > > > instance but it is an Express version, I don't know if this is the
> > > > source of the problem or not.
> > > >
> > > > In the second link you attached Tibor mentions logging into a 2000
> > > > instance. This sounds very much like the error I am receiving, do you
> > > > know what he is referring to? Can express 2005 be configured to run as
> > > > a 2000 instance?
> > > >
> > > > John Bell wrote:
> > > > > Hi
> > > > >
> > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > >
> > > > > John
> > > > >
> > > > > "wolf.emissar@.gmail.com" wrote:
> > > > >
> > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > following error message:
> > > > > >
> > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > batch.
> > > > > >
> > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > >
> > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > restore the database to an Express version of 2005.
> > > > > >
> > > > > > I can restore the database successfully if I restore it to the same
> > > > > > server that I created the backup device on.
> > > > > >
> > > > > > For a little background, I am trying to backup a database on a server
> > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > gig in total size.
> > > > > >
> > > > > > Any insight is greatly appreciated.
> > > > > >
> > > > > >
> > > >
> > > >|||Hi
Yes, the format for a backed up database does not change.
John
"dj" wrote:
> Call me cautious by nature. :)
> Are they compatible between 32-bit and 64-bit editions?
> "John Bell" wrote:
> > Hi
> >
> > I don't think there is a single document! It is basically the same database
> > engine so why would you not think it is compatible?
> >
> > Assuming that both instances are at the same service pack/hotfix level, then
> > if there are issues restoring from one version to another it should not be
> > because of the version which is running e.g. an unsupported collations on the
> > destination instance.
> >
> > If you are planning a DR strategy, you should always test the recovery
> > scenarios and plans regardless!
> >
> > John
> >
> > "dj" wrote:
> >
> > > Hi John -
> > >
> > > Can you point me to a document confirming backups are compatible across SQL
> > > Server 2005 versions? This has been a subject of debate here.
> > >
> > > Thanks!
> > > dj
> > >
> > > "John Bell" wrote:
> > >
> > > > Hi
> > > >
> > > > Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> > > > instance and they have the error message you get. Are you sure that there are
> > > > no SQL 2000 instances on the machine you have? Database restores are
> > > > compatible between all versions of SQL 2005 therefore you should not have any
> > > > problems. Is this a beta or CTP version?
> > > >
> > > > John
> > > >
> > > > "wolf.emissar@.gmail.com" wrote:
> > > >
> > > > > John, thanks for the response. I am trying to restore to a 2005
> > > > > instance but it is an Express version, I don't know if this is the
> > > > > source of the problem or not.
> > > > >
> > > > > In the second link you attached Tibor mentions logging into a 2000
> > > > > instance. This sounds very much like the error I am receiving, do you
> > > > > know what he is referring to? Can express 2005 be configured to run as
> > > > > a 2000 instance?
> > > > >
> > > > > John Bell wrote:
> > > > > > Hi
> > > > > >
> > > > > > Other posts indicate this error message occurs if you try and restore from
> > > > > > SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and http://tinyurl.com/kajam
> > > > > >
> > > > > > John
> > > > > >
> > > > > > "wolf.emissar@.gmail.com" wrote:
> > > > > >
> > > > > > > I am trying to restore a database from a backup device and when I try
> > > > > > > to add the backup device in the restore database wizard I receive the
> > > > > > > following error message:
> > > > > > >
> > > > > > > An exception occurred while executing a transact-sql statement or
> > > > > > > batch.
> > > > > > >
> > > > > > > Too many backup devices specified for backup or restore; only 64 are
> > > > > > > allowed. RESTORE HEADER ONLY is terminating abnormally.
> > > > > > >
> > > > > > > The backup device was created using SQL Server 2005 and I am trying to
> > > > > > > restore the database to an Express version of 2005.
> > > > > > >
> > > > > > > I can restore the database successfully if I restore it to the same
> > > > > > > server that I created the backup device on.
> > > > > > >
> > > > > > > For a little background, I am trying to backup a database on a server
> > > > > > > to a laptop for business continuity purposes. The database is under 1
> > > > > > > gig in total size.
> > > > > > >
> > > > > > > Any insight is greatly appreciated.
> > > > > > >
> > > > > > >
> > > > >
> > > > >|||Hi Sheryl
If you issue a NET START command at a command prompt you can verify is
SQLExpress is running. The default name for a SQL Express instance is
SERVER\SQLEXPRESS (where SERVER is the hostname). If SQLExpress is running
you should be able to use SERVER\EXPRESS this in the connection details.
Have you downloaded Express Management Studio or got the Advanced Services
options?
If you are wanting to enable remote access to your SQL Express instance you
can enable this through the Surface Area Configuration tool sqlsac.exe
John
"Sheryl Briggs" wrote:
> you wrote:
> "It sounds like SQL 2000 is your default instance, in which case if you
> don't
> specify a specific instance to log into it will default to that"
> I installed SQL 2005 express on a machine that already had 2000 on it
> and am having the same problem, but there are only 2 instance names
> showing up in the connection mgr in Express 2005, and both say they are
> version 2000? How do i get to the 2005 connection? THe new instance
> name that appeared when I installed 2005 also says its version is
> 2000? SHould I reinstall it?
> Thanks
> *** Sent via Developersdex http://www.developersdex.com ***
>

Problem restoring a SQL 2005 database from a backup device

Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/ur
l]
John
"wolf.emissar@.gmail.com" wrote:

> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>I am trying to restore a database from a backup device and when I try
to add the backup device in the restore database wizard I receive the
following error message:
An exception occurred while executing a transact-sql statement or
batch.
Too many backup devices specified for backup or restore; only 64 are
allowed. RESTORE HEADER ONLY is terminating abnormally.
The backup device was created using SQL Server 2005 and I am trying to
restore the database to an Express version of 2005.
I can restore the database successfully if I restore it to the same
server that I created the backup device on.
For a little background, I am trying to backup a database on a server
to a laptop for business continuity purposes. The database is under 1
gig in total size.
Any insight is greatly appreciated.|||Hi
Other posts indicate this error message occurs if you try and restore from
SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/ur
l]
John
"wolf.emissar@.gmail.com" wrote:

> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:[vbcol=seagreen]
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/
url]
> John
> "wolf.emissar@.gmail.com" wrote:
>|||John, thanks for the response. I am trying to restore to a 2005
instance but it is an Express version, I don't know if this is the
source of the problem or not.
In the second link you attached Tibor mentions logging into a 2000
instance. This sounds very much like the error I am receiving, do you
know what he is referring to? Can express 2005 be configured to run as
a 2000 instance?
John Bell wrote:[vbcol=seagreen]
> Hi
> Other posts indicate this error message occurs if you try and restore from
> SQL 2005 to SQL 2000 see http://tinyurl.com/nqfls and [url]http://tinyurl.com/kajam[/
url]
> John
> "wolf.emissar@.gmail.com" wrote:
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Hi
Both posters were trying to restore a SQL 2005 database onto a SQL 2000
instance and they have the error message you get. Are you sure that there ar
e
no SQL 2000 instances on the machine you have? Database restores are
compatible between all versions of SQL 2005 therefore you should not have an
y
problems. Is this a beta or CTP version?
John
"wolf.emissar@.gmail.com" wrote:

> John, thanks for the response. I am trying to restore to a 2005
> instance but it is an Express version, I don't know if this is the
> source of the problem or not.
> In the second link you attached Tibor mentions logging into a 2000
> instance. This sounds very much like the error I am receiving, do you
> know what he is referring to? Can express 2005 be configured to run as
> a 2000 instance?
> John Bell wrote:
>|||wolf.emissar@.gmail.com wrote:
> I am trying to restore a database from a backup device and when I try
> to add the backup device in the restore database wizard I receive the
> following error message:
> An exception occurred while executing a transact-sql statement or
> batch.
> Too many backup devices specified for backup or restore; only 64 are
> allowed. RESTORE HEADER ONLY is terminating abnormally.
> The backup device was created using SQL Server 2005 and I am trying to
> restore the database to an Express version of 2005.
> I can restore the database successfully if I restore it to the same
> server that I created the backup device on.
> For a little background, I am trying to backup a database on a server
> to a laptop for business continuity purposes. The database is under 1
> gig in total size.
> Any insight is greatly appreciated.
>
Could you try to post the sql statement you are running?
Regards
Steen Schlter Persson
Databaseadministrator / Systemadministrator|||Steen, here is the SQL that I am running for the restore:
restore database wolf_sonar
from disk =3D 'C:\sqlbackups\sonar_wolf_new.bak'
and I recieve the following error message:
Msg 3205, Level 16, State 2, Line 1
Too many backup devices specified for backup or restore; only 64 are
allowed.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
One thing that I did notice that is I don't have a compatibility level
of 90 on the local server on the laptop that the wolf_sonar database
resides on. I only have 70 and 80 as available choices.
John to answer your question, I do not have any 2000 instances on the
machine that I am trying to restore the database to. This is a fairly
new machine and I actually just installed 2005 Express on this box a
couple of days. As a further chack I only see sql server 2005 in add /
remove programs.
Steen Persson (DK) wrote:
> wolf.emissar@.gmail.com wrote:
> Could you try to post the sql statement you are running?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator|||John, I have a tried a few different version of 2005 express. The
latest one that I have installed is Version 9.00.2047.00. I do not
believe that this is a beta version.
John Bell wrote:[vbcol=seagreen]
> Hi
> Both posters were trying to restore a SQL 2005 database onto a SQL 2000
> instance and they have the error message you get. Are you sure that there
are
> no SQL 2000 instances on the machine you have? Database restores are
> compatible between all versions of SQL 2005 therefore you should not have
any
> problems. Is this a beta or CTP version?
> John
> "wolf.emissar@.gmail.com" wrote:
>

Problem restoring a database from a .bak file

I'm provided with a .bak file that contains a backup of a small database. My
question is how do I restore it to the SQL Server. I used the import featur
e but it gave me all kinds of errors when attempting to import it.
Any ideas!
thanks!Hi,
You can either use SQL server enterprise manager GUI to restore a database
ot use the Query analyzer ... RESTORE database command.
Enterprise manager
1. Open Enterprise manager -- connect to SQL server -- Right click above the
database -- All tasks -- CLick Restore database
2. In the Restore database as "Give a Database name"
3. IN the restore options choose "From Device"
4. Click select devices command button -- CLick add and choose the backup
file name (.BAK file)
5. Click OK
6. IN restore screen also CLICK OK
-- This will restore the database
From Query Analyzer:-
1. Login to Query Analyzer as 'SA' or user with sysadmin ort equalent server
roles.
2. Execute the below command
Restore database <dbname> from disk='d:\backup\dbname.bak' with stats=10
-- Change the directory and file name based on ur requierement.
Thanks
Hari
MCDBA
"sbinev" <sbinev@.discussions.microsoft.com> wrote in message
news:14AACC0D-9E5A-45E6-ABC1-15623DA32CD6@.microsoft.com...
> I'm provided with a .bak file that contains a backup of a small database.
My question is how do I restore it to the SQL Server. I used the import
feature but it gave me all kinds of errors when attempting to import it.
> Any ideas!
> thanks!|||sbinev,
SQL Server Books Online (BOL) installs with SQL Server. I heartily
recommend you look here for how to restore a database. The documentation
is very clear and comprehensive and shows you many methods of
accomplishing this task.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sbinev wrote:
> I'm provided with a .bak file that contains a backup of a small database.
My question is how do I restore it to the SQL Server. I used the import feat
ure but it gave me all kinds of errors when attempting to import it.
> Any ideas!
> thanks!|||Thanks a lot guys!! )
The first method from the Enterprise Manager kept giving me errors, but I us
ed T-SQL looked in the books online and with MOVE TO clause after the RESTOR
E... clause it worked. So thanks for your advice. It's hard to be novice!
"Hari Prasad" wrote:

> Hi,
> You can either use SQL server enterprise manager GUI to restore a database
> ot use the Query analyzer ... RESTORE database command.
> Enterprise manager
> 1. Open Enterprise manager -- connect to SQL server -- Right click above t
he
> database -- All tasks -- CLick Restore database
> 2. In the Restore database as "Give a Database name"
> 3. IN the restore options choose "From Device"
> 4. Click select devices command button -- CLick add and choose the backup
> file name (.BAK file)
> 5. Click OK
> 6. IN restore screen also CLICK OK
> -- This will restore the database
>
> From Query Analyzer:-
> 1. Login to Query Analyzer as 'SA' or user with sysadmin ort equalent serv
er
> roles.
> 2. Execute the below command
> Restore database <dbname> from disk='d:\backup\dbname.bak' with stats=10
> -- Change the directory and file name based on ur requierement.
>
> --
> Thanks
> Hari
> MCDBA
> "sbinev" <sbinev@.discussions.microsoft.com> wrote in message
> news:14AACC0D-9E5A-45E6-ABC1-15623DA32CD6@.microsoft.com...
> My question is how do I restore it to the SQL Server. I used the import
> feature but it gave me all kinds of errors when attempting to import it.
>
>

Problem restoring a database from a .bak file

I'm provided with a .bak file that contains a backup of a small database. My question is how do I restore it to the SQL Server. I used the import feature but it gave me all kinds of errors when attempting to import it.
Any ideas!
thanks!
Hi,
You can either use SQL server enterprise manager GUI to restore a database
ot use the Query analyzer ... RESTORE database command.
Enterprise manager
1. Open Enterprise manager -- connect to SQL server -- Right click above the
database -- All tasks -- CLick Restore database
2. In the Restore database as "Give a Database name"
3. IN the restore options choose "From Device"
4. Click select devices command button -- CLick add and choose the backup
file name (.BAK file)
5. Click OK
6. IN restore screen also CLICK OK
-- This will restore the database
From Query Analyzer:-
1. Login to Query Analyzer as 'SA' or user with sysadmin ort equalent server
roles.
2. Execute the below command
Restore database <dbname> from disk='d:\backup\dbname.bak' with stats=10
-- Change the directory and file name based on ur requierement.
Thanks
Hari
MCDBA
"sbinev" <sbinev@.discussions.microsoft.com> wrote in message
news:14AACC0D-9E5A-45E6-ABC1-15623DA32CD6@.microsoft.com...
> I'm provided with a .bak file that contains a backup of a small database.
My question is how do I restore it to the SQL Server. I used the import
feature but it gave me all kinds of errors when attempting to import it.
> Any ideas!
> thanks!
|||sbinev,
SQL Server Books Online (BOL) installs with SQL Server. I heartily
recommend you look here for how to restore a database. The documentation
is very clear and comprehensive and shows you many methods of
accomplishing this task.
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sbinev wrote:
> I'm provided with a .bak file that contains a backup of a small database. My question is how do I restore it to the SQL Server. I used the import feature but it gave me all kinds of errors when attempting to import it.
> Any ideas!
> thanks!
|||Thanks a lot guys!! )
The first method from the Enterprise Manager kept giving me errors, but I used T-SQL looked in the books online and with MOVE TO clause after the RESTORE... clause it worked. So thanks for your advice. It's hard to be novice!
"Hari Prasad" wrote:

> Hi,
> You can either use SQL server enterprise manager GUI to restore a database
> ot use the Query analyzer ... RESTORE database command.
> Enterprise manager
> 1. Open Enterprise manager -- connect to SQL server -- Right click above the
> database -- All tasks -- CLick Restore database
> 2. In the Restore database as "Give a Database name"
> 3. IN the restore options choose "From Device"
> 4. Click select devices command button -- CLick add and choose the backup
> file name (.BAK file)
> 5. Click OK
> 6. IN restore screen also CLICK OK
> -- This will restore the database
>
> From Query Analyzer:-
> 1. Login to Query Analyzer as 'SA' or user with sysadmin ort equalent server
> roles.
> 2. Execute the below command
> Restore database <dbname> from disk='d:\backup\dbname.bak' with stats=10
> -- Change the directory and file name based on ur requierement.
>
> --
> Thanks
> Hari
> MCDBA
> "sbinev" <sbinev@.discussions.microsoft.com> wrote in message
> news:14AACC0D-9E5A-45E6-ABC1-15623DA32CD6@.microsoft.com...
> My question is how do I restore it to the SQL Server. I used the import
> feature but it gave me all kinds of errors when attempting to import it.
>
>
sql

Problem restoring a database from a .bak file

I'm provided with a .bak file that contains a backup of a small database. My question is how do I restore it to the SQL Server. I used the import feature but it gave me all kinds of errors when attempting to import it.
Any ideas!
thanks!Hi,
You can either use SQL server enterprise manager GUI to restore a database
ot use the Query analyzer ... RESTORE database command.
Enterprise manager
1. Open Enterprise manager -- connect to SQL server -- Right click above the
database -- All tasks -- CLick Restore database
2. In the Restore database as "Give a Database name"
3. IN the restore options choose "From Device"
4. Click select devices command button -- CLick add and choose the backup
file name (.BAK file)
5. Click OK
6. IN restore screen also CLICK OK
-- This will restore the database
From Query Analyzer:-
1. Login to Query Analyzer as 'SA' or user with sysadmin ort equalent server
roles.
2. Execute the below command
Restore database <dbname> from disk='d:\backup\dbname.bak' with stats=10
-- Change the directory and file name based on ur requierement.
Thanks
Hari
MCDBA
"sbinev" <sbinev@.discussions.microsoft.com> wrote in message
news:14AACC0D-9E5A-45E6-ABC1-15623DA32CD6@.microsoft.com...
> I'm provided with a .bak file that contains a backup of a small database.
My question is how do I restore it to the SQL Server. I used the import
feature but it gave me all kinds of errors when attempting to import it.
> Any ideas!
> thanks!|||sbinev,
SQL Server Books Online (BOL) installs with SQL Server. I heartily
recommend you look here for how to restore a database. The documentation
is very clear and comprehensive and shows you many methods of
accomplishing this task.
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
sbinev wrote:
> I'm provided with a .bak file that contains a backup of a small database. My question is how do I restore it to the SQL Server. I used the import feature but it gave me all kinds of errors when attempting to import it.
> Any ideas!
> thanks!|||Thanks a lot guys!! :))
The first method from the Enterprise Manager kept giving me errors, but I used T-SQL looked in the books online and with MOVE TO clause after the RESTORE... clause it worked. So thanks for your advice. It's hard to be novice!
"Hari Prasad" wrote:
> Hi,
> You can either use SQL server enterprise manager GUI to restore a database
> ot use the Query analyzer ... RESTORE database command.
> Enterprise manager
> 1. Open Enterprise manager -- connect to SQL server -- Right click above the
> database -- All tasks -- CLick Restore database
> 2. In the Restore database as "Give a Database name"
> 3. IN the restore options choose "From Device"
> 4. Click select devices command button -- CLick add and choose the backup
> file name (.BAK file)
> 5. Click OK
> 6. IN restore screen also CLICK OK
> -- This will restore the database
>
> From Query Analyzer:-
> 1. Login to Query Analyzer as 'SA' or user with sysadmin ort equalent server
> roles.
> 2. Execute the below command
> Restore database <dbname> from disk='d:\backup\dbname.bak' with stats=10
> -- Change the directory and file name based on ur requierement.
>
> --
> Thanks
> Hari
> MCDBA
> "sbinev" <sbinev@.discussions.microsoft.com> wrote in message
> news:14AACC0D-9E5A-45E6-ABC1-15623DA32CD6@.microsoft.com...
> > I'm provided with a .bak file that contains a backup of a small database.
> My question is how do I restore it to the SQL Server. I used the import
> feature but it gave me all kinds of errors when attempting to import it.
> >
> > Any ideas!
> >
> > thanks!
>
>

Problem restoring a "*.bak" file

I have a Demo db in a MSDE instance called "Instance1"
I used the "RESTORE Database..." statement to back it up into a file called
"demo.bak"
I moved that file onto another machine which is MSDE with an instance
called "MyInstance" and I tried restoring with this line:
RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
It gives the error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I tried with both a newly created blank database called demo in the
destination server and without. It says the same thing.
I seriously don't understand anything of this Errormessage, what should I do
to be able to restore this "demo.bak" onto another machine?
I was looking a little bit into the WITH MOVE thing, but I could figure it
out.
(so you know.. I''m a .NET programmer and haven't messed around with these
backup/restore thingies before)
/LarsGet Books Online, read Restore database and with move option and use it to
set correct destination of files - path to files is kept in the backup but
on this computer there are no such folder.
Bojidar Alexandrov
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>|||By default SQL Server will try to restore to the same location. If the path
does not exist then it will fail. Use the MOVE option to move the files.
Something like this will do it.
RESTORE DATABASE XXXXX
FROM DISK = 'C:\....'
WITH MOVE 'demo_dat' TO 'C:\newlocation',
MOVE 'demo_log' TO 'C:\newlocation'
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>|||Lars,
Yes, you need to specify the MOVE parameter for each logical filename to spe
cify where these files should be
created. Use RESTORE HEADERONLY and then RESTORE FILELISTONLY to examine the
backup file, including getting
the logical file names for each database files. Then use the MOVE option to
specify where these files are to
be created. And don't create the destination database first, it will be crea
ted for you when you do the
RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file calle
d
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsof
t
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>|||Thank you! It works now with those "With MOVE, MOVE". thankx!
/Lars
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> skrev i meddelandet
news:uUaxok0MEHA.2628@.TK2MSFTNGP12.phx.gbl...
> By default SQL Server will try to restore to the same location. If the
path
> does not exist then it will fail. Use the MOVE option to move the files.
> Something like this will do it.
> RESTORE DATABASE XXXXX
> FROM DISK = 'C:\....'
> WITH MOVE 'demo_dat' TO 'C:\newlocation',
> MOVE 'demo_log' TO 'C:\newlocation'
> --
> Barry McAuslin
> Look inside your SQL Server files with SQL File Explorer.
> Go to http://www.sqlfe.com for more information.
> "Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
> news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> called
> Files\Microsoft
> Files\Microsoft
I[vbcol=seagreen]
> do
it[vbcol=seagreen]
these[vbcol=seagreen]
>

Problem restoring a "*.bak" file

I have a Demo db in a MSDE instance called "Instance1"
I used the "RESTORE Database..." statement to back it up into a file called
"demo.bak"
I moved that file onto another machine which is MSDE with an instance
called "MyInstance" and I tried restoring with this line:
RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
It gives the error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I tried with both a newly created blank database called demo in the
destination server and without. It says the same thing.
I seriously don't understand anything of this Errormessage, what should I do
to be able to restore this "demo.bak" onto another machine?
I was looking a little bit into the WITH MOVE thing, but I could figure it
out.
(so you know.. I''m a .NET programmer and haven't messed around with these
backup/restore thingies before)
/Lars
Get Books Online, read Restore database and with move option and use it to
set correct destination of files - path to files is kept in the backup but
on this computer there are no such folder.
Bojidar Alexandrov
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>
|||By default SQL Server will try to restore to the same location. If the path
does not exist then it will fail. Use the MOVE option to move the files.
Something like this will do it.
RESTORE DATABASE XXXXX
FROM DISK = 'C:\....'
WITH MOVE 'demo_dat' TO 'C:\newlocation',
MOVE 'demo_log' TO 'C:\newlocation'
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>
|||Lars,
Yes, you need to specify the MOVE parameter for each logical filename to specify where these files should be
created. Use RESTORE HEADERONLY and then RESTORE FILELISTONLY to examine the backup file, including getting
the logical file names for each database files. Then use the MOVE option to specify where these files are to
be created. And don't create the destination database first, it will be created for you when you do the
RESTORE.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>
|||Thank you! It works now with those "With MOVE, MOVE". thankx!
/Lars
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> skrev i meddelandet
news:uUaxok0MEHA.2628@.TK2MSFTNGP12.phx.gbl...
> By default SQL Server will try to restore to the same location. If the
path[vbcol=seagreen]
> does not exist then it will fail. Use the MOVE option to move the files.
> Something like this will do it.
> RESTORE DATABASE XXXXX
> FROM DISK = 'C:\....'
> WITH MOVE 'demo_dat' TO 'C:\newlocation',
> MOVE 'demo_log' TO 'C:\newlocation'
> --
> Barry McAuslin
> Look inside your SQL Server files with SQL File Explorer.
> Go to http://www.sqlfe.com for more information.
> "Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
> news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> called
> Files\Microsoft
> Files\Microsoft
I[vbcol=seagreen]
> do
it[vbcol=seagreen]
these
>

Problem restoring a "*.bak" file

I have a Demo db in a MSDE instance called "Instance1"
I used the "RESTORE Database..." statement to back it up into a file called
"demo.bak"
I moved that file onto another machine which is MSDE with an instance
called "MyInstance" and I tried restoring with this line:
RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
It gives the error:
Server: Msg 5105, Level 16, State 2, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 5105, Level 16, State 1, Line 1
Device activation error. The physical file name 'C:\Program Files\Microsoft
SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
Server: Msg 3156, Level 16, State 1, Line 1
File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
location for the file.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
I tried with both a newly created blank database called demo in the
destination server and without. It says the same thing.
I seriously don't understand anything of this Errormessage, what should I do
to be able to restore this "demo.bak" onto another machine?
I was looking a little bit into the WITH MOVE thing, but I could figure it
out.
(so you know.. I''m a .NET programmer and haven't messed around with these
backup/restore thingies before)
/LarsGet Books Online, read Restore database and with move option and use it to
set correct destination of files - path to files is kept in the backup but
on this computer there are no such folder.
Bojidar Alexandrov
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>|||By default SQL Server will try to restore to the same location. If the path
does not exist then it will fail. Use the MOVE option to move the files.
Something like this will do it.
RESTORE DATABASE XXXXX
FROM DISK = 'C:\....'
WITH MOVE 'demo_dat' TO 'C:\newlocation',
MOVE 'demo_log' TO 'C:\newlocation'
--
Barry McAuslin
Look inside your SQL Server files with SQL File Explorer.
Go to http://www.sqlfe.com for more information.
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file
called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program
Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I
do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>|||Lars,
Yes, you need to specify the MOVE parameter for each logical filename to specify where these files should be
created. Use RESTORE HEADERONLY and then RESTORE FILELISTONLY to examine the backup file, including getting
the logical file names for each database files. Then use the MOVE option to specify where these files are to
be created. And don't create the destination database first, it will be created for you when you do the
RESTORE.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> I have a Demo db in a MSDE instance called "Instance1"
> I used the "RESTORE Database..." statement to back it up into a file called
> "demo.bak"
> I moved that file onto another machine which is MSDE with an instance
> called "MyInstance" and I tried restoring with this line:
> RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> It gives the error:
> Server: Msg 5105, Level 16, State 2, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 5105, Level 16, State 1, Line 1
> Device activation error. The physical file name 'C:\Program Files\Microsoft
> SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> Server: Msg 3156, Level 16, State 1, Line 1
> File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> location for the file.
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> I tried with both a newly created blank database called demo in the
> destination server and without. It says the same thing.
> I seriously don't understand anything of this Errormessage, what should I do
> to be able to restore this "demo.bak" onto another machine?
> I was looking a little bit into the WITH MOVE thing, but I could figure it
> out.
> (so you know.. I''m a .NET programmer and haven't messed around with these
> backup/restore thingies before)
> /Lars
>|||Thank you! It works now with those "With MOVE, MOVE". thankx!
/Lars
"Barry McAuslin" <barry_mcauslin@.yahoo.com.nospam> skrev i meddelandet
news:uUaxok0MEHA.2628@.TK2MSFTNGP12.phx.gbl...
> By default SQL Server will try to restore to the same location. If the
path
> does not exist then it will fail. Use the MOVE option to move the files.
> Something like this will do it.
> RESTORE DATABASE XXXXX
> FROM DISK = 'C:\....'
> WITH MOVE 'demo_dat' TO 'C:\newlocation',
> MOVE 'demo_log' TO 'C:\newlocation'
> --
> Barry McAuslin
> Look inside your SQL Server files with SQL File Explorer.
> Go to http://www.sqlfe.com for more information.
> "Lars Netzel" <[no_spam_please]lars.netzel@.qlogic.se> wrote in message
> news:%23mlLbe0MEHA.3016@.tk2msftngp13.phx.gbl...
> > I have a Demo db in a MSDE instance called "Instance1"
> >
> > I used the "RESTORE Database..." statement to back it up into a file
> called
> > "demo.bak"
> >
> > I moved that file onto another machine which is MSDE with an instance
> > called "MyInstance" and I tried restoring with this line:
> >
> > RESTORE DATABASE demo FROM DISK ='C:\demo.bak'
> >
> > It gives the error:
> >
> > Server: Msg 5105, Level 16, State 2, Line 1
> > Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> > SQL Server\MSSQL$Instance1\Data\demo.mdf' may be incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 1
> > File 'demo_dat' cannot be restored to 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$Instance1\Data\demo.mdf'. Use WITH MOVE to identify a valid
> > location for the file.
> > Server: Msg 5105, Level 16, State 1, Line 1
> > Device activation error. The physical file name 'C:\Program
> Files\Microsoft
> > SQL Server\MSSQL$Instance1\Data\demo.ldf' may be incorrect.
> > Server: Msg 3156, Level 16, State 1, Line 1
> > File 'demo_log' cannot be restored to 'C:\Program Files\Microsoft SQL
> > Server\MSSQL$Instance1\Data\demo.ldf'. Use WITH MOVE to identify a valid
> > location for the file.
> > Server: Msg 3013, Level 16, State 1, Line 1
> > RESTORE DATABASE is terminating abnormally.
> >
> > I tried with both a newly created blank database called demo in the
> > destination server and without. It says the same thing.
> >
> > I seriously don't understand anything of this Errormessage, what should
I
> do
> > to be able to restore this "demo.bak" onto another machine?
> >
> > I was looking a little bit into the WITH MOVE thing, but I could figure
it
> > out.
> >
> > (so you know.. I''m a .NET programmer and haven't messed around with
these
> > backup/restore thingies before)
> >
> > /Lars
> >
> >
>sql