restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong ?
TIA
atte,
Hernn
atte,
Hernn
ohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me ?
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong ?
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
|
|||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me ?
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong ?
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
atte,
Hernn
atte,
Hernnohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
atte,
Hernn
"bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernn
|
| --
| atte,
| Hernn
|
||||Already replied in .programming. Please don't multi-post.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernn
> "bajopalabra" <bajopalabra@.hotmail.com> escribi en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernn
> |
> | --
> | atte,
> | Hernn
> |
> |
>

restoring with StopBeforeMark

hi
i did :
create table pp ( id int )
backup full
insert into pp values ( 1 )
backup diff
insert into pp values ( 2 )
backup log 1
insert into pp values ( 3 )
backup log 2
insert into pp values ( 4 )
backup log 3 -- tail
i want to restore
the rows 1, 2, and 3
the way i'm trying is this :
restore database [xx] from TheBackUp with
NOrecovery,
StopBeforeMark= 'LSN of backup log 4'
but,
i get a table with no rows
what is wrong '
TIA
--
atte,
Hernán
--
atte,
Hernánohhhh...
i understand now...
i have to restore
the whole the backup chain
from the first to the last
but using the StopBeforeClause...
is there a way to restore
the whole backup chain in one line ?
should i to write a "loop"
for restore each row
that RESTORE HEADERONLY bring me '
--
atte,
Hernán
"bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
| hi
| i did :
|
| create table pp ( id int )
| backup full
| insert into pp values ( 1 )
| backup diff
| insert into pp values ( 2 )
| backup log 1
| insert into pp values ( 3 )
| backup log 2
| insert into pp values ( 4 )
| backup log 3 -- tail
|
| i want to restore
| the rows 1, 2, and 3
|
| the way i'm trying is this :
|
| restore database [xx] from TheBackUp with
| NOrecovery,
| StopBeforeMark= 'LSN of backup log 4'
|
| but,
| i get a table with no rows
|
| what is wrong '
|
| TIA
|
| --
| atte,
| Hernán
|
| --
| atte,
| Hernán
|
||||Already replied in .programming. Please don't multi-post.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"bajopalabra" <bajopalabra@.hotmail.com> wrote in message
news:uVMa5gn3GHA.4976@.TK2MSFTNGP02.phx.gbl...
> ohhhh...
> i understand now...
> i have to restore
> the whole the backup chain
> from the first to the last
> but using the StopBeforeClause...
> is there a way to restore
> the whole backup chain in one line ?
> should i to write a "loop"
> for restore each row
> that RESTORE HEADERONLY bring me '
> --
> atte,
> Hernán
> "bajopalabra" <bajopalabra@.hotmail.com> escribió en el mensaje
> news:%23kiu9On3GHA.696@.TK2MSFTNGP03.phx.gbl...
> | hi
> | i did :
> |
> | create table pp ( id int )
> | backup full
> | insert into pp values ( 1 )
> | backup diff
> | insert into pp values ( 2 )
> | backup log 1
> | insert into pp values ( 3 )
> | backup log 2
> | insert into pp values ( 4 )
> | backup log 3 -- tail
> |
> | i want to restore
> | the rows 1, 2, and 3
> |
> | the way i'm trying is this :
> |
> | restore database [xx] from TheBackUp with
> | NOrecovery,
> | StopBeforeMark= 'LSN of backup log 4'
> |
> | but,
> | i get a table with no rows
> |
> | what is wrong '
> |
> | TIA
> |
> | --
> | atte,
> | Hernán
> |
> | --
> | atte,
> | Hernán
> |
> |
>

Restoring using smo

I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):

db1 = New Management.Smo.Database

db1.Name = Me.lstDatabase.SelectedItem

restore = New Management.Smo.Restore

restore.Action = Management.Smo.RestoreActionType.Database

restore.Database = db1.Name

backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)

restore.Devices.Add(backDeviceItem)

restore.ReplaceDatabase = True

restore.SqlRestore(SqlServerSelection)

In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?

Thanks...

Scott

What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.

db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restrictedsql

Restoring using smo

I have built a VB.NET app that uses SMO to restore a database from a backup device to a database. This works great if I restore the backup into the original database. If, however, i try to restore the backup into ANOTHER database, it fails. My code looks like this (only important parts are included):

db1 = New Management.Smo.Database

db1.Name = Me.lstDatabase.SelectedItem

restore = New Management.Smo.Restore

restore.Action = Management.Smo.RestoreActionType.Database

restore.Database = db1.Name

backDeviceItem = New Management.Smo.BackupDeviceItem(Me.lstBackups.Text, Management.Smo.DeviceType.LogicalDevice)

restore.Devices.Add(backDeviceItem)

restore.ReplaceDatabase = True

restore.SqlRestore(SqlServerSelection)

In this example, the lstBackups contains the list of backup devices. lstDatabase contains the list of databases to restore into. I can successfully connect to the database, but the restore fails. Any ideas?

Thanks...

Scott

What privilege level is the user attempting to do the restore? While a db_owner user can back up a database, the restore is in essence a create database process, which requires sysadmin level privileges.|||Also the database has to be in restricted mode.

db1.DatabaseOptions.UserAccess = DatabseUserAccess.Restricted

Restoring Transaction Logs Into Backups of MDF and LDF

Hello,
Here's a brief outline of my scenario:
I am trying to recover from a database corruption problem. I have a
backup of the db's physical MDF and LDF files from before the
corruption, but no actual backup (ie bak file). I also have daily
transaction log backups right up to today, and my live db is still
operation despite the corruption, so I can take another transaction
log backup when I do the repair.
The problem I have is I don't know how to, or if it's possible to
attach the backup MDF & LDF files and then somehow apply my
transaction log backups to build a new up to date database.
Thanks,
AlexYou would need Attach with NORECOVERY, which currently is not a possibility.
There is a connect item you can vote in for a future feature request.
https://connect.microsoft.com/SQLServer/feedback/ViewFeedback.aspx?FeedbackID=270651
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>|||Alex,
If you don't mind my asking, what kind of corruption did you get and how did
it happen. We're just moving to SQLServer to avoid corruptions and I'd hate
to find that we did not leave the problem behind.
Regards
Brian Morris
<cosmasdev@.yahoo.com> wrote in message
news:1177078845.141070.271000@.n59g2000hsh.googlegroups.com...
> Hello,
> Here's a brief outline of my scenario:
> I am trying to recover from a database corruption problem. I have a
> backup of the db's physical MDF and LDF files from before the
> corruption, but no actual backup (ie bak file). I also have daily
> transaction log backups right up to today, and my live db is still
> operation despite the corruption, so I can take another transaction
> log backup when I do the repair.
> The problem I have is I don't know how to, or if it's possible to
> attach the backup MDF & LDF files and then somehow apply my
> transaction log backups to build a new up to date database.
> Thanks,
> Alex
>

Restoring Transaction Logs

I have a customer that needs to restore a database to
midnite on 12/31. They have a full database backup at
3pm, 2 transaction log without truncate backups and a
transaction log with truncate at midnite. I would assume
that you would run a restore on the full database backup
and then run a restore on the midnite trans log with
truncate with recover database selected. Would it be
necessary to include any of the transaction logs without
tuncate in the restore?
What is confusing to me is the transaction log backups
keep decreasing in size. I would think that if you don't
truncate, the sizes of the transaction log backups would
keep increasing until you truncated. Any help would be
appriciated.
ZackIn principle you will need to restore the most recent full db backup, =then ALL log backups since the full backup. The cause of your confusion =is (I guess) that backing up a log makes the space available for re-use, =The final log backup (that you describe as "transaction log with =truncate at midnite" is I expect not really a log backup at all, can you =confirm if this was taken via the SQL statement BACKUP LOG ... WITH =TRUNCATE ONLY, if so it has truncated the log not backed it up. In which =case the best you can get is to restore the full backup (WITH =NORECOVERY), the first log (WITH NORECOVERY) and the second log (WITH =RECOVERY).
If youre midnight log backup is a 'TRUNCATE ONLY" you are expposed from =a recovery point of view until 3pm the following day, since once the log =has been truncated it cannot be used to rollforward transactions until =the next full backup has been taken.
Hope that helps
Mike John
"Zack Godwin" <zgodwin@.freightsystemsinc.com> wrote in message =news:052b01c3d46b$0a7254c0$a001280a@.phx.gbl...
> I have a customer that needs to restore a database to > midnite on 12/31. They have a full database backup at > 3pm, 2 transaction log without truncate backups and a > transaction log with truncate at midnite. I would assume > that you would run a restore on the full database backup > and then run a restore on the midnite trans log with > truncate with recover database selected. Would it be > necessary to include any of the transaction logs without > tuncate in the restore? > > What is confusing to me is the transaction log backups > keep decreasing in size. I would think that if you don't > truncate, the sizes of the transaction log backups would > keep increasing until you truncated. Any help would be > appriciated.
> > Zack

Restoring to new database doesn't default DB file names

In SQL 2000 EM, when you restore a database from an existing database backup but specify a new database name to restore to, the filenames are automatically updated to match the new name of the database that will be restored.

I tried to do this with SQL 2005 Management Studio but it appears that the filenames aren't updated i.e. they are the same as the original database filenames. Do i have to manually go and change the filenames to reflect the fact that I'm creating a new database via a restore? If so, why doesn't the behaiour follow that of SQL 2000?

Thanks

Hello there

This is by design in SQL Server 2005.

There are two ways to restore to a new database:
1- By launching the restore database dialog from the context of an existing database. This would pre-load the existing backup set. You could change the database name & the dialog would automatically change the DB filenames for you.

2- By launching the resotore database from the 'Databases' node & later specifying the backup set by selecting a backup file thru 'device' option.

#1 above should work fine when you change the database name & hit 'Ok'

#2 above doesn't automatically change the DB file names for you. This is by design in SQL Server 2005. In SQL Server 2000 too, you had to go to the 'options' page for the dialog to automatically change file names.

The grid on the options page in SQL Server 2005's restore dialog is editable. You could directly edit the file names in the grid to accomplish the action.

Thanks,

Restoring to new database doesn't change logical DB name

I created a new database from a restore backup method. The database file names did get updated with the new database name, but the logical file name still remains the old file name.
Do we have to manually change the logical file name in the new database?
what is the use of logical name?
Please help!

The logical file will not change while u restore a db. you have to change it explicitly

alter database gpx mODIFY FILE (NAME=oldLogicalFilename,NEWNAME=NewLogicalFilename)

Madhu

|||

what is the use of logical name?

The name to represent the database data file. The logical name for each file is contained in the name column

The logical design of the database, including the tables and the relationships between them, is the core of an optimized relational database. A good logical database design can lay the foundation for optimal database and application performance. A poor logical database design can hinder the performance of the whole system.

restoring to 2005 doesnt work

Hi peeps
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
DanHi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan|||Cheers John, all sorted now!
"John Bell" wrote:

> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc mak
e
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
>
>

restoring to 2005 doesnt work

Hi peeps
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
Dan
Hi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan
|||Cheers John, all sorted now!
"John Bell" wrote:

> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc make
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
>
>

restoring to 2005 doesnt work

Hi peeps
Bit of a problem... I did a backup of a database on SQL Server 2000 which
I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
Developer Ed), but I'm getting the following error...
Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
operating system system error 21 (the device is not ready).
--
I can't think of any reason why this would be happening, apart from the fact
that I've changed the DOMAIN of the network from DSERV to something else, so
is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
missing?
Cheers
DanHi
It looks like your drives/locations don't match the original server? Try
using the move option to specify a new location. If this is a SAN disc make
sure it has been allocated to SQL Server in Cluster Manager.
John
"musosdev" <musoswire@.community.nospam> wrote in message
news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> Hi peeps
> Bit of a problem... I did a backup of a database on SQL Server 2000 which
> I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> Developer Ed), but I'm getting the following error...
> Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> operating system system error 21 (the device is not ready).
> --
> I can't think of any reason why this would be happening, apart from the
> fact
> that I've changed the DOMAIN of the network from DSERV to something else,
> so
> is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> missing?
> Cheers
>
> Dan|||Cheers John, all sorted now!
"John Bell" wrote:
> Hi
> It looks like your drives/locations don't match the original server? Try
> using the move option to specify a new location. If this is a SAN disc make
> sure it has been allocated to SQL Server in Cluster Manager.
> John
> "musosdev" <musoswire@.community.nospam> wrote in message
> news:E748D4DB-2FB8-4C16-A2A3-12FBC980F9C7@.microsoft.com...
> > Hi peeps
> >
> > Bit of a problem... I did a backup of a database on SQL Server 2000 which
> > I'm now trying to restore to my newly setup server (Win2k3, SQL 2005
> > Developer Ed), but I'm getting the following error...
> >
> > Restore failed for server 'SERVER'. (Microsoft.SqlServer.Smo)
> >
> > System.Data.SqlClient.SqlError: Directory lookup for the file "F:\Program
> > Files\Microsoft SQL Server\MSSQL$DSERV\Data\dartsV2.mdf" failed with the
> > operating system system error 21 (the device is not ready).
> >
> > --
> >
> > I can't think of any reason why this would be happening, apart from the
> > fact
> > that I've changed the DOMAIN of the network from DSERV to something else,
> > so
> > is it looking for MSSQL$DSERV that's causing the prob? Or something I'm
> > missing?
> >
> > Cheers
> >
> >
> >
> > Dan
>
>

Restoring the master to different drive letters

Can you restore the master database, running on windows server 2000, sql
server 2000 from a backup that was taken from c:\ to another server
restoring to d:\?

*** Sent via Developersdex http://www.developersdex.com ***
Don't just participate in USENET...get rewarded for it!Hi

See:
http://support.microsoft.com/defaul...ben-us%3b224071

John

"spec_server" <spec_9@.nwy.com> wrote in message
news:415c6810$0$26132$c397aba@.news.newsgroups.ws.. .
> Can you restore the master database, running on windows server 2000, sql
> server 2000 from a backup that was taken from c:\ to another server
> restoring to d:\?
>
> *** Sent via Developersdex http://www.developersdex.com ***
> Don't just participate in USENET...get rewarded for it!

Restoring the master database

Hello,
I am going throught my disaster recovery. I am at the point where I have a
nightly databases backup and I am backing up the transaction logs frequently
during the day.
I am having the problem that i am trying to restore the backedup master
database, but every time I restore it, I cannot get SQL to run again, so I
have to re-build the master database. I have tried doing it with Enterprise
manager and with Querry analizer, in both cases I get the message 'master
database succesfully recover and then it is shutting donw the service right
away, at this point I cannot re-start the service, am I missing something?
Please help
Joe HernandezMy guess is that the master database you have restored specifies that tempdb
and/or model should be
located on some other place compared to where they are on your restored syst
em.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> Hello,
> I am going throught my disaster recovery. I am at the point where I have a
> nightly databases backup and I am backing up the transaction logs frequent
ly
> during the day.
> I am having the problem that i am trying to restore the backedup master
> database, but every time I restore it, I cannot get SQL to run again, so I
> have to re-build the master database. I have tried doing it with Enterpris
e
> manager and with Querry analizer, in both cases I get the message 'master
> database succesfully recover and then it is shutting donw the service righ
t
> away, at this point I cannot re-start the service, am I missing something?
> Please help
> Joe Hernandez|||Please look at the error log , what error you found in it?
Regards
Amish|||Hello,
I am very new at SQL, can you tell me how to look up the error log?
"amish" wrote:

> Please look at the error log , what error you found in it?
> Regards
> Amish
>|||I dont know how would I tell that.
So do I need to restore the tempdb and the model before I restore the
masterdb?
thanks,
Joe H
"Tibor Karaszi" wrote:

> My guess is that the master database you have restored specifies that temp
db and/or model should be
> located on some other place compared to where they are on your restored sy
stem.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>|||Check sysdatabases on the old installation for the original path for those d
atabases. Or the
errorlog file. You can then create a directory as the original and have copy
the files of those
databases so that they exist in the original location. Then use KB 224071 to
move those databases to
the desired location. Some articles that might be helpful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scr...sp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...[vbcol=seagreen]
>I dont know how would I tell that.
> So do I need to restore the tempdb and the model before I restore the
> masterdb?
> thanks,
> Joe H
> "Tibor Karaszi" wrote:
>|||Tibor,
Thanks for all your help.
Do you know if it makes a difference if I am using 2 different instaces
names? should I have the same instance name on my production server as well
as my test server?
Thanks,
Joe Hernandez
"Tibor Karaszi" wrote:

> Check sysdatabases on the old installation for the original path for those
databases. Or the
> errorlog file. You can then create a directory as the original and have co
py the files of those
> databases so that they exist in the original location. Then use KB 224071
to move those databases to
> the desired location. Some articles that might be helpful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scr...sp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>|||For 2000, the instance name is part for the directory structure for SQL Serv
er. So, it will probably
be easier of you have the same instance name, more likely that you will have
the same directory
structure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...[vbcol=seagreen]
> Tibor,
> Thanks for all your help.
> Do you know if it makes a difference if I am using 2 different instaces
> names? should I have the same instance name on my production server as wel
l
> as my test server?
> Thanks,
> Joe Hernandez
> "Tibor Karaszi" wrote:
>|||you will find it in log folder in the directory where you installed
microsoft sql server.
Open the file Errorlog in notepad. It will show you the error why sql
server not starts.
Post it here to identify the reason why Services not starting.
Regards
Amish|||Tibor,
After I changed the instance name in SQL200, I had no problems restoring the
master database and right after was able to re-start server, took it off fro
m
single user mode, have restore all my other databases and I am running full
boat on the test server.
I want to thank you all that helped me and hope that others can benefit from
this issue.
Joe Hernandez
"Tibor Karaszi" wrote:

> For 2000, the instance name is part for the directory structure for SQL Se
rver. So, it will probably
> be easier of you have the same instance name, more likely that you will ha
ve the same directory
> structure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
>

Restoring the master database

Hello,
I am going throught my disaster recovery. I am at the point where I have a
nightly databases backup and I am backing up the transaction logs frequently
during the day.
I am having the problem that i am trying to restore the backedup master
database, but every time I restore it, I cannot get SQL to run again, so I
have to re-build the master database. I have tried doing it with Enterprise
manager and with Querry analizer, in both cases I get the message 'master
database succesfully recover and then it is shutting donw the service right
away, at this point I cannot re-start the service, am I missing something?
Please help
Joe Hernandez
My guess is that the master database you have restored specifies that tempdb and/or model should be
located on some other place compared to where they are on your restored system.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> Hello,
> I am going throught my disaster recovery. I am at the point where I have a
> nightly databases backup and I am backing up the transaction logs frequently
> during the day.
> I am having the problem that i am trying to restore the backedup master
> database, but every time I restore it, I cannot get SQL to run again, so I
> have to re-build the master database. I have tried doing it with Enterprise
> manager and with Querry analizer, in both cases I get the message 'master
> database succesfully recover and then it is shutting donw the service right
> away, at this point I cannot re-start the service, am I missing something?
> Please help
> Joe Hernandez
|||Please look at the error log , what error you found in it?
Regards
Amish
|||Hello,
I am very new at SQL, can you tell me how to look up the error log?
"amish" wrote:

> Please look at the error log , what error you found in it?
> Regards
> Amish
>
|||I dont know how would I tell that.
So do I need to restore the tempdb and the model before I restore the
masterdb?
thanks,
Joe H
"Tibor Karaszi" wrote:

> My guess is that the master database you have restored specifies that tempdb and/or model should be
> located on some other place compared to where they are on your restored system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>
|||Check sysdatabases on the old installation for the original path for those databases. Or the
errorlog file. You can then create a directory as the original and have copy the files of those
databases so that they exist in the original location. Then use KB 224071 to move those databases to
the desired location. Some articles that might be helpful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scri...p?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...[vbcol=seagreen]
>I dont know how would I tell that.
> So do I need to restore the tempdb and the model before I restore the
> masterdb?
> thanks,
> Joe H
> "Tibor Karaszi" wrote:
|||Tibor,
Thanks for all your help.
Do you know if it makes a difference if I am using 2 different instaces
names? should I have the same instance name on my production server as well
as my test server?
Thanks,
Joe Hernandez
"Tibor Karaszi" wrote:

> Check sysdatabases on the old installation for the original path for those databases. Or the
> errorlog file. You can then create a directory as the original and have copy the files of those
> databases so that they exist in the original location. Then use KB 224071 to move those databases to
> the desired location. Some articles that might be helpful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scri...p?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>
|||For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
be easier of you have the same instance name, more likely that you will have the same directory
structure.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...[vbcol=seagreen]
> Tibor,
> Thanks for all your help.
> Do you know if it makes a difference if I am using 2 different instaces
> names? should I have the same instance name on my production server as well
> as my test server?
> Thanks,
> Joe Hernandez
> "Tibor Karaszi" wrote:
|||you will find it in log folder in the directory where you installed
microsoft sql server.
Open the file Errorlog in notepad. It will show you the error why sql
server not starts.
Post it here to identify the reason why Services not starting.
Regards
Amish
|||Tibor,
After I changed the instance name in SQL200, I had no problems restoring the
master database and right after was able to re-start server, took it off from
single user mode, have restore all my other databases and I am running full
boat on the test server.
I want to thank you all that helped me and hope that others can benefit from
this issue.
Joe Hernandez
"Tibor Karaszi" wrote:

> For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
> be easier of you have the same instance name, more likely that you will have the same directory
> structure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
>

Restoring the master database

Hello,
I am going throught my disaster recovery. I am at the point where I have a
nightly databases backup and I am backing up the transaction logs frequently
during the day.
I am having the problem that i am trying to restore the backedup master
database, but every time I restore it, I cannot get SQL to run again, so I
have to re-build the master database. I have tried doing it with Enterprise
manager and with Querry analizer, in both cases I get the message 'master
database succesfully recover and then it is shutting donw the service right
away, at this point I cannot re-start the service, am I missing something?
Please help
Joe HernandezMy guess is that the master database you have restored specifies that tempdb and/or model should be
located on some other place compared to where they are on your restored system.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> Hello,
> I am going throught my disaster recovery. I am at the point where I have a
> nightly databases backup and I am backing up the transaction logs frequently
> during the day.
> I am having the problem that i am trying to restore the backedup master
> database, but every time I restore it, I cannot get SQL to run again, so I
> have to re-build the master database. I have tried doing it with Enterprise
> manager and with Querry analizer, in both cases I get the message 'master
> database succesfully recover and then it is shutting donw the service right
> away, at this point I cannot re-start the service, am I missing something?
> Please help
> Joe Hernandez|||Please look at the error log , what error you found in it?
Regards
Amish|||Hello,
I am very new at SQL, can you tell me how to look up the error log?
"amish" wrote:
> Please look at the error log , what error you found in it?
> Regards
> Amish
>|||I dont know how would I tell that.
So do I need to restore the tempdb and the model before I restore the
masterdb?
thanks,
Joe H
"Tibor Karaszi" wrote:
> My guess is that the master database you have restored specifies that tempdb and/or model should be
> located on some other place compared to where they are on your restored system.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> > Hello,
> >
> > I am going throught my disaster recovery. I am at the point where I have a
> > nightly databases backup and I am backing up the transaction logs frequently
> > during the day.
> >
> > I am having the problem that i am trying to restore the backedup master
> > database, but every time I restore it, I cannot get SQL to run again, so I
> > have to re-build the master database. I have tried doing it with Enterprise
> > manager and with Querry analizer, in both cases I get the message 'master
> > database succesfully recover and then it is shutting donw the service right
> > away, at this point I cannot re-start the service, am I missing something?
> >
> > Please help
> >
> > Joe Hernandez
>|||Check sysdatabases on the old installation for the original path for those databases. Or the
errorlog file. You can then create a directory as the original and have copy the files of those
databases so that they exist in the original location. Then use KB 224071 to move those databases to
the desired location. Some articles that might be helpful:
Have a look at this list compiled by Andrew Kelly:
Moving DB's between Servers
http://www.support.microsoft.com/?id=314546
Moving SQL Server Databases to a New Location with Detach/Attach
http://www.support.microsoft.com/?id=224071
Using WITH MOVE in a Restore
http://support.microsoft.com/?id=221465
How To Transfer Logins and Passwords Between SQL Servers
http://www.support.microsoft.com/?id=246133
Mapping Logins & SIDs after a Restore
http://www.support.microsoft.com/?id=298897
Utility to map logins to users
http://www.dbmaint.com/SyncSql Logins.asp
User Logon and/or Permission Errors After Restoring Dump
http://www.support.microsoft.com/?id=168001
How to Resolve Permission Issues When a Database Is Moved Between SQL
Servers
http://www.support.microsoft.com/?id=240872
Restoring a .mdf
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Disaster Recovery Articles for SQL Server
http://www.support.microsoft.com/?id=307775
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>I dont know how would I tell that.
> So do I need to restore the tempdb and the model before I restore the
> masterdb?
> thanks,
> Joe H
> "Tibor Karaszi" wrote:
>> My guess is that the master database you have restored specifies that tempdb and/or model should
>> be
>> located on some other place compared to where they are on your restored system.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>> > Hello,
>> >
>> > I am going throught my disaster recovery. I am at the point where I have a
>> > nightly databases backup and I am backing up the transaction logs frequently
>> > during the day.
>> >
>> > I am having the problem that i am trying to restore the backedup master
>> > database, but every time I restore it, I cannot get SQL to run again, so I
>> > have to re-build the master database. I have tried doing it with Enterprise
>> > manager and with Querry analizer, in both cases I get the message 'master
>> > database succesfully recover and then it is shutting donw the service right
>> > away, at this point I cannot re-start the service, am I missing something?
>> >
>> > Please help
>> >
>> > Joe Hernandez
>>|||Tibor,
Thanks for all your help.
Do you know if it makes a difference if I am using 2 different instaces
names? should I have the same instance name on my production server as well
as my test server?
Thanks,
Joe Hernandez
"Tibor Karaszi" wrote:
> Check sysdatabases on the old installation for the original path for those databases. Or the
> errorlog file. You can then create a directory as the original and have copy the files of those
> databases so that they exist in the original location. Then use KB 224071 to move those databases to
> the desired location. Some articles that might be helpful:
> Have a look at this list compiled by Andrew Kelly:
> Moving DB's between Servers
> http://www.support.microsoft.com/?id=314546
> Moving SQL Server Databases to a New Location with Detach/Attach
> http://www.support.microsoft.com/?id=224071
> Using WITH MOVE in a Restore
> http://support.microsoft.com/?id=221465
> How To Transfer Logins and Passwords Between SQL Servers
> http://www.support.microsoft.com/?id=246133
> Mapping Logins & SIDs after a Restore
> http://www.support.microsoft.com/?id=298897
> Utility to map logins to users
> http://www.dbmaint.com/SyncSql Logins.asp
> User Logon and/or Permission Errors After Restoring Dump
> http://www.support.microsoft.com/?id=168001
> How to Resolve Permission Issues When a Database Is Moved Between SQL
> Servers
> http://www.support.microsoft.com/?id=240872
> Restoring a .mdf
> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> Disaster Recovery Articles for SQL Server
> http://www.support.microsoft.com/?id=307775
>
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
> >I dont know how would I tell that.
> >
> > So do I need to restore the tempdb and the model before I restore the
> > masterdb?
> >
> > thanks,
> >
> > Joe H
> >
> > "Tibor Karaszi" wrote:
> >
> >> My guess is that the master database you have restored specifies that tempdb and/or model should
> >> be
> >> located on some other place compared to where they are on your restored system.
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> >> > Hello,
> >> >
> >> > I am going throught my disaster recovery. I am at the point where I have a
> >> > nightly databases backup and I am backing up the transaction logs frequently
> >> > during the day.
> >> >
> >> > I am having the problem that i am trying to restore the backedup master
> >> > database, but every time I restore it, I cannot get SQL to run again, so I
> >> > have to re-build the master database. I have tried doing it with Enterprise
> >> > manager and with Querry analizer, in both cases I get the message 'master
> >> > database succesfully recover and then it is shutting donw the service right
> >> > away, at this point I cannot re-start the service, am I missing something?
> >> >
> >> > Please help
> >> >
> >> > Joe Hernandez
> >>
> >>
>|||For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
be easier of you have the same instance name, more likely that you will have the same directory
structure.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
> Tibor,
> Thanks for all your help.
> Do you know if it makes a difference if I am using 2 different instaces
> names? should I have the same instance name on my production server as well
> as my test server?
> Thanks,
> Joe Hernandez
> "Tibor Karaszi" wrote:
>> Check sysdatabases on the old installation for the original path for those databases. Or the
>> errorlog file. You can then create a directory as the original and have copy the files of those
>> databases so that they exist in the original location. Then use KB 224071 to move those databases
>> to
>> the desired location. Some articles that might be helpful:
>> Have a look at this list compiled by Andrew Kelly:
>> Moving DB's between Servers
>> http://www.support.microsoft.com/?id=314546
>> Moving SQL Server Databases to a New Location with Detach/Attach
>> http://www.support.microsoft.com/?id=224071
>> Using WITH MOVE in a Restore
>> http://support.microsoft.com/?id=221465
>> How To Transfer Logins and Passwords Between SQL Servers
>> http://www.support.microsoft.com/?id=246133
>> Mapping Logins & SIDs after a Restore
>> http://www.support.microsoft.com/?id=298897
>> Utility to map logins to users
>> http://www.dbmaint.com/SyncSql Logins.asp
>> User Logon and/or Permission Errors After Restoring Dump
>> http://www.support.microsoft.com/?id=168001
>> How to Resolve Permission Issues When a Database Is Moved Between SQL
>> Servers
>> http://www.support.microsoft.com/?id=240872
>> Restoring a .mdf
>> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
>> Disaster Recovery Articles for SQL Server
>> http://www.support.microsoft.com/?id=307775
>>
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>> Blog: http://solidqualitylearning.com/blogs/tibor/
>>
>> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
>> >I dont know how would I tell that.
>> >
>> > So do I need to restore the tempdb and the model before I restore the
>> > masterdb?
>> >
>> > thanks,
>> >
>> > Joe H
>> >
>> > "Tibor Karaszi" wrote:
>> >
>> >> My guess is that the master database you have restored specifies that tempdb and/or model
>> >> should
>> >> be
>> >> located on some other place compared to where they are on your restored system.
>> >>
>> >> --
>> >> Tibor Karaszi, SQL Server MVP
>> >> http://www.karaszi.com/sqlserver/default.asp
>> >> http://www.solidqualitylearning.com/
>> >> Blog: http://solidqualitylearning.com/blogs/tibor/
>> >>
>> >>
>> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
>> >> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
>> >> > Hello,
>> >> >
>> >> > I am going throught my disaster recovery. I am at the point where I have a
>> >> > nightly databases backup and I am backing up the transaction logs frequently
>> >> > during the day.
>> >> >
>> >> > I am having the problem that i am trying to restore the backedup master
>> >> > database, but every time I restore it, I cannot get SQL to run again, so I
>> >> > have to re-build the master database. I have tried doing it with Enterprise
>> >> > manager and with Querry analizer, in both cases I get the message 'master
>> >> > database succesfully recover and then it is shutting donw the service right
>> >> > away, at this point I cannot re-start the service, am I missing something?
>> >> >
>> >> > Please help
>> >> >
>> >> > Joe Hernandez
>> >>
>> >>
>>|||you will find it in log folder in the directory where you installed
microsoft sql server.
Open the file Errorlog in notepad. It will show you the error why sql
server not starts.
Post it here to identify the reason why Services not starting.
Regards
Amish|||Tibor,
After I changed the instance name in SQL200, I had no problems restoring the
master database and right after was able to re-start server, took it off from
single user mode, have restore all my other databases and I am running full
boat on the test server.
I want to thank you all that helped me and hope that others can benefit from
this issue.
Joe Hernandez
"Tibor Karaszi" wrote:
> For 2000, the instance name is part for the directory structure for SQL Server. So, it will probably
> be easier of you have the same instance name, more likely that you will have the same directory
> structure.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> news:0C55B74D-03E2-4A73-BAD4-FA8E72BA27EE@.microsoft.com...
> > Tibor,
> >
> > Thanks for all your help.
> >
> > Do you know if it makes a difference if I am using 2 different instaces
> > names? should I have the same instance name on my production server as well
> > as my test server?
> >
> > Thanks,
> >
> > Joe Hernandez
> >
> > "Tibor Karaszi" wrote:
> >
> >> Check sysdatabases on the old installation for the original path for those databases. Or the
> >> errorlog file. You can then create a directory as the original and have copy the files of those
> >> databases so that they exist in the original location. Then use KB 224071 to move those databases
> >> to
> >> the desired location. Some articles that might be helpful:
> >>
> >> Have a look at this list compiled by Andrew Kelly:
> >>
> >> Moving DB's between Servers
> >> http://www.support.microsoft.com/?id=314546
> >>
> >> Moving SQL Server Databases to a New Location with Detach/Attach
> >> http://www.support.microsoft.com/?id=224071
> >>
> >> Using WITH MOVE in a Restore
> >> http://support.microsoft.com/?id=221465
> >>
> >> How To Transfer Logins and Passwords Between SQL Servers
> >> http://www.support.microsoft.com/?id=246133
> >>
> >> Mapping Logins & SIDs after a Restore
> >> http://www.support.microsoft.com/?id=298897
> >>
> >> Utility to map logins to users
> >> http://www.dbmaint.com/SyncSql Logins.asp
> >>
> >> User Logon and/or Permission Errors After Restoring Dump
> >> http://www.support.microsoft.com/?id=168001
> >>
> >> How to Resolve Permission Issues When a Database Is Moved Between SQL
> >> Servers
> >> http://www.support.microsoft.com/?id=240872
> >>
> >> Restoring a .mdf
> >> http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
> >>
> >> Disaster Recovery Articles for SQL Server
> >> http://www.support.microsoft.com/?id=307775
> >>
> >>
> >>
> >> --
> >> Tibor Karaszi, SQL Server MVP
> >> http://www.karaszi.com/sqlserver/default.asp
> >> http://www.solidqualitylearning.com/
> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >>
> >>
> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> news:6F9B255A-7822-4B8D-976C-1C04E44E2374@.microsoft.com...
> >> >I dont know how would I tell that.
> >> >
> >> > So do I need to restore the tempdb and the model before I restore the
> >> > masterdb?
> >> >
> >> > thanks,
> >> >
> >> > Joe H
> >> >
> >> > "Tibor Karaszi" wrote:
> >> >
> >> >> My guess is that the master database you have restored specifies that tempdb and/or model
> >> >> should
> >> >> be
> >> >> located on some other place compared to where they are on your restored system.
> >> >>
> >> >> --
> >> >> Tibor Karaszi, SQL Server MVP
> >> >> http://www.karaszi.com/sqlserver/default.asp
> >> >> http://www.solidqualitylearning.com/
> >> >> Blog: http://solidqualitylearning.com/blogs/tibor/
> >> >>
> >> >>
> >> >> "Joe Hernandez" <JoeHernandez@.discussions.microsoft.com> wrote in message
> >> >> news:046BF402-C476-4489-9773-15DB290E4FC9@.microsoft.com...
> >> >> > Hello,
> >> >> >
> >> >> > I am going throught my disaster recovery. I am at the point where I have a
> >> >> > nightly databases backup and I am backing up the transaction logs frequently
> >> >> > during the day.
> >> >> >
> >> >> > I am having the problem that i am trying to restore the backedup master
> >> >> > database, but every time I restore it, I cannot get SQL to run again, so I
> >> >> > have to re-build the master database. I have tried doing it with Enterprise
> >> >> > manager and with Querry analizer, in both cases I get the message 'master
> >> >> > database succesfully recover and then it is shutting donw the service right
> >> >> > away, at this point I cannot re-start the service, am I missing something?
> >> >> >
> >> >> > Please help
> >> >> >
> >> >> > Joe Hernandez
> >> >>
> >> >>
> >>
> >>
>

Restoring the backedup database

Dear All,

I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..

Regards,Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)

Originally posted by pardhu
Dear All,

I had created the backup for the exisiting datbase by writing the schedule which triggers at the particular time on a day. The backup format i had used is .bak. Now i want to restore these backup files into another database (fake database). When i am trying to restore the backup data, an error stating 'The backup set holds the backup of other than exsting <Database Name> database' is displayed and the restore operation is terminating abnormally. kindly give the excat soultion for this problem..

Regards,|||Hai dbadelphes,

I tried even by checking that option.

Originally posted by dbadelphes
Try to rstore with the option "Force restore over existing database" (Enterprise Manager - All Tasks - Restore Database (option panel)|||Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||Hai,

I am trying thru Enterprise Manager Wizard. I made the database names identical even.

Regards,

Originally posted by Satya
Make sure the database name is identical between these 2 servers.
BTW what command you're using to restore?|||From QA have you tried using WITH MOVE (no existing db1), or REPLACE (when you have a new db1)?

Try making sure that no db1 currently exists, then something like:
RESTORE DATABASE db1 FROM DISK='c:\db.doc'
WITH MOVE 'db1_Data' TO 'R:\Microsoft SQL Server\MSSQL\data\db1.mdf',
MOVE 'db1_Log' TO 'q:\Microsoft SQL Server\MSSQL\data\db1.ldf'

Refer to BOL for more information on BACKUP statement.sql

Restoring SQLServer 7 backup to MSDE

I want to develop an app on my computer running SQLServer 7, then
backup the data and resrote it at the client's running MSDE. When
restoring the backup to MSDE I get this message from SQL-DMO:
Location:upgraddb.cpp:214
Instruction:tableIndex < ARRAY_LEN(upgradeMap)
I've tried it with two backups, one of a 40+ table database and one
with 3 tables, and get the same message both times.
Do you know what this means and how to work around it? Thanks.
hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:095do0da3qccc93i4c59mmc396akdd9tbh@.4ax.com
> I want to develop an app on my computer running SQLServer 7, then
> backup the data and resrote it at the client's running MSDE. When
> restoring the backup to MSDE I get this message from SQL-DMO:
> Location: upgraddb.cpp:214
> Instruction: tableIndex < ARRAY_LEN(upgradeMap)
> I've tried it with two backups, one of a 40+ table database and one
> with 3 tables, and get the same message both times.
> Do you know what this means and how to work around it? Thanks.
you should get that exception when restoring SQL Server/MSDE 2000 databases
on SQL Server 7.0 servers..
you can restore on version 2000 from version 7.0, but not the contrary
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||You indicate I can restore a SQLServer 7.0 backup to an MSDE 2000
database, but that is where I'm getting the error. Am I reading your
reply wrong?
The bigger problem seems to be developing on my computer with
SQLServer 7.0 while my clients are using MSDE. I need to be able to
move the data back and forth easily but it sounds like that's not
really possible. Since I can't (I dont' think) run both SQLServer 7.0
and MSDE on the same machine, it looks like that development concept
won't work. Is that correct, or am I missing something?
On Mon, 1 Nov 2004 21:55:44 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:095do0da3qccc93i4c59mmc396akdd9tbh@.4ax.com
>you should get that exception when restoring SQL Server/MSDE 2000 databases
>on SQL Server 7.0 servers..
>you can restore on version 2000 from version 7.0, but not the contrary
|||hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:nabfo0l29tk7i9bhaa0ndsbktgki3nf9s0@.4ax.com
> ...
> The bigger problem seems to be developing on my computer with
> SQLServer 7.0 while my clients are using MSDE. I need to be able to
> move the data back and forth easily but it sounds like that's not
> really possible. Since I can't (I dont' think) run both SQLServer 7.0
> and MSDE on the same machine, it looks like that development concept
> won't work. Is that correct, or am I missing something?
actually you can... you can have a "default" SQL Server 7.0 instance and a
SQL Server 2000 named instance on the same pc..
keep in mind that, if you only install MSDE 2000 named instance side by side
a SQL Server 7.0 instance, you won't be able to use the SQL Server 7.0
client tools, as MSDE only installs "part of" them, that's to say the type
lybs, dlls, COM dependencies and so on, but not the tools (Enterprise
Manager, Profiler, Query Analyzer).. so you'll be stuck with no tool but
oSql.exe...
if you install SQL Server 2000, the newer tools (and realated dependencies)
will be able to manage SQL Server 7.0 ...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I have to apologize...I am running SQLServer 2000. Too much going on.
What difference does that make?
On Tue, 2 Nov 2004 17:55:36 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:nabfo0l29tk7i9bhaa0ndsbktgki3nf9s0@.4ax.com
>actually you can... you can have a "default" SQL Server 7.0 instance and a
>SQL Server 2000 named instance on the same pc..
>keep in mind that, if you only install MSDE 2000 named instance side by side
>a SQL Server 7.0 instance, you won't be able to use the SQL Server 7.0
>client tools, as MSDE only installs "part of" them, that's to say the type
>lybs, dlls, COM dependencies and so on, but not the tools (Enterprise
>Manager, Profiler, Query Analyzer).. so you'll be stuck with no tool but
>oSql.exe...
>if you install SQL Server 2000, the newer tools (and realated dependencies)
>will be able to manage SQL Server 7.0 ...
|||hi Larry,
"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
news:7phgo0dtkn0rppaa0ee4nhjrv329n47500@.4ax.com
> I have to apologize...I am running SQLServer 2000. Too much going on.
> What difference does that make?
>
:D
ok... this sometimes occur when restoring database across servers that do
not share the same service pack level too..
I'd suggest to upgrade both (or all) servers instances to service pack 3..
please have a look at http://www.microsoft.com/sql/downloads/2000/sp3.asp
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.9.1 - DbaMgr ver 0.55.1
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks very much. I'll be able to work on it Thursday and will let you
know the results.
On Wed, 3 Nov 2004 12:24:08 +0100, "Andrea Montanari"
<andrea.sqlDMO@.virgilio.it> wrote:

>hi Larry,
>"Larry Johnson" <larry@.gjerager.com> ha scritto nel messaggio
>news:7phgo0dtkn0rppaa0ee4nhjrv329n47500@.4ax.com
>:D
>ok... this sometimes occur when restoring database across servers that do
>not share the same service pack level too..
>I'd suggest to upgrade both (or all) servers instances to service pack 3..
>please have a look at http://www.microsoft.com/sql/downloads/2000/sp3.asp
|||Once I got the same versions installed the restore worked perfectly.
Thanks for the assistance.

Restoring sql7 db to sql 2000

Hi all,
I have a cd with a backup of an sql server 7 database - I need to restore it
onto my sqlserver 2000 as a new database.
How do I do this - or can it be done ?
Andy (newbie)
Yes, you can use the regular RESTORE DATABASE command to restore a SQL 7
backup into a SQL 2000 Server.
HTH
Kalen Delaney
SQL Server MVP
www.SolidQualityLearning.com
"Andy Muir" <andy@.ForMeToKnow.au> wrote in message
news:%23gbcHHp1EHA.804@.TK2MSFTNGP12.phx.gbl...
> Hi all,
> I have a cd with a backup of an sql server 7 database - I need to restore
> it onto my sqlserver 2000 as a new database.
> How do I do this - or can it be done ?
> Andy (newbie)
>
sql

Restoring SQL6.5 database

**** Post for FREE via your newsreader at post.mcse.ms ****
Hello,
I have backup data of Runtyime database, but whe I'm trying to restore it I
get folowing message: "[SQL Server] Database in use. System Administrato
r
must have exclusive use of database to run load." What should I do?
Darius
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
*** mcse.ms - The #1 Usenet Newsgroup Service on The Planet! ***
http://www.mcse.ms
Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=Hi,
You can not load the database if any user is connected to the specific
database you are trying to load..
So before restoring kill all the process connected.
Use the below script to kill all the process connected in the database.
use master
go
declare @.x varchar(255)
select @.x = @.x + " kill " + convert(varchar(5), spid)
from master.dbo.sysprocesses
where dbid = db_id ('db_name')
exec (@.x)
go
Replace the db_name with your database name.
Thanks
Hari
MCDBA
"Darius" <darius.ram@.takas.lt> wrote in message
news:404ec4ce@.post.mcse.ms...
> **** Post for FREE via your newsreader at post.mcse.ms ****
> Hello,
> I have backup data of Runtyime database, but whe I'm trying to restore it
I
> get folowing message: "[SQL Server] Database in use. System Administra
tor
> must have exclusive use of database to run load." What should I do?
> Darius
>
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=
> *** mcse.ms - The #1 Usenet Newsgroup Service on The Planet! ***
> http://www.mcse.ms
> Unlimited Download - 19 Seperate Servers - 90,000 groups - Uncensored
> -=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=-=|||If you happened to be using ISQL/w and are actaully in the database you are
trying to restore you will get this error as well.
Rand
This posting is provided "as is" with no warranties and confers no rights.