I have a utility script that I wrote sometime ago to grant access to all
tables and stored procedures in a database to the PUBLIC user group.
Unfortunately, I used it on another server on which my default database was
not setup and as such it has been run against the MASTER database resulting
in completely screwed security on that database.
It is not a production server and as such I can remove and re-install SQL2k
if required, but before I do; is there any known way to re-instate the
default security settings?
Cheers.U can run rebuildm, this however will rebuild (master, msdb, model) so make
sure U have good backup of msdb and possibly model if you have any specifics
in there.|||Thanks. That was what I was looking for.
Martin.
"Olu Adedeji" <anonymous@.discussions.microsoft.com> wrote in message
news:C55C7D94-1F3F-4C79-955F-8B0565129D92@.microsoft.com...
> U can run rebuildm, this however will rebuild (master, msdb, model) so
make sure U have good backup of msdb and possibly model if you have any
specifics in there.
Showing posts with label user. Show all posts
Showing posts with label user. Show all posts
Friday, March 30, 2012
Wednesday, March 28, 2012
restore db dump
Hi,
I have a problem during the recovery of the sql db dump.
A user had sql server installed in driver c, my application backed up a db
using 'backup database'. Later on the user reinstalled sql server to driver
d, and ran my application to do the recovery. The restore db was successful
but after restart my app it complained that cannot connect to the db. I foun
d
the db was restored in driver c but not in driver d.
How can I fix this problem?
Thanks
WilliamThe new installation of SQL server isn't aware of the restored database. Use
the sp_attach_ db stored procedure (or wizards in Enterprise Manager) to add
the database to the server.
"William" wrote:
> Hi,
> I have a problem during the recovery of the sql db dump.
> A user had sql server installed in driver c, my application backed up a db
> using 'backup database'. Later on the user reinstalled sql server to drive
r
> d, and ran my application to do the recovery. The restore db was successfu
l
> but after restart my app it complained that cannot connect to the db. I fo
und
> the db was restored in driver c but not in driver d.
> How can I fix this problem?
> Thanks
> Williamsql
I have a problem during the recovery of the sql db dump.
A user had sql server installed in driver c, my application backed up a db
using 'backup database'. Later on the user reinstalled sql server to driver
d, and ran my application to do the recovery. The restore db was successful
but after restart my app it complained that cannot connect to the db. I foun
d
the db was restored in driver c but not in driver d.
How can I fix this problem?
Thanks
WilliamThe new installation of SQL server isn't aware of the restored database. Use
the sp_attach_ db stored procedure (or wizards in Enterprise Manager) to add
the database to the server.
"William" wrote:
> Hi,
> I have a problem during the recovery of the sql db dump.
> A user had sql server installed in driver c, my application backed up a db
> using 'backup database'. Later on the user reinstalled sql server to drive
r
> d, and ran my application to do the recovery. The restore db was successfu
l
> but after restart my app it complained that cannot connect to the db. I fo
und
> the db was restored in driver c but not in driver d.
> How can I fix this problem?
> Thanks
> Williamsql
Restore database without Tran Log
Can I restore a user created database, from a normal SQL Server Full backup,
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
backup,
a
to
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...
did
http://groups.google.com/groups?oi=...ublic.sqlserver
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=...ublic.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
quote:
> Can I restore a user created database, from a normal SQL Server Full
backup,
quote:
> without restoring the log? My problem is that I have a user who backed up
a
quote:
> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space
to
quote:|||Unfortunately, he doesn't have enough disk to restore to the original
> accomindate the transaction log.
> Thanks in advance.
> Paul
>
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...
quote:
> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you
did
quote:
> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=...ublic.sqlserver
quote:
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> backup,
up[QUOTE]
> a
server,[QUOTE]
space[QUOTE]
> to
>
Restore database without Tran Log
Can I restore a user created database, from a normal SQL Server Full backup,
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Can I restore a user created database, from a normal SQL Server Full
backup,
> without restoring the log? My problem is that I have a user who backed up
a
> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space
to
> accomindate the transaction log.
> Thanks in advance.
> Paul
>|||Unfortunately, he doesn't have enough disk to restore to the original
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you
did
> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> > Can I restore a user created database, from a normal SQL Server Full
> backup,
> > without restoring the log? My problem is that I have a user who backed
up
> a
> > database, deleted it, and now has to restore it again (on another
server,
> > when the old server no longer exists) that doesn't have enough disk
space
> to
> > accomindate the transaction log.
> >
> > Thanks in advance.
> >
> > Paul
> >
> >
>
without restoring the log? My problem is that I have a user who backed up a
database, deleted it, and now has to restore it again (on another server,
when the old server no longer exists) that doesn't have enough disk space to
accomindate the transaction log.
Thanks in advance.
PaulSQL Server will create the transaction log the size it need to, when you
issue the restore. My experience is that it will be the same as when you did
the backup, but I can imagine that it can potentially grow during the
restore operation (pure speculation here).
However, this is not anything you can influence. Just run RESTORE
FILELISTONY and make sure you have the amount of disk that it reports (sum
the files). Note that you can use the MOVE option to specify some other
disk(/path) for any of the files.
--
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Paul Bergstedt" <Paul@.nospam.com> wrote in message
news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> Can I restore a user created database, from a normal SQL Server Full
backup,
> without restoring the log? My problem is that I have a user who backed up
a
> database, deleted it, and now has to restore it again (on another server,
> when the old server no longer exists) that doesn't have enough disk space
to
> accomindate the transaction log.
> Thanks in advance.
> Paul
>|||Unfortunately, he doesn't have enough disk to restore to the original
transaction log file size on any drive. The only solution I can think of is
to do a filegroup restore with NORECOVERY. Set database status = 32768, or
Emergency Mode, restarted SQL Server. Create another database, and use DDL
scripts and DTS packages to transfer over all the objects. If there is a
cleaner way to do it, I would like to know.
Paul
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:utD36vP5DHA.2580@.TK2MSFTNGP11.phx.gbl...
> SQL Server will create the transaction log the size it need to, when you
> issue the restore. My experience is that it will be the same as when you
did
> the backup, but I can imagine that it can potentially grow during the
> restore operation (pure speculation here).
> However, this is not anything you can influence. Just run RESTORE
> FILELISTONY and make sure you have the amount of disk that it reports (sum
> the files). Note that you can use the MOVE option to specify some other
> disk(/path) for any of the files.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
>
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Paul Bergstedt" <Paul@.nospam.com> wrote in message
> news:OF5ykqP5DHA.2064@.TK2MSFTNGP11.phx.gbl...
> > Can I restore a user created database, from a normal SQL Server Full
> backup,
> > without restoring the log? My problem is that I have a user who backed
up
> a
> > database, deleted it, and now has to restore it again (on another
server,
> > when the old server no longer exists) that doesn't have enough disk
space
> to
> > accomindate the transaction log.
> >
> > Thanks in advance.
> >
> > Paul
> >
> >
>
Friday, March 23, 2012
Restore database need to recreate a user
Everytime I restored a user database, I need to delete a user of that
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?
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=246133 How To Transfer Logins and
Passwords Between SQL Servers
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://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system 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.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?
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=246133 How To Transfer Logins and
Passwords Between SQL Servers
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://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/featu...le.php/3379901 Moving
system 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.sqlservercentral.com/scri...p?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
Restore database need to recreate a user
Everytime I restored a user database, I need to delete a user of that
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?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=246133 How To Transfer Logins and
Passwords Between SQL Servers
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://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system 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.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?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=246133 How To Transfer Logins and
Passwords Between SQL Servers
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://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/feat...cle.php/3379901 Moving
system 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.sqlservercentral.com/scr...sp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>
Restore database need to recreate a user
Everytime I restored a user database, I need to delete a user of that
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?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=246133 How To Transfer Logins and
Passwords Between SQL Servers
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://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system 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.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 MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>sql
database and recreate the same one, otherwise my application cannot connect
to that database.
Any idea ?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=246133 How To Transfer Logins and
Passwords Between SQL Servers
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://vyaskn.tripod.com/moving_sql_server.htm Moving DBs
http://www.databasejournal.com/features/mssql/article.php/3379901 Moving
system 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.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 MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>|||Hi,
No need to recreate the user. You could re-sync the Logins and users using
the below system stored procedure.
sp_change_users_login 'update_one','user_name','login_name'
See more details of this procedure in books online
Thanks
Hari
SQL Server MVP
"Man Utd" <alanpltseNOSPAM@.yahoo.com.au> wrote in message
news:uhF4RAXkFHA.3288@.TK2MSFTNGP09.phx.gbl...
> Everytime I restored a user database, I need to delete a user of that
> database and recreate the same one, otherwise my application cannot
> connect
> to that database.
> Any idea ?
>sql
restore database from another server
after restore database from another server to a new server, i cannot delete
the past user attached to the database.
some objects are attached to the user, the user cannot be dropped.
how can i remove the user.
i saw some solutions but cannot search them out.
grateful for any assistance.
tonyHave you looked at the Microsoft supplied stored procedure
sp_change_users_login?
--
Keith Kratochvil
"tony wong" <x34@.netvigator.com> wrote in message
news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> after restore database from another server to a new server, i cannot
> delete the past user attached to the database.
> some objects are attached to the user, the user cannot be dropped.
> how can i remove the user.
> i saw some solutions but cannot search them out.
> grateful for any assistance.
> tony
>
>|||seems not worked
let me explain in more details
i find the said login (to be removed) owns 3 tables in the old database.
when it is restored at a new server, i cannot delete the said login
once i delete these 3 tables, the said login can be deleted ( but it is not
a solution to me)
how can i change the owner of these 3 tables back to 'sa'?
Thanks a lot.
tony
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you looked at the Microsoft supplied stored procedure
> sp_change_users_login?
> --
> Keith Kratochvil
>
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>|||fixed by sp_changeobjectowner
thanks a lot
"Tony WONG" <x34@.hknet.com> ¼¶¼g©ó¶l¥ó·s»D:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
> seems not worked
> let me explain in more details
> i find the said login (to be removed) owns 3 tables in the old database.
> when it is restored at a new server, i cannot delete the said login
> once i delete these 3 tables, the said login can be deleted ( but it is
> not a solution to me)
> how can i change the owner of these 3 tables back to 'sa'?
> Thanks a lot.
> tony
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
>> Have you looked at the Microsoft supplied stored procedure
>> sp_change_users_login?
>> --
>> Keith Kratochvil
>>
>> "tony wong" <x34@.netvigator.com> wrote in message
>> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>>
>|||Glad to help. In SQL Server 2000 it is usually recommended to have objects
owned by dbo, not users.
--
Keith Kratochvil
"Tony WONG" <x34@.hknet.com> wrote in message
news:OQdl30ehGHA.3496@.TK2MSFTNGP04.phx.gbl...
> fixed by sp_changeobjectowner
> thanks a lot
>
> "Tony WONG" <x34@.hknet.com>
> ¼¶¼g©ó¶l¥ó·s»D:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> seems not worked
>> let me explain in more details
>> i find the said login (to be removed) owns 3 tables in the old database.
>> when it is restored at a new server, i cannot delete the said login
>> once i delete these 3 tables, the said login can be deleted ( but it is
>> not a solution to me)
>> how can i change the owner of these 3 tables back to 'sa'?
>> Thanks a lot.
>> tony
>>
>> "Keith Kratochvil" <sqlguy.back2u@.comcast.net>
>> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
>> Have you looked at the Microsoft supplied stored procedure
>> sp_change_users_login?
>> --
>> Keith Kratochvil
>>
>> "tony wong" <x34@.netvigator.com> wrote in message
>> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>>
>>
>
the past user attached to the database.
some objects are attached to the user, the user cannot be dropped.
how can i remove the user.
i saw some solutions but cannot search them out.
grateful for any assistance.
tonyHave you looked at the Microsoft supplied stored procedure
sp_change_users_login?
--
Keith Kratochvil
"tony wong" <x34@.netvigator.com> wrote in message
news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> after restore database from another server to a new server, i cannot
> delete the past user attached to the database.
> some objects are attached to the user, the user cannot be dropped.
> how can i remove the user.
> i saw some solutions but cannot search them out.
> grateful for any assistance.
> tony
>
>|||seems not worked
let me explain in more details
i find the said login (to be removed) owns 3 tables in the old database.
when it is restored at a new server, i cannot delete the said login
once i delete these 3 tables, the said login can be deleted ( but it is not
a solution to me)
how can i change the owner of these 3 tables back to 'sa'?
Thanks a lot.
tony
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
> Have you looked at the Microsoft supplied stored procedure
> sp_change_users_login?
> --
> Keith Kratochvil
>
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>|||fixed by sp_changeobjectowner
thanks a lot
"Tony WONG" <x34@.hknet.com> ¼¶¼g©ó¶l¥ó·s»D:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
> seems not worked
> let me explain in more details
> i find the said login (to be removed) owns 3 tables in the old database.
> when it is restored at a new server, i cannot delete the said login
> once i delete these 3 tables, the said login can be deleted ( but it is
> not a solution to me)
> how can i change the owner of these 3 tables back to 'sa'?
> Thanks a lot.
> tony
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
>> Have you looked at the Microsoft supplied stored procedure
>> sp_change_users_login?
>> --
>> Keith Kratochvil
>>
>> "tony wong" <x34@.netvigator.com> wrote in message
>> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>>
>|||Glad to help. In SQL Server 2000 it is usually recommended to have objects
owned by dbo, not users.
--
Keith Kratochvil
"Tony WONG" <x34@.hknet.com> wrote in message
news:OQdl30ehGHA.3496@.TK2MSFTNGP04.phx.gbl...
> fixed by sp_changeobjectowner
> thanks a lot
>
> "Tony WONG" <x34@.hknet.com>
> ¼¶¼g©ó¶l¥ó·s»D:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
>> seems not worked
>> let me explain in more details
>> i find the said login (to be removed) owns 3 tables in the old database.
>> when it is restored at a new server, i cannot delete the said login
>> once i delete these 3 tables, the said login can be deleted ( but it is
>> not a solution to me)
>> how can i change the owner of these 3 tables back to 'sa'?
>> Thanks a lot.
>> tony
>>
>> "Keith Kratochvil" <sqlguy.back2u@.comcast.net>
>> ¼¶¼g©ó¶l¥ó·s»D:O5na4pXhGHA.4864@.TK2MSFTNGP05.phx.gbl...
>> Have you looked at the Microsoft supplied stored procedure
>> sp_change_users_login?
>> --
>> Keith Kratochvil
>>
>> "tony wong" <x34@.netvigator.com> wrote in message
>> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>> after restore database from another server to a new server, i cannot
>> delete the past user attached to the database.
>> some objects are attached to the user, the user cannot be dropped.
>> how can i remove the user.
>> i saw some solutions but cannot search them out.
>> grateful for any assistance.
>> tony
>>
>>
>>
>
restore database from another server
after restore database from another server to a new server, i cannot delete
the past user attached to the database.
some objects are attached to the user, the user cannot be dropped.
how can i remove the user.
i saw some solutions but cannot search them out.
grateful for any assistance.
tonyHave you looked at the Microsoft supplied stored procedure
sp_change_users_login?
Keith Kratochvil
"tony wong" <x34@.netvigator.com> wrote in message
news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> after restore database from another server to a new server, i cannot
> delete the past user attached to the database.
> some objects are attached to the user, the user cannot be dropped.
> how can i remove the user.
> i saw some solutions but cannot search them out.
> grateful for any assistance.
> tony
>
>|||seems not worked
let me explain in more details
i find the said login (to be removed) owns 3 tables in the old database.
when it is restored at a new server, i cannot delete the said login
once i delete these 3 tables, the said login can be deleted ( but it is not
a solution to me)
how can i change the owner of these 3 tables back to 'sa'?
Thanks a lot.
tony
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> glsD:O5na4pXhGHA.4864@.TK2MSFTNGP05.
phx.gbl...
> Have you looked at the Microsoft supplied stored procedure
> sp_change_users_login?
> --
> Keith Kratochvil
>
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>|||fixed by sp_changeobjectowner
thanks a lot
"Tony WONG" <x34@.hknet.com> glsD:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...agreen">
> seems not worked
> let me explain in more details
> i find the said login (to be removed) owns 3 tables in the old database.
> when it is restored at a new server, i cannot delete the said login
> once i delete these 3 tables, the said login can be deleted ( but it is
> not a solution to me)
> how can i change the owner of these 3 tables back to 'sa'?
> Thanks a lot.
> tony
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> glsD:O5na4pXhGHA.
4864@.TK2MSFTNGP05.phx.gbl...
>|||Glad to help. In SQL Server 2000 it is usually recommended to have objects
owned by dbo, not users.
Keith Kratochvil
"Tony WONG" <x34@.hknet.com> wrote in message
news:OQdl30ehGHA.3496@.TK2MSFTNGP04.phx.gbl...
> fixed by sp_changeobjectowner
> thanks a lot
>
> "Tony WONG" <x34@.hknet.com>
> glsD:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
>sql
the past user attached to the database.
some objects are attached to the user, the user cannot be dropped.
how can i remove the user.
i saw some solutions but cannot search them out.
grateful for any assistance.
tonyHave you looked at the Microsoft supplied stored procedure
sp_change_users_login?
Keith Kratochvil
"tony wong" <x34@.netvigator.com> wrote in message
news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
> after restore database from another server to a new server, i cannot
> delete the past user attached to the database.
> some objects are attached to the user, the user cannot be dropped.
> how can i remove the user.
> i saw some solutions but cannot search them out.
> grateful for any assistance.
> tony
>
>|||seems not worked
let me explain in more details
i find the said login (to be removed) owns 3 tables in the old database.
when it is restored at a new server, i cannot delete the said login
once i delete these 3 tables, the said login can be deleted ( but it is not
a solution to me)
how can i change the owner of these 3 tables back to 'sa'?
Thanks a lot.
tony
"Keith Kratochvil" <sqlguy.back2u@.comcast.net> glsD:O5na4pXhGHA.4864@.TK2MSFTNGP05.
phx.gbl...
> Have you looked at the Microsoft supplied stored procedure
> sp_change_users_login?
> --
> Keith Kratochvil
>
> "tony wong" <x34@.netvigator.com> wrote in message
> news:OsxhRcXhGHA.3956@.TK2MSFTNGP02.phx.gbl...
>|||fixed by sp_changeobjectowner
thanks a lot
"Tony WONG" <x34@.hknet.com> glsD:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...agreen">
> seems not worked
> let me explain in more details
> i find the said login (to be removed) owns 3 tables in the old database.
> when it is restored at a new server, i cannot delete the said login
> once i delete these 3 tables, the said login can be deleted ( but it is
> not a solution to me)
> how can i change the owner of these 3 tables back to 'sa'?
> Thanks a lot.
> tony
>
> "Keith Kratochvil" <sqlguy.back2u@.comcast.net> glsD:O5na4pXhGHA.
4864@.TK2MSFTNGP05.phx.gbl...
>|||Glad to help. In SQL Server 2000 it is usually recommended to have objects
owned by dbo, not users.
Keith Kratochvil
"Tony WONG" <x34@.hknet.com> wrote in message
news:OQdl30ehGHA.3496@.TK2MSFTNGP04.phx.gbl...
> fixed by sp_changeobjectowner
> thanks a lot
>
> "Tony WONG" <x34@.hknet.com>
> glsD:u6TbsuehGHA.1856@.TK2MSFTNGP03.phx.gbl...
>sql
Tuesday, March 20, 2012
restore database
I am having problems with using restoring a database. I am implementing the
application at one site, and the user is running the application at another
site. Originally, I created the database at the user's site. Now they have
a problem. I did a backup of the database, and moved the copy of the backup
to the development system. I am having trouble restoring my 'test'
database with the restore I moved from the other system.
How do you recommned doing this, with details.
Thank you.
Hi
You don't give the actual error message that you are getting which makes
this hard to diagnose! At a guess you need to use the MOVE clause when
recoving the database see the Books online topic "How to restore files to a
new location"
John
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
|||Hi,
You will have problem if you dont have the same directory structure if you
the direct RETORE DATABASE command. In that case you have to use the RESTORE
database with Move option
How to identify the Logical MDF and LDF names which needs to be used with
MOVE option:-
RESTORE FILELISTONLY FROM disk='c:\backup\dbname.bak' (Give the exact path
and file name of the backup file)
This will return you the MDF and LDF file infiormation. Name is the logical
name. Execute the below command:-
RESTORE DATABASE <dbname> from disk='c:\backup\dbname.bak'
with move 'logical_mdf_name' to 'new_physical_name_with path_for_mdf',
move 'logical_ldf_name' to 'new_physical_name_with path_for_ldf', stats=10
Note:
Stats=10 will shhow the restore progress in percentage
Thanks
hari
MCDBA
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
application at one site, and the user is running the application at another
site. Originally, I created the database at the user's site. Now they have
a problem. I did a backup of the database, and moved the copy of the backup
to the development system. I am having trouble restoring my 'test'
database with the restore I moved from the other system.
How do you recommned doing this, with details.
Thank you.
Hi
You don't give the actual error message that you are getting which makes
this hard to diagnose! At a guess you need to use the MOVE clause when
recoving the database see the Books online topic "How to restore files to a
new location"
John
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
|||Hi,
You will have problem if you dont have the same directory structure if you
the direct RETORE DATABASE command. In that case you have to use the RESTORE
database with Move option
How to identify the Logical MDF and LDF names which needs to be used with
MOVE option:-
RESTORE FILELISTONLY FROM disk='c:\backup\dbname.bak' (Give the exact path
and file name of the backup file)
This will return you the MDF and LDF file infiormation. Name is the logical
name. Execute the below command:-
RESTORE DATABASE <dbname> from disk='c:\backup\dbname.bak'
with move 'logical_mdf_name' to 'new_physical_name_with path_for_mdf',
move 'logical_ldf_name' to 'new_physical_name_with path_for_ldf', stats=10
Note:
Stats=10 will shhow the restore progress in percentage
Thanks
hari
MCDBA
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
Restore database
Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.
Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive and
> I need to to Restore several of them to the SQL Server 2000. I have tried to
> restore three of them but in Enterprise Manager it lists the three that I am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen
|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Jim Richards wrote:
Drive. I[vbcol=seagreen]
defined[vbcol=seagreen]
and[vbcol=seagreen]
tried to[vbcol=seagreen]
I am[vbcol=seagreen]
(Loading/Suspect)
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen
|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.[vbcol=seagreen]
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)
>
|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.[vbcol=seagreen]
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)
>
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.
Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive and
> I need to to Restore several of them to the SQL Server 2000. I have tried to
> restore three of them but in Enterprise Manager it lists the three that I am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen
|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...[vbcol=seagreen]
> Jim Richards wrote:
Drive. I[vbcol=seagreen]
defined[vbcol=seagreen]
and[vbcol=seagreen]
tried to[vbcol=seagreen]
I am[vbcol=seagreen]
(Loading/Suspect)
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen
|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.[vbcol=seagreen]
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)
>
|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.[vbcol=seagreen]
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)
>
restore database
I have a copy of a backup from a user site. a .sbk file..
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
Thanks
See the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.co m...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>
|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =
f:\sqldata\\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\..\lams.sdk
with
move hrattend_data to c:\program files\\hrattend.mdf,
move hrattend_log to c:\program files\..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?
|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you are restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ?
Where do you see this physical name field?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com ...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>
|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com ...
>
|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name =
> f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen
|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?
|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
Thanks
See the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.co m...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>
|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =
f:\sqldata\\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\..\lams.sdk
with
move hrattend_data to c:\program files\\hrattend.mdf,
move hrattend_log to c:\program files\..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?
|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you are restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ?
Where do you see this physical name field?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com ...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>
|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com ...
>
|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name =
> f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen
|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?
|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.
restore database
I have a copy of a backup from a user site. a .sbk file..
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
ThanksSee the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.com...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\?\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =f:\sqldata\'\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\?..\lams.sdk
with
move ?hrattend_data to c:\program files\'\hrattend.mdf?,
move ?hrattend_log to c:\program files\?..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you are restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ?
Where do you see this physical name field?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\?\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\'\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\?..\lams.sdk
> with
> move ?hrattend_data to c:\program files\'\hrattend.mdf?,
> move ?hrattend_log to c:\program files\?..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
>> the data is still the old data.
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
>> my question is following the restore filelistonly, why does the physical
>> name fields reference f:\ ?
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
>> here are the steps I am taking to do the restore
>> step 1
>> Restore filelistonly
>> from disk = c:\projects\.\lams.sdk
>> this produces a table with
>> logical filename = hrattend_data physical name =>> f:\sqldata\..\hrattend.mdf
>> logical filename = hrattend_log physical name =>> f:\sqldata\..\hrattend.ldf
>> step 2
>> restore database hrattend
>> from disk = c:\projects\...\lams.sdk
>> with
>> move 'hrattend_data to c:\program files\..\hrattend.mdf',
>> move 'hrattend_log to c:\program files\...\hrattend.ldf,
>> replace,
>> stats = 10
>> the data is still the old data.
>> my question is following the restore filelistonly, why does the physical
>> name fields reference f:\ ? shouldn't this be in c:\ where the backup is
>> located?
>>
>|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\?\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name => f:\sqldata\'\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\?..\lams.sdk
> with
> move ?hrattend_data to c:\program files\'\hrattend.mdf?,
> move ?hrattend_log to c:\program files\?..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
ThanksSee the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
--
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.com...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\?\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =f:\sqldata\'\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\?..\lams.sdk
with
move ?hrattend_data to c:\program files\'\hrattend.mdf?,
move ?hrattend_log to c:\program files\?..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you are restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ?
Where do you see this physical name field?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\?\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\'\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\?..\lams.sdk
> with
> move ?hrattend_data to c:\program files\'\hrattend.mdf?,
> move ?hrattend_log to c:\program files\?..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical name fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
>> the data is still the old data.
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
>> my question is following the restore filelistonly, why does the physical
>> name fields reference f:\ ?
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
>> here are the steps I am taking to do the restore
>> step 1
>> Restore filelistonly
>> from disk = c:\projects\.\lams.sdk
>> this produces a table with
>> logical filename = hrattend_data physical name =>> f:\sqldata\..\hrattend.mdf
>> logical filename = hrattend_log physical name =>> f:\sqldata\..\hrattend.ldf
>> step 2
>> restore database hrattend
>> from disk = c:\projects\...\lams.sdk
>> with
>> move 'hrattend_data to c:\program files\..\hrattend.mdf',
>> move 'hrattend_log to c:\program files\...\hrattend.ldf,
>> replace,
>> stats = 10
>> the data is still the old data.
>> my question is following the restore filelistonly, why does the physical
>> name fields reference f:\ ? shouldn't this be in c:\ where the backup is
>> located?
>>
>|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\?\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name => f:\sqldata\'\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\'\hrattend.ldf
> step 2
> restore database hrattend
> from disk = c:\projects\?..\lams.sdk
> with
> move ?hrattend_data to c:\program files\'\hrattend.mdf?,
> move ?hrattend_log to c:\program files\?..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.
restore database
I am having problems with using restoring a database. I am implementing the
application at one site, and the user is running the application at another
site. Originally, I created the database at the user's site. Now they have
a problem. I did a backup of the database, and moved the copy of the backup
to the development system. I am having trouble restoring my 'test'
database with the restore I moved from the other system.
How do you recommned doing this, with details.
Thank you.Hi
You don't give the actual error message that you are getting which makes
this hard to diagnose! At a guess you need to use the MOVE clause when
recoving the database see the Books online topic "How to restore files to a
new location"
John
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>|||Hi,
You will have problem if you dont have the same directory structure if you
the direct RETORE DATABASE command. In that case you have to use the RESTORE
database with Move option
How to identify the Logical MDF and LDF names which needs to be used with
MOVE option:-
RESTORE FILELISTONLY FROM disk='c:\backup\dbname.bak' (Give the exact path
and file name of the backup file)
This will return you the MDF and LDF file infiormation. Name is the logical
name. Execute the below command:-
RESTORE DATABASE <dbname> from disk='c:\backup\dbname.bak'
with move 'logical_mdf_name' to 'new_physical_name_with path_for_mdf',
move 'logical_ldf_name' to 'new_physical_name_with path_for_ldf', stats=10
Note:
Stats=10 will shhow the restore progress in percentage
Thanks
hari
MCDBA
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
application at one site, and the user is running the application at another
site. Originally, I created the database at the user's site. Now they have
a problem. I did a backup of the database, and moved the copy of the backup
to the development system. I am having trouble restoring my 'test'
database with the restore I moved from the other system.
How do you recommned doing this, with details.
Thank you.Hi
You don't give the actual error message that you are getting which makes
this hard to diagnose! At a guess you need to use the MOVE clause when
recoving the database see the Books online topic "How to restore files to a
new location"
John
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>|||Hi,
You will have problem if you dont have the same directory structure if you
the direct RETORE DATABASE command. In that case you have to use the RESTORE
database with Move option
How to identify the Logical MDF and LDF names which needs to be used with
MOVE option:-
RESTORE FILELISTONLY FROM disk='c:\backup\dbname.bak' (Give the exact path
and file name of the backup file)
This will return you the MDF and LDF file infiormation. Name is the logical
name. Execute the below command:-
RESTORE DATABASE <dbname> from disk='c:\backup\dbname.bak'
with move 'logical_mdf_name' to 'new_physical_name_with path_for_mdf',
move 'logical_ldf_name' to 'new_physical_name_with path_for_ldf', stats=10
Note:
Stats=10 will shhow the restore progress in percentage
Thanks
hari
MCDBA
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
Monday, March 12, 2012
Restore database
Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive a
nd
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> Jim Richards wrote:
Drive. I[vbcol=seagreen]
defined[vbcol=seagreen]
and[vbcol=seagreen]
tried to[vbcol=seagreen]
I am[vbcol=seagreen]
(Loading/Suspect)[vbcol=seagreen]
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)[vbcol=seagreen]
>|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)[vbcol=seagreen]
>
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive a
nd
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> Jim Richards wrote:
Drive. I[vbcol=seagreen]
defined[vbcol=seagreen]
and[vbcol=seagreen]
tried to[vbcol=seagreen]
I am[vbcol=seagreen]
(Loading/Suspect)[vbcol=seagreen]
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)[vbcol=seagreen]
>|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
Drive.[vbcol=seagreen]
> I
defined[vbcol=seagreen]
> and
tried[vbcol=seagreen]
> to
I[vbcol=seagreen]
> am
(Loading/Suspect)[vbcol=seagreen]
>
restore database
I am having problems with using restoring a database. I am implementing the
application at one site, and the user is running the application at another
site. Originally, I created the database at the user's site. Now they have
a problem. I did a backup of the database, and moved the copy of the backup
to the development system. I am having trouble restoring my 'test'
database with the restore I moved from the other system.
How do you recommned doing this, with details.
Thank you.Hi
You don't give the actual error message that you are getting which makes
this hard to diagnose! At a guess you need to use the MOVE clause when
recoving the database see the Books online topic "How to restore files to a
new location"
John
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>|||Hi,
You will have problem if you dont have the same directory structure if you
the direct RETORE DATABASE command. In that case you have to use the RESTORE
database with Move option
How to identify the Logical MDF and LDF names which needs to be used with
MOVE option:-
RESTORE FILELISTONLY FROM disk='c:\backup\dbname.bak' (Give the exact path
and file name of the backup file)
This will return you the MDF and LDF file infiormation. Name is the logical
name. Execute the below command:-
RESTORE DATABASE <dbname> from disk='c:\backup\dbname.bak'
with move 'logical_mdf_name' to 'new_physical_name_with path_for_mdf',
move 'logical_ldf_name' to 'new_physical_name_with path_for_ldf', stats=10
Note:
Stats=10 will shhow the restore progress in percentage
Thanks
hari
MCDBA
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
application at one site, and the user is running the application at another
site. Originally, I created the database at the user's site. Now they have
a problem. I did a backup of the database, and moved the copy of the backup
to the development system. I am having trouble restoring my 'test'
database with the restore I moved from the other system.
How do you recommned doing this, with details.
Thank you.Hi
You don't give the actual error message that you are getting which makes
this hard to diagnose! At a guess you need to use the MOVE clause when
recoving the database see the Books online topic "How to restore files to a
new location"
John
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>|||Hi,
You will have problem if you dont have the same directory structure if you
the direct RETORE DATABASE command. In that case you have to use the RESTORE
database with Move option
How to identify the Logical MDF and LDF names which needs to be used with
MOVE option:-
RESTORE FILELISTONLY FROM disk='c:\backup\dbname.bak' (Give the exact path
and file name of the backup file)
This will return you the MDF and LDF file infiormation. Name is the logical
name. Execute the below command:-
RESTORE DATABASE <dbname> from disk='c:\backup\dbname.bak'
with move 'logical_mdf_name' to 'new_physical_name_with path_for_mdf',
move 'logical_ldf_name' to 'new_physical_name_with path_for_ldf', stats=10
Note:
Stats=10 will shhow the restore progress in percentage
Thanks
hari
MCDBA
"Terry" <cooleyt@.woh.rr.com> wrote in message
news:emoqc.31540$sA.1968@.fe2.columbus.rr.com...
> I am having problems with using restoring a database. I am implementing
the
> application at one site, and the user is running the application at
another
> site. Originally, I created the database at the user's site. Now they
have
> a problem. I did a backup of the database, and moved the copy of the
backup
> to the development system. I am having trouble restoring my 'test'
> database with the restore I moved from the other system.
> How do you recommned doing this, with details.
> Thank you.
>
restore database
I have a copy of a backup from a user site. a .sbk file..
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
ThanksSee the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.com...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =
f:\sqldata\\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\..\lams.sdk
with
move hrattend_data to c:\program files\\hrattend.mdf,
move hrattend_log to c:\program files\..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you a
re restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical n
ame fields reference f:\
> ?
Where do you see this physical name field?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr
.com...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\\hrattend
.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ld
f
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical n
ame fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
>|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name =
> f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ld
f
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.
I have a development server at my work site and an old copy of the
database that I use for testing. I would like to overlay my old copy of
the database with the backup from the user site. What is the best way
to do this?
ThanksSee the RESTORE DATABASE topic in Books Online. To overwrite the old copy,
just specify the same name and the REPLACE options, along with all the other
requried items...
Kevin Hill
President
3NF Consulting
www.3nf-inc.com/NewsGroups.htm
"terry" <cooleyt@.woh.rr.com> wrote in message
news:phaaf.179542$lI5.158808@.tornado.ohiordc.rr.com...
>I have a copy of a backup from a user site. a .sbk file..
> I have a development server at my work site and an old copy of the
> database that I use for testing. I would like to overlay my old copy of
> the database with the backup from the user site. What is the best way to
> do this?
> Thanks
>|||here are the steps I am taking to do the restore
step 1
Restore filelistonly
from disk = c:\projects\\lams.sdk
this produces a table with
logical filename = hrattend_data physical name =
f:\sqldata\\hrattend.mdf
logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ldf
step 2
restore database hrattend
from disk = c:\projects\..\lams.sdk
with
move hrattend_data to c:\program files\\hrattend.mdf,
move hrattend_log to c:\program files\..\hrattend.ldf,
replace,
stats = 10
the data is still the old data.
my question is following the restore filelistonly, why does the physical
name fields reference f:\ ? shouldn't this be in c:\ where the backup
is located?|||> the data is still the old data.
Do RESTORE HEADERONLY. You might have several backups on that file and you a
re restorring the oldest
one. Use the FILE option to specify anything else than the first (1).
> my question is following the restore filelistonly, why does the physical n
ame fields reference f:\
> ?
Where do you see this physical name field?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"terry" <cooleyt@.woh.rr.com> wrote in message news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr
.com...
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name = f:\sqldata\\hrattend
.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ld
f
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical n
ame fields reference f:\
> ? shouldn't this be in c:\ where the backup is located?
>
>|||Hi,
Physical name field is the actual place your MDF and LDF were stored when
you backup the source database. Based on that
you can decide whether you need a MOVE command or not. Incase if you have
same folder in destination server then you do not want to
give a MOVE command; else you have mention move command to redirect the MDF
and LDF to a new path.
The other question:- As Tibor mentioned use RESTORE HEADERONLY command to
find the latest backup file and use FILE = 'latest number'
in your RESTORE DATABASE command.
Thanks
Hari
SQL server MVP
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:e%23PXQcE4FHA.252@.TK2MSFTNGP15.phx.gbl...
> Do RESTORE HEADERONLY. You might have several backups on that file and you
> are restorring the oldest one. Use the FILE option to specify anything
> else than the first (1).
>
> Where do you see this physical name field?
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "terry" <cooleyt@.woh.rr.com> wrote in message
> news:Rbdaf.181702$lI5.74710@.tornado.ohiordc.rr.com...
>|||terry wrote:
> here are the steps I am taking to do the restore
> step 1
> Restore filelistonly
> from disk = c:\projects\\lams.sdk
> this produces a table with
> logical filename = hrattend_data physical name =
> f:\sqldata\\hrattend.mdf
> logical filename = hrattend_log physical name = f:\sqldata\\hrattend.ld
f
> step 2
> restore database hrattend
> from disk = c:\projects\..\lams.sdk
> with
> move hrattend_data to c:\program files\\hrattend.mdf,
> move hrattend_log to c:\program files\..\hrattend.ldf,
> replace,
> stats = 10
> the data is still the old data.
> my question is following the restore filelistonly, why does the physical
> name fields reference f:\ ? shouldn't this be in c:\ where the backup
> is located?
>
>
The physical name in the RESTORE FILELISTONLY, refers to the original
location of the files. This is info that is stored in the backup file
and isn't related to anything on your server.
If you want to overwrite your existing database, you don't have to use
the MOVE option. Basically you just run -
RESTORE DATABASE hrattend FROM DISK = 'c:\projects\...\lams.sdk'
WITH RECOVERY, REPLACE, STATS =10
HTH
Regards
Steen|||this did not work as expected. The backup file that I am using was
created on 11/02. I previously restored the database in Jun. The new
back up file is larger than the previous one, so I am assuming that it
contains more records. Following the restore, my tables still only
contain records ending in June.
I tried to create a new database from the 11/02 backup using the Restore
function in the enterprise manager. I successfully created a new
database, but it still only contains records up to Jun. What am I missing?|||I finally figured it out. Thanks for the help...
The restore headeronly set me off in the right direction. There were
three backup sets in the backup file. They neglected to tell me that.
restore database
hi,
i am facing one problem when i am going to restore backup file. there is one maasage is come (restore detabase must be used in single user mode when trying to restore the master database. restore database is terminating abnormally)
The master database contains information about every other DB in the system.
From a command prompt type:
i am facing one problem when i am going to restore backup file. there is one maasage is come (restore detabase must be used in single user mode when trying to restore the master database. restore database is terminating abnormally)
Quote:
Originally Posted by deepji2007
hi,
i am facing one problem when i am going to restore backup file. there is one maasage is come (restore detabase must be used in single user mode when trying to restore the master database. restore database is terminating abnormally)
The master database contains information about every other DB in the system.
Therefore in order to overwrite it, it has to be in a special mode where only one string of information could possibly be coming in.
To start SQL Server in single user mode, stop the SQL server and start it again using this command.
sqlservr -m
If this doesnt work, try browsing to the \Microsoft SQL Server\80\Tools\binn directory.
**note overwriting your master database can be very dangerous. Only do this if its completely necessary.|||hi,
i have stoped the SQL server but from where i give the commabd (sqlservr-m)|||
Quote:
Originally Posted by deepji2007
hi,
i have stoped the SQL server but from where i give the commabd (sqlservr-m)
From a command prompt type:
C:\> CD program files\microsoft sql server\80\tools\binn
C:\> sqlservr -m
Restore database
Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive and
> I need to to Restore several of them to the SQL Server 2000. I have tried to
> restore three of them but in Enterprise Manager it lists the three that I am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> Jim Richards wrote:
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive. I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried to
> > restore three of them but in Enterprise Manager it lists the three that
I am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive.
> I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
> and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried
> to
> > restore three of them but in Enterprise Manager it lists the three that
I
> am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
>|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive.
> I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
> and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried
> to
> > restore three of them but in Enterprise Manager it lists the three that
I
> am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
>
have re-formatted my HD, re-installed SQL Server 2000, but my user defined
databases are not there of course. The backup files are on the ZIP drive and
I need to to Restore several of them to the SQL Server 2000. I have tried to
restore three of them but in Enterprise Manager it lists the three that I am
trying to restore but just to the right of each it shows (Loading/Suspect)
and when I click the plus sign it shows "No Items". Can someone help me
with this, please? If I can't get these three restored I will have to
re-create them again. Thanks in advance, Jim.Jim Richards wrote:
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive. I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive and
> I need to to Restore several of them to the SQL Server 2000. I have tried to
> restore three of them but in Enterprise Manager it lists the three that I am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>
...maybe a stupid question, but have you tried to hif "Refresh"?
Regards
Steen|||I had not tried it so I selected each of the three dbs one at a time and
then hit View->Refresh on each one of them. It didn't help a bit but I do
appreciate your suggestion. Jim.
"Steen Persson" <spe@.REMOVEdatea.dk> wrote in message
news:eM3cRIBJFHA.1308@.TK2MSFTNGP15.phx.gbl...
> Jim Richards wrote:
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive. I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried to
> > restore three of them but in Enterprise Manager it lists the three that
I am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
> ...maybe a stupid question, but have you tried to hif "Refresh"?
> Regards
> Steen|||Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
statement. You should get an error, post it here so we can help you further.
-Argenis
"Jim Richards" <JWRichards@.satx.rr.com> wrote in message
news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> Good morning. I had previously backed up my SQL Databases to a ZIP Drive.
I
> have re-formatted my HD, re-installed SQL Server 2000, but my user defined
> databases are not there of course. The backup files are on the ZIP drive
and
> I need to to Restore several of them to the SQL Server 2000. I have tried
to
> restore three of them but in Enterprise Manager it lists the three that I
am
> trying to restore but just to the right of each it shows (Loading/Suspect)
> and when I click the plus sign it shows "No Items". Can someone help me
> with this, please? If I can't get these three restored I will have to
> re-create them again. Thanks in advance, Jim.
>|||Thank you, Argenis, so very much for your offer to help. I am just an
individual who is trying to learn how to use SQL Server 2000 at home. I do
not run a business from my home. I have never tried to Restore a database
before either by t-sql statements or the Enterprise Manager. I have "SAMS
Teach Yourself Microsoft SQL Server 2000 in 21 Days" (I have the Developers
Edition) and day 8 covers "Restoring Your Databases" but all the statements
syntax and examples cover restoring a database that you backed up from SQL
Server so the Backup Log is in the SQL Server. That is not the case in my
situation as I re-formatted the hard drive and reinstalled SQL Server 2000
so there are no Backup logs for the three databases that I am trying to
restore (Bindery - ROE - ROE6) which are shown in
http://www.jimwrichards.com/images/Restore2.gif . If I use this restore
statement: RESTORE DATABASE Bindery FILE = Bindery_Data FROM
F:\SQL_DB_BUs\Bindery2.BAK WITH NORECOVERY REPLACE
RESTORE LOG Bindery_Log FROM F:\SQL_DB_BUs\Bindery2.BAK WITH RECOVERY
REPLACE
will these statements restore the database "Bindery"? Thanks in advance for
your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive.
> I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
> and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried
> to
> > restore three of them but in Enterprise Manager it lists the three that
I
> am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
>|||Argenis, I don't know if this graphic will help but it is what is shown in
Enterprise Manager when the Databases are expanded. Notice that the three
that I am trying to Restore all show the (Loading/Suspect) to the right of
the database name.
(http://www.jimwrichards.com/images/Restore3.gif
I sure do appreciate your help. Jim.
"Argenis Fernandez" <argenis@.spam.sucks.gmail.com> wrote in message
news:ugnSJ9CJFHA.3832@.TK2MSFTNGP12.phx.gbl...
> Try to restore using Query Analyzer and the RESTORE DATABASE t-sql
> statement. You should get an error, post it here so we can help you
further.
> -Argenis
> "Jim Richards" <JWRichards@.satx.rr.com> wrote in message
> news:_bmXd.52340$Qz1.32086@.fe2.texas.rr.com...
> > Good morning. I had previously backed up my SQL Databases to a ZIP
Drive.
> I
> > have re-formatted my HD, re-installed SQL Server 2000, but my user
defined
> > databases are not there of course. The backup files are on the ZIP drive
> and
> > I need to to Restore several of them to the SQL Server 2000. I have
tried
> to
> > restore three of them but in Enterprise Manager it lists the three that
I
> am
> > trying to restore but just to the right of each it shows
(Loading/Suspect)
> > and when I click the plus sign it shows "No Items". Can someone help me
> > with this, please? If I can't get these three restored I will have to
> > re-create them again. Thanks in advance, Jim.
> >
> >
>
Restore being annoying
Hi,
I am trying to restore a database and call it a different name, but it comes up with the following error:
Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file '_datafile' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Please help this is doing my head in.
The database I am trying to restore from is called TMG and I would like to call the new Database TMG2Howdy
I suspect you have a filename problem - go to the options tab and make sure the filenames for the mdf & ldf files for TMG2 are unique to the server. Alter them if need be just before starting the restore.
The logical file names dont have to be unique.
Cheers,
SG.|||Hi,
TMG2 is not created as a database yet, I want the restore to create the new database.|||Hi again,
This is my code:
RESTORE FILELISTONLY
FROM Dumps_TMG_PM
RESTORE DATABASE TMG2 from Dumps_TMG_PM with RECOVERY,
MOVE 'TMG_dat.mdf' TO 'D:\Data\TMG2_data.mdf',
MOVE 'TMG_log.ldf' TO 'E:\TMG2_log.ldf'|||What does...
RESTORE FILIELISTONLY FROM DISK = '<pathname><dbname>.bak'
Give you?
Check out BOL for more examples?|||Hi,
It gives me this:
Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file 'tmg_dat.mdf' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Same error basically.|||That's from the restore...did you highlight an execute the command I gave you only?
If you just execute, it'll do the whole window...you are in QA, correct?
What did you type exactly?|||Brett .. what exactly does this table do ... 'msdb.dbo.restorestatus'... any idea. Is it created on the run time coz my msdb also does not have this object (table or sp or whatever)|||TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000
IN QA this is what it gave me.|||How about:
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'D:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO 'E:\TMG_Log.LDF'
, REPLACE
And I have no idea about that system table...
and what version of sql are we talking about?|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000
IN QA this is what it gave me.
try this command :
restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000
IN QA this is what it gave me.
try this command :
restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Sorry chaps still gives me the same error, by the way this database does not exist yet.
There is already a database called TMG on this server, but it is in warm standby mode as it is being log shipped.|||It doesn't have to exist...
my bad btw
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE
Cut and paste that...it should work...|||Hi in QA it came up with this:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TMG_Data' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Same thing........this is doing my head in!
Thanks for all your help so far chaps|||Originally posted by Brett Kaiser
It doesn't have to exist...
my bad btw
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE
Cut and paste that...it should work...
Your Bad again .. Brett !!!
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Dat' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE|||Good catch...thanks...
But let me ask you...do ever set it up as dat?
dat is da funnyest ting I even sawed...|||Originally posted by Brett Kaiser
dat is da funnyest ting I even sawed...
it is dat same case here :)|||Guys,
dat was the problem! lol
Thanks very much for your help,
Take it easy!|||Guys,
dat was the problem! lol
Thanks very much for your help,
Take it easy!|||Run this first:
--Query_1:
restore headeronly from Logical_device_name
Get the position fo the backup set you want to restore.
Next as stated in the responses above, use:
--Query_2:
restore filelistonly from Logical_device_name
Get the database filenames.
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.bak'
,WITH file = 'position from Query_1', RECOVERY, REPLACE,
MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'K:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'
Items after the keyword 'MOVE' are obtained from Query_2.
Make sure, you have sufficient space on your discs.|||Can you specify a different location to restore to?
For instance:
MOVE 'TMG_Data' TO 'J:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'R:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'
where J and R are on a diffrent server than where it was backed up from?
The problem I am having is I get his
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
Server: Msg 3158, Level 16, State 1, Line 1
Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection Broken
I am trying to restore a database and call it a different name, but it comes up with the following error:
Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file '_datafile' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Please help this is doing my head in.
The database I am trying to restore from is called TMG and I would like to call the new Database TMG2Howdy
I suspect you have a filename problem - go to the options tab and make sure the filenames for the mdf & ldf files for TMG2 are unique to the server. Alter them if need be just before starting the restore.
The logical file names dont have to be unique.
Cheers,
SG.|||Hi,
TMG2 is not created as a database yet, I want the restore to create the new database.|||Hi again,
This is my code:
RESTORE FILELISTONLY
FROM Dumps_TMG_PM
RESTORE DATABASE TMG2 from Dumps_TMG_PM with RECOVERY,
MOVE 'TMG_dat.mdf' TO 'D:\Data\TMG2_data.mdf',
MOVE 'TMG_log.ldf' TO 'E:\TMG2_log.ldf'|||What does...
RESTORE FILIELISTONLY FROM DISK = '<pathname><dbname>.bak'
Give you?
Check out BOL for more examples?|||Hi,
It gives me this:
Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file 'tmg_dat.mdf' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
Same error basically.|||That's from the restore...did you highlight an execute the command I gave you only?
If you just execute, it'll do the whole window...you are in QA, correct?
What did you type exactly?|||Brett .. what exactly does this table do ... 'msdb.dbo.restorestatus'... any idea. Is it created on the run time coz my msdb also does not have this object (table or sp or whatever)|||TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000
IN QA this is what it gave me.|||How about:
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'D:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO 'E:\TMG_Log.LDF'
, REPLACE
And I have no idea about that system table...
and what version of sql are we talking about?|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000
IN QA this is what it gave me.
try this command :
restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000
IN QA this is what it gave me.
try this command :
restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Sorry chaps still gives me the same error, by the way this database does not exist yet.
There is already a database called TMG on this server, but it is in warm standby mode as it is being log shipped.|||It doesn't have to exist...
my bad btw
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE
Cut and paste that...it should work...|||Hi in QA it came up with this:
Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TMG_Data' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Same thing........this is doing my head in!
Thanks for all your help so far chaps|||Originally posted by Brett Kaiser
It doesn't have to exist...
my bad btw
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE
Cut and paste that...it should work...
Your Bad again .. Brett !!!
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Dat' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE|||Good catch...thanks...
But let me ask you...do ever set it up as dat?
dat is da funnyest ting I even sawed...|||Originally posted by Brett Kaiser
dat is da funnyest ting I even sawed...
it is dat same case here :)|||Guys,
dat was the problem! lol
Thanks very much for your help,
Take it easy!|||Guys,
dat was the problem! lol
Thanks very much for your help,
Take it easy!|||Run this first:
--Query_1:
restore headeronly from Logical_device_name
Get the position fo the backup set you want to restore.
Next as stated in the responses above, use:
--Query_2:
restore filelistonly from Logical_device_name
Get the database filenames.
RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.bak'
,WITH file = 'position from Query_1', RECOVERY, REPLACE,
MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'K:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'
Items after the keyword 'MOVE' are obtained from Query_2.
Make sure, you have sufficient space on your discs.|||Can you specify a different location to restore to?
For instance:
MOVE 'TMG_Data' TO 'J:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'R:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'
where J and R are on a diffrent server than where it was backed up from?
The problem I am having is I get his
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
Server: Msg 3158, Level 16, State 1, Line 1
Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection Broken
Subscribe to:
Posts (Atom)