Hi all
just a quick question for you. has anyone had any problems restoring a SQL7 database to a SQL server 2000?
is there anything special you have to do to get it working?
thanks for your time.
Andrew.it's ok i have had it all sorted. if the admins want to delete this thread it's ok by me :)
Showing posts with label sql2000. Show all posts
Showing posts with label sql2000. Show all posts
Friday, March 23, 2012
Restoring SQL2000 Transaction Logs to SQL2005
Hey all,
Just went through migrating to SQL 2005 as well and we have a need to apply log shipping between SQL 2000 and SQL 2005. Well not shipping per say in the automated fashion that SQL 2005 offers but rather we need to apply SQL 2000 transaction logs to a SQL 2005 instance. Though the database is still in SQL 2000 version 80 and the secondary database needs to be available in read only. (STANDBY MODE)
When I attempt to restore either a FULL backup or transaction log I get the error:
RESTORE DATABASE is terminating abnormally. This backup cannot be restored using WITH STANDBY because a database upgrade is needed. Reissue the RESTORE without WITH STANDBY
The only way I could restore the FULL was by using the WITH RECOVERY OR NORECOVERY option during the restore process. The issue with this is I need the database in standby by mode in order to continue to append transaction logs.
Anybody have any thoughts how I might be able to get around this?
Thanks
Eric
The physical structure of the database has changed between SQL2000 and SQL 2005. Since the transaction logs contain low-level data, you cannot apply SQL 2000 transaction logs to a SQL 2005 database, or vice-versa
|||Bummer, ya figure MS would build the 2005 transactional restore with a backward compatibility method, (in the since of restoring a database in STANDYBY mode) though what’s the point of keeping a database in (80) SQL 2000 mode if it truly is a semi-upgraded 2005 database.
Please correct me if I am wrong here. but I understand the reason why the 2000 versioned database is it can't be restored in stand by mode without created a log of is own during the upgrade.( not to repeat myself but if the destination database is in SQL 2000 format yet is attached to a SQL 2005 instance) it shouldn't have a issue, that is if SQL 2005 was truly backward compatible.
Thanks
|||OK, There are several things here, and I'll try to address them all.
First off, there is a common misconception about compatibility modes and what they are.
Setting a compatibility mode governs how TSQL is interpereted and behaves, but does NOT have any impact to the physical structure of the database itself. So, a SQL 2005 database in 80 compatibility mode will have a SQL 2005 structure in the database files, but will respond to TSQL as if it were a SQL 2000 database. Meaning that any deprecated/removed keywords will be honored, and any behaviors which have been changed will act as they did in SQL 2000.
When you upgrade to SQL 2005, the database structures are upgraded at the point in time when the database is recovered.
So, you can continue to apply SQL 2000-based logs to the database as long as it is never recovered. Unfortunately, you cannot access it for any purpose other than applying logs. You cannot put it in standby mode, as that implies recovering the database, which triggers the update. You also cannot create a database snapshot without recovering/upgrading the database.
|||
Hmm, well that make since. i was a little shady on the compatibility modes though if the database structure does not change, they whats the big deal with attaching a once sql 2000 database to a 2005 instance and restoring a sql 2000 transaction file to it. ( during the attach process does the database automatically get upgraded?)
Thanks
|||Part of attaching a database involves recovering it. This triggers the upgrading of the structures.
There is currently not a CREATE DATABASE FOR ATTACH WITH NORECOVERY.
Restoring SQL2000 DB to SQLExpress on different machine
Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest. mdf D PRIMARY 27590656 35184372080640 1
0 0 000
00000-0000-0000-0000- 000000000000 0 0 0 512 1 NULL 1740000000
004500003 153EE
4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log. ldf L NULL 13238272 35184372080640 2 0 0
00
000000-0000-0000-0000- 000000000000 0 0 0 512 0 NULL 0 00000000
-0000-0000-000
0-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL. 1\MSSQL\Backup\DeviceTest_db_20060806020
0'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY
|
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVER
Y |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restore
d
> (the roll forward set) is consistent with the database. If the roll forwar
d
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.
Restoring SQL2000 DB to SQLExpress on different machine
Really having a problem...
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql
I have a full backup in a file where RESTORE FILELISTONLY
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the results...
DeviceTest E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
DeviceTest_log E:\Microsoft SQL
Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0 00000000-0000-0000-0000-000000000000 0 0
When I try to restore this database on another machine with the following...
RESTORE DATABASE [DeviceTest]
FILE = N'DeviceTest'
FROM DISK = N'C:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
WITH FILE = 1, NORECOVERY,
MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
STATS = 10
it seems to work with the following output...
Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file 1.
100 percent processed.
RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in 2.447
seconds (8.811 MB/sec).
BUT the newly restored database STAYS in the restoring state and I can't do
a thing with it! What is the secret that I'm missing? I've searched Books on
Line, this forum, product support, Q314546, and nothing seems to help.
Wishing for help...
Dave Gardner"DGardner" <DGardner@.discussions.microsoft.com> wrote in message
news:0916F23F-29B7-4460-A761-235D7A67A9C5@.microsoft.com...
> Really having a problem...
> I have a full backup in a file where RESTORE FILELISTONLY
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200' shows the
> results...
> DeviceTest E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest.mdf D PRIMARY 27590656 35184372080640 1 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 1 NULL 1740000000004500003
> 153EE4C3-3307-4FCE-9B9B-3B79B81705D8 0 1
> DeviceTest_log E:\Microsoft SQL
> Server\MSSQL\Data\DeviceTest_log.ldf L NULL 13238272 35184372080640 2 0 0
> 00000000-0000-0000-0000-000000000000 0 0 0 512 0 NULL 0
> 00000000-0000-0000-0000-000000000000 0 0
> When I try to restore this database on another machine with the
> following...
> RESTORE DATABASE [DeviceTest]
> FILE = N'DeviceTest'
> FROM DISK = N'C:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Backup\DeviceTest_db_200608060200'
> WITH FILE = 1, NORECOVERY,
> MOVE N'DeviceTest' TO N'c:\Program Files\Microsoft SQL
> Server\MSSQL.1\MSSQL\Data\DeviceTest.mdf',
> STATS = 10
> it seems to work with the following output...
> Processed 2632 pages for database 'DeviceTest', file 'DeviceTest' on file
> 1.
> 100 percent processed.
> RESTORE DATABASE ... FILE=<name> successfully processed 2632 pages in
> 2.447
> seconds (8.811 MB/sec).
> BUT the newly restored database STAYS in the restoring state and I can't
> do
> a thing with it! What is the secret that I'm missing? I've searched Books
> on
> Line, this forum, product support, Q314546, and nothing seems to help.
>
You left the database in restoring mode by not recovering it.
from BOL RESTORE (Transact-SQL)
Comparison of RECOVERY and NORECOVERY
Roll back is controlled by the RESTORE statement through the [ RECOVERY |
NORECOVERY ] options:
NORECOVERY specifies that roll back not occur. This allows roll forward to
continue with the next statement in the sequence.
In this case, the restore sequence can restore other backups and roll them
forward.
RECOVERY (the default) indicates that roll back should be performed after
roll forward is completed for the current backup.
Recovering the database requires that the entire set of data being restored
(the roll forward set) is consistent with the database. If the roll forward
set has not been rolled forward far enough to be consistent with the
database and RECOVERY is specified, the Database Engine issues an error.
David|||"David Browne" wrote:
> You left the database in restoring mode by not recovering it.
> from BOL RESTORE (Transact-SQL)
> Comparison of RECOVERY and NORECOVERY
> Roll back is controlled by the RESTORE statement through the [ RECOVERY |
> NORECOVERY ] options:
> NORECOVERY specifies that roll back not occur. This allows roll forward to
> continue with the next statement in the sequence.
> In this case, the restore sequence can restore other backups and roll them
> forward.
>
> RECOVERY (the default) indicates that roll back should be performed after
> roll forward is completed for the current backup.
> Recovering the database requires that the entire set of data being restored
> (the roll forward set) is consistent with the database. If the roll forward
> set has not been rolled forward far enough to be consistent with the
> database and RECOVERY is specified, the Database Engine issues an error.
> David
>
>
David,
Sorry, but that's not the problem. Been there, done that. Using RECOVERY or
NORECOVERY still keeps the database in RECOVERING status with no hope of
doing anything.
--Dave G.sql
Restoring SQL2000 bkp file on SQL2005
Is it possible to restore SQL2000 backup to SQL2005? Or I have to restore the DB to SQL2000 and then upgrade it to SQL2005?
Can someone provide me ways to upgrade SQL2000 DB to SQL2005?
Thanks,
Hiten
Simply restore it on the SQL 2k5 server. Backups of 2k are compatible with SQL2k5 (but not vice versa). You have to be aware that the internal (meta) structure of the database is upgraded during the restore process and therefore can′t be backuped and restored on the SQL2k after the upgrade.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||Is this possible on SQL 2005 Express too? Or I need to have SQL server 2005 ?|||That also valid for SQL Server 2k5 Express.HTH, Jens K. Suessmeyer.
http://www.sqlserver2005.de
Restoring SQL2000 after upgrade
I performed an in-place upgrade of my SQL2000 database to SQL2005. I am having too many problems at the moment with compatability issues between SQL 2005 and VS2003 and need to roll back SQL 9.0 to 8.0. Are there any white papers, or published procedures for doing this? I have a tape backup of the 2000 system, but there have been various updates to the databases during the past 3 weeks since the upgrade. The structure of the databases have not changed.
Please help!
There is no vehicle to rollback. You will want to reinstall sql2k as a different instance, restore data from your tape backup and then use data import/export to update sql2k data with sql2k5 data.If you're looking for a way to mount a sql2k5 database on a sql2k instance, it's not possible.|||
Are you running the SQL Server database in 9.0 or 8.0 compatibility mode? When you upgrade from 2000 to 2005, you will be in 8.0 mode unless you explicitly changed it to 9.0. If you are running in 9.0 mode and having problems you might try switching back to 8.0 and see if that helps as a temporary measure. What kind of problems are you having?
If you are already in 8.0 mode and still having problems that you cannot work through, then I would install a fresh copy of SQL Server 2000 (on a different server), apply SP4 and any hotfixes, and then restore from your tape backup. Then, you can use SSMS from SQL Server 2005 to connect to both servers are start trying to get your data back in synch on your three week old backup.
You can use "DataDude" (Visual Studio 2005 Team Edition for Database Professionals) to to compare the data between the two databases, and generate scripts to help you get the old copy up to date.
|||I am running in 9.0 mode. The main problem is that all my applications are written in VS2003 and when I need to change a data adapter, I have to manually code the insert/update and delete methods rather than having VS generate the automatic code, which is a pain.
These applications manage extensive databases and there are probably a hundred data adapters involved, granted, most of them never change, but it makes maintenance a bear when they do. I know that the DB operations are generally more efficient if you code those methods manually, but the applications are used by only a handfull of people (20 or so) and I doubt the increased processing efficiency would be noticable vs. the ease of letting VB auto-create the code. Plus, I think the 2000 enterprise manager is much more intutitive and much faster than the the SQL2005 studio. Call me old fashion, but it strikes me that the 2005 Studio has so many bells and whistles it's become bloated and sluggish.
I've now re-installed SQL2000 as a separate instance and applied SP4, restored the data from tape and am about to copy the 2005 data to 2000. When all is well, I'll uninstall 2005.
At some point I will bite the bullet and migrate my applications to VS2005 and then it will probably make sense to upgrade SQL to 2005 as well. For the moment, I'll stay a version behind. Thanks for your comments.
restoring sql to another server
What are the step to restore sql database to another server
FYI: I have sql2000 server
I want to restore the database from sql-server to old-server
thanksAssuming you have a full backup of database on sql-server machine:
1) copy the backup file to old-server
2) run the restore database from EM or QA and change the location of files
accordingly
3) run sp_changedbowner if required
OR
if u just want to move copy of database to another machine:
1) detach the database on sql-server
2) copy the data and log files to old-server
3) attach the files to both sql-server and old-server
"frank" wrote:
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"frank" <frank@.discussions.microsoft.com> wrote in message
news:8623F485-A988-48D6-86AD-056E41CC132D@.microsoft.com...
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
FYI: I have sql2000 server
I want to restore the database from sql-server to old-server
thanksAssuming you have a full backup of database on sql-server machine:
1) copy the backup file to old-server
2) run the restore database from EM or QA and change the location of files
accordingly
3) run sp_changedbowner if required
OR
if u just want to move copy of database to another machine:
1) detach the database on sql-server
2) copy the data and log files to old-server
3) attach the files to both sql-server and old-server
"frank" wrote:
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/scripts/scriptdetails.asp?scriptid=599
Restoring a .mdf
http://www.support.microsoft.com/?id=307775 Disaster Recovery Articles
for SQL Server
Andrew J. Kelly SQL MVP
"frank" <frank@.discussions.microsoft.com> wrote in message
news:8623F485-A988-48D6-86AD-056E41CC132D@.microsoft.com...
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
restoring sql to another server
What are the step to restore sql database to another server
FYI: I have sql2000 server
I want to restore the database from sql-server to old-server
thanksAssuming you have a full backup of database on sql-server machine:
1) copy the backup file to old-server
2) run the restore database from EM or QA and change the location of files
accordingly
3) run sp_changedbowner if required
OR
if u just want to move copy of database to another machine:
1) detach the database on sql-server
2) copy the data and log files to old-server
3) attach the files to both sql-server and old-server
"frank" wrote:
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/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
"frank" <frank@.discussions.microsoft.com> wrote in message
news:8623F485-A988-48D6-86AD-056E41CC132D@.microsoft.com...
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
FYI: I have sql2000 server
I want to restore the database from sql-server to old-server
thanksAssuming you have a full backup of database on sql-server machine:
1) copy the backup file to old-server
2) run the restore database from EM or QA and change the location of files
accordingly
3) run sp_changedbowner if required
OR
if u just want to move copy of database to another machine:
1) detach the database on sql-server
2) copy the data and log files to old-server
3) attach the files to both sql-server and old-server
"frank" wrote:
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/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
"frank" <frank@.discussions.microsoft.com> wrote in message
news:8623F485-A988-48D6-86AD-056E41CC132D@.microsoft.com...
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
restoring sql to another server
What are the step to restore sql database to another server
FYI: I have sql2000 server
I want to restore the database from sql-server to old-server
thanks
Assuming you have a full backup of database on sql-server machine:
1) copy the backup file to old-server
2) run the restore database from EM or QA and change the location of files
accordingly
3) run sp_changedbowner if required
OR
if u just want to move copy of database to another machine:
1) detach the database on sql-server
2) copy the data and log files to old-server
3) attach the files to both sql-server and old-server
"frank" wrote:
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/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
"frank" <frank@.discussions.microsoft.com> wrote in message
news:8623F485-A988-48D6-86AD-056E41CC132D@.microsoft.com...
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
sql
FYI: I have sql2000 server
I want to restore the database from sql-server to old-server
thanks
Assuming you have a full backup of database on sql-server machine:
1) copy the backup file to old-server
2) run the restore database from EM or QA and change the location of files
accordingly
3) run sp_changedbowner if required
OR
if u just want to move copy of database to another machine:
1) detach the database on sql-server
2) copy the data and log files to old-server
3) attach the files to both sql-server and old-server
"frank" wrote:
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
|||http://www.support.microsoft.com/?id=314546 Moving DB's between Servers
http://www.support.microsoft.com/?id=224071 Moving SQL Server Databases
to a New Location with Detach/Attach
http://support.microsoft.com/?id=221465 Using WITH MOVE in a
Restore
http://www.support.microsoft.com/?id=246133 How To Transfer Logins and
Passwords Between SQL Servers
http://www.support.microsoft.com/?id=298897 Mapping Logins & SIDs after a
Restore
http://www.dbmaint.com/SyncSqlLogins.asp Utility to map logins to
users
http://www.support.microsoft.com/?id=168001 User Logon and/or Permission
Errors After Restoring Dump
http://www.support.microsoft.com/?id=240872 How to Resolve Permission
Issues When a Database Is Moved Between SQL Servers
http://www.sqlservercentral.com/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
"frank" <frank@.discussions.microsoft.com> wrote in message
news:8623F485-A988-48D6-86AD-056E41CC132D@.microsoft.com...
> What are the step to restore sql database to another server
> FYI: I have sql2000 server
> I want to restore the database from sql-server to old-server
> thanks
>
sql
Wednesday, March 21, 2012
restoring old mdf file
I need to restore a Sql2000 database from an .mdf file from last July. Can
people tell me if the following procedure is correct, or am I missing
something? :
1. restore the .mdf file from backup to temp location (done)
2. backup existing database (in case I need to restore this one)
3. delete existing database (to cleanup metadata in master)
4. shutdown sqlserver
5. replace existing .mdf file with old one
6. startup sqlserver
7. attach database to .mdf -- how do I do this?
Thanks for any assistance with this.
-Frank Brown
Seattle Fire Dept
http://www.inwa.net/~frog/take a look at the sp_attach_db procedure, in BOL.
Alex Ivascu
"frank brown" <someone@.somewhere.net> wrote in message
news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> I need to restore a Sql2000 database from an .mdf file from last July.
Can
> people tell me if the following procedure is correct, or am I missing
> something? :
> 1. restore the .mdf file from backup to temp location (done)
> 2. backup existing database (in case I need to restore this one)
> 3. delete existing database (to cleanup metadata in master)
> 4. shutdown sqlserver
> 5. replace existing .mdf file with old one
> 6. startup sqlserver
> 7. attach database to .mdf -- how do I do this?
> Thanks for any assistance with this.
> -Frank Brown
> Seattle Fire Dept
> http://www.inwa.net/~frog/
>|||Hi ,
This activity do not require a SQL server shutdown. Please use the below
commands to perform:
1. drop database databasename
2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
3. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Now your old database will be ready to use.
Incase if you need the old and new database in server then u have to use
Move option along with
sp_attach_db command.
Thanks
Hari
MCDBA
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> take a look at the sp_attach_db procedure, in BOL.
> Alex Ivascu
>
> "frank brown" <someone@.somewhere.net> wrote in message
> news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > I need to restore a Sql2000 database from an .mdf file from last July.
> Can
> > people tell me if the following procedure is correct, or am I missing
> > something? :
> >
> > 1. restore the .mdf file from backup to temp location (done)
> > 2. backup existing database (in case I need to restore this one)
> > 3. delete existing database (to cleanup metadata in master)
> > 4. shutdown sqlserver
> > 5. replace existing .mdf file with old one
> > 6. startup sqlserver
> > 7. attach database to .mdf -- how do I do this?
> >
> > Thanks for any assistance with this.
> >
> > -Frank Brown
> > Seattle Fire Dept
> > http://www.inwa.net/~frog/
> >
> >
>|||Huh? If you drop the database, there are no more database file...
Also, sp_attach_db is only guaranteed to work if you first detach the database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> This activity do not require a SQL server shutdown. Please use the below
> commands to perform:
> 1. drop database databasename
> 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
> 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> @.filename1 = N'c:\mssql\data\dbname.mdf',
> @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> Now your old database will be ready to use.
> Incase if you need the old and new database in server then u have to use
> Move option along with
> sp_attach_db command.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > take a look at the sp_attach_db procedure, in BOL.
> >
> > Alex Ivascu
> >
> >
> > "frank brown" <someone@.somewhere.net> wrote in message
> > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > I need to restore a Sql2000 database from an .mdf file from last July.
> > Can
> > > people tell me if the following procedure is correct, or am I missing
> > > something? :
> > >
> > > 1. restore the .mdf file from backup to temp location (done)
> > > 2. backup existing database (in case I need to restore this one)
> > > 3. delete existing database (to cleanup metadata in master)
> > > 4. shutdown sqlserver
> > > 5. replace existing .mdf file with old one
> > > 6. startup sqlserver
> > > 7. attach database to .mdf -- how do I do this?
> > >
> > > Thanks for any assistance with this.
> > >
> > > -Frank Brown
> > > Seattle Fire Dept
> > > http://www.inwa.net/~frog/
> > >
> > >
> >
> >
>|||Hi,
Frank's mail says that he need to drop the existing database and load the
old database. That is the reason I mentioned the "drop database" command
initially.
I do agree with you , some times the SP_attachdb wont work incase if we are
not using SP_detachdb.
Frank,
What you can do is perform the below steps to put you in safer
side.
1. Perform a backup of your existing database and keep it in a safe folder.
2. drop database databasename
3. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
4. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e0RKXhfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Huh? If you drop the database, there are no more database file...
> Also, sp_attach_db is only guaranteed to work if you first detach the
database.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> >
> > This activity do not require a SQL server shutdown. Please use the below
> > commands to perform:
> >
> > 1. drop database databasename
> > 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any
directory)
> > 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> > @.filename1 = N'c:\mssql\data\dbname.mdf',
> > @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> >
> > Now your old database will be ready to use.
> >
> > Incase if you need the old and new database in server then u have to use
> > Move option along with
> > sp_attach_db command.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> > news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > > take a look at the sp_attach_db procedure, in BOL.
> > >
> > > Alex Ivascu
> > >
> > >
> > > "frank brown" <someone@.somewhere.net> wrote in message
> > > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > > I need to restore a Sql2000 database from an .mdf file from last
July.
> > > Can
> > > > people tell me if the following procedure is correct, or am I
missing
> > > > something? :
> > > >
> > > > 1. restore the .mdf file from backup to temp location (done)
> > > > 2. backup existing database (in case I need to restore this one)
> > > > 3. delete existing database (to cleanup metadata in master)
> > > > 4. shutdown sqlserver
> > > > 5. replace existing .mdf file with old one
> > > > 6. startup sqlserver
> > > > 7. attach database to .mdf -- how do I do this?
> > > >
> > > > Thanks for any assistance with this.
> > > >
> > > > -Frank Brown
> > > > Seattle Fire Dept
> > > > http://www.inwa.net/~frog/
> > > >
> > > >
> > >
> > >
> >
> >
>
people tell me if the following procedure is correct, or am I missing
something? :
1. restore the .mdf file from backup to temp location (done)
2. backup existing database (in case I need to restore this one)
3. delete existing database (to cleanup metadata in master)
4. shutdown sqlserver
5. replace existing .mdf file with old one
6. startup sqlserver
7. attach database to .mdf -- how do I do this?
Thanks for any assistance with this.
-Frank Brown
Seattle Fire Dept
http://www.inwa.net/~frog/take a look at the sp_attach_db procedure, in BOL.
Alex Ivascu
"frank brown" <someone@.somewhere.net> wrote in message
news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> I need to restore a Sql2000 database from an .mdf file from last July.
Can
> people tell me if the following procedure is correct, or am I missing
> something? :
> 1. restore the .mdf file from backup to temp location (done)
> 2. backup existing database (in case I need to restore this one)
> 3. delete existing database (to cleanup metadata in master)
> 4. shutdown sqlserver
> 5. replace existing .mdf file with old one
> 6. startup sqlserver
> 7. attach database to .mdf -- how do I do this?
> Thanks for any assistance with this.
> -Frank Brown
> Seattle Fire Dept
> http://www.inwa.net/~frog/
>|||Hi ,
This activity do not require a SQL server shutdown. Please use the below
commands to perform:
1. drop database databasename
2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
3. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Now your old database will be ready to use.
Incase if you need the old and new database in server then u have to use
Move option along with
sp_attach_db command.
Thanks
Hari
MCDBA
"alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> take a look at the sp_attach_db procedure, in BOL.
> Alex Ivascu
>
> "frank brown" <someone@.somewhere.net> wrote in message
> news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > I need to restore a Sql2000 database from an .mdf file from last July.
> Can
> > people tell me if the following procedure is correct, or am I missing
> > something? :
> >
> > 1. restore the .mdf file from backup to temp location (done)
> > 2. backup existing database (in case I need to restore this one)
> > 3. delete existing database (to cleanup metadata in master)
> > 4. shutdown sqlserver
> > 5. replace existing .mdf file with old one
> > 6. startup sqlserver
> > 7. attach database to .mdf -- how do I do this?
> >
> > Thanks for any assistance with this.
> >
> > -Frank Brown
> > Seattle Fire Dept
> > http://www.inwa.net/~frog/
> >
> >
>|||Huh? If you drop the database, there are no more database file...
Also, sp_attach_db is only guaranteed to work if you first detach the database.
--
Tibor Karaszi, SQL Server MVP
Archive at: http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> Hi ,
> This activity do not require a SQL server shutdown. Please use the below
> commands to perform:
> 1. drop database databasename
> 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
> 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> @.filename1 = N'c:\mssql\data\dbname.mdf',
> @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> Now your old database will be ready to use.
> Incase if you need the old and new database in server then u have to use
> Move option along with
> sp_attach_db command.
> Thanks
> Hari
> MCDBA
>
>
>
>
> "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > take a look at the sp_attach_db procedure, in BOL.
> >
> > Alex Ivascu
> >
> >
> > "frank brown" <someone@.somewhere.net> wrote in message
> > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > I need to restore a Sql2000 database from an .mdf file from last July.
> > Can
> > > people tell me if the following procedure is correct, or am I missing
> > > something? :
> > >
> > > 1. restore the .mdf file from backup to temp location (done)
> > > 2. backup existing database (in case I need to restore this one)
> > > 3. delete existing database (to cleanup metadata in master)
> > > 4. shutdown sqlserver
> > > 5. replace existing .mdf file with old one
> > > 6. startup sqlserver
> > > 7. attach database to .mdf -- how do I do this?
> > >
> > > Thanks for any assistance with this.
> > >
> > > -Frank Brown
> > > Seattle Fire Dept
> > > http://www.inwa.net/~frog/
> > >
> > >
> >
> >
>|||Hi,
Frank's mail says that he need to drop the existing database and load the
old database. That is the reason I mentioned the "drop database" command
initially.
I do agree with you , some times the SP_attachdb wont work incase if we are
not using SP_detachdb.
Frank,
What you can do is perform the below steps to put you in safer
side.
1. Perform a backup of your existing database and keep it in a safe folder.
2. drop database databasename
3. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any directory)
4. EXEC sp_attach_db @.dbname = N'DBNAME',
@.filename1 = N'c:\mssql\data\dbname.mdf',
@.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
Thanks
Hari
"Tibor Karaszi" <tibor.please_reply_to_public_forum.karaszi@.cornerstone.se>
wrote in message news:e0RKXhfmDHA.1244@.TK2MSFTNGP11.phx.gbl...
> Huh? If you drop the database, there are no more database file...
> Also, sp_attach_db is only guaranteed to work if you first detach the
database.
> --
> Tibor Karaszi, SQL Server MVP
> Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
>
> "Hari Prasad" <hari_prasad_k@.hotmail.com> wrote in message
> news:eLPaYodmDHA.1072@.TK2MSFTNGP09.phx.gbl...
> > Hi ,
> >
> > This activity do not require a SQL server shutdown. Please use the below
> > commands to perform:
> >
> > 1. drop database databasename
> > 2. copy the .MDF and .LDF files to c:\mssql\data\ (Itcan be any
directory)
> > 3. EXEC sp_attach_db @.dbname = N'DBNAME',
> > @.filename1 = N'c:\mssql\data\dbname.mdf',
> > @.filename2 = N'c:\MSSQL\Data\dbname_log.ldf'
> >
> > Now your old database will be ready to use.
> >
> > Incase if you need the old and new database in server then u have to use
> > Move option along with
> > sp_attach_db command.
> >
> > Thanks
> > Hari
> > MCDBA
> >
> >
> >
> >
> >
> >
> >
> >
> >
> > "alex ivascu" <alexdivascu@.sbcglobalNO.SPAMnet> wrote in message
> > news:q6Ulb.5679$sP6.5569@.newssvr27.news.prodigy.com...
> > > take a look at the sp_attach_db procedure, in BOL.
> > >
> > > Alex Ivascu
> > >
> > >
> > > "frank brown" <someone@.somewhere.net> wrote in message
> > > news:LPTlb.18$Nd3.3944@.news-west.eli.net...
> > > > I need to restore a Sql2000 database from an .mdf file from last
July.
> > > Can
> > > > people tell me if the following procedure is correct, or am I
missing
> > > > something? :
> > > >
> > > > 1. restore the .mdf file from backup to temp location (done)
> > > > 2. backup existing database (in case I need to restore this one)
> > > > 3. delete existing database (to cleanup metadata in master)
> > > > 4. shutdown sqlserver
> > > > 5. replace existing .mdf file with old one
> > > > 6. startup sqlserver
> > > > 7. attach database to .mdf -- how do I do this?
> > > >
> > > > Thanks for any assistance with this.
> > > >
> > > > -Frank Brown
> > > > Seattle Fire Dept
> > > > http://www.inwa.net/~frog/
> > > >
> > > >
> > >
> > >
> >
> >
>
Wednesday, March 7, 2012
restoring from a backup file to a new database
I am trying to restore from a backup file to a new database with SQL2000. Here is my C# code:
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.LoginSecure = true;
srv.Connect("servername","","");
//create a restore class instance
SQLDMO.Restore res = new SQLDMO.RestoreClass();
//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files = @."\\server\backupfile.bak";
//set the database to the chosen database
res.Database = "databasename" + "-restored";
// Restore the database
res.ReplaceDatabase = false;
res.SQLRestore(srv);
This code gives me an error basically telling me it can't restore as the ".mdf" and ".ldf" files are already in use. I think I need to specify new names for these like I have I have specified a new name for the database. (e.g. appended "-restored" to it.) but I am not sure how. Any help would be greatly appreciated.
Sorry, I don't know DMO well enough to answer your question, but here's an example using SMO and VB that may help. After setting the Database name, insert these lines of code, before the Restore:
Dim alRSFile As New RelocateFile
Dim alRSLog As New RelocateFile
alRSFile.LogicalFileName = "AdventureWorks_Data"
alRSFile.PhysicalFileName = "d:\MSSQL\Data\AdWorks.mdf"
alRSLog.LogicalFileName = "AdventureWorks_Log"
alRSLog.PhysicalFileName = "d:\MSSQL\Data\AdWorks.ldf"
res.RelocateFiles.Add(alRSFile)
res.RelocateFiles.Add(alRSLog)
Allen
|||
I believe SQLDMO is more easy to use and comprehensive. You already mentioned the problem.
When you take backup, and then restore it in a new database, the name of the .mdf file for the new database is same as was backup containing.
So if a database with the same .mdf file name is there conflict arises. Even if you dont have it there it would work fine for the first time but for 2nd time you need to change the physical file name as we do in sql commands mentioned below....
RESTORE DATABASE db3
FROM DISK = 'c:\me.bak' \\ Original name of the .mdf file was me.mdf.
WITH MOVE 'me' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db3.mdf'
RestoreClass of SQLDMO library provided these features...but how can we specify the MOVE attribute here....still have no clues about it 
..If any one has any idea...post the reply as early as possible...
The code I included in my last post effectively does the MOVE operation, by setting the properties of the File objects before the restore. While you may find DMO easier to use, it's probably more due to familiarity than anything else. Everything SSMS does is done through SMO, so there can't be an argument that it's not comprehensive, and the features that are new to SQL Server 2005 aren't available in DMO.
Most importantly, though, is that there are a variety of ways to accomplish anything, so if DMO suits your needs, then by all means use it.
restoring from a backup file to a new database
I am trying to restore from a backup file to a new database with SQL2000. Here is my C# code:
SQLDMO._SQLServer srv = new SQLDMO.SQLServerClass();
//connect to the server
srv.LoginSecure = true;
srv.Connect("servername","","");
//create a restore class instance
SQLDMO.Restore res = new SQLDMO.RestoreClass();
//set the backup device = files property ( easy way )
res.Devices = res.Files;
//set the files property to the File Name text box
res.Files = @."\\server\backupfile.bak";
//set the database to the chosen database
res.Database = "databasename" + "-restored";
// Restore the database
res.ReplaceDatabase = false;
res.SQLRestore(srv);
This code gives me an error basically telling me it can't restore as the ".mdf" and ".ldf" files are already in use. I think I need to specify new names for these like I have I have specified a new name for the database. (e.g. appended "-restored" to it.) but I am not sure how. Any help would be greatly appreciated.
Sorry, I don't know DMO well enough to answer your question, but here's an example using SMO and VB that may help. After setting the Database name, insert these lines of code, before the Restore:
Dim alRSFile As New RelocateFile
Dim alRSLog As New RelocateFile
alRSFile.LogicalFileName = "AdventureWorks_Data"
alRSFile.PhysicalFileName = "d:\MSSQL\Data\AdWorks.mdf"
alRSLog.LogicalFileName = "AdventureWorks_Log"
alRSLog.PhysicalFileName = "d:\MSSQL\Data\AdWorks.ldf"
res.RelocateFiles.Add(alRSFile)
res.RelocateFiles.Add(alRSLog)
Allen
|||
I believe SQLDMO is more easy to use and comprehensive. You already mentioned the problem.
When you take backup, and then restore it in a new database, the name of the .mdf file for the new database is same as was backup containing.
So if a database with the same .mdf file name is there conflict arises. Even if you dont have it there it would work fine for the first time but for 2nd time you need to change the physical file name as we do in sql commands mentioned below....
RESTORE DATABASE db3
FROM DISK = 'c:\me.bak' \\ Original name of the .mdf file was me.mdf.
WITH MOVE 'me' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\db3.mdf'
RestoreClass of SQLDMO library provided these features...but how can we specify the MOVE attribute here....still have no clues about it ..If any one has any idea...post the reply as early as possible...
The code I included in my last post effectively does the MOVE operation, by setting the properties of the File objects before the restore. While you may find DMO easier to use, it's probably more due to familiarity than anything else. Everything SSMS does is done through SMO, so there can't be an argument that it's not comprehensive, and the features that are new to SQL Server 2005 aren't available in DMO.
Most importantly, though, is that there are a variety of ways to accomplish anything, so if DMO suits your needs, then by all means use it.
restoring from 7 to 2000
When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
sql7, and sa looses it rights to the database, how do you fix this
You might be interested in these stored procedures:
sp_change_users_login
sp_changedbowner
Keith
"sd" <susan.dunn@.ncsl.org> wrote in message
news:%23vu0YbkLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
> sql7, and sa looses it rights to the database, how do you fix this
>
sql7, and sa looses it rights to the database, how do you fix this
You might be interested in these stored procedures:
sp_change_users_login
sp_changedbowner
Keith
"sd" <susan.dunn@.ncsl.org> wrote in message
news:%23vu0YbkLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
> sql7, and sa looses it rights to the database, how do you fix this
>
restoring from 7 to 2000
When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
sql7, and sa looses it rights to the database, how do you fix thisYou might be interested in these stored procedures:
sp_change_users_login
sp_changedbowner
--
Keith
"sd" <susan.dunn@.ncsl.org> wrote in message
news:%23vu0YbkLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
> sql7, and sa looses it rights to the database, how do you fix this
>
sql7, and sa looses it rights to the database, how do you fix thisYou might be interested in these stored procedures:
sp_change_users_login
sp_changedbowner
--
Keith
"sd" <susan.dunn@.ncsl.org> wrote in message
news:%23vu0YbkLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
> sql7, and sa looses it rights to the database, how do you fix this
>
restoring from 7 to 2000
When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
sql7, and sa looses it rights to the database, how do you fix thisYou might be interested in these stored procedures:
sp_change_users_login
sp_changedbowner
Keith
"sd" <susan.dunn@.ncsl.org> wrote in message
news:%23vu0YbkLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
> sql7, and sa looses it rights to the database, how do you fix this
>
sql7, and sa looses it rights to the database, how do you fix thisYou might be interested in these stored procedures:
sp_change_users_login
sp_changedbowner
Keith
"sd" <susan.dunn@.ncsl.org> wrote in message
news:%23vu0YbkLFHA.1476@.TK2MSFTNGP09.phx.gbl...
> When doing a restore from Sql 7 to Sql2000 the sysusers seems to reflect
> sql7, and sa looses it rights to the database, how do you fix this
>
Restoring from 2000 backup to 2005 instance
I did a backup of a SQL2000 database (named Winstis) using the 2005 management studio. I then created a blank database on my 2005 instance (named Winstis). I then tried to do a database restore to the new 2005 database. I got an error:
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different error:
System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.SqlServer.Smo)
Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Winstis_Data.mdf
and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005 with all tables, store procedures, and functions?you can restore from a 2000 backup to a 2005 server, but only if the db you are restoring to doesn't exist on the 2005 server at restore time.
so delete the winstis db on your 2005 instance then restore from the bak, should work ok then.|||Hi Jezemine,
I actually tried it that way as well. I finally got an answer from another forum. The key was using the move options on the RESTORE.
Thanks though.
System.Data.SqlClient.SqlError: The backup set holds a backup of a database other than the existing 'Winstis' database. (Microsoft.SqlServer.Smo)
I even tried the option to Overwrite Existing database and I got a different error:
System.Data.SqlClient.SqlError: The operating system returned the error '32(The process cannot access the file because it is being used by another process.)' while attempting 'RestoreContainer::ValidateTargetForCreation' on 'D:\Program Files\Microsoft SQL Server\MSSQL\Data\test.ldf'. (Microsoft.SqlServer.Smo)
Also the 2005 instance database is on C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Winstis_Data.mdf
and the 2000 instance is on
D:\Program Files\Microsoft SQL Server\MSSQL\Data\Winstis_Data.mdf
How can I accomplish getting the database from 2000 to 2005 with all tables, store procedures, and functions?you can restore from a 2000 backup to a 2005 server, but only if the db you are restoring to doesn't exist on the 2005 server at restore time.
so delete the winstis db on your 2005 instance then restore from the bak, should work ok then.|||Hi Jezemine,
I actually tried it that way as well. I finally got an answer from another forum. The key was using the move options on the RESTORE.
Thanks though.
Subscribe to:
Posts (Atom)