Hi there,
I am having a problem with Microsoft SQL Server 2000, a copy of one of
my backups was corrupt, which I only discovered on the server I wanted
to res. Running the RESTORE WITH VERIFYONLY gave me the standard error
about there being an issue with the media set/family, which I could
accept.
However when I then opened query analyser against the original server
and ran the verifyonly restore command (and I have done it twice with
different results).
Myself and a colleague ran this against the original server and
received very odd messages. They received a weird error like this:
Command Issued:
RESTORE WITH ONLY Disk='C:\abc.BAK'
And received a error back saying that it could not find the file 'X:
\db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
db files are kept!!!)
Later on, I ran the same command however, this time I received an
error telling me that the device was an invalid tape object! I was
running the same command, with the DISK='<drive>:\XYZ.BAK'
Has anyone ever seen something like this?
Obviously the error numbers would be useful here, but I don't have
these on me at the moment.
Any help appreciated.On Jan 29, 9:32 pm, Marc <mvandenhee...@.gmail.com> wrote:
> Hi there,
> I am having a problem with Microsoft SQL Server 2000, a copy of one of
> my backups was corrupt, which I only discovered on the server I wanted
> to res. Running the RESTORE WITH VERIFYONLY gave me the standard error
> about there being an issue with the media set/family, which I could
> accept.
> However when I then opened query analyser against the original server
> and ran the verifyonly restore command (and I have done it twice with
> different results).
> Myself and a colleague ran this against the original server and
> received very odd messages. They received a weird error like this:
> Command Issued:
> RESTORE WITH ONLY Disk='C:\abc.BAK'
> And received a error back saying that it could not find the file 'X:
> \db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
> db files are kept!!!)
> Later on, I ran the same command however, this time I received an
> error telling me that the device was an invalid tape object! I was
> running the same command, with the DISK='<drive>:\XYZ.BAK'
> Has anyone ever seen something like this?
> Obviously the error numbers would be useful here, but I don't have
> these on me at the moment.
> Any help appreciated.
Also I should of added that SP 4 has been applied on this server...|||Mark
What is it ? T_SQL?
RESTORE WITH ONLY Disk='C:\abc.BAK'
Perhaps you meant
RESTORE VERIFYONLY FROM DISK = 'C:\abc.BAK'
"Marc" <mvandenheever@.gmail.com> wrote in message
news:12a10458-6e70-4817-b7a4-edf360167106@.d4g2000prg.googlegroups.com...
> Hi there,
> I am having a problem with Microsoft SQL Server 2000, a copy of one of
> my backups was corrupt, which I only discovered on the server I wanted
> to res. Running the RESTORE WITH VERIFYONLY gave me the standard error
> about there being an issue with the media set/family, which I could
> accept.
> However when I then opened query analyser against the original server
> and ran the verifyonly restore command (and I have done it twice with
> different results).
> Myself and a colleague ran this against the original server and
> received very odd messages. They received a weird error like this:
> Command Issued:
>
> RESTORE WITH ONLY Disk='C:\abc.BAK'
> And received a error back saying that it could not find the file 'X:
> \db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
> db files are kept!!!)
> Later on, I ran the same command however, this time I received an
> error telling me that the device was an invalid tape object! I was
> running the same command, with the DISK='<drive>:\XYZ.BAK'
> Has anyone ever seen something like this?
> Obviously the error numbers would be useful here, but I don't have
> these on me at the moment.
> Any help appreciated.
>
>|||On Jan 29, 9:43 pm, "Uri Dimant" <u...@.iscar.co.il> wrote:
> Mark
> What is it ? T_SQL?RESTOREWITH ONLY Disk='C:\abc.BAK'
> Perhaps you meantRESTOREVERIFYONLYFROM DISK = 'C:\abc.BAK'
> "Marc" <mvandenhee...@.gmail.com> wrote in message
> news:12a10458-6e70-4817-b7a4-edf360167106@.d4g2000prg.googlegroups.com...
> > Hi there,
> > I am having a problem with Microsoft SQL Server 2000, a copy of one of
> > my backups was corrupt, which I only discovered on the server I wanted
> > to res. Running theRESTOREWITHVERIFYONLYgave me the standard error
> > about there being an issue with the media set/family, which I could
> > accept.
> > However when I then opened query analyser against the original server
> > and ran theverifyonlyrestorecommand (and I have done it twice with
> > different results).
> > Myself and a colleague ran this against the original server and
> > received very odd messages. They received a weird error like this:
> > Command Issued:
> >RESTOREWITH ONLY Disk='C:\abc.BAK'
> > And received a error back saying that it could not find the file 'X:
> > \db_files\C:\abc.BAK' (where X:\db_files is the actual path where the
> > db files are kept!!!)
> > Later on, I ran the same command however, this time I received an
> > error telling me that the device was an invalid tape object! I was
> > running the same command, with the DISK='<drive>:\XYZ.BAK'
> > Has anyone ever seen something like this?
> > Obviously the error numbers would be useful here, but I don't have
> > these on me at the moment.
> > Any help appreciated.
Yes sorry that is simply a typo...
Showing posts with label copy. Show all posts
Showing posts with label copy. Show all posts
Friday, March 30, 2012
Monday, March 26, 2012
restore to another server of db with FT catalogs
If we restore a backup copy of a production db having FT catalogs onto a
laptop running SQL Server Developer version, the restored db will contain
references to FT catalogs whose underlying system files are not yet present
on the laptop (because backup doesn't grab the FT system files). The index
rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
move the FT system files. In that scenario, is it safe for the db on the
laptop to deactivate and drop the FT catalogs? Or can the missing system
files cause SQL Server to become unstable during the deactivation/drop
process? I'm thinking it shouldn't cause a problem, because if it did,
there'd be no way to drop a catalog whose system files had gotten corrupted.
But I lack the mental fortitude to experiment after the 7342 error consumed
most of my weekend, and am hoping you FTS gurus could handle this scenario
with your eyes closed and one arm tied behind your back. :-)
Thanks
Timo
consult this kb article for more information.
http://support.microsoft.com/default...b;en-us;240867
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
> present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
> corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
> consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
|||Timo,
Yes, it is best to re-create the small FT Catalogs via normal procedures on
your laptop. However, successfully restoring a FT-enabled SQL Server 2000
database and then being able to re-create the lost FT Catalog depends upon
several factors - are the disk drives (drive letter & path) exactly the same
on the source server as the destination server? Are you restoring over an
existing database or are you restoring the database as a new database on the
laptop? The first factor will cause you problems (that can be overcome), the
second factor should work successfully. Overall, for SQL Server 2000 when
backing up FT-enabled databases, its best to disenable Full Text and then
backup the database...
You can also use the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867 to help you
as well understand these issues.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
|||FYI, SQL 2K5 backups (as with sp_attach/detach_db) will contain FT indexes
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
laptop running SQL Server Developer version, the restored db will contain
references to FT catalogs whose underlying system files are not yet present
on the laptop (because backup doesn't grab the FT system files). The index
rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
move the FT system files. In that scenario, is it safe for the db on the
laptop to deactivate and drop the FT catalogs? Or can the missing system
files cause SQL Server to become unstable during the deactivation/drop
process? I'm thinking it shouldn't cause a problem, because if it did,
there'd be no way to drop a catalog whose system files had gotten corrupted.
But I lack the mental fortitude to experiment after the 7342 error consumed
most of my weekend, and am hoping you FTS gurus could handle this scenario
with your eyes closed and one arm tied behind your back. :-)
Thanks
Timo
consult this kb article for more information.
http://support.microsoft.com/default...b;en-us;240867
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
> present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
> corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
> consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
|||Timo,
Yes, it is best to re-create the small FT Catalogs via normal procedures on
your laptop. However, successfully restoring a FT-enabled SQL Server 2000
database and then being able to re-create the lost FT Catalog depends upon
several factors - are the disk drives (drive letter & path) exactly the same
on the source server as the destination server? Are you restoring over an
existing database or are you restoring the database as a new database on the
laptop? The first factor will cause you problems (that can be overcome), the
second factor should work successfully. Overall, for SQL Server 2000 when
backing up FT-enabled databases, its best to disenable Full Text and then
backup the database...
You can also use the procedures in KB article: 240867 (Q240867) "INF: How to
Move, Copy, and Backup Full-Text Catalog Folders and Files" at
http://support.microsoft.com/default...b;EN-US;240867 to help you
as well understand these issues.
Hope that helps!
John
SQL Full Text Search Blog
http://spaces.msn.com/members/jtkane/
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
|||FYI, SQL 2K5 backups (as with sp_attach/detach_db) will contain FT indexes
"Timo" <timo@.noneofyer.biz> wrote in message
news:eglQLxBGFHA.1188@.tk2msftngp13.phx.gbl...
> If we restore a backup copy of a production db having FT catalogs onto a
> laptop running SQL Server Developer version, the restored db will contain
> references to FT catalogs whose underlying system files are not yet
present
> on the laptop (because backup doesn't grab the FT system files). The index
> rebuild takes only 20 minutes, so I'd prefer to recreate them rather than
> move the FT system files. In that scenario, is it safe for the db on the
> laptop to deactivate and drop the FT catalogs? Or can the missing system
> files cause SQL Server to become unstable during the deactivation/drop
> process? I'm thinking it shouldn't cause a problem, because if it did,
> there'd be no way to drop a catalog whose system files had gotten
corrupted.
> But I lack the mental fortitude to experiment after the 7342 error
consumed
> most of my weekend, and am hoping you FTS gurus could handle this scenario
> with your eyes closed and one arm tied behind your back. :-)
> Thanks
> Timo
>
Wednesday, March 21, 2012
Restore Sql Express (2005) to Sql Server 2000
Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.
|||
Doesn't work.
When I launch the restore, I get the following error dialog:
Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK
Only got one backup device selected. Is this possible or not?
I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.
TIA,
Rick
No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps
(a) Create Script of Database Objects from 2005
(c) Create new database in sql server 2000
(d) run the objects script in 2000
(e) use DTS to transfer data from 2005 to 2000
Madhu
|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?|||
Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.
Madhu
|||Madju,Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).
Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:
Cannot perform requested task because full-text memory manager is not initialized
Not sure what that pertains to.
(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?
(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.
|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.
This method worked much smoother. I also had to run the DTS on the server itself for it to work.
Restore Sql Express (2005) to Sql Server 2000
Well, my understanding is this should be possible and easy -- use a backup created by Sql Express (via Management Studio Express), copy the backup to the Sql 2000 server, select Database Restore through EM, specify Restore from Device, point to the Sql Express generated backup file as the *only* device, and launch.
|||
Doesn't work.
When I launch the restore, I get the following error dialog:
Too many backup devices for backup or restore. Only 64 are allowed. <yada-yada> .... OK
Only got one backup device selected. Is this possible or not?
I have resorted to sql script generation with data in the past, but that's taxes the server resources heavily.
TIA,
Rick
No ... its not possible.. you can't restore SQL Server 2005 backup in SQL Server 2000. You may follow these steps
(a) Create Script of Database Objects from 2005
(c) Create new database in sql server 2000
(d) run the objects script in 2000
(e) use DTS to transfer data from 2005 to 2000
Madhu
|||Thanks. I assume you have to use DTS provided with full blown Sql Server 2005 (which ain't provided with SqlExpress)?|||
Yes , SSIS(DTS of 2005) is not provided with Express edition. you can use sql server 2000 DTS and pull data from 2005 to 2000.
Madhu
|||Madju,Okay ... so I've created the database objects in Sql Server 2000, created a System DSN to connect to the Sql Express database (different server).
Next, when I use 2000's DTS and specify SQL Native Client I am able to connect successfully to the Sql Express database. However, after only several seconds into the transer, I get the follow error from DTS:
Cannot perform requested task because full-text memory manager is not initialized
Not sure what that pertains to.
(later)
Okay, I've found it. It's the MSSearch service on the server that it needs running. I've started the service, stopped and restarted MS Sql Server serice, dependent services and EA says full-text support is running now. Guess what? DTS still yields the same error message! Any other idea, anyone? I'm running DTS from my workstation. I shouldn't have to run it from the server, should I?
(later)
For whatever reason, have to run DTS from the Sql Server 2000 server and not a client workstation. Done deal now.
|||Thanks. This was really helpful. I tried a similar approach by exporting the table defs and data from SQL Express 2005 using the data publishing wizard, but i ran into an error recreating the database on the SQL Server 2000 due to the limit of 64k for command lengths.
This method worked much smoother. I also had to run the DTS on the server itself for it to work.
Restore SQL database from a network drive
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
You must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
You must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
..
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan
|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
Restore SQL database from a network drive
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'
.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I u
se
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'
.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I u
se
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>
Restore SQL database from a network drive
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>sql
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel ChanYou must:
1) Use the UNC name
2) Have SQL Server running under a domain account - not Local System
3) Have read permission on the share for the domain account in #1 above
--
Tom
----
Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
SQL Server MVP
Columnist, SQL Server Professional
Toronto, ON Canada
www.pinnaclepublishing.com
.
"Abel Chan" <awong@.newsgroup.nospam> wrote in message
news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
Hi there,
I have a copy of a database on a network drive.
How could restore this database into my local test box without copy the
database down first?
I tried to use UNC and map a drive using net use but none of these work.
I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
Device error or device off-line. See the SQL Server error log for more
details. RESTORE DATABASE is terminating abnormally.'
I go to the SQL server Error Log and it said 'The system cannot find the
path specified (if I use the map drive syntax) or Access is denied (if I use
the UNC syntax like \\server\drive\dir\my backup.bak)
Any idea of how to do this?
Thanks.
Abel Chan|||Tom,
Your solution works!!!
Thanks so mcuh Tom.
Abel
"Tom Moreau" wrote:
> You must:
> 1) Use the UNC name
> 2) Have SQL Server running under a domain account - not Local System
> 3) Have read permission on the share for the domain account in #1 above
> --
> Tom
> ----
> Thomas A. Moreau, BSc, PhD, MCSE, MCDBA
> SQL Server MVP
> Columnist, SQL Server Professional
> Toronto, ON Canada
> www.pinnaclepublishing.com
> ..
> "Abel Chan" <awong@.newsgroup.nospam> wrote in message
> news:136DAF3F-B192-41C4-A819-D6BEC9CE1449@.microsoft.com...
> Hi there,
> I have a copy of a database on a network drive.
> How could restore this database into my local test box without copy the
> database down first?
> I tried to use UNC and map a drive using net use but none of these work.
> I always get the 'Cannot open backup device 'Z:\mapdrive\databasename.bak'.
> Device error or device off-line. See the SQL Server error log for more
> details. RESTORE DATABASE is terminating abnormally.'
> I go to the SQL server Error Log and it said 'The system cannot find the
> path specified (if I use the map drive syntax) or Access is denied (if I use
> the UNC syntax like \\server\drive\dir\my backup.bak)
> Any idea of how to do this?
> Thanks.
> Abel Chan
>sql
Tuesday, March 20, 2012
restore question
I use sql2000/sp3 in w2k/sp3,if i can
restore a database from a net device direct(not copy the
bak file to local disk)?I think you want to do something like this
RESTORE DATABASE FROM DISK = '\\ServerName\ShareName\FileName'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"peng" <zhoupeng1971@.yahoo.com> wrote in message
news:022d01c35cbd$68fde570$a001280a@.phx.gbl...
> I use sql2000/sp3 in w2k/sp3,if i can
> restore a database from a net device direct(not copy the
> bak file to local disk)?|||Hi Peng,
Make sure that the SQL Server Startup service account has the permission to
access the network for this to work.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
restore a database from a net device direct(not copy the
bak file to local disk)?I think you want to do something like this
RESTORE DATABASE FROM DISK = '\\ServerName\ShareName\FileName'
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"peng" <zhoupeng1971@.yahoo.com> wrote in message
news:022d01c35cbd$68fde570$a001280a@.phx.gbl...
> I use sql2000/sp3 in w2k/sp3,if i can
> restore a database from a net device direct(not copy the
> bak file to local disk)?|||Hi Peng,
Make sure that the SQL Server Startup service account has the permission to
access the network for this to work.
Sincerely,
Yih-Yoon Lee [Microsoft]
Microsoft SQL Server Support
This posting is provided "AS IS" with no warranties, and confers no rights.
Subscribe to MSDN & use http://msdn.microsoft.com/newsgroups.
Restore puts copy of user tables in master
Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
Can you show us the script?
Monday, March 12, 2012
Restore puts copy of user tables in master
Environment is SQL Server 2000 64 bit.
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
I restore from a script 'my' database, this works fine. However, all
the tables are also found in master, no data though.
Anyone experienced this?"Emille378" <dishonty@.seidata.com> wrote in message
news:d25b2692.0405050650.854b85c@.posting.google.co m...
> Environment is SQL Server 2000 64 bit.
> I restore from a script 'my' database, this works fine. However, all
> the tables are also found in master, no data though.
> Anyone experienced this?
Can you show us the script?|||"Greg D. Moore \(Strider\)" <mooregr_deleteth1s@.greenms.com> wrote in message news:<%fgmc.160405$M3.149305@.twister.nyroc.rr.com>...
> "Emille378" <dishonty@.seidata.com> wrote in message
> news:d25b2692.0405050650.854b85c@.posting.google.co m...
> > Environment is SQL Server 2000 64 bit.
> > I restore from a script 'my' database, this works fine. However, all
> > the tables are also found in master, no data though.
> > Anyone experienced this?
> Can you show us the script?
RESTORE DATABASE XX
FROM DISK = 'g:\XX_db.BAK'
WITH STATS = 10, REPLACE,
MOVE 'XX_data' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Data.mdf',
MOVE 'XX_log' TO 'g:\logs\XX_Log.ldf',
MOVE 'XX_Indx' TO 'h:\Program Files\Microsoft SQL
Server\MSSQL\Data\XX_Indx_Data.NDF'
Restore Problem
I need to copy a database FSPROD to FSSYS on a different SQL server. In
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
Friday, March 9, 2012
Restore Problem
I need to copy a database FSPROD to FSSYS on a different SQL server. In
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
enterprise manager, I hightlighted the FSPROD and performed a complete
backup and named it FSPROD.bak. After the backup completed, I copied that
file to the server it needs to be restored on.
I didn't have a FSSYS database, but I thought SQL would do it on its own,
so when I did this:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: Could not find database ID 65535. Database may not be
activated yet or may be in transition.
I then created a FSSYS database, and went to the Query Analyzer:
RESTORE DATABASE FSSYS
FROM Disk='F:\FSPROD.bak'
WITH
MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
REPLACE
I got this message: The backup set holds a backup of a database other than
the existing 'FSSYS' database.
I then tried created the FSPROD on the second server to use the same name,
as I figured I could ALTER the name later, but I still get: The backup set
holds a backup of a database other than the existing 'FSPROD' database.
If the DB name is the same, why isn't it letting me restore? Is it because
I used the wizard to perform the backup?
Thanks,
RockyWhat happens if you try it the first way (without the database existing)
without the REPLACE statement?
Christian Smith
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||I think you made it comnplicated. Let's say you already copied the backup
file to the root of drive F: on the new server. Frist you need to know the
logical file names of FSPROD.
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
it returns logical files name of data file and log file of FSPROD. Use
them for restore command:
RESTORE DATABASE FSSYS FROM DISK = 'F:\FSPROD.bak'
WITH MOVE 'logical file name of data file of FSPROD' TO
'F:\Data\TDSFSSYS_data.mdf',
MOVE 'logical file name of log file of FSPROD' TO
'F:\Log\TDSFSSYS_log.ldf'
You don't need to create the DB first.
hth,
"Rocky" <me@.me.co> wrote in message
news:Xns948B7C1D62EB3memeco@.207.46.248.16...
> I need to copy a database FSPROD to FSSYS on a different SQL server. In
> enterprise manager, I hightlighted the FSPROD and performed a complete
> backup and named it FSPROD.bak. After the backup completed, I copied that
> file to the server it needs to be restored on.
> I didn't have a FSSYS database, but I thought SQL would do it on its own,
> so when I did this:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: Could not find database ID 65535. Database may not be
> activated yet or may be in transition.
> I then created a FSSYS database, and went to the Query Analyzer:
> RESTORE DATABASE FSSYS
> FROM Disk='F:\FSPROD.bak'
> WITH
> MOVE 'TDSFSSYSdata' TO 'F:\Data\TDSFSSYS_data.mdf',
> MOVE 'TDSFSSYSlog' TO 'F:\Logs\TDSFSSYS_log.ldf',
> REPLACE
> I got this message: The backup set holds a backup of a database other than
> the existing 'FSSYS' database.
> I then tried created the FSPROD on the second server to use the same name,
> as I figured I could ALTER the name later, but I still get: The backup set
> holds a backup of a database other than the existing 'FSPROD' database.
> If the DB name is the same, why isn't it letting me restore? Is it
because
> I used the wizard to perform the backup?
> Thanks,
> Rocky|||> What happens if you try it the first way (without the database
> existing) without the REPLACE statement?
Could not find database ID 65535. Database may not be activated yet or may
be in transition.
I'm re-building a backup from a Transact-SQL command, but it is about 50gb
so it will take awhile to copy to the other box. Hopefully it is just an
issue from the backup wizard-thing.
Later,
Rocky|||> I think you made it comnplicated. Let's say you already copied the
> backup file to the root of drive F: on the new server. Frist you need
> to know the logical file names of FSPROD.
> RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
> it returns logical files name of data file and log file of FSPROD.
> Use them for restore command:
RESTORE FILELISTONLY FROM DISK = 'F:\FSPROD.bak'
TDSFSSYSdata I:\Data\TDSFSSYS_data.mdf D
TDSFSSYSlog K:\Log\TDSFSSYS_log.ldf L
I was using those names in my move commands in the restore statement.
Later,
Rocky|||> I'm re-building a backup from a Transact-SQL command, but it is about
> 50gb so it will take awhile to copy to the other box. Hopefully it is
> just an issue from the backup wizard-thing.
Worked fine as usual from Query Analyzer.
Thanks for the help,
Rocky
Saturday, February 25, 2012
Restore msdb file
I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
backup files to another. I reinstalled the operating system and restored al
l
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Star
t by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY
.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored
all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
Restore msdb file
I had a problem with operating system so copy copied all of my database
backup files to another. I reinstalled the operating system and restored all
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Start by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
backup files to another. I reinstalled the operating system and restored all
of the except the msdb database.
I received the following error when trying to restore msdb database.
The file () cannot be overwritten. It is being used by database msdb use
WITH MOVE to identify a valid location for the file.
Please help me resolve this issue.Use the MOVE option of the RESTORE command. Documented in Books Online. Start by investigating the
file layout of your backup using RESTORE HEADERONLY and RESTORE FILELISTONLY.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe K." <Joe K.@.discussions.microsoft.com> wrote in message
news:8744E2D0-F15A-4338-91D1-2F21B4BC9669@.microsoft.com...
> I had a problem with operating system so copy copied all of my database
> backup files to another. I reinstalled the operating system and restored all
> of the except the msdb database.
> I received the following error when trying to restore msdb database.
> The file () cannot be overwritten. It is being used by database msdb use
> WITH MOVE to identify a valid location for the file.
> Please help me resolve this issue.
>
Restore mdf after system crash in sqls2k
During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?
Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?
|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
>
>
|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?
|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?
Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?
|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
>
>
|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?
|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
Restore mdf after system crash in sqls2k
During a system crash I was able to manually copy my .mdf but not the log.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
--
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> > During a system crash I was able to manually copy my .mdf but not the log.
> > After restoring the system sql runs an older version of the file, which i
> > want to replace with the copied mdf. Sql manager denies attaching the file
> > because it doesn't mach the log-file.
> > Is there a way of resetting the database or any other way for me to
> > restore
> > the .mdf without dataloss?
>
>|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
After restoring the system sql runs an older version of the file, which i
want to replace with the copied mdf. Sql manager denies attaching the file
because it doesn't mach the log-file.
Is there a way of resetting the database or any other way for me to restore
the .mdf without dataloss?Try sp_attach_single_file_db (complete syntax in Books Online).
However, it is not _guaranteed_ to work. It is only guaranteed to work if
you explicitly detach the database via EM or sp_detach_db. You might be
lucky though.
--
Jacco Schalkwijk
SQL Server MVP
"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to
> restore
> the .mdf without dataloss?|||Hi,
Best option will be restore using good full database backup and apply your
transaction log backup. This
will provide you the data integrity. Attaching MDF file only might cause you
a data integrity problem
since you have not detached the database.
Thanks
Hari
MCDBA
"Jacco Schalkwijk" wrote:
> Try sp_attach_single_file_db (complete syntax in Books Online).
> However, it is not _guaranteed_ to work. It is only guaranteed to work if
> you explicitly detach the database via EM or sp_detach_db. You might be
> lucky though.
> --
> Jacco Schalkwijk
> SQL Server MVP
>
> "peteroff" <peteroff@.discussions.microsoft.com> wrote in message
> news:D9111F32-7436-488A-A4E6-7EB62CC00DA8@.microsoft.com...
> > During a system crash I was able to manually copy my .mdf but not the log.
> > After restoring the system sql runs an older version of the file, which i
> > want to replace with the copied mdf. Sql manager denies attaching the file
> > because it doesn't mach the log-file.
> > Is there a way of resetting the database or any other way for me to
> > restore
> > the .mdf without dataloss?
>
>|||thanks for the posts. I tried sp_attach_single_file_db. The server denied and
gave 9003 mistake. The problem is, that I do not have a valid transaction log
file. I only was able to copy the *.mdf
I restored a good version, I tried building up a whole new set of files, but
none of these would allow me to sneak in with my old *.mdf.
Seems like the security system between *.mdf and *.ldf is too perfect.
Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
together?
"peteroff" wrote:
> During a system crash I was able to manually copy my .mdf but not the log.
> After restoring the system sql runs an older version of the file, which i
> want to replace with the copied mdf. Sql manager denies attaching the file
> because it doesn't mach the log-file.
> Is there a way of resetting the database or any other way for me to restore
> the .mdf without dataloss?|||"peteroff" <peteroff@.discussions.microsoft.com> wrote in message
news:39D11384-A905-49C9-8C93-F128ADC192E0@.microsoft.com...
> thanks for the posts. I tried sp_attach_single_file_db. The server denied
and
> gave 9003 mistake. The problem is, that I do not have a valid transaction
log
> file. I only was able to copy the *.mdf
> I restored a good version, I tried building up a whole new set of files,
but
> none of these would allow me to sneak in with my old *.mdf.
> Seems like the security system between *.mdf and *.ldf is too perfect.
> Or does anybody know a reliable trick to put an *.mdf and an "empty" *.ldf
> together?
Microsoft can provide a method, but you will need to open a ticket with them
and they'll basically warn you that corruption is very likely.
Monday, February 20, 2012
Restore master database with upgrade from SQL7 to SQL2000
Dear all,
I would like to copy all DB's (incl. master) from a machine running SQL7 to another machine running SQL2000.
I was thinking about a backup/restore method, but I have a problem with 'master'. I get this error message:
The backup of the system database on device T:\BACKUPS\DataBkp\MASTERBKP cannot be restored because it was created by a different version of the server (117441473) than this server (134218112).
How can I upgrade my backup file so that SQL2000 can read it?
Thanks in advance.If I understand you want to port you SQL Server 7.0 environment to a SQL Server 2000 environment and retain your SQL Server 7.0 environment as well.
This may be the long way but how about the following steps:
1) Install SQL Server 7.0 (A) on the soon to be SQL Server 2000 first.
2) Move the databases from the original SQL Server 7 (B) over
3) Upgrade SQL Server 7.0 (A) to 2000|||Right!
It seems to be the only solution. And I would have had the same problem with msdb and model (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264474)
Thanks for your help!
--marc
I would like to copy all DB's (incl. master) from a machine running SQL7 to another machine running SQL2000.
I was thinking about a backup/restore method, but I have a problem with 'master'. I get this error message:
The backup of the system database on device T:\BACKUPS\DataBkp\MASTERBKP cannot be restored because it was created by a different version of the server (117441473) than this server (134218112).
How can I upgrade my backup file so that SQL2000 can read it?
Thanks in advance.If I understand you want to port you SQL Server 7.0 environment to a SQL Server 2000 environment and retain your SQL Server 7.0 environment as well.
This may be the long way but how about the following steps:
1) Install SQL Server 7.0 (A) on the soon to be SQL Server 2000 first.
2) Move the databases from the original SQL Server 7 (B) over
3) Upgrade SQL Server 7.0 (A) to 2000|||Right!
It seems to be the only solution. And I would have had the same problem with msdb and model (see http://support.microsoft.com/default.aspx?scid=kb;en-us;Q264474)
Thanks for your help!
--marc
Restore master database from backup
I wish to restore master database which was located on "e:" on my server.
the restored copy is residing on "f:"
when i restore the backup and restart, the server does not start.
is there any way to avoid this and perform the restoration succesfully.
or the process followed by me is wrong/incomplete.
do let me know...TIAtry this link
http://www.dbarecovery.com/restoremasterdb.html
the restored copy is residing on "f:"
when i restore the backup and restart, the server does not start.
is there any way to avoid this and perform the restoration succesfully.
or the process followed by me is wrong/incomplete.
do let me know...TIAtry this link
http://www.dbarecovery.com/restoremasterdb.html
restore master database backup to a new server, single user mode
Hi,
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
Michael
Hi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
Michael
Hi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>
restore master database backup to a new server, single user mode
Hi,
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
MichaelHi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>
I am using Win2000+SQL2000.
I have a backup copy of MASTER database, I need to retore it my newly setup
SQL server.
I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
SQL server single user,
I can not connect to this sql servre instance by using SQL query analyzer,
kindly please tell me the steps to restore the master database to a new
server.
Thanks
MichaelHi,
What is the error you are getting while connecting to query analyzer.
Have a look into steps (15 - 28) in the attached link.
http://www.dbarecovery.com/restoremasterdb.html
You have to restore the Master database and then the user databases.
Otherwise Master database restore will
overwrite the existing database entries.
Thanks
Hari
MCDBA
"Michael" <Ruihuiwen@.hotmail.com> wrote in message
news:Oo6cOqNgEHA.3132@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I am using Win2000+SQL2000.
> I have a backup copy of MASTER database, I need to retore it my newly
setup
> SQL server.
> I use the command "sqlservr.exe -c -m" from DOS command prompt to startup
> SQL server single user,
> I can not connect to this sql servre instance by using SQL query analyzer,
> kindly please tell me the steps to restore the master database to a new
> server.
> Thanks
> Michael
>
>
>
Subscribe to:
Posts (Atom)