Restore databases under SQL 6.5

Hello,

I'm running SQL 6.5 on an old NT4 server. I built a new W2K server with SQL 6.5.

When I tried to restore a database to the new server I got the following error: "The database you are attempting to LOAD was DUMPed under a different sort order ID (52) than the one currently running on this server (32), and at least one of them is a non-binary sort order."
Since SQL 6.5 is no longer supported by Microsoft, I have a hard time finding information related to that error. Can someone help me and tell me what's wrong?

Regards,
Toan.The default sort order for MS-SQL 6.5 was 52. My guess is that you specified a different sort order, and that you can't restore the database unless you reinstall SQL 6.5 with the default sort order.

-PatP|||Pat,

Thanks for your help. I installed SQL 6.5 with the default settings. Where do you specify the sort order?|||It has been a LONG time since I've installed MS-SQL 6.5, but the sort order is specified somewhere very early in the installation process.

Have you considered using SQL 7.0 instead, and restoring the dump into a database there? It seems to me that you can restore a 6.5 dump onto a 7.0 server, and that they aren't nearly as finicky about sort orders.

-PatP|||You were right, the sort order ID has to be selected during the installation. Now I have to figure out which sort order was used on the old box.

Thanks Pat for your help.

Toan.|||52 is the default value, if that helps any.

-PatPsql

Restore databases into a new installation of SQL Server

Hi,
I want to restore my databases from the old SQL Server installation
into a new installation of SQL Server. My new installation of SQL
Server has different data path from the old installation. In addition,
the data owners of some databases in the old SQL Server installation
are not dbo. I have backups for all the databases in the old
installation SQL Server.
Can anyone tell me how to restore my databases from the old SQL Server
installation into a new installation of SQL Server for my case? (I
want to use new path for data and log files as well as keep the old
data owners in the restored databases.) Do I need to perform restore
operation against my master database in the new installation of SQL
Server? (The data path for a newly created database has been changed
in the new installation of SQL Server.)
My idea is:
1. Create new databases whose names are the same with the databases of
the old SQL installation. Set the new databases with the new data path
I want.
2. Create new login accounts whose user names and passwords are the
same with the old login accounts. Assign appropriate permissions to
the new login accounts (e.g, the data owner for some database).
3. Use Enterprise Manager to perform restore operation against the
newly created databases using the backups from the old SQL Server
installation.
Please advice.
Hai-ChuHave a look at these:
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
"Hai-Chu Hsu" <seapearl1023@.ms65.url.com.tw> wrote in message
news:e6c8ee4f.0402281515.23041d51@.posting.google.com...
> Hi,
> I want to restore my databases from the old SQL Server installation
> into a new installation of SQL Server. My new installation of SQL
> Server has different data path from the old installation. In addition,
> the data owners of some databases in the old SQL Server installation
> are not dbo. I have backups for all the databases in the old
> installation SQL Server.
> Can anyone tell me how to restore my databases from the old SQL Server
> installation into a new installation of SQL Server for my case? (I
> want to use new path for data and log files as well as keep the old
> data owners in the restored databases.) Do I need to perform restore
> operation against my master database in the new installation of SQL
> Server? (The data path for a newly created database has been changed
> in the new installation of SQL Server.)
> My idea is:
> 1. Create new databases whose names are the same with the databases of
> the old SQL installation. Set the new databases with the new data path
> I want.
> 2. Create new login accounts whose user names and passwords are the
> same with the old login accounts. Assign appropriate permissions to
> the new login accounts (e.g, the data owner for some database).
> 3. Use Enterprise Manager to perform restore operation against the
> newly created databases using the backups from the old SQL Server
> installation.
> Please advice.
> Hai-Chu

Restore databases into a new installation of SQL Server

Hi,

I want to restore my databases from the old SQL Server installation
into a new installation of SQL Server. My new installation of SQL
Server has different data path from the old installation. In addition,
the data owners of some databases in the old SQL Server installation
are not dbo. I have backups for all the databases in the old
installation SQL Server.

Can anyone tell me how to restore my databases from the old SQL Server
installation into a new installation of SQL Server for my case? (I
want to use new path for data and log files as well as keep the old
data owners in the restored databases.) Do I need to perform restore
operation against my master database in the new installation of SQL
Server? (The data path for a newly created database has been changed
in the new installation of SQL Server.)

My idea is:
1. Create new databases whose names are the same with the databases of
the old SQL installation. Set the new databases with the new data path
I want.
2. Create new login accounts whose user names and passwords are the
same with the old login accounts. Assign appropriate permissions to
the new login accounts (e.g, the data owner for some database).
3. Use Enterprise Manager to perform restore operation against the
newly created databases using the backups from the old SQL Server
installation.

Please advice.

Hai-Chu"Hai-Chu Hsu" <seapearl1023@.ms65.url.com.tw> wrote in message
news:e6c8ee4f.0402281517.401bd196@.posting.google.c om...
> Hi,
> I want to restore my databases from the old SQL Server installation
> into a new installation of SQL Server. My new installation of SQL
> Server has different data path from the old installation. In addition,
> the data owners of some databases in the old SQL Server installation
> are not dbo. I have backups for all the databases in the old
> installation SQL Server.
> Can anyone tell me how to restore my databases from the old SQL Server
> installation into a new installation of SQL Server for my case? (I
> want to use new path for data and log files as well as keep the old
> data owners in the restored databases.) Do I need to perform restore
> operation against my master database in the new installation of SQL
> Server? (The data path for a newly created database has been changed
> in the new installation of SQL Server.)
> My idea is:
> 1. Create new databases whose names are the same with the databases of
> the old SQL installation. Set the new databases with the new data path
> I want.
> 2. Create new login accounts whose user names and passwords are the
> same with the old login accounts. Assign appropriate permissions to
> the new login accounts (e.g, the data owner for some database).
> 3. Use Enterprise Manager to perform restore operation against the
> newly created databases using the backups from the old SQL Server
> installation.

When restoring via Enterprise manager you have the option
of specifying new physical data and logfiles (paths). Also,
there is the option to restore from a physical device that
does not yet exist (eg: one of your data backups).

In EM you can add a device and point at the data file to
restore, and as above, respecify physical paths.

It should work OK.
Good luck,

Pete Brown
Falls Creek
Oz.
www.mountainman.com.au

Restore databases into a new installation of SQL Server

Hi,
I want to restore my databases from the old SQL Server installation
into a new installation of SQL Server. My new installation of SQL
Server has different data path from the old installation. In addition,
the data owners of some databases in the old SQL Server installation
are not dbo. I have backups for all the databases in the old
installation SQL Server.
Can anyone tell me how to restore my databases from the old SQL Server
installation into a new installation of SQL Server for my case? (I
want to use new path for data and log files as well as keep the old
data owners in the restored databases.) Do I need to perform restore
operation against my master database in the new installation of SQL
Server? (The data path for a newly created database has been changed
in the new installation of SQL Server.)
My idea is:
1. Create new databases whose names are the same with the databases of
the old SQL installation. Set the new databases with the new data path
I want.
2. Create new login accounts whose user names and passwords are the
same with the old login accounts. Assign appropriate permissions to
the new login accounts (e.g, the data owner for some database).
3. Use Enterprise Manager to perform restore operation against the
newly created databases using the backups from the old SQL Server
installation.
Please advice.
Hai-ChuHave a look at these:
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
"Hai-Chu Hsu" <seapearl1023@.ms65.url.com.tw> wrote in message
news:e6c8ee4f.0402281515.23041d51@.posting.google.com...
> Hi,
> I want to restore my databases from the old SQL Server installation
> into a new installation of SQL Server. My new installation of SQL
> Server has different data path from the old installation. In addition,
> the data owners of some databases in the old SQL Server installation
> are not dbo. I have backups for all the databases in the old
> installation SQL Server.
> Can anyone tell me how to restore my databases from the old SQL Server
> installation into a new installation of SQL Server for my case? (I
> want to use new path for data and log files as well as keep the old
> data owners in the restored databases.) Do I need to perform restore
> operation against my master database in the new installation of SQL
> Server? (The data path for a newly created database has been changed
> in the new installation of SQL Server.)
> My idea is:
> 1. Create new databases whose names are the same with the databases of
> the old SQL installation. Set the new databases with the new data path
> I want.
> 2. Create new login accounts whose user names and passwords are the
> same with the old login accounts. Assign appropriate permissions to
> the new login accounts (e.g, the data owner for some database).
> 3. Use Enterprise Manager to perform restore operation against the
> newly created databases using the backups from the old SQL Server
> installation.
> Please advice.
> Hai-Chu

restore databases in T-SQL.

--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('d')
GO
Am I correct in creating the database and the way of backup?
Would you teach me the senarios that restore the database to 'a','b','c','d'
states?
Thanks in advance.You'll need to add another backup to backup 'd', but something like this
should do:
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('d')
GO
--whole backup 3
BACKUP DATABASE Test TO mydiskdump
go
restore headeronly from mydiskdump
go
restore database Test from mydiskdump with file = 1, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 1, norecovery
restore database Test from mydiskdump with file = 2, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 3, norecovery
restore database Test from mydiskdump with file = 4, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 5, recovery
select * from Test.dbo.tblTest
go
Note that dump devices can store multiple backup sets. You can get the
backup sets that a dump device contains using the restore headeronly command
which lets you know which backup sets to restore. I'd suggest you consider
naming your backup sets though as this can help work out which backups to
restore as well..
HTH
Regards,
Greg Linwood
SQL Server MVP
"haode" <haode@.hao.com> wrote in message
news:%23eNTtec9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> --Drop database
> if exists (select * from master.dbo.sysdatabases where name='Test')
> drop database Test
> GO
> --Drop device
> if exists (select * from master.dbo.sysdevices where name='mydiskdump')
> exec master.dbo.sp_dropdevice mydiskdump
> GO
> --Create Test database
> CREATE DATABASE Test
> ON
> ( NAME = Test_dat,
> FILENAME = 'c:\Test.mdf',
> SIZE = 5,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'Test_log',
> FILENAME = 'c:\Test.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> GO
> --add device
> EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
> GO
> CREATE TABLE Test.dbo.tblTest (
> col1 char(1)
> )
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values ('a')
> GO
> --whole backup 1
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('b')
> GO
> --differential backup 1
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> --whole backup 2
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('c')
> GO
> --differential backup 2
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('d')
> GO
> Am I correct in creating the database and the way of backup?
> Would you teach me the senarios that restore the database to
'a','b','c','d'
> states?
> Thanks in advance.
>

restore databases in T-SQL.

--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest values
insert into Test.dbo.tblTest values('d')
GO
Am I correct in creating the database and the way of backup?
Would you teach me the senarios that restore the database to 'a','b','c','d'
states?
Thanks in advance.You'll need to add another backup to backup 'd', but something like this
should do:
--Drop database
if exists (select * from master.dbo.sysdatabases where name='Test')
drop database Test
GO
--Drop device
if exists (select * from master.dbo.sysdevices where name='mydiskdump')
exec master.dbo.sp_dropdevice mydiskdump
GO
--Create Test database
CREATE DATABASE Test
ON
( NAME = Test_dat,
FILENAME = 'c:\Test.mdf',
SIZE = 5,
MAXSIZE = 50,
FILEGROWTH = 5 )
LOG ON
( NAME = 'Test_log',
FILENAME = 'c:\Test.ldf',
SIZE = 5MB,
MAXSIZE = 25MB,
FILEGROWTH = 5MB )
GO
--add device
EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
GO
CREATE TABLE Test.dbo.tblTest (
col1 char(1)
)
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values ('a')
GO
--whole backup 1
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('b')
GO
--differential backup 1
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
--whole backup 2
BACKUP DATABASE Test TO mydiskdump
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('c')
GO
--differential backup 2
BACKUP DATABASE Test
TO mydiskdump
WITH DIFFERENTIAL
GO
delete Test.dbo.tblTest
insert into Test.dbo.tblTest values('d')
GO
--whole backup 3
BACKUP DATABASE Test TO mydiskdump
go
restore headeronly from mydiskdump
go
restore database Test from mydiskdump with file = 1, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 1, norecovery
restore database Test from mydiskdump with file = 2, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 3, norecovery
restore database Test from mydiskdump with file = 4, recovery
select * from Test.dbo.tblTest
restore database Test from mydiskdump with file = 5, recovery
select * from Test.dbo.tblTest
go
Note that dump devices can store multiple backup sets. You can get the
backup sets that a dump device contains using the restore headeronly command
which lets you know which backup sets to restore. I'd suggest you consider
naming your backup sets though as this can help work out which backups to
restore as well..
HTH
Regards,
Greg Linwood
SQL Server MVP
"haode" <haode@.hao.com> wrote in message
news:%23eNTtec9DHA.1504@.TK2MSFTNGP12.phx.gbl...
> --Drop database
> if exists (select * from master.dbo.sysdatabases where name='Test')
> drop database Test
> GO
> --Drop device
> if exists (select * from master.dbo.sysdevices where name='mydiskdump')
> exec master.dbo.sp_dropdevice mydiskdump
> GO
> --Create Test database
> CREATE DATABASE Test
> ON
> ( NAME = Test_dat,
> FILENAME = 'c:\Test.mdf',
> SIZE = 5,
> MAXSIZE = 50,
> FILEGROWTH = 5 )
> LOG ON
> ( NAME = 'Test_log',
> FILENAME = 'c:\Test.ldf',
> SIZE = 5MB,
> MAXSIZE = 25MB,
> FILEGROWTH = 5MB )
> GO
> --add device
> EXEC master.dbo.sp_addumpdevice 'disk', 'mydiskdump', 'c:\dump.bak'
> GO
> CREATE TABLE Test.dbo.tblTest (
> col1 char(1)
> )
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values ('a')
> GO
> --whole backup 1
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('b')
> GO
> --differential backup 1
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> --whole backup 2
> BACKUP DATABASE Test TO mydiskdump
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('c')
> GO
> --differential backup 2
> BACKUP DATABASE Test
> TO mydiskdump
> WITH DIFFERENTIAL
> GO
> delete Test.dbo.tblTest values
> insert into Test.dbo.tblTest values('d')
> GO
> Am I correct in creating the database and the way of backup?
> Would you teach me the senarios that restore the database to
'a','b','c','d'
> states?
> Thanks in advance.
>sql

Restore Databases in AS 2005 from CAB Files created in AS 2000

I have to restore some analysis services 2000 databases on AS 2005.

Is there some tool which can perform this?

Mathew,

To migrate AS2000 databases to AS2005, you can use the Migration Wizard (MigrationWizard.exe). For more info, please see: <http://msdn2.microsoft.com/en-us/library/ms143409.aspx>.

Hope this helps,

Artur

|||

I have CAB files being created by another team. Previously i used to restore them on my SQL 2000 Analysis Services using msmdarch.

Now i am planning to upgrade to SQL server 2005. But the team which creates the CAB files will continue to be on SQL 2000 Analysis Services for at least 6 months.

Therefore migrationwizard will not be helpful in my case. (I don't have access to their SQL Server or Analysis Server databases)

|||

If your machine already has AS2000 installed, you could install AS2005 on the same machine as a named instance and then restore the CAB files onto your local AS2000 server and use the migration wizard to migrate these databases from your AS2000 instance to the AS2005 instance on the same machine.

--Artur

|||

Hello,

I understand that the solution of Artur will work, if you have access to the relational database from which the cube is calculated because when you finish migrate the cube it has to be recalculated. I have the same problem (a few cubes that I′d like to try in 2005, but I don′t posess the access to the relational database). Is there any way of migrating the cab without the access to the relational db?

Regards

restore databases 7.0

I have taken backups of 3 datbases

as

man1.dbf

msdb,model databases on drive d: but not taken master datbases then i had to format the c: on which sql server installed.

can i take full restore in this case or i have to create datbases and then sp_attach and sp_detach databases pls help me.

Some more details would be helpful:

1) what version of SQL Server is involved? Is it 7.0 or other?

2) What command was used to produce the backup files?

That will help us figure out the best course.

|||

how have u taken the backup. Is it using Backup Database command or using SP_Detach_db to detach the db and then backup the mdf and ldf files. If you have used detach method you can use sp_attach_db to attach the database. If you have used Backup database command then you need to use Restore database command probably with MOVE Option.

Madhu

Restore database...schedule

SQL Server Enterprise Edition
Microsoft Windows NT - 5.2 (3790)
Product version 8.00.760 (SP3)
1. I am moving two databases to different location (Both ldf and mdf files
because of size) on the same server. Can I automate the process and schedule
to run it at midnight. When I try to create a restore task using enterprise
manager it does not give me a option to schedule the task.
2. While performing same task(restore), few services on another server (Non
sql server) needs to be stopped, once restore is complete needs to be started
again. How to automate (start and stop services).
--
ontario, canadaProbably better to detach the database(s), move the files, thn re-attach
Check out sp_detach_db and sp_attach_db in Books Online. You can write a
few simple T-SQL statements and schedule these as a SQL job for the Agent to
run
Step 1 - sp_detach...etc.
Step 2 - xp_cmdshell 'copy the files...'
Step 3 - sp_attach_db
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
> SQL Server Enterprise Edition
> Microsoft Windows NT - 5.2 (3790)
> Product version 8.00.760 (SP3)
> 1. I am moving two databases to different location (Both ldf and mdf files
> because of size) on the same server. Can I automate the process and
> schedule
> to run it at midnight. When I try to create a restore task using
> enterprise
> manager it does not give me a option to schedule the task.
> 2. While performing same task(restore), few services on another server
> (Non
> sql server) needs to be stopped, once restore is complete needs to be
> started
> again. How to automate (start and stop services).
> --
> ontario, canada|||Can I schedule database restore. I have schedules non sql services to stop
at desired time.
--
ontario, canada
"Kevin3NF" wrote:
> Probably better to detach the database(s), move the files, thn re-attach
> Check out sp_detach_db and sp_attach_db in Books Online. You can write a
> few simple T-SQL statements and schedule these as a SQL job for the Agent to
> run
> Step 1 - sp_detach...etc.
> Step 2 - xp_cmdshell 'copy the files...'
> Step 3 - sp_attach_db
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
> >
> > SQL Server Enterprise Edition
> > Microsoft Windows NT - 5.2 (3790)
> > Product version 8.00.760 (SP3)
> >
> > 1. I am moving two databases to different location (Both ldf and mdf files
> > because of size) on the same server. Can I automate the process and
> > schedule
> > to run it at midnight. When I try to create a restore task using
> > enterprise
> > manager it does not give me a option to schedule the task.
> >
> > 2. While performing same task(restore), few services on another server
> > (Non
> > sql server) needs to be stopped, once restore is complete needs to be
> > started
> > again. How to automate (start and stop services).
> > --
> > ontario, canada
>
>|||You can. Create a job with a RESTORE Database statement in it...
--
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:4B5A046B-5A3F-4FB7-8FED-D4319B59DF81@.microsoft.com...
> Can I schedule database restore. I have schedules non sql services to
> stop
> at desired time.
> --
> ontario, canada
>
> "Kevin3NF" wrote:
>> Probably better to detach the database(s), move the files, thn re-attach
>> Check out sp_detach_db and sp_attach_db in Books Online. You can write a
>> few simple T-SQL statements and schedule these as a SQL job for the Agent
>> to
>> run
>> Step 1 - sp_detach...etc.
>> Step 2 - xp_cmdshell 'copy the files...'
>> Step 3 - sp_attach_db
>> --
>> Kevin3NF
>> SQL Server dude
>> You want fries with that?
>> http://kevin3nf.blogspot.com/
>> I only check the newsgroups during work hours, M-F.
>> Hit my blog and the contact links if necessary...I may be available.
>>
>> "db" <db@.discussions.microsoft.com> wrote in message
>> news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
>> >
>> > SQL Server Enterprise Edition
>> > Microsoft Windows NT - 5.2 (3790)
>> > Product version 8.00.760 (SP3)
>> >
>> > 1. I am moving two databases to different location (Both ldf and mdf
>> > files
>> > because of size) on the same server. Can I automate the process and
>> > schedule
>> > to run it at midnight. When I try to create a restore task using
>> > enterprise
>> > manager it does not give me a option to schedule the task.
>> >
>> > 2. While performing same task(restore), few services on another server
>> > (Non
>> > sql server) needs to be stopped, once restore is complete needs to be
>> > started
>> > again. How to automate (start and stop services).
>> > --
>> > ontario, canada
>>

Restore database...schedule

SQL Server Enterprise Edition
Microsoft Windows NT - 5.2 (3790)
Product version 8.00.760 (SP3)
1. I am moving two databases to different location (Both ldf and mdf files
because of size) on the same server. Can I automate the process and schedule
to run it at midnight. When I try to create a restore task using enterprise
manager it does not give me a option to schedule the task.
2. While performing same task(restore), few services on another server (Non
sql server) needs to be stopped, once restore is complete needs to be started
again. How to automate (start and stop services).
ontario, canada
Probably better to detach the database(s), move the files, thn re-attach
Check out sp_detach_db and sp_attach_db in Books Online. You can write a
few simple T-SQL statements and schedule these as a SQL job for the Agent to
run
Step 1 - sp_detach...etc.
Step 2 - xp_cmdshell 'copy the files...'
Step 3 - sp_attach_db
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
> SQL Server Enterprise Edition
> Microsoft Windows NT - 5.2 (3790)
> Product version 8.00.760 (SP3)
> 1. I am moving two databases to different location (Both ldf and mdf files
> because of size) on the same server. Can I automate the process and
> schedule
> to run it at midnight. When I try to create a restore task using
> enterprise
> manager it does not give me a option to schedule the task.
> 2. While performing same task(restore), few services on another server
> (Non
> sql server) needs to be stopped, once restore is complete needs to be
> started
> again. How to automate (start and stop services).
> --
> ontario, canada
|||Can I schedule database restore. I have schedules non sql services to stop
at desired time.
ontario, canada
"Kevin3NF" wrote:

> Probably better to detach the database(s), move the files, thn re-attach
> Check out sp_detach_db and sp_attach_db in Books Online. You can write a
> few simple T-SQL statements and schedule these as a SQL job for the Agent to
> run
> Step 1 - sp_detach...etc.
> Step 2 - xp_cmdshell 'copy the files...'
> Step 3 - sp_attach_db
> --
> Kevin3NF
> SQL Server dude
> You want fries with that?
> http://kevin3nf.blogspot.com/
> I only check the newsgroups during work hours, M-F.
> Hit my blog and the contact links if necessary...I may be available.
>
> "db" <db@.discussions.microsoft.com> wrote in message
> news:63CA1294-582D-47BF-9508-65E44EDF74C6@.microsoft.com...
>
>
|||You can. Create a job with a RESTORE Database statement in it...
Kevin3NF
SQL Server dude
You want fries with that?
http://kevin3nf.blogspot.com/
I only check the newsgroups during work hours, M-F.
Hit my blog and the contact links if necessary...I may be available.
"db" <db@.discussions.microsoft.com> wrote in message
news:4B5A046B-5A3F-4FB7-8FED-D4319B59DF81@.microsoft.com...[vbcol=seagreen]
> Can I schedule database restore. I have schedules non sql services to
> stop
> at desired time.
> --
> ontario, canada
>
> "Kevin3NF" wrote:

Restore Database With Move

OK. I have a good one. I have the following setup:
ServerA
Data on: C:\Data
Log on: C:\Logs
ServerB
System Databases on: D:\SysData
Data on: E:\Data
Indexes on : D:\Indexes
Log on: F:\Logs
So, what I want to do is restore a database from ServerA to ServerB. Placing the
files and data in the correct locations. I want the data in a filegroup called
DatabaseName_Data. Problem is when I restore to ServerB, the data always goes to
PRIMARY filegroup during restore. The only thing I can find is to manually
change the location of the data for each table in Enterprise Manager (YUCK!)
Isn't there an easier way?
Thanks for the help
DarrellYou cannot change file or filegroup layout using backup/restore (which basically gives you an image
of your database). Either use EM to move or do it from QA using more or less clever/complicated TSQL
scripts. You change location of a table by (re)creating a clustered index. For an index, you rebuild
the index.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB. Placing the files and data in
> the correct locations. I want the data in a filegroup called DatabaseName_Data. Problem is when I
> restore to ServerB, the data always goes to PRIMARY filegroup during restore. The only thing I can
> find is to manually change the location of the data for each table in Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell|||Refer to "How to restore files to a new location (Transact-SQL)" in BOL...
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB.
> Placing the files and data in the correct locations. I want the data in a
> filegroup called DatabaseName_Data. Problem is when I restore to ServerB,
> the data always goes to PRIMARY filegroup during restore. The only thing I
> can find is to manually change the location of the data for each table in
> Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell

Restore Database With Move

OK. I have a good one. I have the following setup:
ServerA
Data on: C:\Data
Log on: C:\Logs
ServerB
System Databases on: D:\SysData
Data on: E:\Data
Indexes on : D:\Indexes
Log on: F:\Logs
So, what I want to do is restore a database from ServerA to ServerB. Placing the
files and data in the correct locations. I want the data in a filegroup called
DatabaseName_Data. Problem is when I restore to ServerB, the data always goes to
PRIMARY filegroup during restore. The only thing I can find is to manually
change the location of the data for each table in Enterprise Manager (YUCK!)
Isn't there an easier way?
Thanks for the help
Darrell
You cannot change file or filegroup layout using backup/restore (which basically gives you an image
of your database). Either use EM to move or do it from QA using more or less clever/complicated TSQL
scripts. You change location of a table by (re)creating a clustered index. For an index, you rebuild
the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB. Placing the files and data in
> the correct locations. I want the data in a filegroup called DatabaseName_Data. Problem is when I
> restore to ServerB, the data always goes to PRIMARY filegroup during restore. The only thing I can
> find is to manually change the location of the data for each table in Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell
|||Refer to "How to restore files to a new location (Transact-SQL)" in BOL...
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB.
> Placing the files and data in the correct locations. I want the data in a
> filegroup called DatabaseName_Data. Problem is when I restore to ServerB,
> the data always goes to PRIMARY filegroup during restore. The only thing I
> can find is to manually change the location of the data for each table in
> Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell

Restore Database With Move

OK. I have a good one. I have the following setup:
ServerA
Data on: C:\Data
Log on: C:\Logs
ServerB
System Databases on: D:\SysData
Data on: E:\Data
Indexes on : D:\Indexes
Log on: F:\Logs
So, what I want to do is restore a database from ServerA to ServerB. Placing
the
files and data in the correct locations. I want the data in a filegroup call
ed
DatabaseName_Data. Problem is when I restore to ServerB, the data always goe
s to
PRIMARY filegroup during restore. The only thing I can find is to manually
change the location of the data for each table in Enterprise Manager (YUCK!)
Isn't there an easier way?
Thanks for the help
DarrellYou cannot change file or filegroup layout using backup/restore (which basic
ally gives you an image
of your database). Either use EM to move or do it from QA using more or less
clever/complicated TSQL
scripts. You change location of a table by (re)creating a clustered index. F
or an index, you rebuild
the index.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB. Placi
ng the files and data in
> the correct locations. I want the data in a filegroup called DatabaseName_
Data. Problem is when I
> restore to ServerB, the data always goes to PRIMARY filegroup during resto
re. The only thing I can
> find is to manually change the location of the data for each table in Ente
rprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell|||Refer to "How to restore files to a new location (Transact-SQL)" in BOL...
"Darrell" <Darrell.Wright.nospam@.okc.gov> wrote in message
news:ehhWgpTvFHA.3740@.TK2MSFTNGP14.phx.gbl...
> OK. I have a good one. I have the following setup:
> ServerA
> Data on: C:\Data
> Log on: C:\Logs
> ServerB
> System Databases on: D:\SysData
> Data on: E:\Data
> Indexes on : D:\Indexes
> Log on: F:\Logs
> So, what I want to do is restore a database from ServerA to ServerB.
> Placing the files and data in the correct locations. I want the data in a
> filegroup called DatabaseName_Data. Problem is when I restore to ServerB,
> the data always goes to PRIMARY filegroup during restore. The only thing I
> can find is to manually change the location of the data for each table in
> Enterprise Manager (YUCK!)
> Isn't there an easier way?
> Thanks for the help
> Darrell

Restore database stuck on (Loading)

I recently installed MSDE on a laptop. I then restored several databases from backup files without problem. However, one database is constantly stuck at the grey colour displaying the (Loading) message.

Why won't this database restore ? It is of course the most important database and I only have the one backup file.

This only appears to fail on his server. Do I need to uninstall MSDE and start over ?It's probably the machine's "critical need detector", it might be a wee bit too sensitive or mis-adjusted.

On a slightly more serious note, what tools are you using to do the restore? The only tool that could do a restore that ships with MSDE is OSQL.EXE, and I'm pretty comfortable that you aren't using that! Enterprise Mangler would be my first guess, but that is still only a guess.

Could the dump (backup) have been made with a later service pack than you are using to restore it? That can make things get really crabby sometimes.

Is the database file one that this instance of MSDE can handle? Depending on the MSDE version, you can handle up to 2 Gb of data (although I've seen data and log dumps over 10 Gb).

If none of those ideas pan out, then my first suggestion would be to:

1 Disable everything you can that uses MSDE (keep notes!)
2 Stop the MSDE service
3 Restart the MSDE service
4 Try to load the offending database
5 If you can't load the database, stop the MSDE service
6 Examine the Errorlog file carefully
7 If you can't find anything in the Errorlog, consider posting it here.
8 if the database can be posted, consider posting the backup too for us to play with!

-PatP|||If it's in Loading state, could it be that you are restoring from a backup device that contained incomplete set of full db backup and some trx logs?|||But it restores without fail on any number of other servers. So I think the backup file is ok.

I have gone through the steps above and can't see anything wrong. I have pasted the errorlog below for perusal.

Yes I am using Enterprise manager. I also tried the restore database command from isqlw utility and that also produced the error :

Processed 8432 pages for database 'IPSHOW_NEW', file 'IPSHOW_Data' on file 1.
[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead (WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
Processed 1 pages for database 'IPSHOW_NEW', file 'IPSHOW_Log' on file 1.

Connection Broken

Errorlog
2004-12-23 15:56:36.01 server Microsoft SQL Server 2000 - 8.00.760 (Intel X86)
Dec 17 2002 14:22:05
Copyright (c) 1988-2003 Microsoft Corporation
Personal Edition on Windows NT 5.1 (Build 2600: Service Pack 2)

2004-12-23 15:56:36.01 server Copyright (C) 1988-2002 Microsoft Corporation.
2004-12-23 15:56:36.01 server All rights reserved.
2004-12-23 15:56:36.01 server Server Process ID is 1392.
2004-12-23 15:56:36.01 server Logging SQL Server messages in file 'C:\Program Files\Microsoft SQL Server\MSSQL\log\ERRORLOG'.
2004-12-23 15:56:36.09 server SQL Server is starting at priority class 'normal'(1 CPU detected).
2004-12-23 15:56:36.13 server SQL Server configured for thread mode processing.
2004-12-23 15:56:36.13 server Using dynamic lock allocation. [500] Lock Blocks, [1000] Lock Owner Blocks.
2004-12-23 15:56:36.15 spid3 Starting up database 'master'.
2004-12-23 15:56:36.53 server Using 'SSNETLIB.DLL' version '8.0.766'.
2004-12-23 15:56:36.53 spid5 Starting up database 'model'.
2004-12-23 15:56:36.68 spid3 Server name is 'SBUTLER'.
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 4
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 5
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 6
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 9
2004-12-23 15:56:36.68 spid3 Skipping startup of clean database id 10
2004-12-23 15:56:36.68 spid3 Starting up database 'IPSHOW_NEW'.
2004-12-23 15:56:36.68 server SQL server listening on 192.168.1.110: 1433.
2004-12-23 15:56:36.69 server SQL server listening on 127.0.0.1: 1433.
2004-12-23 15:56:36.98 spid3 Bypassing recovery for database 'IPSHOW_NEW' because it is marked IN LOAD.
2004-12-23 15:56:37.09 server SQL server listening on TCP, Shared Memory, Named Pipes.
2004-12-23 15:56:37.09 server SQL Server is ready for client connections
2004-12-23 15:56:37.22 spid5 Clearing tempdb database.
2004-12-23 15:56:38.58 spid5 Starting up database 'tempdb'.
2004-12-23 15:56:38.78 spid3 Recovery complete.
2004-12-23 15:56:38.78 spid3 SQL global counter collection task is created.
2004-12-23 15:57:35.63 spid51 Using 'xpstar.dll' version '2000.80.760' to execute extended stored procedure 'sp_MSgetversion'.
2004-12-23 15:57:35.78 spid51 Starting up database 'msdb'.|||Post the statement used in ISQL to restore the database.
And ensure the backup file is stored on the MSDE server and if you are trying to restore from a network location due to th network issues it may be failing with the above error.

Refer to this KBA http://support.microsoft.com/default.aspx?scid=kb;en-us;827452 for relevancy.|||I gave up and uninstalled MSDE. As it turns out he required Reporting Services and a Developer Edition of SQL Server so all for the best in the end. Thanks for the assistance.

Restore database overwriting databases without the Replace option (SQL Server 2000)

Hi everybody,
I'm trying to figure out why my installation of SQL Server 2000 is allowing
databases to be restored that overwrite an exisitng databases, when I do
*not* specify the replace option.
Is the server currupted? Did a setting get set somewhere that allows this?
What should I look for,
Thanks, JRAccording to the SQL Server 2000 Books Online
<tsqlref.chm::/ts_ra-rz_25rm.htm>, REPLACE is required only if both of
the following are true:
a. The database named in the RESTORE statement already exists on the
current server, and
b. The database name is different from the database name recorded in the
backup set.
Consequently, if the database name you backed up from is the same as the
target, the RESTORE will be allowed even without the REPLACE option.
Your server is not corrupted if this is the case in your situation.
--
Hope this helps.
Dan Guzman
SQL Server MVP
--
SQL FAQ links (courtesy Neil Pike):
http://www.ntfaq.com/Articles/Index.cfm?DepartmentID=800
http://www.sqlserverfaq.com
http://www.mssqlserver.com/faq
--
"JR" <newsreader@.jmrcs.com> wrote in message
news:%23jjMpQ5TDHA.3188@.tk2msftngp13.phx.gbl...
> Hi everybody,
> I'm trying to figure out why my installation of SQL Server 2000 is
allowing
> databases to be restored that overwrite an exisitng databases, when I
do
> *not* specify the replace option.
> Is the server currupted? Did a setting get set somewhere that allows
this?
> What should I look for,
> Thanks, JR
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACE
You are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
--
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

RESTORE DATABASE Error!? (SQL2kSP3)

Hi,
while testing my backup job I've come to a severe error: If certain
conditions are met, RESTORE DATABASE restores databases wrong!
This is what I did:
* Made Full Backup of five databases using SQL Agent job.
* Made Differential Backup of three of those five databases using SQL Agent
job.
* In SQL Server Enterprise Manager I've deleted two of those five databases
(dba and dbc). (This deletes corresponding entries in msdb..backupfile etc.)
* Tried to recover one of those two databases (dbc). ERROR!
The error is that dba(!) is getting restored under the name of dbc!
Did I make a severe error here or did I find one? I'm using SQL Server 2000,
SP3.
TIA,
Axel Dahmen
These are my T-SQL statements:
Complete backup:
BACKUP DATABASE dba
TO DISK = @.buPath
WITH
DESCRIPTION = 'dba complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
INIT, -- Kill file and create
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dba'
BACKUP DATABASE dbb
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbb complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbb'
BACKUP DATABASE dbc
TO DISK = @.buPath
WITH
DESCRIPTION = 'dbc complete',
RETAINDAYS = 14,
PASSWORD = 'mann',
NOINIT,
MEDIADESCRIPTION = 'Full Backup',
MEDIANAME = 'SBC',
MEDIAPASSWORD = 'lander',
NAME = 'dbc'
...
Restore command:
RESTORE DATABASE dbc
FROM DISK = @.buPath
WITH
PASSWORD = 'mann'
,MEDIAPASSWORD = 'lander'
,REPLACEYou are putting multiple logical backups into the same physical media...
When you use that media to restore, you should include the file = part of
the command to specify which logical backup should be used for the
restore... If you do not specify WHICH backup the first item in the file is
chosen... The backup of DBA is the first thing in the file, so when you try
to restore DBC from the file ( and do not specify FILE =3) it uses the first
item which is the backup of DBA.. The security checks then fail... Change
your restore command to :
RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE, FILE = 3
>
Wayne Snyder, MCDBA, SQL Server MVP
Computer Education Services Corporation (CESC), Charlotte, NC
www.computeredservices.com
(Please respond only to the newsgroups.)
I support the Professional Association of SQL Server (PASS) and it's
community of SQL Server professionals.
www.sqlpass.org
"Axel Dahmen" <NO_SPAM@.NoOneKnows.de> wrote in message
news:uP2$w3iHEHA.716@.TK2MSFTNGP12.phx.gbl...
> Hi,
> while testing my backup job I've come to a severe error: If certain
> conditions are met, RESTORE DATABASE restores databases wrong!
> This is what I did:
> * Made Full Backup of five databases using SQL Agent job.
> * Made Differential Backup of three of those five databases using SQL
Agent
> job.
> * In SQL Server Enterprise Manager I've deleted two of those five
databases
> (dba and dbc). (This deletes corresponding entries in msdb..backupfile
etc.)
> * Tried to recover one of those two databases (dbc). ERROR!
> The error is that dba(!) is getting restored under the name of dbc!
> Did I make a severe error here or did I find one? I'm using SQL Server
2000,
> SP3.
> TIA,
> Axel Dahmen
> --
> These are my T-SQL statements:
> Complete backup:
> BACKUP DATABASE dba
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dba complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> INIT, -- Kill file and create
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dba'
> BACKUP DATABASE dbb
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbb complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbb'
> BACKUP DATABASE dbc
> TO DISK = @.buPath
> WITH
> DESCRIPTION = 'dbc complete',
> RETAINDAYS = 14,
> PASSWORD = 'mann',
> NOINIT,
> MEDIADESCRIPTION = 'Full Backup',
> MEDIANAME = 'SBC',
> MEDIAPASSWORD = 'lander',
> NAME = 'dbc'
> ...
>
> Restore command:
> RESTORE DATABASE dbc
> FROM DISK = @.buPath
> WITH
> PASSWORD = 'mann'
> ,MEDIAPASSWORD = 'lander'
> ,REPLACE
>

restore database back to sql 7.0?

Hi,
I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
(win2k) is installed. I was transferring one of the database (db1) from 7.0
to 2000 by using copy database wizard. But I found that instead of copying,
it actually moved the db1 to the new server. Since the new server is not in
production stage, I want to restore the db1 back to 7.0. I can see the data
and log files are still there. How can I have it operational again?
Please help me with this, I would greatly appreciate it.
Thanks a lot,
SarahAre the original data and log files still on the 7.0 server? If so, you can
reattach the original files to 7.0 using sp_attach_db. Otherwise, you'll
need to restore the 7.0 database from a 7.0 backup.
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||SG,
As I understand it a restore from version 2000 to 7 is not possible and
would require the database/objects/data to be recreated via scripts, DTS
and/or BCP.
Was there a script location that contains the schema and data files for the
upgrade? Might be able to leverage from that.
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||SG,
Optionally if you have a backup of the v7 database you could just restore
it - would be alot easier if the backup exists!
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||Thanks Lori and Jerry for your quick response. Both of you have a same
solution which is to use backup. We are using veritas backup exec to backup
SQL7.0. I've been trying to restore the database to the server. For some
reason, all failed.
Yes, all of the data and log files are still on SQL 7.0 under
\mssql\data\db1_data.mdf and db1_log.ldf.
I will try Lori, reattach the db1 to see first, if it is not working, I will
come back to you.
Thanks a lot,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||Hi Lori,
I tried, query:
sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
'd:\mssql7\data\db1_log.ldf'
But I got the error message saying, "I/O error (bad page id)......
connection broken.
What does this mean? Is the file corrupt?
Thanks,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
> Thanks Lori and Jerry for your quick response. Both of you have a same
> solution which is to use backup. We are using veritas backup exec to
> backup SQL7.0. I've been trying to restore the database to the server. For
> some reason, all failed.
> Yes, all of the data and log files are still on SQL 7.0 under
> \mssql\data\db1_data.mdf and db1_log.ldf.
> I will try Lori, reattach the db1 to see first, if it is not working, I
> will come back to you.
> Thanks a lot,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
>> (win2k) is installed. I was transferring one of the database (db1) from
>> 7.0 to 2000 by using copy database wizard. But I found that instead of
>> copying, it actually moved the db1 to the new server. Since the new
>> server is not in production stage, I want to restore the db1 back to 7.0.
>> I can see the data and log files are still there. How can I have it
>> operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>|||Not sure, but usually I/O errors indicate database corruption or hardware
issues. Did you do a dbcc checkdb before you did the copy/move? Are you
sure that the files you're trying to attach are SQL 7.0 files?
"SG" <sguo@.coopervision.ca> wrote in message
news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Hi Lori,
> I tried, query:
> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
> 'd:\mssql7\data\db1_log.ldf'
> But I got the error message saying, "I/O error (bad page id)......
> connection broken.
> What does this mean? Is the file corrupt?
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>> Thanks Lori and Jerry for your quick response. Both of you have a same
>> solution which is to use backup. We are using veritas backup exec to
>> backup SQL7.0. I've been trying to restore the database to the server.
>> For some reason, all failed.
>> Yes, all of the data and log files are still on SQL 7.0 under
>> \mssql\data\db1_data.mdf and db1_log.ldf.
>> I will try Lori, reattach the db1 to see first, if it is not working, I
>> will come back to you.
>> Thanks a lot,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
>> (win2k) is installed. I was transferring one of the database (db1) from
>> 7.0 to 2000 by using copy database wizard. But I found that instead of
>> copying, it actually moved the db1 to the new server. Since the new
>> server is not in production stage, I want to restore the db1 back to
>> 7.0. I can see the data and log files are still there. How can I have it
>> operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>>
>|||attach is only guaranteed if you actually detached the database first. Are you saying that you don't
have a database backup of the 7.0 database? That you only "ripped" the database files using Veritas?
You could try creating a database on the 7.0 server with same db name and same file names, stop the
SQL Server, delete the new files and copy your old files in place of the new ones.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SG" <sguo@.coopervision.ca> wrote in message news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Hi Lori,
> I tried, query:
> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf', 'd:\mssql7\data\db1_log.ldf'
> But I got the error message saying, "I/O error (bad page id)......
> connection broken.
> What does this mean? Is the file corrupt?
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>> Thanks Lori and Jerry for your quick response. Both of you have a same solution which is to use
>> backup. We are using veritas backup exec to backup SQL7.0. I've been trying to restore the
>> database to the server. For some reason, all failed.
>> Yes, all of the data and log files are still on SQL 7.0 under \mssql\data\db1_data.mdf and
>> db1_log.ldf.
>> I will try Lori, reattach the db1 to see first, if it is not working, I will come back to you.
>> Thanks a lot,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000 (win2k) is installed. I
>> was transferring one of the database (db1) from 7.0 to 2000 by using copy database wizard. But I
>> found that instead of copying, it actually moved the db1 to the new server. Since the new server
>> is not in production stage, I want to restore the db1 back to 7.0. I can see the data and log
>> files are still there. How can I have it operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>>
>|||Hi Lori,
Yes, I'm sure that file is SQL7.0. But I forgot one thing, while I was doing
"Copy database" from 7.0 to 2k, there was an error "detach failed". I guess
that is where the problem was. Instead of copying, it did move. Before done
this database, I did a small database test, it was fine. Don't know what
happened.
Any idea?
Thanks,
Sarah
"Lori Clark" <lclark@.dbadvisor.com> wrote in message
news:uSfds42zFHA.904@.tk2msftngp13.phx.gbl...
> Not sure, but usually I/O errors indicate database corruption or hardware
> issues. Did you do a dbcc checkdb before you did the copy/move? Are you
> sure that the files you're trying to attach are SQL 7.0 files?
> "SG" <sguo@.coopervision.ca> wrote in message
> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>> Hi Lori,
>> I tried, query:
>> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
>> 'd:\mssql7\data\db1_log.ldf'
>> But I got the error message saying, "I/O error (bad page id)......
>> connection broken.
>> What does this mean? Is the file corrupt?
>> Thanks,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>> Thanks Lori and Jerry for your quick response. Both of you have a same
>> solution which is to use backup. We are using veritas backup exec to
>> backup SQL7.0. I've been trying to restore the database to the server.
>> For some reason, all failed.
>> Yes, all of the data and log files are still on SQL 7.0 under
>> \mssql\data\db1_data.mdf and db1_log.ldf.
>> I will try Lori, reattach the db1 to see first, if it is not working, I
>> will come back to you.
>> Thanks a lot,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
>> (win2k) is installed. I was transferring one of the database (db1) from
>> 7.0 to 2000 by using copy database wizard. But I found that instead of
>> copying, it actually moved the db1 to the new server. Since the new
>> server is not in production stage, I want to restore the db1 back to
>> 7.0. I can see the data and log files are still there. How can I have
>> it operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>>
>>
>|||Hi Tibor,
I did get an error while I was copy the database about "Detach database
failed".
We have Veritas backup agent for SQL on SQL 7.0 server, it should actually
backup the database not only "ripped" the files. I might need to try your
suggestion in this case. If I create the new database, I like to use the
data file, but not the log file. Can I use new log file with the old data
file?
It turned really mess now. Does anybody have any note before I do the "copy
database wizard"? I do have another very important database needs to be
transferred from 7.0 to 2k. I cannot afford to have the same mistake.
Appreciate your idea,
Sarah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oa3uJ67zFHA.464@.TK2MSFTNGP15.phx.gbl...
> attach is only guaranteed if you actually detached the database first. Are
> you saying that you don't have a database backup of the 7.0 database? That
> you only "ripped" the database files using Veritas? You could try creating
> a database on the 7.0 server with same db name and same file names, stop
> the SQL Server, delete the new files and copy your old files in place of
> the new ones.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>> Hi Lori,
>> I tried, query:
>> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
>> 'd:\mssql7\data\db1_log.ldf'
>> But I got the error message saying, "I/O error (bad page id)......
>> connection broken.
>> What does this mean? Is the file corrupt?
>> Thanks,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>> Thanks Lori and Jerry for your quick response. Both of you have a same
>> solution which is to use backup. We are using veritas backup exec to
>> backup SQL7.0. I've been trying to restore the database to the server.
>> For some reason, all failed.
>> Yes, all of the data and log files are still on SQL 7.0 under
>> \mssql\data\db1_data.mdf and db1_log.ldf.
>> I will try Lori, reattach the db1 to see first, if it is not working, I
>> will come back to you.
>> Thanks a lot,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
>> (win2k) is installed. I was transferring one of the database (db1) from
>> 7.0 to 2000 by using copy database wizard. But I found that instead of
>> copying, it actually moved the db1 to the new server. Since the new
>> server is not in production stage, I want to restore the db1 back to
>> 7.0. I can see the data and log files are still there. How can I have
>> it operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>>
>>
>|||> We have Veritas backup agent for SQL on SQL 7.0 server, it should actually backup the database
If so, you are most probably fine. You would have a SQL Server backup of the database, and you can
use the TSQL RESTORE command to restore it.
> If I create the new database, I like to use the data file, but not the log file. Can I use new log
> file with the old data file?
Most probably not. Read in Books Online about what the recovery process does and you will
understand. You could try without the log file and see if SQL Server can create a log file for you.
But if the attach failed, probably this will as well.
But first you need to determine whether you have a copy of the database file or a backup of the
database...
> Does anybody have any note before I do the "copy database wizard"?
Don't. Use sp_detach_db and sp_attach_db instead. Then you can use DTS for the other tasks that the
CDW has. This gives you control.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SG" <sguo@.coopervision.ca> wrote in message news:O84LYx$zFHA.3856@.tk2msftngp13.phx.gbl...
> Hi Tibor,
> I did get an error while I was copy the database about "Detach database failed".
> We have Veritas backup agent for SQL on SQL 7.0 server, it should actually backup the database not
> only "ripped" the files. I might need to try your suggestion in this case. If I create the new
> database, I like to use the data file, but not the log file. Can I use new log file with the old
> data file?
> It turned really mess now. Does anybody have any note before I do the "copy database wizard"? I do
> have another very important database needs to be transferred from 7.0 to 2k. I cannot afford to
> have the same mistake.
> Appreciate your idea,
> Sarah
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:Oa3uJ67zFHA.464@.TK2MSFTNGP15.phx.gbl...
>> attach is only guaranteed if you actually detached the database first. Are you saying that you
>> don't have a database backup of the 7.0 database? That you only "ripped" the database files using
>> Veritas? You could try creating a database on the 7.0 server with same db name and same file
>> names, stop the SQL Server, delete the new files and copy your old files in place of the new
>> ones.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SG" <sguo@.coopervision.ca> wrote in message news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>> Hi Lori,
>> I tried, query:
>> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf', 'd:\mssql7\data\db1_log.ldf'
>> But I got the error message saying, "I/O error (bad page id)......
>> connection broken.
>> What does this mean? Is the file corrupt?
>> Thanks,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>> Thanks Lori and Jerry for your quick response. Both of you have a same solution which is to use
>> backup. We are using veritas backup exec to backup SQL7.0. I've been trying to restore the
>> database to the server. For some reason, all failed.
>> Yes, all of the data and log files are still on SQL 7.0 under \mssql\data\db1_data.mdf and
>> db1_log.ldf.
>> I will try Lori, reattach the db1 to see first, if it is not working, I will come back to you.
>> Thanks a lot,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000 (win2k) is installed. I
>> was transferring one of the database (db1) from 7.0 to 2000 by using copy database wizard. But
>> I found that instead of copying, it actually moved the db1 to the new server. Since the new
>> server is not in production stage, I want to restore the db1 back to 7.0. I can see the data
>> and log files are still there. How can I have it operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>>
>>
>>
>|||Hi Tibor,
Thanks so much for your comments. I will check the backup again. That's the
best way.
Thanks,
sarah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:OH39WPA0FHA.720@.TK2MSFTNGP15.phx.gbl...
>> We have Veritas backup agent for SQL on SQL 7.0 server, it should
>> actually backup the database
> If so, you are most probably fine. You would have a SQL Server backup of
> the database, and you can use the TSQL RESTORE command to restore it.
>
>> If I create the new database, I like to use the data file, but not the
>> log file. Can I use new log file with the old data file?
> Most probably not. Read in Books Online about what the recovery process
> does and you will understand. You could try without the log file and see
> if SQL Server can create a log file for you. But if the attach failed,
> probably this will as well.
> But first you need to determine whether you have a copy of the database
> file or a backup of the database...
>
>> Does anybody have any note before I do the "copy database wizard"?
> Don't. Use sp_detach_db and sp_attach_db instead. Then you can use DTS for
> the other tasks that the CDW has. This gives you control.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:O84LYx$zFHA.3856@.tk2msftngp13.phx.gbl...
>> Hi Tibor,
>> I did get an error while I was copy the database about "Detach database
>> failed".
>> We have Veritas backup agent for SQL on SQL 7.0 server, it should
>> actually backup the database not only "ripped" the files. I might need to
>> try your suggestion in this case. If I create the new database, I like to
>> use the data file, but not the log file. Can I use new log file with the
>> old data file?
>> It turned really mess now. Does anybody have any note before I do the
>> "copy database wizard"? I do have another very important database needs
>> to be transferred from 7.0 to 2k. I cannot afford to have the same
>> mistake.
>> Appreciate your idea,
>> Sarah
>> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote
>> in message news:Oa3uJ67zFHA.464@.TK2MSFTNGP15.phx.gbl...
>> attach is only guaranteed if you actually detached the database first.
>> Are you saying that you don't have a database backup of the 7.0
>> database? That you only "ripped" the database files using Veritas? You
>> could try creating a database on the 7.0 server with same db name and
>> same file names, stop the SQL Server, delete the new files and copy your
>> old files in place of the new ones.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>> Hi Lori,
>> I tried, query:
>> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
>> 'd:\mssql7\data\db1_log.ldf'
>> But I got the error message saying, "I/O error (bad page id)......
>> connection broken.
>> What does this mean? Is the file corrupt?
>> Thanks,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>> Thanks Lori and Jerry for your quick response. Both of you have a same
>> solution which is to use backup. We are using veritas backup exec to
>> backup SQL7.0. I've been trying to restore the database to the server.
>> For some reason, all failed.
>> Yes, all of the data and log files are still on SQL 7.0 under
>> \mssql\data\db1_data.mdf and db1_log.ldf.
>> I will try Lori, reattach the db1 to see first, if it is not working,
>> I will come back to you.
>> Thanks a lot,
>> Sarah
>> "SG" <sguo@.coopervision.ca> wrote in message
>> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>> Hi,
>> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
>> (win2k) is installed. I was transferring one of the database (db1)
>> from 7.0 to 2000 by using copy database wizard. But I found that
>> instead of copying, it actually moved the db1 to the new server.
>> Since the new server is not in production stage, I want to restore
>> the db1 back to 7.0. I can see the data and log files are still
>> there. How can I have it operational again?
>> Please help me with this, I would greatly appreciate it.
>> Thanks a lot,
>> Sarah
>>
>>
>>
>>
>

restore database back to sql 7.0?

Hi,
I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
(win2k) is installed. I was transferring one of the database (db1) from 7.0
to 2000 by using copy database wizard. But I found that instead of copying,
it actually moved the db1 to the new server. Since the new server is not in
production stage, I want to restore the db1 back to 7.0. I can see the data
and log files are still there. How can I have it operational again?
Please help me with this, I would greatly appreciate it.
Thanks a lot,
SarahAre the original data and log files still on the 7.0 server? If so, you can
reattach the original files to 7.0 using sp_attach_db. Otherwise, you'll
need to restore the 7.0 database from a 7.0 backup.
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||SG,
As I understand it a restore from version 2000 to 7 is not possible and
would require the database/objects/data to be recreated via scripts, DTS
and/or BCP.
Was there a script location that contains the schema and data files for the
upgrade? Might be able to leverage from that.
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||SG,
Optionally if you have a backup of the v7 database you could just restore
it - would be alot easier if the backup exists!
HTH
Jerry
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||Thanks Lori and Jerry for your quick response. Both of you have a same
solution which is to use backup. We are using veritas backup exec to backup
SQL7.0. I've been trying to restore the database to the server. For some
reason, all failed.
Yes, all of the data and log files are still on SQL 7.0 under
\mssql\data\db1_data.mdf and db1_log.ldf.
I will try Lori, reattach the db1 to see first, if it is not working, I will
come back to you.
Thanks a lot,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
> Hi,
> I have a SQL7.0 (NT4) with all the databases. Recently a new SQL 2000
> (win2k) is installed. I was transferring one of the database (db1) from
> 7.0 to 2000 by using copy database wizard. But I found that instead of
> copying, it actually moved the db1 to the new server. Since the new server
> is not in production stage, I want to restore the db1 back to 7.0. I can
> see the data and log files are still there. How can I have it operational
> again?
> Please help me with this, I would greatly appreciate it.
> Thanks a lot,
> Sarah
>|||Hi Lori,
I tried, query:
sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
'd:\mssql7\data\db1_log.ldf'
But I got the error message saying, "I/O error (bad page id)......
connection broken.
What does this mean? Is the file corrupt?
Thanks,
Sarah
"SG" <sguo@.coopervision.ca> wrote in message
news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
> Thanks Lori and Jerry for your quick response. Both of you have a same
> solution which is to use backup. We are using veritas backup exec to
> backup SQL7.0. I've been trying to restore the database to the server. For
> some reason, all failed.
> Yes, all of the data and log files are still on SQL 7.0 under
> \mssql\data\db1_data.mdf and db1_log.ldf.
> I will try Lori, reattach the db1 to see first, if it is not working, I
> will come back to you.
> Thanks a lot,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:Oc5Dmw0zFHA.1252@.TK2MSFTNGP09.phx.gbl...
>|||Not sure, but usually I/O errors indicate database corruption or hardware
issues. Did you do a dbcc checkdb before you did the copy/move? Are you
sure that the files you're trying to attach are SQL 7.0 files?
"SG" <sguo@.coopervision.ca> wrote in message
news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
> Hi Lori,
> I tried, query:
> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf',
> 'd:\mssql7\data\db1_log.ldf'
> But I got the error message saying, "I/O error (bad page id)......
> connection broken.
> What does this mean? Is the file corrupt?
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message
> news:uZMVgA1zFHA.908@.tk2msftngp13.phx.gbl...
>|||attach is only guaranteed if you actually detached the database first. Are y
ou saying that you don't
have a database backup of the 7.0 database? That you only "ripped" the datab
ase files using Veritas?
You could try creating a database on the 7.0 server with same db name and sa
me file names, stop the
SQL Server, delete the new files and copy your old files in place of the new
ones.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"SG" <sguo@.coopervision.ca> wrote in message news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...[v
bcol=seagreen]
> Hi Lori,
> I tried, query:
> sp_attach_db 'db1', 'd:\mssql7\data\db1_data.mdf', 'd:\mssql7\data\db1_log
.ldf'
> But I got the error message saying, "I/O error (bad page id)......
> connection broken.
> What does this mean? Is the file corrupt?
> Thanks,
> Sarah
> "SG" <sguo@.coopervision.ca> wrote in message news:uZMVgA1zFHA.908@.tk2msftn
gp13.phx.gbl...
>[/vbcol]|||Hi Lori,
Yes, I'm sure that file is SQL7.0. But I forgot one thing, while I was doing
"Copy database" from 7.0 to 2k, there was an error "detach failed". I guess
that is where the problem was. Instead of copying, it did move. Before done
this database, I did a small database test, it was fine. Don't know what
happened.
Any idea?
Thanks,
Sarah
"Lori Clark" <lclark@.dbadvisor.com> wrote in message
news:uSfds42zFHA.904@.tk2msftngp13.phx.gbl...
> Not sure, but usually I/O errors indicate database corruption or hardware
> issues. Did you do a dbcc checkdb before you did the copy/move? Are you
> sure that the files you're trying to attach are SQL 7.0 files?
> "SG" <sguo@.coopervision.ca> wrote in message
> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>|||Hi Tibor,
I did get an error while I was copy the database about "Detach database
failed".
We have Veritas backup agent for SQL on SQL 7.0 server, it should actually
backup the database not only "ripped" the files. I might need to try your
suggestion in this case. If I create the new database, I like to use the
data file, but not the log file. Can I use new log file with the old data
file?
It turned really mess now. Does anybody have any note before I do the "copy
database wizard"? I do have another very important database needs to be
transferred from 7.0 to 2k. I cannot afford to have the same mistake.
Appreciate your idea,
Sarah
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oa3uJ67zFHA.464@.TK2MSFTNGP15.phx.gbl...
> attach is only guaranteed if you actually detached the database first. Are
> you saying that you don't have a database backup of the 7.0 database? That
> you only "ripped" the database files using Veritas? You could try creating
> a database on the 7.0 server with same db name and same file names, stop
> the SQL Server, delete the new files and copy your old files in place of
> the new ones.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "SG" <sguo@.coopervision.ca> wrote in message
> news:e4bFrp2zFHA.3180@.TK2MSFTNGP14.phx.gbl...
>