MS SQL Server - restoring 2000 backup to 2005

Author

restoring 2000 backup to 2005

luna

2006-02-09, 7:23 am

trying to import 2000 bak file into 2005, and im getting this error, any
ideas ? - seems a pain getting my old data into the 200 server!

Restore failed for Server '********'. (Microsoft.SqlServer.Smo)

------------------------------
For help, click:
http://go.microsoft.com/fwlink? Pro...> &LinkId=20476

------------------------------
Program Location:

at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
at
Microsoft.SqlServer.Management.SqlManagerUI. SqlRestoreDatabaseOp
tions.RunRestore()

====================
===============

System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
Files\Microsoft SQL Server\MSSQL\data\Bu
bble_Data.MDF" failed with the
operating system error 3(error not found). (Microsoft.SqlServer.Smo)

------------------------------
For help, click:
http://go.microsoft.com/fwlink? Pro...00&LinkId=20476

------------------------------
Program Location:

at
Microsoft.SqlServer.Management.Smo.ExecutionManager. ExecuteNonQueryWithM
essage(StringCollect
ion
queries, ServerMessageEventHa
ndler dbccMessageHandler, Boolean
errorsAsMessages)
at Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server
server, StringCollection queries)
at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)

luna

2006-02-09, 7:23 am

nm i sussed it

for some reason it defaults to C:\Program Files\Microsoft SQL
Server\MSSQL\data\Bu
bble_Data.MDF

when in fact it should be C:\Program Files\Microsoft SQL
Server\MSSQL.1\data\Bubble_Data.MDF

any reason for the mssql.1 folder ?

Uri Dimant

2006-02-09, 7:23 am

Luna
Do you try to restore database by using SMO objects library?

Why not just running RESTORE command from Query Builder?

"luna" <luna@themoon.com> wrote in message
news:F2GGf.56507$0N1.24818@newsfe5-win.ntli.net...
> trying to import 2000 bak file into 2005, and im getting this error, any
> ideas ? - seems a pain getting my old data into the 200 server!
>
> Restore failed for Server '********'. (Microsoft.SqlServer.Smo)
>
> ------------------------------
> For help, click:
> http://go.microsoft.com/fwlink? Pro...> &LinkId=20476
>
> ------------------------------
> Program Location:
>
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
> at
> Microsoft.SqlServer.Management.SqlManagerUI. SqlRestoreDatabaseOp
tions.RunRestore()
>
> ====================
===============
>
> System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
> Files\Microsoft SQL Server\MSSQL\data\Bu
bble_Data.MDF" failed with the
> operating system error 3(error not found). (Microsoft.SqlServer.Smo)
>
> ------------------------------
> For help, click:
> http://go.microsoft.com/fwlink? Pro...00&LinkId=20476
>
> ------------------------------
> Program Location:
>
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManager. ExecuteNonQueryWithM
essage(StringCollect
ion
> queries, ServerMessageEventHa
ndler dbccMessageHandler, Boolean
> errorsAsMessages)
> at
> Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server
> server, StringCollection queries)
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
>

luna

2006-02-09, 7:23 am

"Uri Dimant" <urid@iscar.co.il> wrote in message
news:evqpNOXLGHA.2604@TK2MSFTNGP09.phx.gbl...
> Luna
> Do you try to restore database by using SMO objects library?
>
> Why not just running RESTORE command from Query Builder?
>
i managed to sort it, see my 10 mins later post!,
still cant understand why its under mssql.1 folder
but at least its works !!

thanks for the reply

Dan Guzman

2006-02-09, 7:23 am

From the command prompt, 'NET HELPMSG 3' returns 'The system cannot find the
path specified.'. It looks like the backup file path is not correct. Note
that the file location needs to be specified relative to the server (i.e.
local server drive or UNC path). You might also try a restore command from
a query window. For example:

RESTORE DATABASE MyDatabase
FROM DISK='C:\Backups\MyD
atabase.bak'

--
Hope this helps.

Dan Guzman
SQL Server MVP

"luna" <luna@themoon.com> wrote in message
news:F2GGf.56507$0N1.24818@newsfe5-win.ntli.net...
> trying to import 2000 bak file into 2005, and im getting this error, any
> ideas ? - seems a pain getting my old data into the 200 server!
>
> Restore failed for Server '********'. (Microsoft.SqlServer.Smo)
>
> ------------------------------
> For help, click:
> http://go.microsoft.com/fwlink? Pro...> &LinkId=20476
>
> ------------------------------
> Program Location:
>
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
> at
> Microsoft.SqlServer.Management.SqlManagerUI. SqlRestoreDatabaseOp
tions.RunRestore()
>
> ====================
===============
>
> System.Data.SqlClient.SqlError: Directory lookup for the file "C:\Program
> Files\Microsoft SQL Server\MSSQL\data\Bu
bble_Data.MDF" failed with the
> operating system error 3(error not found). (Microsoft.SqlServer.Smo)
>
> ------------------------------
> For help, click:
> http://go.microsoft.com/fwlink? Pro...00&LinkId=20476
>
> ------------------------------
> Program Location:
>
> at
> Microsoft.SqlServer.Management.Smo.ExecutionManager. ExecuteNonQueryWithM
essage(StringCollect
ion
> queries, ServerMessageEventHa
ndler dbccMessageHandler, Boolean
> errorsAsMessages)
> at
> Microsoft.SqlServer.Management.Smo.BackupRestoreBase.ExecuteSql(Server
> server, StringCollection queries)
> at Microsoft.SqlServer.Management.Smo.Restore.SqlRestore(Server srv)
>

luna

2006-02-09, 9:23 am

"Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
news:edEZaSXLGHA.3060@TK2MSFTNGP10.phx.gbl...
> From the command prompt, 'NET HELPMSG 3' returns 'The system cannot find
> the path specified.'. It looks like the backup file path is not correct.
> Note that the file location needs to be specified relative to the server
> (i.e. local server drive or UNC path). You might also try a restore
> command from a query window. For example:
>
> RESTORE DATABASE MyDatabase
> FROM DISK='C:\Backups\MyD
atabase.bak'
>
> --
> Hope this helps.
>
> Dan Guzman
> SQL Server MVP
yeah, i found that out, as said on my previous post, restore defaults to

C:\Program Files\Microsoft SQL Server\MSSQL\data\Bu
bble_Data.MDF

whereas it was supposed to be

C:\Program Files\Microsoft SQL Server\MSSQL.1\data\Bubble_Data.MDF

any ideas as to why i have mssql.1 ?

thanks

mark

Andrew J. Kelly

2006-02-09, 11:23 am

This is how SQL Server 2005 names the folders now. If you add another
instance or another service such as reporting services or FTS it will be
named mssql.2.

--
Andrew J. Kelly SQL MVP

"luna" <luna@themoon.com> wrote in message
news:7zHGf.38038$mf2.34659@newsfe6-win.ntli.net...
>
> "Dan Guzman" <guzmanda@nospam-online.sbcglobal.net> wrote in message
> news:edEZaSXLGHA.3060@TK2MSFTNGP10.phx.gbl...
> yeah, i found that out, as said on my previous post, restore defaults to
>
> C:\Program Files\Microsoft SQL Server\MSSQL\data\Bu
bble_Data.MDF
>
>
> whereas it was supposed to be
>
> C:\Program Files\Microsoft SQL Server\MSSQL.1\data\Bubble_Data.MDF
>
> any ideas as to why i have mssql.1 ?
>
> thanks
>
> mark
>
>

Randy

2006-02-10, 8:23 pm

Further, that sequence number gets incremented for each Reporting
Services and Analysis Services instance. So, for a full install on a
single instance, you can end up with MSSQL.1, MSSQL.2, and MSSQL.3.
Thus, a second instance would actually install starting wtih MSSQL.4.

Sponsored Links