Restoring to new database doesn't default DB file names

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

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

Thanks

Hello there

This is by design in SQL Server 2005.

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

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

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

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

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

Thanks,

Restoring to new database doesn't change logical DB name

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

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

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

Madhu

|||

what is the use of logical name?

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

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

Restoring to current info using LDF file?

Hi,
Just wondering if it is possible to get current info by
using the .LDF file? After the power outage last
Thursday, we lost a whole day of work (E: drive or .MDF
file was obliterated), but the F: drive or .LDF file is
available/okay/present. Can I use this file in anyway?
We only do backups at night not transactional or other
types of backups throughout the day (this will be changing
now...).
Thanks,
~DWithout the Main Database file then the log file is of no use to you. You
are going to have to RESTORE from backup I'm afraid
--
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"daj" <daj@.yeah.ca> wrote in message
news:074201c3657e$3442bee0$a501280a@.phx.gbl...
> Hi,
> Just wondering if it is possible to get current info by
> using the .LDF file? After the power outage last
> Thursday, we lost a whole day of work (E: drive or .MDF
> file was obliterated), but the F: drive or .LDF file is
> available/okay/present. Can I use this file in anyway?
> We only do backups at night not transactional or other
> types of backups throughout the day (this will be changing
> now...).
> Thanks,
> ~D|||IN addition to the other posters comments... you might try Log Explorer and
see if it will help you... ( that is part of what it might be able to do...)
www.lumigent.com
"daj" <daj@.yeah.ca> wrote in message
news:074201c3657e$3442bee0$a501280a@.phx.gbl...
> Hi,
> Just wondering if it is possible to get current info by
> using the .LDF file? After the power outage last
> Thursday, we lost a whole day of work (E: drive or .MDF
> file was obliterated), but the F: drive or .LDF file is
> available/okay/present. Can I use this file in anyway?
> We only do backups at night not transactional or other
> types of backups throughout the day (this will be changing
> now...).
> Thanks,
> ~D

Restoring to another server issue with data file sizes

I'm using SQL Server 2000 and need to restore a large database onto a different node. The problem is the original database has a 74 gb first datafile and the node where I need to restore it doesn't have a single drive that big. I'm trying to use a backup of the original database and restore it into an existing database on another node and am using the move options to put the files in the right places.

Is there a way I run the restore to split the 74 gb datafile across drives on my target node?

Thanks for any help.Is there a way I run the restore to split the 74 gb datafile across drives on my target node?

I don't think so; you would have to do this first on the source database and then edit the file paths when restoring to the backup database.

Regards,

hmscott

Restoring to a network paths?

I'm getting the error "The file \\xxx... is on a network path that is not supported for database files. File xx cannot be restored to \\xx..... Use WITH MOVE to identify a valid location for the file. Below is my code for the restore. I've even shared the folder on the network but still no success.

Try
Dim restoreToServer As New Server("xxxx")
Dim rest As New Restore
rest.Devices.AddDevice("\\xx\x$\Program Files\Microsoft SQL Server\MSSQL\BACKUP\myData\myData.BAK", DeviceType.File)

rest.Action = RestoreActionType.Database
rest.Database = "myDatabase"
rest.NoRecovery = False
rest.ReplaceDatabase = True

rest.RelocateFiles.Add(New RelocateFile("myData", "\\xx\data\myData.mdf"))
rest.RelocateFiles.Add(New RelocateFile("myData_log", "\\xx\logs\myData.ldf"))

rest.SqlRestore(restoreToServer)
Catch ex As Exception
MessageBox.Show(ex.InnerException.ToString)

End Try

Can you run a database from a network path?

Why would you want to do it?
|||

We are running the vb app on a desktop. To connect to the production db we issue smo commands. Those commands will backup the production database and then we need to do a restore on another database. To connect to any of the two mentioned we connect using \\host-machine\drive$ but this does not work.

|||That what is wrote for placing data files on a network shares some days ago:

http://forums.microsoft.com/MSDN/ShowPost.aspx?PostID=477137&SiteID=1

HTH, Jens Suessmeyer.

http://www.sqlserver2005.de

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.

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

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 SQL Server 6.5 .DAT file to SQL 2000

All,
My client has give me a 6.5 backup file in a .DAT format.
I want to restore it in SQL2000.
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>
You cannot restore it to SQL 2k.
You can only restore it (painfully) to a 6.5 system and then perform
upgrades on it.
Rick Sawtell
MCT, MCSD, MCDBA
|||No can do. You will have to build a SQL 6.5 server, restore the file, then
convert to 7.0/2000. Without the sysdevices, sysdatabases, and sysusages
information, you cannot restore a SQL 6.5 database. Unlike SQL 7.0 and
higher, SQL 6.5 database backups are not self-describing.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>
|||Hi,
SQL 6.5 and SQL 2000 architecture is different and since the backup restore
is not allowed. So only possibility is:-
1. Restore the 6.5 backup DAT file into SQL 6.5 machine
2. INstall SQL 2000 and service pack
3. Use Upgrade wizard rto upgrade the SQL 6.5 database to SQL 2000
4. Upgrade wizard will copy all the data / objects to SQL 2000.
Note:-
Execute a DBCC and update statistics before the upgrade
Thanks
Hari
SQL Server MVP
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>
|||In addition to the other posts:
You *might* be able to restore your 6.5 backup on another 6.5 machine even if you don't know the
database fragments (master..sysusages etc). But you will end up with data pages where SQL Server
expects log pages and vice versa. For data pages where SQL Server expects log pages, DBCC NEWALLOG
will return 2558 errors (I believe) and you have to physically move the data. Possibly create script
and transfer all over to a new database. Possibly by rebuilding all indexes. Not to be taken lightly
if you don't have good 6.5 expertise.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>
|||Thanks every one for taking time to reply :-)
Regards
Amit
"Amit" wrote:

> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>

Restoring SQL Server 6.5 .DAT file to SQL 2000

All,
My client has give me a 6.5 backup file in a .DAT format.
I want to restore it in SQL2000."Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>
You cannot restore it to SQL 2k.
You can only restore it (painfully) to a 6.5 system and then perform
upgrades on it.
Rick Sawtell
MCT, MCSD, MCDBA|||No can do. You will have to build a SQL 6.5 server, restore the file, then
convert to 7.0/2000. Without the sysdevices, sysdatabases, and sysusages
information, you cannot restore a SQL 6.5 database. Unlike SQL 7.0 and
higher, SQL 6.5 database backups are not self-describing.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>|||Hi,
SQL 6.5 and SQL 2000 architecture is different and since the backup restore
is not allowed. So only possibility is:-
1. Restore the 6.5 backup DAT file into SQL 6.5 machine
2. INstall SQL 2000 and service pack
3. Use Upgrade wizard rto upgrade the SQL 6.5 database to SQL 2000
4. Upgrade wizard will copy all the data / objects to SQL 2000.
Note:-
Execute a DBCC and update statistics before the upgrade
Thanks
Hari
SQL Server MVP
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>|||In addition to the other posts:
You *might* be able to restore your 6.5 backup on another 6.5 machine even if you don't know the
database fragments (master..sysusages etc). But you will end up with data pages where SQL Server
expects log pages and vice versa. For data pages where SQL Server expects log pages, DBCC NEWALLOG
will return 2558 errors (I believe) and you have to physically move the data. Possibly create script
and transfer all over to a new database. Possibly by rebuilding all indexes. Not to be taken lightly
if you don't have good 6.5 expertise.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>|||Thanks every one for taking time to reply :-)
Regards
Amit
"Amit" wrote:
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>

Restoring SQL Server 6.5 .DAT file to SQL 2000

All,
My client has give me a 6.5 backup file in a .DAT format.
I want to restore it in SQL2000."Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>
You cannot restore it to SQL 2k.
You can only restore it (painfully) to a 6.5 system and then perform
upgrades on it.
Rick Sawtell
MCT, MCSD, MCDBA|||No can do. You will have to build a SQL 6.5 server, restore the file, then
convert to 7.0/2000. Without the sysdevices, sysdatabases, and sysusages
information, you cannot restore a SQL 6.5 database. Unlike SQL 7.0 and
higher, SQL 6.5 database backups are not self-describing.
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>|||Hi,
SQL 6.5 and SQL 2000 architecture is different and since the backup restore
is not allowed. So only possibility is:-
1. Restore the 6.5 backup DAT file into SQL 6.5 machine
2. INstall SQL 2000 and service pack
3. Use Upgrade wizard rto upgrade the SQL 6.5 database to SQL 2000
4. Upgrade wizard will copy all the data / objects to SQL 2000.
Note:-
Execute a DBCC and update statistics before the upgrade
Thanks
Hari
SQL Server MVP
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>|||In addition to the other posts:
You *might* be able to restore your 6.5 backup on another 6.5 machine even i
f you don't know the
database fragments (master..sysusages etc). But you will end up with data pa
ges where SQL Server
expects log pages and vice versa. For data pages where SQL Server expects lo
g pages, DBCC NEWALLOG
will return 2558 errors (I believe) and you have to physically move the data
. Possibly create script
and transfer all over to a new database. Possibly by rebuilding all indexes.
Not to be taken lightly
if you don't have good 6.5 expertise.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Amit" <Amit@.discussions.microsoft.com> wrote in message
news:E15A0D61-F208-4FC8-B7A4-53A7461B24EA@.microsoft.com...
> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>|||Thanks every one for taking time to reply :-)
Regards
Amit
"Amit" wrote:

> All,
> My client has give me a 6.5 backup file in a .DAT format.
> I want to restore it in SQL2000.
>

Restoring SQL Server 2000 backup to Express

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

hi,

withers wrote:

When I try to restore my SQL Server 2000 backup file to a newly installed copy of SQL Server 2005 Express using Studio Express, I get the following error:

The backup set holds a backup of a database other than the existing 'UpperBridge' database. (Microsoft.sqlservfer.Express.smo)

The backup is made from an SQL Server 2000 database called 'UpperBridge'

I am trying to restore to a database called 'UpperBridge' which I created under 'New databases' in Studio Express.

Any help very much appreciated.

try forcing the "overwrite" of the database already present in the SQLExpress instance,
RESTORE DATABASE ....
....
WITH REPLACE;

or just drop the database before restoring (obviously take a full local backup of it before dropping it, if required )..

regards

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring or attaching the *.mdf file

Hai,

I am trying to attach the *.mdf file then i am getting the error 1813.
The problem occurs when our development database transcation running out of memory disk, then I dettach database and at the time of dettaching database some of users are connected. I kill the session of all users.
After I deleted the log file. Now when i am trying to attach the database then I am getting the error no 1813.

Anybody urgent reply

--
1) move the existing .MDF file to a new location (to backup)
2) Start SQL Server Enterprise manager, and create a new (dummy) database where the .MDF file is the exact same name and size of your old/existing .MDF. The ldf file can remain at 1mb in size.

3) Stop the SQL Server service and copy only the .MDF to the location of the new/dummy .MDF file.

4) Start SQL Server. At this point, the database should come up as suspect. You will then want to place the database into emergency bypass mode and rebuild the Transaction log. This can be accomplished by performing the following steps:

a) Change the database context to Master and allow updates to system tables. Note you will be performing this within Query Anayzler:
Use Master
go
sp_configure 'allow updates',1
reconfigure with override
go
b) Set the database in emergency bypass mode:
Select dbid, name, status from sysdatabases where name = '<database name>'
-- Note the value of status and write it down for future reference
begin tran
update sysdatabases set status = 32768 where name = '<database name>'
commit tran
c) Stop and restart your SQL Server. At this point, the database will come up in emergency mode. At this point, you will be able to browse, but not update any data within the database.

d) We will now want to rebuild the log/ldf file. To do this, you will want to run the following:

DBCC rebuild_log('<database name>','')
If you do not receive any errors, we will want to reset the status of the database by running the following commmand and restarting SQL Server:
use master
exec sp_dboption 'database name','single user',true
go
begin tran
update sysdatabases set status = 0 where name = '<database name>'
commit tran
e) set database option to not allow updates to the system tables:

sp_configure 'allow updates', 0
reconfigure with override
go
f) stop and restart SQL Server
When the server restarts, you will then want to launch Query Analyzer and run the following to validate the database and check the overall integrity:

use master
go
sp_dboption '<database name>','Single user',true
You will then want to run the following:
DBCC checkdb('<database name>')

|||Thank you so much. You just saved me alot of headaches. My transaction log spun out of control, it was up to 28 GB. I detached the db and tried to reatach and I got this problem. Normally it would just automatically create a log file I thought.

Thanks again!|||Thank You!!!

Restoring only .MDF file

Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I res
tore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.Katrhik
Regardless of recovery model of you SQL Server you will have log file.
if you detached your database and removed the log file so use
This example detaches pubs and then attaches one file from pubs to the
current server.
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf'Note : you will have log file created by sql
server with (if i remmember well) size of 2MB.
"Karthik" <anonymous@.discussions.microsoft.com> wrote in message
news:AA2705B7-F22A-4BB8-B9CD-6064D554721B@.microsoft.com...

quote:

---

> Hi,
> I have a backup of a database which is around 1.5 GB in size. Whenever I

---

restore the database via EM I get both the MDF and the LDF file.

quote:

---

> Because of this I need a tremendous amount of space in the hard disk.
> Is there anyway, wherein I can only restore the MDF file?
> Regards,
> Karthik.

---

|||Try simply attaching the MDF and a blank LDF files
Glen Victor
-- Karthik wrote: --
Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I res
tore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.sql

Restoring only .MDF file

Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I restore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.Katrhik
Regardless of recovery model of you SQL Server you will have log file.
if you detached your database and removed the log file so use
This example detaches pubs and then attaches one file from pubs to the
current server.
EXEC sp_detach_db @.dbname = 'pubs'
EXEC sp_attach_single_file_db @.dbname = 'pubs',
@.physname = 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\pubs.mdf'Note : you will have log file created by sql
server with (if i remmember well) size of 2MB.
"Karthik" <anonymous@.discussions.microsoft.com> wrote in message
news:AA2705B7-F22A-4BB8-B9CD-6064D554721B@.microsoft.com...
> Hi,
> I have a backup of a database which is around 1.5 GB in size. Whenever I
restore the database via EM I get both the MDF and the LDF file.
> Because of this I need a tremendous amount of space in the hard disk.
> Is there anyway, wherein I can only restore the MDF file?
> Regards,
> Karthik.|||Try simply attaching the MDF and a blank LDF files
Glen Victor
-- Karthik wrote: --
Hi,
I have a backup of a database which is around 1.5 GB in size. Whenever I restore the database via EM I get both the MDF and the LDF file.
Because of this I need a tremendous amount of space in the hard disk.
Is there anyway, wherein I can only restore the MDF file?
Regards,
Karthik.

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/
> > > >
> > > >
> > >
> > >
> >
> >
>

Restoring of backup file giving error

Hi,
Recently when I restoring my backup file from another XP 2003 standard
server on another XP 2003 server, I get the 'internal consistency error
occurred..., SQLState: 42000'
Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file
is valid but I can't restore it.
Therefore,
01) How can I ensure that my backup file is fine, able to restore back in
this case.
02) Is there any ways that I need to ensure/process to take note of before
backing up my database
03) How to fix above issues if I encountered similar issue again.
04) Any recommendation/advise to prevent similar problem again
05) Any good tool to fix/solve this issue.
Thank youSee what Books Online has to say about VERIFYONLY and you will see that it b
asically only check that
the file is readable and it is a SQL Server backup file. With that in mind:

> 01) How can I ensure that my backup file is fine, able to restore back in this cas
e.
Perform an actual restore of the backup.

> 02) Is there any ways that I need to ensure/process to take note of before backing
up my database
Run regular DBCC CHECKDB for the database. You should always have the option
to restore from a
database backup taken when the database was clean (and then possibly restore
all subsequent
transaction log backups if you want to try to reach zero data loss).

> 03) How to fix above issues if I encountered similar issue again.
See 02

> 04) Any recommendation/advise to prevent similar problem again
See 02

> 05) Any good tool to fix/solve this issue.
The problem is that probably the source database has a corruption in it. Res
tore will not allow
restoring an inconsistent database. And you probably don't want to bring ove
r the database with
corruption in it. I recommend you start on the source machine and follow bel
ow guidelines:
http://www.karaszi.com/SQLServer/in..._suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Recently when I restoring my backup file from another XP 2003 standard ser
ver on another XP 2003
> server, I get the 'internal consistency error occurred..., SQLState: 42000
'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup fi
le is valid but I can't
> restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in
this case.
> 02) Is there any ways that I need to ensure/process to take note of before
backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>|||From BOL:
"Verifies the backup but does not restore the backup. Checks to see that the
backup set is complete and that all volumes are readable. However, RESTORE
VERIFYONLY does not attempt to verify the structure of the data contained in
the backup volumes"
In effect, it checks that all the files are there nd that the header
inforamtion is right.
With SQL Server 2000, the only way you know you have valid backups is the
actaully restore it on another machine.
What is the actual error code as you might have actual DB corruption, and
the restore process is actaully finding it.
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vladimir Sim" wrote:

> Hi,
> Recently when I restoring my backup file from another XP 2003 standard
> server on another XP 2003 server, I get the 'internal consistency error
> occurred..., SQLState: 42000'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup fi
le
> is valid but I can't restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in
> this case.
> 02) Is there any ways that I need to ensure/process to take note of before
> backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>|||Hi,
Thank for your valuable advise.
However, I have problem restoring the same database that was backup on
Thursday
but able to restore the same database that was backup on Friday, Satursday
and Sunday.
Why is that so, please explain ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||Hi,
In addition, if the only way to test that the backup is valid is by restore,
is there
any best know way to automate this process as I need to ensure the backup
files
are not corrupted.
Does other third party SQL server tool offer any help in this area ? Do they
suffer the same problem ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/in..._suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>|||> Why is that so, please explain ?
Impossible to answer. If you post the RESTORE command you execute and the er
ror message(s), we might
get a clue. Also, post the RESTORE command you execute from a backup that wa
s successfully restored
(the Fri, Sat or Sun backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:%236YE4schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thank for your valuable advise.
> However, I have problem restoring the same database that was backup on Thu
rsday
> but able to restore the same database that was backup on Friday, Satursday
and Sunday.
> Why is that so, please explain ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>|||It all depends on how you schedule your backups. If you schedule a simple BA
CKUP command from a TSQL
Agent jobstep, then just add a jobstep that does a RESTORE (to a new databas
e name) and then drop
that database. Log the errors to an output file and make sure you are notifi
ed on errors.
Most people don't to restore of each backup, but are satisfied doing regular
DBCC CHECKDB and then
do test restore at more or less random intervals.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:uUje7schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In addition, if the only way to test that the backup is valid is by restor
e, is there
> any best know way to automate this process as I need to ensure the backup
files
> are not corrupted.
> Does other third party SQL server tool offer any help in this area ? Do th
ey
> suffer the same problem ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>

Restoring of backup file giving error

Hi,
Recently when I restoring my backup file from another XP 2003 standard
server on another XP 2003 server, I get the 'internal consistency error
occurred..., SQLState: 42000'
Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file
is valid but I can't restore it.
Therefore,
01) How can I ensure that my backup file is fine, able to restore back in
this case.
02) Is there any ways that I need to ensure/process to take note of before
backing up my database
03) How to fix above issues if I encountered similar issue again.
04) Any recommendation/advise to prevent similar problem again
05) Any good tool to fix/solve this issue.
Thank you
See what Books Online has to say about VERIFYONLY and you will see that it basically only check that
the file is readable and it is a SQL Server backup file. With that in mind:

> 01) How can I ensure that my backup file is fine, able to restore back in this case.
Perform an actual restore of the backup.

> 02) Is there any ways that I need to ensure/process to take note of before backing up my database
Run regular DBCC CHECKDB for the database. You should always have the option to restore from a
database backup taken when the database was clean (and then possibly restore all subsequent
transaction log backups if you want to try to reach zero data loss).

> 03) How to fix above issues if I encountered similar issue again.
See 02

> 04) Any recommendation/advise to prevent similar problem again
See 02

> 05) Any good tool to fix/solve this issue.
The problem is that probably the source database has a corruption in it. Restore will not allow
restoring an inconsistent database. And you probably don't want to bring over the database with
corruption in it. I recommend you start on the source machine and follow below guidelines:
http://www.karaszi.com/SQLServer/inf...suspect_db.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
> Hi,
> Recently when I restoring my backup file from another XP 2003 standard server on another XP 2003
> server, I get the 'internal consistency error occurred..., SQLState: 42000'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file is valid but I can't
> restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in this case.
> 02) Is there any ways that I need to ensure/process to take note of before backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>
|||From BOL:
"Verifies the backup but does not restore the backup. Checks to see that the
backup set is complete and that all volumes are readable. However, RESTORE
VERIFYONLY does not attempt to verify the structure of the data contained in
the backup volumes"
In effect, it checks that all the files are there nd that the header
inforamtion is right.
With SQL Server 2000, the only way you know you have valid backups is the
actaully restore it on another machine.
What is the actual error code as you might have actual DB corruption, and
the restore process is actaully finding it.
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Vladimir Sim" wrote:

> Hi,
> Recently when I restoring my backup file from another XP 2003 standard
> server on another XP 2003 server, I get the 'internal consistency error
> occurred..., SQLState: 42000'
> Even using the 'RESTORE VERIFYONLY ...' command reported that my backup file
> is valid but I can't restore it.
> Therefore,
> 01) How can I ensure that my backup file is fine, able to restore back in
> this case.
> 02) Is there any ways that I need to ensure/process to take note of before
> backing up my database
> 03) How to fix above issues if I encountered similar issue again.
> 04) Any recommendation/advise to prevent similar problem again
> 05) Any good tool to fix/solve this issue.
> Thank you
>
>
>
|||Hi,
Thank for your valuable advise.
However, I have problem restoring the same database that was backup on
Thursday
but able to restore the same database that was backup on Friday, Satursday
and Sunday.
Why is that so, please explain ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>
|||Hi,
In addition, if the only way to test that the backup is valid is by restore,
is there
any best know way to automate this process as I need to ensure the backup
files
are not corrupted.
Does other third party SQL server tool offer any help in this area ? Do they
suffer the same problem ?
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
> See what Books Online has to say about VERIFYONLY and you will see that it
> basically only check that the file is readable and it is a SQL Server
> backup file. With that in mind:
>
> Perform an actual restore of the backup.
>
> Run regular DBCC CHECKDB for the database. You should always have the
> option to restore from a database backup taken when the database was clean
> (and then possibly restore all subsequent transaction log backups if you
> want to try to reach zero data loss).
>
>
> See 02
>
> See 02
>
> The problem is that probably the source database has a corruption in it.
> Restore will not allow restoring an inconsistent database. And you
> probably don't want to bring over the database with corruption in it. I
> recommend you start on the source machine and follow below guidelines:
> http://www.karaszi.com/SQLServer/inf...suspect_db.asp
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
> news:u%23VLlBhgFHA.572@.TK2MSFTNGP15.phx.gbl...
>
|||> Why is that so, please explain ?
Impossible to answer. If you post the RESTORE command you execute and the error message(s), we might
get a clue. Also, post the RESTORE command you execute from a backup that was successfully restored
(the Fri, Sat or Sun backup).
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:%236YE4schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> Thank for your valuable advise.
> However, I have problem restoring the same database that was backup on Thursday
> but able to restore the same database that was backup on Friday, Satursday and Sunday.
> Why is that so, please explain ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>
|||It all depends on how you schedule your backups. If you schedule a simple BACKUP command from a TSQL
Agent jobstep, then just add a jobstep that does a RESTORE (to a new database name) and then drop
that database. Log the errors to an output file and make sure you are notified on errors.
Most people don't to restore of each backup, but are satisfied doing regular DBCC CHECKDB and then
do test restore at more or less random intervals.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Vladimir Sim" <fengchun@.newsgroup.nospam> wrote in message
news:uUje7schFHA.3936@.TK2MSFTNGP10.phx.gbl...
> Hi,
> In addition, if the only way to test that the backup is valid is by restore, is there
> any best know way to automate this process as I need to ensure the backup files
> are not corrupted.
> Does other third party SQL server tool offer any help in this area ? Do they
> suffer the same problem ?
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:O50riKhgFHA.3656@.TK2MSFTNGP09.phx.gbl...
>