Hi,
Two of my hard drives went down yesterday. and with it went the Sql server database.The data recovery guys recovered the back up database (.bak) file.I have since reinstalled SQL Server 2000 and now when i try and restore the .bak file it says the Microsoft tape back up file set is not valid when i try and open the .bak file.
Whats the right way to add my databse to the SQl Server2000 using my .bak file and if anyone has come across the error they could help.
Thanks
LDid you apply all SPs that your old server used to have?|||what do u mean by sps|||what do u mean by sps
Stored Procedure|||It's "Service Packs", not stored procedures...how do you apply a stored procedure anyway?|||Restore the databases...which she can't do. DOH!!! :)
You should always verify your backups, especially if writing directly to tape. Any luck though after applying the service packs? Don't you guys have a DBA who can help with this?|||It's "Service Packs", not stored procedures...how do you apply a stored procedure anyway?
Yes. It should be Service Packs|||Hi Lohit,
hows was your process of restoring the backup?
Showing posts with label bak. Show all posts
Showing posts with label bak. Show all posts
Monday, March 26, 2012
Friday, March 23, 2012
Restoring SQL databases
We often build databases on behalf of our clients and we're still using SQL
7 as a base level as we can generate .BAK files which can be restored to SQL
7 and SQL 2000.
I assume that if we upgraded to SQL 2005 on our production server that we
will be *unable* to restore .BAK files generated on that version onto an
older server running SQL 2000 or SQL 7.
If so, how to developers normally distribute their database? Note: we can't
use DTS and we're restoring to a server on a completely separate network.
Thanks, Rob.Rob Nicholson (rob.nicholson@.nospam_unforgettable.com) writes:
> We often build databases on behalf of our clients and we're still using
> SQL 7 as a base level as we can generate .BAK files which can be
> restored to SQL 7 and SQL 2000.
> I assume that if we upgraded to SQL 2005 on our production server that we
> will be *unable* to restore .BAK files generated on that version onto an
> older server running SQL 2000 or SQL 7.
Yes, you cannot restore a backup on SQL 2005 on SQL 2000 or SQL 7.
> If so, how to developers normally distribute their database? Note: we
> can't use DTS and we're restoring to a server on a completely separate
> network.
We build our databases from scripts using our own load tool, which we
then but into an install kit. Distributing a shrink-wrapped database is good
for the first installation, but it does not fly with upgrades. So in the
end, you need an ability do to scripts.
But of course, for a first installation, attaching a complete database
makes that installation faster. For us this is not an issue, as we work
with an expensive product with a small customer base, so we upgrade
far more often that we build a new databse.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> But of course, for a first installation, attaching a complete database
> makes that installation faster. For us this is not an issue, as we work
> with an expensive product with a small customer base, so we upgrade
> far more often that we build a new databse.
Sounds a similar scenario to our app - small user base, niche product. We
typically build and configure the system locally and then take the BAK file
to the customer for pilot installation.
Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
might be an option. We can set-up a SQL 2000 server in development, DTS to
there and take the BAK from that environment.
We'll not be using any SQL 2005 specific functions but I'm looking at our
strategy for 2006 which is including major upgrades of all production
servers like SQL.
Cheers, Rob.|||Rob Nicholson (rob.nicholson@.nospam_unforgettable.com) writes:
> Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
> might be an option. We can set-up a SQL 2000 server in development, DTS to
> there and take the BAK from that environment.
There isn't even a DTS in SQL 2005, it's called SQL Server Integration
Services. Maybe there is a way to export from SQL 2005 to SQL 2000, but
I actually tried to use the Copy Database Wizard (which uses SSIS) in that
direction the other w
, but I was told to that it was not supported.
(Not that I would recommend the Copy Database Wizard anyway. I filed a
number of bugs for it during the beta programme, and not all are fixed
in the RTM version.)
If you want to look at what we use, our toolset is available as freeware
from http://www.abaris.se/abaperls/. It's fairly complex, and not easy to
get started with, but it might give you some inspiration.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
7 as a base level as we can generate .BAK files which can be restored to SQL
7 and SQL 2000.
I assume that if we upgraded to SQL 2005 on our production server that we
will be *unable* to restore .BAK files generated on that version onto an
older server running SQL 2000 or SQL 7.
If so, how to developers normally distribute their database? Note: we can't
use DTS and we're restoring to a server on a completely separate network.
Thanks, Rob.Rob Nicholson (rob.nicholson@.nospam_unforgettable.com) writes:
> We often build databases on behalf of our clients and we're still using
> SQL 7 as a base level as we can generate .BAK files which can be
> restored to SQL 7 and SQL 2000.
> I assume that if we upgraded to SQL 2005 on our production server that we
> will be *unable* to restore .BAK files generated on that version onto an
> older server running SQL 2000 or SQL 7.
Yes, you cannot restore a backup on SQL 2005 on SQL 2000 or SQL 7.
> If so, how to developers normally distribute their database? Note: we
> can't use DTS and we're restoring to a server on a completely separate
> network.
We build our databases from scripts using our own load tool, which we
then but into an install kit. Distributing a shrink-wrapped database is good
for the first installation, but it does not fly with upgrades. So in the
end, you need an ability do to scripts.
But of course, for a first installation, attaching a complete database
makes that installation faster. For us this is not an issue, as we work
with an expensive product with a small customer base, so we upgrade
far more often that we build a new databse.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||> But of course, for a first installation, attaching a complete database
> makes that installation faster. For us this is not an issue, as we work
> with an expensive product with a small customer base, so we upgrade
> far more often that we build a new databse.
Sounds a similar scenario to our app - small user base, niche product. We
typically build and configure the system locally and then take the BAK file
to the customer for pilot installation.
Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
might be an option. We can set-up a SQL 2000 server in development, DTS to
there and take the BAK from that environment.
We'll not be using any SQL 2005 specific functions but I'm looking at our
strategy for 2006 which is including major upgrades of all production
servers like SQL.
Cheers, Rob.|||Rob Nicholson (rob.nicholson@.nospam_unforgettable.com) writes:
> Is it possible to DTS a database from SQL 2005 -> SQL 2000? It so, that
> might be an option. We can set-up a SQL 2000 server in development, DTS to
> there and take the BAK from that environment.
There isn't even a DTS in SQL 2005, it's called SQL Server Integration
Services. Maybe there is a way to export from SQL 2005 to SQL 2000, but
I actually tried to use the Copy Database Wizard (which uses SSIS) in that
direction the other w
, but I was told to that it was not supported.(Not that I would recommend the Copy Database Wizard anyway. I filed a
number of bugs for it during the beta programme, and not all are fixed
in the RTM version.)
If you want to look at what we use, our toolset is available as freeware
from http://www.abaris.se/abaperls/. It's fairly complex, and not easy to
get started with, but it might give you some inspiration.
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx
Wednesday, March 21, 2012
Restoring SQL 2000 database to another SQL Server
I get all sorts of errors using this:
RESTORE DATABASE TMW_LIVE FROM disk
= '\\lbtsql\sqlbackup\backup\tmw_live_db_200307010100.bak'
WITH MOVE 'psmaster52data' TO 'c:\program files\ms sql
server\mssql\data\tmw_live.mdf',
MOVE 'psmaster52log' to 'c:\program files\ms sql
server\mssql\data\tmw_live.ldf',
REPLACE
go
Couple of questions: my database's name is TMW_LIVE and
the logical name is psmaster52data - am I using them in
the correct spot? I get an error saying that the physical
name tmw_live.mdf may be incorrect - that is where I want
to restore to, how can it be incorrect? I've tried it
with a database named this and then deleted it. I also
get an error saying: "File 'psmaster52Data' cannot be
restored to 'c:\program ..." use MOVE instead. I was
using move. All I'm trying to do is to take a backup on
one SQL Server and restore to another (that doesn't have
the same drives), can anyone help me out on this?Larry,
Make sure that there's no files with the same name in that directory where
you're trying to restore the database. This is usually the cause of the
"File 'psmaster52Data' cannot be restored to 'c:\program ...' use MOVE
instead" error.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Larry Shulan" <lshulan@.lindencompanies.com> wrote in message
news:060b01c33fdc$d4df12d0$a001280a@.phx.gbl...
> I get all sorts of errors using this:
> RESTORE DATABASE TMW_LIVE FROM disk
> = '\\lbtsql\sqlbackup\backup\tmw_live_db_200307010100.bak'
> WITH MOVE 'psmaster52data' TO 'c:\program files\ms sql
> server\mssql\data\tmw_live.mdf',
> MOVE 'psmaster52log' to 'c:\program files\ms sql
> server\mssql\data\tmw_live.ldf',
> REPLACE
> go
> Couple of questions: my database's name is TMW_LIVE and
> the logical name is psmaster52data - am I using them in
> the correct spot? I get an error saying that the physical
> name tmw_live.mdf may be incorrect - that is where I want
> to restore to, how can it be incorrect? I've tried it
> with a database named this and then deleted it. I also
> get an error saying: "File 'psmaster52Data' cannot be
> restored to 'c:\program ..." use MOVE instead. I was
> using move. All I'm trying to do is to take a backup on
> one SQL Server and restore to another (that doesn't have
> the same drives), can anyone help me out on this?
RESTORE DATABASE TMW_LIVE FROM disk
= '\\lbtsql\sqlbackup\backup\tmw_live_db_200307010100.bak'
WITH MOVE 'psmaster52data' TO 'c:\program files\ms sql
server\mssql\data\tmw_live.mdf',
MOVE 'psmaster52log' to 'c:\program files\ms sql
server\mssql\data\tmw_live.ldf',
REPLACE
go
Couple of questions: my database's name is TMW_LIVE and
the logical name is psmaster52data - am I using them in
the correct spot? I get an error saying that the physical
name tmw_live.mdf may be incorrect - that is where I want
to restore to, how can it be incorrect? I've tried it
with a database named this and then deleted it. I also
get an error saying: "File 'psmaster52Data' cannot be
restored to 'c:\program ..." use MOVE instead. I was
using move. All I'm trying to do is to take a backup on
one SQL Server and restore to another (that doesn't have
the same drives), can anyone help me out on this?Larry,
Make sure that there's no files with the same name in that directory where
you're trying to restore the database. This is usually the cause of the
"File 'psmaster52Data' cannot be restored to 'c:\program ...' use MOVE
instead" error.
--
Carlos E. Rojas
SQL Server MVP
Co-Author SQL Server 2000 Programming by Example
"Larry Shulan" <lshulan@.lindencompanies.com> wrote in message
news:060b01c33fdc$d4df12d0$a001280a@.phx.gbl...
> I get all sorts of errors using this:
> RESTORE DATABASE TMW_LIVE FROM disk
> = '\\lbtsql\sqlbackup\backup\tmw_live_db_200307010100.bak'
> WITH MOVE 'psmaster52data' TO 'c:\program files\ms sql
> server\mssql\data\tmw_live.mdf',
> MOVE 'psmaster52log' to 'c:\program files\ms sql
> server\mssql\data\tmw_live.ldf',
> REPLACE
> go
> Couple of questions: my database's name is TMW_LIVE and
> the logical name is psmaster52data - am I using them in
> the correct spot? I get an error saying that the physical
> name tmw_live.mdf may be incorrect - that is where I want
> to restore to, how can it be incorrect? I've tried it
> with a database named this and then deleted it. I also
> get an error saying: "File 'psmaster52Data' cannot be
> restored to 'c:\program ..." use MOVE instead. I was
> using move. All I'm trying to do is to take a backup on
> one SQL Server and restore to another (that doesn't have
> the same drives), can anyone help me out on this?
Wednesday, March 7, 2012
restoring from .bak file
Tried restoring a database from .bak file through Enterprise Manager and also by usng the following code :
RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH
REPLACE,
RECOVERY,
MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'
But, I get an error :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'd:\mssql7\data\apm.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
I have got the two filenames i.e."ap0data" and "ap0log" by using the command "restore fileslistonly from disk = c:\dba.bak".
Can anyone help me to do the things rigtly ?This should work !!!
RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'|||Thanks for your help.
I tried the code, but got error:
Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'apm' database.
I don't have the original database. I have just created a blank database and I am trying to restore.|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'
RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2
Run these two and get back with the results|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'
RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2
Server: Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device 'c:\windows\desktop\cmpbk.BAK'.|||Try this :
RESTORE DATABASE DBB FROM DISK = N'c:\DBA.BAK' WITH FILE = 1,
RECOVERY , REPLACE ,
MOVE N'ap0data' TO N'c:\mssql\data\apm_data.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apm_log.ldf'|||Tried :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'c:\mssql\data\apm_data.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.|||sp_helpdb dbb
?|||name = apm
db_size = 2.00mb
owner = sa
bdid = 8
status = select into/bulkocopy, trun. log on chkpt
name = apm_data
fileid = 1
filename = c:\mssql7\data\apm_data.mdf
filegroup = primary
maxsize = unlimited
growth = 10%
usage = data only
name = apm_log
fileid = 2
filename = c:\mssql7\data\apm_log.ldf
filegroup = null
maxsize = unlimited
growth = 10%
usage = log only|||am clutching at straws now
RESTORE DATABASE TestDB FROM DISK = N'c:\DBA.BAK' WITH
MOVE N'ap0data' TO N'c:\mssql\data\apb1_data1.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apb1_log1.ldf'
i mean try restoring to a completely new database ... let the restore statements create the db|||Hey, It worked.
Thank you very much, Sir|||now you can use the sp_renamedb command to change it to the name you want.
RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH
REPLACE,
RECOVERY,
MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'
But, I get an error :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'd:\mssql7\data\apm.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.
I have got the two filenames i.e."ap0data" and "ap0log" by using the command "restore fileslistonly from disk = c:\dba.bak".
Can anyone help me to do the things rigtly ?This should work !!!
RESTORE DATABASE DBB
FROM DISK = 'c:\DBA.BAK'
WITH MOVE 'ap0data' TO 'c:\mssql\data\apm_data.mdf',
MOVE 'ap0Log' TO 'c:\mssql\data\apm_log.ldf'|||Thanks for your help.
I tried the code, but got error:
Server: Msg 3154, Level 16, State 1, Line 1
The backup set holds a backup of a database other than the existing 'apm' database.
I don't have the original database. I have just created a blank database and I am trying to restore.|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'
RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2
Run these two and get back with the results|||RESTORE FILELISTONLY
FROM 'c:\DBA.BAK'
RESTORE FILELISTONLY
FROM 'c:\DBA.BAK' WITH FILE = 2
Server: Msg 4038, Level 16, State 1, Line 1
Cannot find file ID 2 on device 'c:\windows\desktop\cmpbk.BAK'.|||Try this :
RESTORE DATABASE DBB FROM DISK = N'c:\DBA.BAK' WITH FILE = 1,
RECOVERY , REPLACE ,
MOVE N'ap0data' TO N'c:\mssql\data\apm_data.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apm_log.ldf'|||Tried :
Server: Msg 3156, Level 16, State 2, Line 1
The file 'c:\mssql\data\apm_data.mdf' cannot be used by RESTORE. Consider using the WITH MOVE option to identify a valid location for the file.|||sp_helpdb dbb
?|||name = apm
db_size = 2.00mb
owner = sa
bdid = 8
status = select into/bulkocopy, trun. log on chkpt
name = apm_data
fileid = 1
filename = c:\mssql7\data\apm_data.mdf
filegroup = primary
maxsize = unlimited
growth = 10%
usage = data only
name = apm_log
fileid = 2
filename = c:\mssql7\data\apm_log.ldf
filegroup = null
maxsize = unlimited
growth = 10%
usage = log only|||am clutching at straws now
RESTORE DATABASE TestDB FROM DISK = N'c:\DBA.BAK' WITH
MOVE N'ap0data' TO N'c:\mssql\data\apb1_data1.mdf',
MOVE N'ap0Log' TO N'c:\mssql\data\apb1_log1.ldf'
i mean try restoring to a completely new database ... let the restore statements create the db|||Hey, It worked.
Thank you very much, Sir|||now you can use the sp_renamedb command to change it to the name you want.
restoring from .bak
i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.Did you refresh your GUI afterte restore? Can you connect to your database?|||it's now the morning and been at least a good 12 hours. refresh in the GUI shows them still in restoring status. i closed and reopened the management studio and they are still both restoring. i am unable to choose either database from the drop down when i open a new query window. i told it during restore to do them in norecovery mode under the expectation that this would be the fastest.|||i did notice that the data files created were substantially bigger... one backup created a 7.2gb mdf, 92mb ldf, the other created a 543mb mdf, 1.3gb ldf, and 371mb ndf... actually it appears when I specified the new filepath for that one from the backup, that I forgot the suffix. will that matter? I suppose I could stop the db service and rename it to an .ndf real quick. doesn't seem to be the cause of my slow restore issue though..|||FYI i restored the db in norecovery mode, under the impression that db access was denied only for the duration of the restore. i tried it with the first option later and the db's loaded fine. there must be some step to flip the db into available once in the restoring status, however it was not obvious/intuitive to me based on the gui or error messages i was receiving. but i seemed to be missing that step.|||I've experienced the same symptom, but with both norecovery and recovery options. Each time this produces a process that cannot be terminated until the machine is turned off. No errors are logged in sql server or windows. I've also verified the database backup through the verifyrestoreonly option and by restoring to another machine/instance.
Any thoughts?
|||i did not appear to have an active ongoing process; my cpu appeared idle. i didn't have a problem installing sp2 hoping it would fix the problem while the databases were in the restoring state. my assumption is there is some toggle (that should be made more apparent in gui error messages) that one could flip in there or some hidden sql to flip the db to go. i'm just naively jumping into this and figuring it out, my assumption is some book (a book online maybe) covers this situation (?) or perhaps it is some flakey bug.|||
That's your key.
Restoring a database in norecovery mode tells SQL Server that you intend to apply transaction log backups to roll the database forward before making it available. Thus the restore process is not complete. That's why your database is staying in 'restoring' state.
To bring your database online, simply use the command RESTORE DATABASE <dbname> WITH RECOVERY
You don't need a backup file for this - it just invokes the recovery logic to bring the DB online.
restoring from .bak
i have sql server 2005 dev installed on a laptop. i have 2 .bak's from production sql server 2005 database. i went through the gui restore procedure and both backup files completed successfully. now both databases are in the (Restoring...) status and have been for quite some time. i have sp2 applied. when will these become available? each .bak was around 400 mb. it has been a few hours.Did you refresh your GUI afterte restore? Can you connect to your database?|||it's now the morning and been at least a good 12 hours. refresh in the GUI shows them still in restoring status. i closed and reopened the management studio and they are still both restoring. i am unable to choose either database from the drop down when i open a new query window. i told it during restore to do them in norecovery mode under the expectation that this would be the fastest.|||i did notice that the data files created were substantially bigger... one backup created a 7.2gb mdf, 92mb ldf, the other created a 543mb mdf, 1.3gb ldf, and 371mb ndf... actually it appears when I specified the new filepath for that one from the backup, that I forgot the suffix. will that matter? I suppose I could stop the db service and rename it to an .ndf real quick. doesn't seem to be the cause of my slow restore issue though..|||FYI i restored the db in norecovery mode, under the impression that db access was denied only for the duration of the restore. i tried it with the first option later and the db's loaded fine. there must be some step to flip the db into available once in the restoring status, however it was not obvious/intuitive to me based on the gui or error messages i was receiving. but i seemed to be missing that step.|||I've experienced the same symptom, but with both norecovery and recovery options. Each time this produces a process that cannot be terminated until the machine is turned off. No errors are logged in sql server or windows. I've also verified the database backup through the verifyrestoreonly option and by restoring to another machine/instance.
Any thoughts?
|||i did not appear to have an active ongoing process; my cpu appeared idle. i didn't have a problem installing sp2 hoping it would fix the problem while the databases were in the restoring state. my assumption is there is some toggle (that should be made more apparent in gui error messages) that one could flip in there or some hidden sql to flip the db to go. i'm just naively jumping into this and figuring it out, my assumption is some book (a book online maybe) covers this situation (?) or perhaps it is some flakey bug.|||
That's your key.
Restoring a database in norecovery mode tells SQL Server that you intend to apply transaction log backups to roll the database forward before making it available. Thus the restore process is not complete. That's why your database is staying in 'restoring' state.
To bring your database online, simply use the command RESTORE DATABASE <dbname> WITH RECOVERY
You don't need a backup file for this - it just invokes the recovery logic to bring the DB online.
Tuesday, February 21, 2012
Restoring database problem
While restoring backup from ad.bak file to already existing database followi
ng error is appeared "Cannot find file Id 2 on device C:\dbBackup\ad.bak ba
ckup or restore operation terminated abnormally " This is happened when i tr
y to restore database.Hi
Can you show us your RESTORE command?
"nadeem" <anonymous@.discussions.microsoft.com> wrote in message
news:CE62DBDE-C168-49B1-BB42-F89B1881A438@.microsoft.com...
> While restoring backup from ad.bak file to already existing database
following error is appeared "Cannot find file Id 2 on device
C:\dbBackup\ad.bak backup or restore operation terminated abnormally " This
is happened when i try to restore database.|||Hi,
You can use the RESTORE HEADERONLY command to idenfify the backup file list
and use RESTORE DATABASE to restore the specific file.
Sample code
RESTORE HEADERONLY FROM DISK='c:\backup\dbname.bak'
RESTORE DATABASE mydatabase FROM DISK='C:\backup\dbname.bak' WITH FILE =
FileNumber
(Use option MOVE if you have the database already in the same server)
Thanks
Hari
MCDBA
"nadeem" <anonymous@.discussions.microsoft.com> wrote in message
news:CE62DBDE-C168-49B1-BB42-F89B1881A438@.microsoft.com...
> While restoring backup from ad.bak file to already existing database
following error is appeared "Cannot find file Id 2 on device
C:\dbBackup\ad.bak backup or restore operation terminated abnormally " This
is happened when i try to restore database.
ng error is appeared "Cannot find file Id 2 on device C:\dbBackup\ad.bak ba
ckup or restore operation terminated abnormally " This is happened when i tr
y to restore database.Hi
Can you show us your RESTORE command?
"nadeem" <anonymous@.discussions.microsoft.com> wrote in message
news:CE62DBDE-C168-49B1-BB42-F89B1881A438@.microsoft.com...
> While restoring backup from ad.bak file to already existing database
following error is appeared "Cannot find file Id 2 on device
C:\dbBackup\ad.bak backup or restore operation terminated abnormally " This
is happened when i try to restore database.|||Hi,
You can use the RESTORE HEADERONLY command to idenfify the backup file list
and use RESTORE DATABASE to restore the specific file.
Sample code
RESTORE HEADERONLY FROM DISK='c:\backup\dbname.bak'
RESTORE DATABASE mydatabase FROM DISK='C:\backup\dbname.bak' WITH FILE =
FileNumber
(Use option MOVE if you have the database already in the same server)
Thanks
Hari
MCDBA
"nadeem" <anonymous@.discussions.microsoft.com> wrote in message
news:CE62DBDE-C168-49B1-BB42-F89B1881A438@.microsoft.com...
> While restoring backup from ad.bak file to already existing database
following error is appeared "Cannot find file Id 2 on device
C:\dbBackup\ad.bak backup or restore operation terminated abnormally " This
is happened when i try to restore database.
Restoring database on a different server
Can a user database .bak be restored to another server
that has never had the database on it?
We lost a development server and reinstalled SQL Server I
have user database backups but no system database
backups. When I attempt a restore I cannot see the .bak
file that I would like to restore. I thought that I
could create a new database with the same name and then
restore the .bak to get the database back. is this an
incorrect procedure?
When you restore, choose "From Device" and locate the bak
file.
>--Original Message--
>Can a user database .bak be restored to another server
>that has never had the database on it?
>We lost a development server and reinstalled SQL Server
I
>have user database backups but no system database
>backups. When I attempt a restore I cannot see the .bak
>file that I would like to restore. I thought that I
>could create a new database with the same name and then
>restore the .bak to get the database back. is this an
>incorrect procedure?
>
>.
>
|||>>Can a user database .bak be restored to another server that has never had
the database on it?
Yes.
[vbcol=seagreen]
restore
because it (the backup) was recorded on the original server.
You can restore with or without the database created first. Look for
RESTORE DATABASE in BOL.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:246d01c427d7$6392e640$a601280a@.phx.gbl...
> Can a user database .bak be restored to another server
> that has never had the database on it?
> We lost a development server and reinstalled SQL Server I
> have user database backups but no system database
> backups. When I attempt a restore I cannot see the .bak
> file that I would like to restore. I thought that I
> could create a new database with the same name and then
> restore the .bak to get the database back. is this an
> incorrect procedure?
>
|||Hi Jim,
Thanks for posting in the Newsgroup. Assuming the database you want to
restore your "c:\db.bak" file into a database named "newdb" and you are
using SQL Server 2000
You could try to restore it with SQL Server Enterprise Manager by following
steps
1.Open SQL Server Enterprise Manager and select the instance and "newdb"
2.Right Click "newdb" -> All Tasks -> Restore database
3.Select "From device" in General Tab
4.Click Select DevicesX
5.Add the file you want in the pop dialog
6.Click OK and you will see Restore Process Dialog and then you will be
notified that you have successfully restored your database.
Also, you could try to restore it with Query Analyzer by following steps:
1.Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
2.type the T-SQL
RESTORE DATABASE newdb from disk='c:\db.bak'
3. you will get the result like
RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485
MB/sec).
Bottom line - have a good backup strategy.
Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Thanks for the quick reply. The restore went well.[vbcol=seagreen]
>--Original Message--
that has never had[vbcol=seagreen]
>the database on it?
>Yes.
that I would like to
>restore
>because it (the backup) was recorded on the original
server.
>You can restore with or without the database created
first. Look for[vbcol=seagreen]
>RESTORE DATABASE in BOL.
>
>"Jim Abel" <jim.abel@.lmco.com> wrote in message
>news:246d01c427d7$6392e640$a601280a@.phx.gbl...
Server I
>
>.
>
|||Thanks for the quick reply. The restore went well.
>--Original Message--
>When you restore, choose "From Device" and locate the
bak
>file.
>I
>.
>
|||Thanks for the quick reply. The restore went well.
>--Original Message--
>Hi Jim,
>Thanks for posting in the Newsgroup. Assuming the=20
database you want to=20
>restore your "c:\db.bak" file into a database=20
named "newdb" and you are=20
>using SQL Server 2000
>You could try to restore it with SQL Server Enterprise=20
Manager by following=20
>steps
>1.Open SQL Server Enterprise Manager and select the=20
instance and "newdb"
>2.Right Click "newdb" -> All Tasks -> Restore=20
database
>3.Select "From device" in General Tab
>4.Click Select Devices=A1=AD
>5.Add the file you want in the pop dialog
>6.Click OK and you will see Restore Process Dialog=20
and then you will be=20
>notified that you have successfully restored your=20
database.
>
>Also, you could try to restore it with Query Analyzer by=20
following steps:
>1.Open Query Analyzer, Login as sa (or whatever=20
have RESTORE privilege)
>2.type the T-SQL
>RESTORE DATABASE newdb from disk=3D'c:\db.bak'
>3. you will get the result like=20
>RESTORE DATABASE successfully processed 113 pages in=20
0.370 seconds (2.485=20
>MB/sec).
>
>Bottom line - have a good backup strategy.
>Please feel free to post in the group if this solves=20
your problem or if you=20
>would like further help. We are here to be of assistance!
>Sincerely yours,
>Michael Cheng
>Microsoft Online Support
>************************************************* ********
**
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and=20
confers no rights.
>Please reply to newsgroups only, many thanks.
>.
>
|||I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.
|||You can backup and restore the one user database to another
server. It's done frequently. You still need to be aware of
anything configured or setup on the source server and issues
you could run into related to this. Examples of some things
to watch out for would be users in the database that don't
exist on the destination server, using linked servers,
cross-database stored procedures, etc.
-Sue
On Tue, 25 May 2004 17:01:04 -0700, "Ling"
<anonymous@.discussions.microsoft.com> wrote:
>I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.
|||Hi,
Thank you for using MSDN Newsgroup!
It is highly appreciated that you should post your own questions as a new
thread, which, I believe, will get us closer and quicker to the resolutions
In general, we need not do anything with other databases such as msdb,
master etc. However, I am not sure your detailed requirements. You are
welcome to raise your issue to the newsgroup by a new post
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
that has never had the database on it?
We lost a development server and reinstalled SQL Server I
have user database backups but no system database
backups. When I attempt a restore I cannot see the .bak
file that I would like to restore. I thought that I
could create a new database with the same name and then
restore the .bak to get the database back. is this an
incorrect procedure?
When you restore, choose "From Device" and locate the bak
file.
>--Original Message--
>Can a user database .bak be restored to another server
>that has never had the database on it?
>We lost a development server and reinstalled SQL Server
I
>have user database backups but no system database
>backups. When I attempt a restore I cannot see the .bak
>file that I would like to restore. I thought that I
>could create a new database with the same name and then
>restore the .bak to get the database back. is this an
>incorrect procedure?
>
>.
>
|||>>Can a user database .bak be restored to another server that has never had
the database on it?
Yes.
[vbcol=seagreen]
restore
because it (the backup) was recorded on the original server.
You can restore with or without the database created first. Look for
RESTORE DATABASE in BOL.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:246d01c427d7$6392e640$a601280a@.phx.gbl...
> Can a user database .bak be restored to another server
> that has never had the database on it?
> We lost a development server and reinstalled SQL Server I
> have user database backups but no system database
> backups. When I attempt a restore I cannot see the .bak
> file that I would like to restore. I thought that I
> could create a new database with the same name and then
> restore the .bak to get the database back. is this an
> incorrect procedure?
>
|||Hi Jim,
Thanks for posting in the Newsgroup. Assuming the database you want to
restore your "c:\db.bak" file into a database named "newdb" and you are
using SQL Server 2000
You could try to restore it with SQL Server Enterprise Manager by following
steps
1.Open SQL Server Enterprise Manager and select the instance and "newdb"
2.Right Click "newdb" -> All Tasks -> Restore database
3.Select "From device" in General Tab
4.Click Select DevicesX
5.Add the file you want in the pop dialog
6.Click OK and you will see Restore Process Dialog and then you will be
notified that you have successfully restored your database.
Also, you could try to restore it with Query Analyzer by following steps:
1.Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
2.type the T-SQL
RESTORE DATABASE newdb from disk='c:\db.bak'
3. you will get the result like
RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485
MB/sec).
Bottom line - have a good backup strategy.
Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
|||Thanks for the quick reply. The restore went well.[vbcol=seagreen]
>--Original Message--
that has never had[vbcol=seagreen]
>the database on it?
>Yes.
that I would like to
>restore
>because it (the backup) was recorded on the original
server.
>You can restore with or without the database created
first. Look for[vbcol=seagreen]
>RESTORE DATABASE in BOL.
>
>"Jim Abel" <jim.abel@.lmco.com> wrote in message
>news:246d01c427d7$6392e640$a601280a@.phx.gbl...
Server I
>
>.
>
|||Thanks for the quick reply. The restore went well.
>--Original Message--
>When you restore, choose "From Device" and locate the
bak
>file.
>I
>.
>
|||Thanks for the quick reply. The restore went well.
>--Original Message--
>Hi Jim,
>Thanks for posting in the Newsgroup. Assuming the=20
database you want to=20
>restore your "c:\db.bak" file into a database=20
named "newdb" and you are=20
>using SQL Server 2000
>You could try to restore it with SQL Server Enterprise=20
Manager by following=20
>steps
>1.Open SQL Server Enterprise Manager and select the=20
instance and "newdb"
>2.Right Click "newdb" -> All Tasks -> Restore=20
database
>3.Select "From device" in General Tab
>4.Click Select Devices=A1=AD
>5.Add the file you want in the pop dialog
>6.Click OK and you will see Restore Process Dialog=20
and then you will be=20
>notified that you have successfully restored your=20
database.
>
>Also, you could try to restore it with Query Analyzer by=20
following steps:
>1.Open Query Analyzer, Login as sa (or whatever=20
have RESTORE privilege)
>2.type the T-SQL
>RESTORE DATABASE newdb from disk=3D'c:\db.bak'
>3. you will get the result like=20
>RESTORE DATABASE successfully processed 113 pages in=20
0.370 seconds (2.485=20
>MB/sec).
>
>Bottom line - have a good backup strategy.
>Please feel free to post in the group if this solves=20
your problem or if you=20
>would like further help. We are here to be of assistance!
>Sincerely yours,
>Michael Cheng
>Microsoft Online Support
>************************************************* ********
**
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and=20
confers no rights.
>Please reply to newsgroups only, many thanks.
>.
>
|||I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.
|||You can backup and restore the one user database to another
server. It's done frequently. You still need to be aware of
anything configured or setup on the source server and issues
you could run into related to this. Examples of some things
to watch out for would be users in the database that don't
exist on the destination server, using linked servers,
cross-database stored procedures, etc.
-Sue
On Tue, 25 May 2004 17:01:04 -0700, "Ling"
<anonymous@.discussions.microsoft.com> wrote:
>I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.
|||Hi,
Thank you for using MSDN Newsgroup!
It is highly appreciated that you should post your own questions as a new
thread, which, I believe, will get us closer and quicker to the resolutions
In general, we need not do anything with other databases such as msdb,
master etc. However, I am not sure your detailed requirements. You are
welcome to raise your issue to the newsgroup by a new post
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Support
************************************************** *********
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Restoring database on a different server
Can a user database .bak be restored to another server
that has never had the database on it?
We lost a development server and reinstalled SQL Server I
have user database backups but no system database
backups. When I attempt a restore I cannot see the .bak
file that I would like to restore. I thought that I
could create a new database with the same name and then
restore the .bak to get the database back. is this an
incorrect procedure?When you restore, choose "From Device" and locate the bak
file.
>--Original Message--
>Can a user database .bak be restored to another server
>that has never had the database on it?
>We lost a development server and reinstalled SQL Server
I
>have user database backups but no system database
>backups. When I attempt a restore I cannot see the .bak
>file that I would like to restore. I thought that I
>could create a new database with the same name and then
>restore the .bak to get the database back. is this an
>incorrect procedure?
>
>.
>|||>>Can a user database .bak be restored to another server that has never had
the database on it?
Yes.
>>When I attempt a restore I cannot see the .bak file that I would like to
restore
because it (the backup) was recorded on the original server.
You can restore with or without the database created first. Look for
RESTORE DATABASE in BOL.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:246d01c427d7$6392e640$a601280a@.phx.gbl...
> Can a user database .bak be restored to another server
> that has never had the database on it?
> We lost a development server and reinstalled SQL Server I
> have user database backups but no system database
> backups. When I attempt a restore I cannot see the .bak
> file that I would like to restore. I thought that I
> could create a new database with the same name and then
> restore the .bak to get the database back. is this an
> incorrect procedure?
>|||Hi Jim,
Thanks for posting in the Newsgroup. Assuming the database you want to
restore your "c:\db.bak" file into a database named "newdb" and you are
using SQL Server 2000
You could try to restore it with SQL Server Enterprise Manager by following
steps
1. Open SQL Server Enterprise Manager and select the instance and "newdb"
2. Right Click "newdb" -> All Tasks -> Restore database
3. Select "From device" in General Tab
4. Click Select Devices¡
5. Add the file you want in the pop dialog
6. Click OK and you will see Restore Process Dialog and then you will be
notified that you have successfully restored your database.
Also, you could try to restore it with Query Analyzer by following steps:
1. Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
2. type the T-SQL
RESTORE DATABASE newdb from disk='c:\db.bak'
3. you will get the result like
RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485
MB/sec).
Bottom line - have a good backup strategy.
Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Thanks for the quick reply. The restore went well.
>--Original Message--
>>Can a user database .bak be restored to another server
that has never had
>the database on it?
>Yes.
>>When I attempt a restore I cannot see the .bak file
that I would like to
>restore
>because it (the backup) was recorded on the original
server.
>You can restore with or without the database created
first. Look for
>RESTORE DATABASE in BOL.
>
>"Jim Abel" <jim.abel@.lmco.com> wrote in message
>news:246d01c427d7$6392e640$a601280a@.phx.gbl...
>> Can a user database .bak be restored to another server
>> that has never had the database on it?
>> We lost a development server and reinstalled SQL
Server I
>> have user database backups but no system database
>> backups. When I attempt a restore I cannot see the .bak
>> file that I would like to restore. I thought that I
>> could create a new database with the same name and then
>> restore the .bak to get the database back. is this an
>> incorrect procedure?
>>
>
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>When you restore, choose "From Device" and locate the
bak
>file.
>>--Original Message--
>>Can a user database .bak be restored to another server
>>that has never had the database on it?
>>We lost a development server and reinstalled SQL Server
>I
>>have user database backups but no system database
>>backups. When I attempt a restore I cannot see the .bak
>>file that I would like to restore. I thought that I
>>could create a new database with the same name and then
>>restore the .bak to get the database back. is this an
>>incorrect procedure?
>>
>>.
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>Hi Jim,
>Thanks for posting in the Newsgroup. Assuming the database you want to >restore your "c:\db.bak" file into a database named "newdb" and you are >using SQL Server 2000
>You could try to restore it with SQL Server Enterprise Manager by following >steps
>1. Open SQL Server Enterprise Manager and select the instance and "newdb"
>2. Right Click "newdb" -> All Tasks -> Restore database
>3. Select "From device" in General Tab
>4. Click Select Devices=A1=AD
>5. Add the file you want in the pop dialog
>6. Click OK and you will see Restore Process Dialog and then you will be >notified that you have successfully restored your database.
>
>Also, you could try to restore it with Query Analyzer by following steps:
>1. Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
>2. type the T-SQL
>RESTORE DATABASE newdb from disk=3D'c:\db.bak'
>3. you will get the result like >RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485 >MB/sec).
>
>Bottom line - have a good backup strategy.
>Please feel free to post in the group if this solves your problem or if you >would like further help. We are here to be of assistance!
>Sincerely yours,
>Michael Cheng
>Microsoft Online Support
>*********************************************************
**
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only, many thanks.
>.
>|||I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.|||You can backup and restore the one user database to another
server. It's done frequently. You still need to be aware of
anything configured or setup on the source server and issues
you could run into related to this. Examples of some things
to watch out for would be users in the database that don't
exist on the destination server, using linked servers,
cross-database stored procedures, etc.
-Sue
On Tue, 25 May 2004 17:01:04 -0700, "Ling"
<anonymous@.discussions.microsoft.com> wrote:
>I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.|||Hi,
Thank you for using MSDN Newsgroup!
It is highly appreciated that you should post your own questions as a new
thread, which, I believe, will get us closer and quicker to the resolutions
:)
In general, we need not do anything with other databases such as msdb,
master etc. However, I am not sure your detailed requirements. You are
welcome to raise your issue to the newsgroup by a new post
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Maybe these will help answer some questions related to moving db's:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:C142BBB9-0F56-4354-9569-79A7038B7AB4@.microsoft.com...
> I also need to "copy" one of our databases to a different server. Can I
only backup and restore the one user database? Do I have to do anything
with msdb, master, model.etc? Please advise.|||Thank you all for your replies!!
that has never had the database on it?
We lost a development server and reinstalled SQL Server I
have user database backups but no system database
backups. When I attempt a restore I cannot see the .bak
file that I would like to restore. I thought that I
could create a new database with the same name and then
restore the .bak to get the database back. is this an
incorrect procedure?When you restore, choose "From Device" and locate the bak
file.
>--Original Message--
>Can a user database .bak be restored to another server
>that has never had the database on it?
>We lost a development server and reinstalled SQL Server
I
>have user database backups but no system database
>backups. When I attempt a restore I cannot see the .bak
>file that I would like to restore. I thought that I
>could create a new database with the same name and then
>restore the .bak to get the database back. is this an
>incorrect procedure?
>
>.
>|||>>Can a user database .bak be restored to another server that has never had
the database on it?
Yes.
>>When I attempt a restore I cannot see the .bak file that I would like to
restore
because it (the backup) was recorded on the original server.
You can restore with or without the database created first. Look for
RESTORE DATABASE in BOL.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:246d01c427d7$6392e640$a601280a@.phx.gbl...
> Can a user database .bak be restored to another server
> that has never had the database on it?
> We lost a development server and reinstalled SQL Server I
> have user database backups but no system database
> backups. When I attempt a restore I cannot see the .bak
> file that I would like to restore. I thought that I
> could create a new database with the same name and then
> restore the .bak to get the database back. is this an
> incorrect procedure?
>|||Hi Jim,
Thanks for posting in the Newsgroup. Assuming the database you want to
restore your "c:\db.bak" file into a database named "newdb" and you are
using SQL Server 2000
You could try to restore it with SQL Server Enterprise Manager by following
steps
1. Open SQL Server Enterprise Manager and select the instance and "newdb"
2. Right Click "newdb" -> All Tasks -> Restore database
3. Select "From device" in General Tab
4. Click Select Devices¡
5. Add the file you want in the pop dialog
6. Click OK and you will see Restore Process Dialog and then you will be
notified that you have successfully restored your database.
Also, you could try to restore it with Query Analyzer by following steps:
1. Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
2. type the T-SQL
RESTORE DATABASE newdb from disk='c:\db.bak'
3. you will get the result like
RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485
MB/sec).
Bottom line - have a good backup strategy.
Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Thanks for the quick reply. The restore went well.
>--Original Message--
>>Can a user database .bak be restored to another server
that has never had
>the database on it?
>Yes.
>>When I attempt a restore I cannot see the .bak file
that I would like to
>restore
>because it (the backup) was recorded on the original
server.
>You can restore with or without the database created
first. Look for
>RESTORE DATABASE in BOL.
>
>"Jim Abel" <jim.abel@.lmco.com> wrote in message
>news:246d01c427d7$6392e640$a601280a@.phx.gbl...
>> Can a user database .bak be restored to another server
>> that has never had the database on it?
>> We lost a development server and reinstalled SQL
Server I
>> have user database backups but no system database
>> backups. When I attempt a restore I cannot see the .bak
>> file that I would like to restore. I thought that I
>> could create a new database with the same name and then
>> restore the .bak to get the database back. is this an
>> incorrect procedure?
>>
>
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>When you restore, choose "From Device" and locate the
bak
>file.
>>--Original Message--
>>Can a user database .bak be restored to another server
>>that has never had the database on it?
>>We lost a development server and reinstalled SQL Server
>I
>>have user database backups but no system database
>>backups. When I attempt a restore I cannot see the .bak
>>file that I would like to restore. I thought that I
>>could create a new database with the same name and then
>>restore the .bak to get the database back. is this an
>>incorrect procedure?
>>
>>.
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>Hi Jim,
>Thanks for posting in the Newsgroup. Assuming the database you want to >restore your "c:\db.bak" file into a database named "newdb" and you are >using SQL Server 2000
>You could try to restore it with SQL Server Enterprise Manager by following >steps
>1. Open SQL Server Enterprise Manager and select the instance and "newdb"
>2. Right Click "newdb" -> All Tasks -> Restore database
>3. Select "From device" in General Tab
>4. Click Select Devices=A1=AD
>5. Add the file you want in the pop dialog
>6. Click OK and you will see Restore Process Dialog and then you will be >notified that you have successfully restored your database.
>
>Also, you could try to restore it with Query Analyzer by following steps:
>1. Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
>2. type the T-SQL
>RESTORE DATABASE newdb from disk=3D'c:\db.bak'
>3. you will get the result like >RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485 >MB/sec).
>
>Bottom line - have a good backup strategy.
>Please feel free to post in the group if this solves your problem or if you >would like further help. We are here to be of assistance!
>Sincerely yours,
>Michael Cheng
>Microsoft Online Support
>*********************************************************
**
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and confers no rights.
>Please reply to newsgroups only, many thanks.
>.
>|||I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.|||You can backup and restore the one user database to another
server. It's done frequently. You still need to be aware of
anything configured or setup on the source server and issues
you could run into related to this. Examples of some things
to watch out for would be users in the database that don't
exist on the destination server, using linked servers,
cross-database stored procedures, etc.
-Sue
On Tue, 25 May 2004 17:01:04 -0700, "Ling"
<anonymous@.discussions.microsoft.com> wrote:
>I also need to "copy" one of our databases to a different server. Can I only backup and restore the one user database? Do I have to do anything with msdb, master, model.etc? Please advise.|||Hi,
Thank you for using MSDN Newsgroup!
It is highly appreciated that you should post your own questions as a new
thread, which, I believe, will get us closer and quicker to the resolutions
:)
In general, we need not do anything with other databases such as msdb,
master etc. However, I am not sure your detailed requirements. You are
welcome to raise your issue to the newsgroup by a new post
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Support
***********************************************************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Maybe these will help answer some questions related to moving db's:
http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly
SQL Server MVP
"Ling" <anonymous@.discussions.microsoft.com> wrote in message
news:C142BBB9-0F56-4354-9569-79A7038B7AB4@.microsoft.com...
> I also need to "copy" one of our databases to a different server. Can I
only backup and restore the one user database? Do I have to do anything
with msdb, master, model.etc? Please advise.|||Thank you all for your replies!!
Restoring database on a different server
Can a user database .bak be restored to another server
that has never had the database on it?
We lost a development server and reinstalled SQL Server I
have user database backups but no system database
backups. When I attempt a restore I cannot see the .bak
file that I would like to restore. I thought that I
could create a new database with the same name and then
restore the .bak to get the database back. is this an
incorrect procedure?When you restore, choose "From Device" and locate the bak
file.
>--Original Message--
>Can a user database .bak be restored to another server
>that has never had the database on it?
>We lost a development server and reinstalled SQL Server
I
>have user database backups but no system database
>backups. When I attempt a restore I cannot see the .bak
>file that I would like to restore. I thought that I
>could create a new database with the same name and then
>restore the .bak to get the database back. is this an
>incorrect procedure?
>
>.
>|||>>Can a user database .bak be restored to another server that has never had
the database on it?
Yes.
restore
because it (the backup) was recorded on the original server.
You can restore with or without the database created first. Look for
RESTORE DATABASE in BOL.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:246d01c427d7$6392e640$a601280a@.phx.gbl...[vbcol=seagreen]
> Can a user database .bak be restored to another server
> that has never had the database on it?
> We lost a development server and reinstalled SQL Server I
> have user database backups but no system database
> backups. When I attempt a restore I cannot see the .bak
> file that I would like to restore. I thought that I
> could create a new database with the same name and then
> restore the .bak to get the database back. is this an
> incorrect procedure?
>|||Hi Jim,
Thanks for posting in the Newsgroup. Assuming the database you want to
restore your "c:\db.bak" file into a database named "newdb" and you are
using SQL Server 2000
You could try to restore it with SQL Server Enterprise Manager by following
steps
1. Open SQL Server Enterprise Manager and select the instance and "newdb"
2. Right Click "newdb" -> All Tasks -> Restore database
3. Select "From device" in General Tab
4. Click Select Devices_
5. Add the file you want in the pop dialog
6. Click OK and you will see Restore Process Dialog and then you will be
notified that you have successfully restored your database.
Also, you could try to restore it with Query Analyzer by following steps:
1. Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
2. type the T-SQL
RESTORE DATABASE newdb from disk='c:\db.bak'
3. you will get the result like
RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485
MB/sec).
Bottom line - have a good backup strategy.
Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Thanks for the quick reply. The restore went well.
>--Original Message--
that has never had[vbcol=seagreen]
>the database on it?
>Yes.
>
that I would like to[vbcol=seagreen]
>restore
>because it (the backup) was recorded on the original
server.
>You can restore with or without the database created
first. Look for
>RESTORE DATABASE in BOL.
>
>"Jim Abel" <jim.abel@.lmco.com> wrote in message
>news:246d01c427d7$6392e640$a601280a@.phx.gbl...
Server I[vbcol=seagreen]
>
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>When you restore, choose "From Device" and locate the
bak
>file.
>I
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>Hi Jim,
>Thanks for posting in the Newsgroup. Assuming the=20
database you want to=20
>restore your "c:\db.bak" file into a database=20
named "newdb" and you are=20
>using SQL Server 2000
>You could try to restore it with SQL Server Enterprise=20
Manager by following=20
>steps
>1. Open SQL Server Enterprise Manager and select the=20
instance and "newdb"
>2. Right Click "newdb" -> All Tasks -> Restore=20
database
>3. Select "From device" in General Tab
>4. Click Select Devices=A1=AD
>5. Add the file you want in the pop dialog
>6. Click OK and you will see Restore Process Dialog=20
and then you will be=20
>notified that you have successfully restored your=20
database.
>
>Also, you could try to restore it with Query Analyzer by=20
following steps:
>1. Open Query Analyzer, Login as sa (or whatever=20
have RESTORE privilege)
>2. type the T-SQL
>RESTORE DATABASE newdb from disk=3D'c:\db.bak'
>3. you will get the result like=20
>RESTORE DATABASE successfully processed 113 pages in=20
0.370 seconds (2.485=20
>MB/sec).
>
>Bottom line - have a good backup strategy.
>Please feel free to post in the group if this solves=20
your problem or if you=20
>would like further help. We are here to be of assistance!
>Sincerely yours,
>Michael Cheng
>Microsoft Online Support
> ****************************************
*****************
**
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and=20
confers no rights.
>Please reply to newsgroups only, many thanks.
>.
>|||I also need to "copy" one of our databases to a different server. Can I onl
y backup and restore the one user database? Do I have to do anything with m
sdb, master, model.etc? Please advise.|||You can backup and restore the one user database to another
server. It's done frequently. You still need to be aware of
anything configured or setup on the source server and issues
you could run into related to this. Examples of some things
to watch out for would be users in the database that don't
exist on the destination server, using linked servers,
cross-database stored procedures, etc.
-Sue
On Tue, 25 May 2004 17:01:04 -0700, "Ling"
<anonymous@.discussions.microsoft.com> wrote:
>I also need to "copy" one of our databases to a different server. Can I only backu
p and restore the one user database? Do I have to do anything with msdb, master, mo
del.etc? Please advise.|||Hi,
Thank you for using MSDN Newsgroup!
It is highly appreciated that you should post your own questions as a new
thread, which, I believe, will get us closer and quicker to the resolutions
In general, we need not do anything with other databases such as msdb,
master etc. However, I am not sure your detailed requirements. You are
welcome to raise your issue to the newsgroup by a new post
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
that has never had the database on it?
We lost a development server and reinstalled SQL Server I
have user database backups but no system database
backups. When I attempt a restore I cannot see the .bak
file that I would like to restore. I thought that I
could create a new database with the same name and then
restore the .bak to get the database back. is this an
incorrect procedure?When you restore, choose "From Device" and locate the bak
file.
>--Original Message--
>Can a user database .bak be restored to another server
>that has never had the database on it?
>We lost a development server and reinstalled SQL Server
I
>have user database backups but no system database
>backups. When I attempt a restore I cannot see the .bak
>file that I would like to restore. I thought that I
>could create a new database with the same name and then
>restore the .bak to get the database back. is this an
>incorrect procedure?
>
>.
>|||>>Can a user database .bak be restored to another server that has never had
the database on it?
Yes.
restore
because it (the backup) was recorded on the original server.
You can restore with or without the database created first. Look for
RESTORE DATABASE in BOL.
"Jim Abel" <jim.abel@.lmco.com> wrote in message
news:246d01c427d7$6392e640$a601280a@.phx.gbl...[vbcol=seagreen]
> Can a user database .bak be restored to another server
> that has never had the database on it?
> We lost a development server and reinstalled SQL Server I
> have user database backups but no system database
> backups. When I attempt a restore I cannot see the .bak
> file that I would like to restore. I thought that I
> could create a new database with the same name and then
> restore the .bak to get the database back. is this an
> incorrect procedure?
>|||Hi Jim,
Thanks for posting in the Newsgroup. Assuming the database you want to
restore your "c:\db.bak" file into a database named "newdb" and you are
using SQL Server 2000
You could try to restore it with SQL Server Enterprise Manager by following
steps
1. Open SQL Server Enterprise Manager and select the instance and "newdb"
2. Right Click "newdb" -> All Tasks -> Restore database
3. Select "From device" in General Tab
4. Click Select Devices_
5. Add the file you want in the pop dialog
6. Click OK and you will see Restore Process Dialog and then you will be
notified that you have successfully restored your database.
Also, you could try to restore it with Query Analyzer by following steps:
1. Open Query Analyzer, Login as sa (or whatever have RESTORE privilege)
2. type the T-SQL
RESTORE DATABASE newdb from disk='c:\db.bak'
3. you will get the result like
RESTORE DATABASE successfully processed 113 pages in 0.370 seconds (2.485
MB/sec).
Bottom line - have a good backup strategy.
Please feel free to post in the group if this solves your problem or if you
would like further help. We are here to be of assistance!
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.|||Thanks for the quick reply. The restore went well.
>--Original Message--
that has never had[vbcol=seagreen]
>the database on it?
>Yes.
>
that I would like to[vbcol=seagreen]
>restore
>because it (the backup) was recorded on the original
server.
>You can restore with or without the database created
first. Look for
>RESTORE DATABASE in BOL.
>
>"Jim Abel" <jim.abel@.lmco.com> wrote in message
>news:246d01c427d7$6392e640$a601280a@.phx.gbl...
Server I[vbcol=seagreen]
>
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>When you restore, choose "From Device" and locate the
bak
>file.
>I
>.
>|||Thanks for the quick reply. The restore went well.
>--Original Message--
>Hi Jim,
>Thanks for posting in the Newsgroup. Assuming the=20
database you want to=20
>restore your "c:\db.bak" file into a database=20
named "newdb" and you are=20
>using SQL Server 2000
>You could try to restore it with SQL Server Enterprise=20
Manager by following=20
>steps
>1. Open SQL Server Enterprise Manager and select the=20
instance and "newdb"
>2. Right Click "newdb" -> All Tasks -> Restore=20
database
>3. Select "From device" in General Tab
>4. Click Select Devices=A1=AD
>5. Add the file you want in the pop dialog
>6. Click OK and you will see Restore Process Dialog=20
and then you will be=20
>notified that you have successfully restored your=20
database.
>
>Also, you could try to restore it with Query Analyzer by=20
following steps:
>1. Open Query Analyzer, Login as sa (or whatever=20
have RESTORE privilege)
>2. type the T-SQL
>RESTORE DATABASE newdb from disk=3D'c:\db.bak'
>3. you will get the result like=20
>RESTORE DATABASE successfully processed 113 pages in=20
0.370 seconds (2.485=20
>MB/sec).
>
>Bottom line - have a good backup strategy.
>Please feel free to post in the group if this solves=20
your problem or if you=20
>would like further help. We are here to be of assistance!
>Sincerely yours,
>Michael Cheng
>Microsoft Online Support
> ****************************************
*****************
**
>Get Secure! - www.microsoft.com/security
>This posting is provided "as is" with no warranties and=20
confers no rights.
>Please reply to newsgroups only, many thanks.
>.
>|||I also need to "copy" one of our databases to a different server. Can I onl
y backup and restore the one user database? Do I have to do anything with m
sdb, master, model.etc? Please advise.|||You can backup and restore the one user database to another
server. It's done frequently. You still need to be aware of
anything configured or setup on the source server and issues
you could run into related to this. Examples of some things
to watch out for would be users in the database that don't
exist on the destination server, using linked servers,
cross-database stored procedures, etc.
-Sue
On Tue, 25 May 2004 17:01:04 -0700, "Ling"
<anonymous@.discussions.microsoft.com> wrote:
>I also need to "copy" one of our databases to a different server. Can I only backu
p and restore the one user database? Do I have to do anything with msdb, master, mo
del.etc? Please advise.|||Hi,
Thank you for using MSDN Newsgroup!
It is highly appreciated that you should post your own questions as a new
thread, which, I believe, will get us closer and quicker to the resolutions
In general, we need not do anything with other databases such as msdb,
master etc. However, I am not sure your detailed requirements. You are
welcome to raise your issue to the newsgroup by a new post
Thank you for your patience and cooperation.
Sincerely yours,
Michael Cheng
Microsoft Online Support
****************************************
*******************
Get Secure! - www.microsoft.com/security
This posting is provided "as is" with no warranties and confers no rights.
Please reply to newsgroups only, many thanks.
Restoring database in SQL - 7
Hi,
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match th
e
physical locations on the machine you are restoring to.
"h" wrote:
> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but
I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match th
e
physical locations on the machine you are restoring to.
"h" wrote:
> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but
I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>
Restoring database in SQL - 7
Hi,
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match the
physical locations on the machine you are restoring to.
"h" wrote:
> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>
my problem is :-
I have installed SQL - 7 at my home and work. instance name of both SQL is
differ.
I have created mydata.bak file at home and trying to restore at work, but I
am fail .
Any hints will be sincerly appreciated.
Hardik ShahJust taking a stab in the dark, since you have not provided what error
message you are getting. If you are trying to restore using Enterprise
Manager, you must click the Options tab, and check 'force restore over
existing database'. Then, change the location of the data files to match the
physical locations on the machine you are restoring to.
"h" wrote:
> Hi,
> my problem is :-
> I have installed SQL - 7 at my home and work. instance name of both SQL is
> differ.
> I have created mydata.bak file at home and trying to restore at work, but I
> am fail .
> Any hints will be sincerly appreciated.
> Hardik Shah
>
>
>
Subscribe to:
Posts (Atom)