Restore DB**

Hi
Thanks for your reply,
now I tried to restore master DB to create related
logins, but following error appeared:
--
restore database must be used in single user mode
when tring to restore the master database .
restore database is terminated abnormally.
--
what's wrong?
any help would be greatly appreciated.
On Thu, 8 Jan 2004 07:36:12 -0500, Ray Higdon <sqlhigdon@.nospam.yahoo.com>
wrote:

quote:

---

> This link should help you
> http://support.microsoft.com/defaul...kb;en-us;246133
> HTH
>

---

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/Thanks for your reply
I used "sp_change_users_login" and I've got
successfull result ,but there's a question
with me,
(note : my DB =dbtest, Login=L1 , User=U1)
now when I select login properties of my L1 which
added manually I see there's user U1 related to it,
nut when I refer to database dbtest and I expand the
user section of it in enterprise manager I just
see the U1 in "name" colunm and there's nothing in
"loginname" colunm,and permit in "database access" colunm.
why?
but when I connect as L1 and try to select or create or ...
table,there's no problem.
thanks,
On Thu, 8 Jan 2004 17:59:16 +0530, Hari <hari_prasad_k@.hotmail.com> wrote:

quote:

---

> Hi,
> After restoration you can use the system stored procedure to fix the
> login
> issue,
> sp_change_users_login
> Please refere BOL for more infomation. The only issue is you may need to
> create the logins manually based on the first server. After that you can
> run
> the above procedure with required parameters.
> Thanks
> Hari
> MCDBA
>
>
> "RM" <m_r1824@.yahoo.co.uk> wrote in message
> news:opr1gd7geghqligo@.msnews.microsoft.com...
>

---

Using M2, Opera's revolutionary e-mail client: http://www.opera.com/m2/sql

Restore DB to sql 2k

After restoring a db from SQL 7.0 running on a Win98
system (English version) to a SQL 2000 on an XP system
(Spanish version) I get the following message on a stored
procedure:
Server: Msg 446, Level 16, State 9, Procedure
sp_RptPartida, Line 33
Cannot resolve collation conflict for equal to operation.
I did not get such message in the old system. Is there a
way to avoid it. I need to be able to maintain the db on
my client (they have sql 7.0) and I wish to do the
development on sql 2000.There is a conflict in collations between your Charcter table attributes
that you are comparing
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_7ory.asp
http://tinyurl.com/3dr2j
--
Allan Mitchell (Microsoft SQL Server MVP)
MCSE,MCDBA
www.SQLDTS.com
I support PASS - the definitive, global community
for SQL Server professionals - http://www.sqlpass.org
"Mauricio" <macs01@.telesal.net> wrote in message
news:036f01c3cb24$72727230$a001280a@.phx.gbl...
> After restoring a db from SQL 7.0 running on a Win98
> system (English version) to a SQL 2000 on an XP system
> (Spanish version) I get the following message on a stored
> procedure:
> Server: Msg 446, Level 16, State 9, Procedure
> sp_RptPartida, Line 33
> Cannot resolve collation conflict for equal to operation.
> I did not get such message in the old system. Is there a
> way to avoid it. I need to be able to maintain the db on
> my client (they have sql 7.0) and I wish to do the
> development on sql 2000.

restore database without some tables records

Hi There,
I'm using following script to restore
Restore Database adminsys_ex_avc from disk =
'\\ppml31\DBBkups\fromPPML0112\adminsys_
ex_avc.bak' with replace
is it possible can i ignore the some table on restore, for e.g audit_log
table and i don't want to use any logs
Thanks
GaneshActually yes
drop database test
go
create database test
GO
alter database test set recovery full
go
create table test..test(id int identity)
create table test..test1(id int identity)
insert test..test default values
insert test..test1 default values
backup database test to disk = 'c:\db.bak' WITH INIT
insert test..test default values
insert test..test1 default values
backup log test to disk = 'c:\log.bak'WITH INIT
insert test..test default values
insert test..test1 default values
backup log test to disk = 'c:\log.bak' WITH NOINIT
insert test..test default values
drop table test..test1
backup log test to disk = 'c:\log.bak' WITH NOINIT
GO
RESTORE DATABASE test FROM disk = 'c:\db.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 1, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 2, norecovery
RESTORE LOG test FROM disk = 'c:\log.bak' WITH FILE = 3, recovery
GO
select * from test..test
select * from test..test1
--Server: Msg 208, Level 16, State 1, Line 1
--Invalid object name 'test..test1'.
"Ganesh" <gsganesh@.yahoo.com> wrote in message
news:6D4A5AFB-50AD-4E93-889D-2169F92BBB82@.microsoft.com...
> Hi There,
> I'm using following script to restore
> Restore Database adminsys_ex_avc from disk =
> '\\ppml31\DBBkups\fromPPML0112\adminsys_
ex_avc.bak' with replace
> is it possible can i ignore the some table on restore, for e.g audit_log
> table and i don't want to use any logs
> --
> Thanks
> Ganesh|||No,
You can not avoid selected tables.
You can do one thing. Move all unrequired tables to one filegroup which
should not be primary.
Then use partial restore and do not restore selected filegroup.
Look at BOL Partial Database Restore Operations.
Best way is just restore database and delete unwanted tables.
Regards
Amish Shah.

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 Timeout in SQL 2000 with large backup files

Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:\MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LDF\MyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat'

using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

Have you tried changing the timeout setting of the ADO.NET Command?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

As Wesley indicated you should change the query timeout.

SQL 2000 does not have fast file initialization so it can take awhile to create and initialize the files.

|||

Yes I forgot to mention. The object which executes query is SMO.Database and it runs

Smo.Database.ExecuteNonQuery(sSQL)

So I set the timeout on the Smo.Server.Connection = 0.

I've also tried to set it to Smo.Server.Connection=3600 but the timeout occure in about 12 min so

sorry It did not help...

|||

For those of you who might be looking for answer to the similar problem - I found the solution. SMO has StatementTimeout property. This is different from the one I was trying to change - which was a Connection timeout.

That is the one which needed to be increased.

I do appreciate the responses to my post though. It helped. Thanks

sql

RESTORE DATABASE Timeout in SQL 2000 with large backup files

Hello,

I am attempting to restore the database from within VB.NET application I am making the following 3 calls:

RESTORE FileListOnly FROM DISK = 'C:\MyDatabase.dat'

USE Master RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat' WITH NORECOVERY, MOVE 'MyDatabase' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\MyDatabase.mdf', MOVE 'MyDatabase_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL\Data\LDF\MyDatabase.ldf', REPLACE

RESTORE DATABASE MyDatabase FROM DISK = 'C:\MyDatabase.dat'

using SMO. This logic works fine with small *.dat files, however when using *.dat file of about 4Gb I get an error on the 3d restore database call:

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

ExecuteNonQuery failed for Database 'master'.

An exception occurred while executing a Transact-SQL statement or batch.

Timeout expired. The timeout period elapsed prior to completion of the operation or the server is not responding.

Operator aborted backup or restore. See the error messages returned to the console for more details.

The same program/logic also works fine when I use MS SQL 2005 and it runs fine from MS SQL 2005 Query Analyzer for both 2005 and 2000 databases. There seem to be only problem with MS SQL 2000 from within VB.NET. Anybody has any idea? I'd appreciate any response. Thanks

Eugene

Have you tried changing the timeout setting of the ADO.NET Command?

WesleyB

Visit my SQL Server weblog @. http://dis4ea.blogspot.com

|||

As Wesley indicated you should change the query timeout.

SQL 2000 does not have fast file initialization so it can take awhile to create and initialize the files.

|||

Yes I forgot to mention. The object which executes query is SMO.Database and it runs

Smo.Database.ExecuteNonQuery(sSQL)

So I set the timeout on the Smo.Server.Connection = 0.

I've also tried to set it to Smo.Server.Connection=3600 but the timeout occure in about 12 min so

sorry It did not help...

|||

For those of you who might be looking for answer to the similar problem - I found the solution. SMO has StatementTimeout property. This is different from the one I was trying to change - which was a Connection timeout.

That is the one which needed to be increased.

I do appreciate the responses to my post though. It helped. Thanks

Restore database from a TRN file

I am suppose to create a database from a TRN file sent to me. I execute the
following in Query Analyzer:
RESTORE LOG omegaproposal from
disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
go
I get this error:
The preceding restore operation did not specify WITH NORECOVERY or WITH
STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
STANDBY for all but the final step.
My question is: Can I create a database from a TRN file? If so, what is
wrong with my statement? The error says I did not specify WITH NORECOVERY
but I obviously did.
Thanks.No, you can not create a db from a transaction log. You can create a db,
restoring from a full db backup.
AMB
"Darin" wrote:
> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.|||You would have to have both the *BAK file (full database backup) and any
*.TRN files that were associated with that backup. A TRN file is only the
transactions since the last transaction log backup or full database backup.
"Darin" wrote:
> I am suppose to create a database from a TRN file sent to me. I execute the
> following in Query Analyzer:
> RESTORE LOG omegaproposal from
> disk='c:\temp\OmegaProposal_tlog_200506070000.TRN' WITH NORECOVERY
> go
> I get this error:
> The preceding restore operation did not specify WITH NORECOVERY or WITH
> STANDBY. Restart the restore sequence, specifying WITH NORECOVERY or WITH
> STANDBY for all but the final step.
> My question is: Can I create a database from a TRN file? If so, what is
> wrong with my statement? The error says I did not specify WITH NORECOVERY
> but I obviously did.
> Thanks.

restore database form MSSQL server with no servicepack

Hi,
I have the following situation: My source server is MSSQL 2K without
servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
with sp4.
Assume I don't want to upgrade the source server. What are my options:
1) Is it possible to backup the database on the source server and restore it
on the destination? Do I have to run some scripts to upgrade the database to
a sp3 db?
2) Is the same also possible for a attach/detach action?
3) Does somebody have an article about what a MSSQL servicepack is actually
doing (only the master database, or also user databases)?
thanks!
Hi
Attach/Detach and Backup/Restore will both work without any issue.
If you want to see what a SP does to Master and User DB's, look at the
scripts that ship with the SP. Generally, user DB's are not touched, only the
system ones.
Running a non-SP 3 box is rather dangerous, especially with Slammer virus
around.
Cheers
Mike
"Wilfred van Dijk" wrote:

> Hi,
> I have the following situation: My source server is MSSQL 2K without
> servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
> with sp4.
> Assume I don't want to upgrade the source server. What are my options:
> 1) Is it possible to backup the database on the source server and restore it
> on the destination? Do I have to run some scripts to upgrade the database to
> a sp3 db?
> 2) Is the same also possible for a attach/detach action?
> 3) Does somebody have an article about what a MSSQL servicepack is actually
> doing (only the master database, or also user databases)?
> thanks!

restore database form MSSQL server with no servicepack

Hi,
I have the following situation: My source server is MSSQL 2K without
servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
with sp4.
Assume I don't want to upgrade the source server. What are my options:
1) Is it possible to backup the database on the source server and restore it
on the destination? Do I have to run some scripts to upgrade the database to
a sp3 db?
2) Is the same also possible for a attach/detach action?
3) Does somebody have an article about what a MSSQL servicepack is actually
doing (only the master database, or also user databases)?
thanks!Hi
Attach/Detach and Backup/Restore will both work without any issue.
If you want to see what a SP does to Master and User DB's, look at the
scripts that ship with the SP. Generally, user DB's are not touched, only the
system ones.
Running a non-SP 3 box is rather dangerous, especially with Slammer virus
around.
Cheers
Mike
"Wilfred van Dijk" wrote:
> Hi,
> I have the following situation: My source server is MSSQL 2K without
> servicepacks. My destination server is MSSQL 2K with SP3. Both running Win2K
> with sp4.
> Assume I don't want to upgrade the source server. What are my options:
> 1) Is it possible to backup the database on the source server and restore it
> on the destination? Do I have to run some scripts to upgrade the database to
> a sp3 db?
> 2) Is the same also possible for a attach/detach action?
> 3) Does somebody have an article about what a MSSQL servicepack is actually
> doing (only the master database, or also user databases)?
> thanks!

Restore database fails because database is in use.

I'm a newbie so please be gentle.

In attempting to run a restore I get the following error message:
"Exclusive access could not be obtained because the database is in
use." However, it doesn't appear that there are any connections to the
database. At the suggestion of another thread, I ran sp_who2 and there
are no connections to the database in question. I've been running the
same restore for months and all has been well, until yesterday...

I wrote an .asp page (below) to test connectivity to this database.
The vb code ran successfully and soon thereafter, my nightly restore
failed. Task manager does no show that the asp page is active.

Lastly, I did add a user ("jbtest") as well as change the option to use
either Windows authentication or SQL Server authentication in
Enterprise Manager.

Any ideas would be greatly appreciated.

Thanks.

<%@. Language=VBScript %
<%
companyName = "agemni"

Set cnn = Server.CreateObject("ADODB.Connection")
cnn.ConnectionString = "DRIVER=SQL
Server;SERVER=SHEELA-NA-GIG;UID=jbtest;PWD=test1;APP=Microsoft
Development Environment;WSID=SHEELA-NA-GIG;DATABASE=" & companyName &
";Trusted_Connection=No"

cnn.ConnectionTimeout = 300
cnn.CommandTimeout = 300
cnn.Open

Set RS = Server.CreateObject("ADODB.Recordset")
strSQL = "SELECT * FROM Preferences"
RS.Open strSQL, cnn

Response.Write "connection established with the <b>" & RS("Company
Name") & "</b> database. They are a <b>" & RS("street") & "</b>
customer. "

RS.Close
Set RS = Nothing
%<jbmccluskey@.satcountry.com> wrote in message
news:1112117455.317127.10740@.g14g2000cwa.googlegro ups.com...
> I'm a newbie so please be gentle.
> In attempting to run a restore I get the following error message:
> "Exclusive access could not be obtained because the database is in
> use." However, it doesn't appear that there are any connections to the
> database. At the suggestion of another thread, I ran sp_who2 and there
> are no connections to the database in question. I've been running the
> same restore for months and all has been well, until yesterday...
> I wrote an .asp page (below) to test connectivity to this database.
> The vb code ran successfully and soon thereafter, my nightly restore
> failed. Task manager does no show that the asp page is active.
> Lastly, I did add a user ("jbtest") as well as change the option to use
> either Windows authentication or SQL Server authentication in
> Enterprise Manager.
> Any ideas would be greatly appreciated.
> Thanks.

<snip
Probably the most reliable solution is to use ALTER DATABASE to get rid of
any open connections, wherever they're from:

alter database MyDB set restricted_user with rollback immediate

See Books Online for the other state options (single_user, offline etc.) -
one of them might suit your needs better.

You don't mention how you're running the restore, but if it's via a
scheduled job, you should also check that the selected database for the
restore step is set to master, not to the database you're restoring,
otherwise the job can block itself.

Simon|||Thanks for the reply Simon. The problem was that I was trying to run
the RESTORE in the database itself as opposed to running it under
MASTER.

JB

Restore database error: Microsoft SQL-DMO (ODBC SQLState: 42000)

I created a complete backup of a SQL Server 7 database so that I can
recreate it on a SQL Server 2000 database.
I tried 2 options but got the following errors.
1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
of the database and tried restoring it. I selected the backup
BaseFinal_Backup.BAK as the device.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
'BaseFinal_Data' cannot be restored to
'E:\Mssql\Data\BaseFinal_Data.mdf'.
Use WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
2. Using EM, I created an empty BaseFinal database. Using Tools |
Restore Database, selected BaseFinal and tried restoring it selecting
the device from my C drive.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The backup set holds a backup of a database other than the existing
'BaseFinal' database.
RESTORE DATABASE is terminating abnormally.
Thanks.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!When you are restoring with EM, check the Options tab, part "Restore
database files as:" in the middle. There you can change the path to the
files, so it points to a valid drive and folder.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I created a complete backup of a SQL Server 7 database so that I can
> recreate it on a SQL Server 2000 database.
> I tried 2 options but got the following errors.
> 1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
> of the database and tried restoring it. I selected the backup
> BaseFinal_Backup.BAK as the device.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
> 'BaseFinal_Data' cannot be restored to
> 'E:\Mssql\Data\BaseFinal_Data.mdf'.
> Use WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
> cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> 2. Using EM, I created an empty BaseFinal database. Using Tools |
> Restore Database, selected BaseFinal and tried restoring it selecting
> the device from my C drive.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The backup set holds a backup of a database other than the existing
> 'BaseFinal' database.
> RESTORE DATABASE is terminating abnormally.
> Thanks.
> PLEASE REPLY TO THE GROUP. THANKS.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!|||Hi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.ldf'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si> wrote in
message news:unJyQaFiEHA.4064@.TK2MSFTNGP12.phx.gbl...
> When you are restoring with EM, check the Options tab, part "Restore
> database files as:" in the middle. There you can change the path to the
> files, so it points to a valid drive and folder.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
> news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
>|||The following code did the trick.
RESTORE FILELISTONLY
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
RESTORE DATABASE DB1639
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
WITH MOVE 'DBFinal_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Data.mdf',
MOVE 'DBFinal_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Log.ldf'
GO
Thanks for the help.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!

Restore database error: Microsoft SQL-DMO (ODBC SQLState: 42000)

I created a complete backup of a SQL Server 7 database so that I can
recreate it on a SQL Server 2000 database.
I tried 2 options but got the following errors.
1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
of the database and tried restoring it. I selected the backup
BaseFinal_Backup.BAK as the device.
Microsoft SQL-DMO (ODBC SQLState: 42000)
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
'BaseFinal_Data' cannot be restored to
'E:\Mssql\Data\BaseFinal_Data.mdf'.
Use WITH MOVE to identify a valid location for the file.
Device activation error. The physical file name
'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
WITH MOVE to identify a valid location for the file.
RESTORE DATABASE is terminating abnormally.
2. Using EM, I created an empty BaseFinal database. Using Tools |
Restore Database, selected BaseFinal and tried restoring it selecting
the device from my C drive.
Microsoft SQL-DMO (ODBC SQLState: 42000)
The backup set holds a backup of a database other than the existing
'BaseFinal' database.
RESTORE DATABASE is terminating abnormally.
Thanks.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
When you are restoring with EM, check the Options tab, part "Restore
database files as:" in the middle. There you can change the path to the
files, so it points to a valid drive and folder.
Dejan Sarka, SQL Server MVP
Associate Mentor
Solid Quality Learning
More than just Training
www.SolidQualityLearning.com
"malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
> I created a complete backup of a SQL Server 7 database so that I can
> recreate it on a SQL Server 2000 database.
> I tried 2 options but got the following errors.
> 1. Using EM's Tools | Restore Database, I typed in BaseFinal as the name
> of the database and tried restoring it. I selected the backup
> BaseFinal_Backup.BAK as the device.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Data.mdf' may be incorrect. File
> 'BaseFinal_Data' cannot be restored to
> 'E:\Mssql\Data\BaseFinal_Data.mdf'.
> Use WITH MOVE to identify a valid location for the file.
> Device activation error. The physical file name
> 'E:\Mssql\Data\BaseFinal_Log.ldf' may be incorrect. File 'BaseFinal_Log'
> cannot be restored to 'E:\Mssql\Data\BaseFinal_Log.ldf'. Use
> WITH MOVE to identify a valid location for the file.
> RESTORE DATABASE is terminating abnormally.
> 2. Using EM, I created an empty BaseFinal database. Using Tools |
> Restore Database, selected BaseFinal and tried restoring it selecting
> the device from my C drive.
> Microsoft SQL-DMO (ODBC SQLState: 42000)
> The backup set holds a backup of a database other than the existing
> 'BaseFinal' database.
> RESTORE DATABASE is terminating abnormally.
> Thanks.
> PLEASE REPLY TO THE GROUP. THANKS.
> *** Sent via Developersdex http://www.codecomments.com ***
> Don't just participate in USENET...get rewarded for it!
|||Hi,
It seems you have given a physical location which is not in the new server
(Drive and directory) while loading in SQL 2000.
Please follow the below steps in query analyzer:-
Restore filelistonly from disk='c:\backup\dbname.bak'
( replace the 'c:\backup\dbname.bak' with the actual backup file name with
path where the file resides.)
This will give you the Logical and Physical file names of the Backup file
name. While loading you should give the
correct logical file name and the place to keep the physical file. But
Physical file name can be a diffrent one.
Restore Database <dbname> from disk= 'c:\backup\dbname.bak' with
move 'logical_mdf_name' to 'c:\mssql\data\phys_data_name.mdf',
move 'logical_ldf_name' to 'c:\mssql\data\phys_log_name.ldf'
(Replace the logical_mdf_name and logical_ldf_name with the logical name you
got from RESTORE FILELISTONLY command.
Ensure that the directory give in physical file name is there in the server)
Thanks
Hari
MCDBA
"Dejan Sarka" <dejan_please_reply_to_newsgroups.sarka@.avtenta.si > wrote in
message news:unJyQaFiEHA.4064@.TK2MSFTNGP12.phx.gbl...
> When you are restoring with EM, check the Options tab, part "Restore
> database files as:" in the middle. There you can change the path to the
> files, so it points to a valid drive and folder.
> --
> Dejan Sarka, SQL Server MVP
> Associate Mentor
> Solid Quality Learning
> More than just Training
> www.SolidQualityLearning.com
> "malehit@.devdex.com" <anonymous@.devdex.com> wrote in message
> news:uVSmPTFiEHA.2340@.TK2MSFTNGP11.phx.gbl...
>
|||The following code did the trick.
RESTORE FILELISTONLY
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
RESTORE DATABASE DB1639
FROM DISK = 'C:\INTOP\DBFinal_Backup.BAK'
WITH MOVE 'DBFinal_Data' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Data.mdf',
MOVE 'DBFinal_Log' TO 'C:\Program Files\Microsoft SQL
Server\MSSQL\Data\DB1639_Log.ldf'
GO
Thanks for the help.
PLEASE REPLY TO THE GROUP. THANKS.
*** Sent via Developersdex http://www.codecomments.com ***
Don't just participate in USENET...get rewarded for it!
sql

Restore database error

I'm trying to restore a database and am getting the following error:
"MODIFY FILE encountered operating system error 112(There is not enough spac
e
on the disk.) while attempting to expand the physical file. Could not adjust
the space allocation for file 'MyFile_Log'. RESTORE database is terminating
abnormally."
What do I need to do to solve this issue?
Thanks,
Ninel
Message posted via webservertalk.com
http://www.webservertalk.com/Uwe/Forum...amming/200512/1Hi
You don't have enough disk space for the DB. Full stop.
Don't forget, the size of the backup does not indicate the size of the DB.
Look at RESTORE FILELISTONLY and RESTORE HEADERONLY to get the information
as to how much space you need on disk.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
IM: mike@.epprecht.net
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"ninel g via webservertalk.com" <u9125@.uwe> wrote in message
news:5895a870b9427@.uwe...
> I'm trying to restore a database and am getting the following error:
> "MODIFY FILE encountered operating system error 112(There is not enough
> space
> on the disk.) while attempting to expand the physical file. Could not
> adjust
> the space allocation for file 'MyFile_Log'. RESTORE database is
> terminating
> abnormally."
> What do I need to do to solve this issue?
> Thanks,
> Ninel
> --
> Message posted via webservertalk.com
> http://www.webservertalk.com/Uwe/Forum...amming/200512/1sql

Restore Database daily without SPs

Hello all,

I have the following requirement (SQL 2000, SP2)

1. Need to restore production database(A) to another database (B) on
another server (No direct connection)
2. In the restored back-up(B) SP codes should not be visible (even to
sa) or all SP's can be deleted .

3. This needs to be carried out daily

4. New tables may be added frequently to A

5. This job should be scheduled

6. I can overwrite the entire database (B)

7. I can not encrypt SP in the original database (A)

I thought of Snap-shot replication first. But when new tables are
added , I need to add new tables manually to publication .

Can some one suggest most reliable and implementable method ?

Thanks
Srinivas"M A Srinivas" <masri@.vsnl.com> wrote in message
news:f7e90f78.0307242125.3da82cb8@.posting.google.c om...
> Hello all,
> I have the following requirement (SQL 2000, SP2)
> 1. Need to restore production database(A) to another database (B) on
> another server (No direct connection)
> 2. In the restored back-up(B) SP codes should not be visible (even to
> sa) or all SP's can be deleted .

Hmm, a normal Backup/Restore won't do this (since it'll restore the stored
procs.)

You're probably better off writing a DTS package.

> 3. This needs to be carried out daily
> 4. New tables may be added frequently to A

That might be difficult to handle with a DTS package automatically, but if
you make sure you add them manually you're all set.

Another option would be to have a SECOND DB and put the stored procs in
there and then just qualify all stored procs by prepending the name of the
second DB.

So, myProc_foo in the first database would be moved to the 2nd one and now
called SecondDB..myProc_foo.

A little more complicated, but doable.

> 5. This job should be scheduled
> 6. I can overwrite the entire database (B)
> 7. I can not encrypt SP in the original database (A)
>
> I thought of Snap-shot replication first. But when new tables are
> added , I need to add new tables manually to publication .
> Can some one suggest most reliable and implementable method ?
> Thanks
> Srinivas|||Greg D. Moore (Strider) (mooregr@.greenms.com) writes:
> Hmm, a normal Backup/Restore won't do this (since it'll restore the stored
> procs.)
>...
> Another option would be to have a SECOND DB and put the stored procs in
> there and then just qualify all stored procs by prepending the name of the
> second DB.

Aargh! Never hard-code DB-names! You get all sorts of trouble if you
need to set up a test environment on the same server.

However, a second DB was my thought to. First backup original DB. Restore
locally into a second database. Drop all stored procedures. Backup the
second database. Ship and restore on the second server.

If the disk space and time is available, this is probably the simplest
way.

--
Erland Sommarskog, SQL Server MVP, sommar@.algonet.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Restore database April CTP 2005 Failed?

The following failed in EM (or whatever it is called now management
studio I think) and I scripted it out and ran it in a query window to
get more info. This works fine in SQL 2000 but not in 2005. I also tried
to 'create' the database first then ran this command below but with the
'force restore over existing database' checked on but this failed also
which is odd as well. Any ideas. This is my very first play with 2005 so
it is not off to a great start although to be fair, i seem to have gor
lucky on the install as it all went OK. BTW, is there a more approriate
newsgroup for this query?

Thanks

RESTORE DATABASE [bob] FROM DISK = N'C:\BigFujiCopies\Bob.bak' WITH
FILE = 1,
MOVE N'bob_Data' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Data.MDF',
MOVE N'bob_Log' TO N'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Log.LDF',
NOUNLOAD, STATS = 10
GO

Msg 5122, Level 16, State 1, Line 1
Directory lookup for the file "c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Data.MDF"
failed with the operating system error 3(error not found).
Msg 3156, Level 16, State 3, Line 1
File 'bob_Data' cannot be restored to 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Data.MDF'.
Use WITH MOVE to identify a valid location for the file.
Msg 5122, Level 16, State 1, Line 1
Directory lookup for the file "c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Log.LDF" failed with the operating system error
3(error not found).
Msg 3156, Level 16, State 3, Line 1
File 'bob_Log' cannot be restored to 'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Log.LDF'. Use WITH MOVE to identify a valid
location for the file.
Msg 3119, Level 16, State 1, Line 1
Problems were identified while planning for the RESTORE statement.
Previous messages provide details.
Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

*** Sent via Developersdex http://www.developersdex.com ***Dearie me, if i would just take a closer look at what was stating me in
the face, i might just save myself countless wasted hours and an eggy
coupon. Suffice as to say the error is pretty much right as a database
cannot be restored/created to that location because of a complete lack
of a directory!! The directory structure in 2005 is a litlle differet as
MSSQL now appears under MSSQL.1, so ,he says (hanging head) it should be
'c:\Program Files\Microsoft SQL
Server\MSSQL.1\MSSQL\Data\bob_Data.MDF'.

and NOT

'c:\Program Files\Microsoft SQL
Server\MSSQL\Data\bob_Data.MDF'.

Worth making a not of (although is there not a case for the restore
command to simply create the directory structure you wish to restore to
on the hoof which would save people having to come of management studio,
make the directory tree up for cases where they want to store data and
log files in odd places?)

*** Sent via Developersdex http://www.developersdex.com ***|||DMAC@.devdex.com (DMAC@.devdex.com) writes:
> Worth making a not of (although is there not a case for the restore
> command to simply create the directory structure you wish to restore to
> on the hoof which would save people having to come of management studio,
> make the directory tree up for cases where they want to store data and
> log files in odd places?)

Maybe there is, but the newsgroup to post this to is really
microsoft.private.sqlserver2005.tools.graphical. See
http://go.microsoft.com/fwlink/?linkid=31765 for access information.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

RESTORE DATABASE ... WITH PARTIAL

I posted this to another group but I must not be subscribed as nothing
appeared. Sooo...

I've backed up a databse with the following command:

BACKUP DATABASE Thomasville
FILEGROUP = 'PRIMARY'
TO DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH FORMAT,
Password = 'p@.$$w0rd'

I back up using Filegroups because I dont' want to back up the large,
static ones on a regular basis. This backup worked just fine. When I
try to restore it I run:

RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
PASSWORD = 'p@.$$w0rd',
MOVE 'Thomasville_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_Thomas.LDF',
NORECOVERY

I get this:

Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak' was created by BACKUP DATABASE...FILE=<name>
and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Any ideas what I'm doing wrong here? Except for what applies to my
backup this is copied character for character from some Microsoft BOL.

Please help.

Thanks,
Utah(Utahduck@.hotmail.com) writes:

Quote:

Originally Posted by

I back up using Filegroups because I dont' want to back up the large,
static ones on a regular basis. This backup worked just fine. When I
try to restore it I run:
>
RESTORE DATABASE Thomas
FILEGROUP = 'PRIMARY'
FROM DISK = 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak'
WITH PARTIAL,
PASSWORD = 'p@.$$w0rd',
MOVE 'Thomasville_Data' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Data_Thomas.MDF',
MOVE 'Thomasville_Log' TO 'd:\SQLServerData\MSSQL\data
\Thomasville_Log_Thomas.LDF',
NORECOVERY
>
I get this:
>
Server: Msg 3135, Level 16, State 2, Line 1
The backup set in file 'D:\SQLBackups\2 - Monday\Full-Thomasville-
PRIMARY-070409-1528h.bak' was created by BACKUP DATABASE...FILE=<name>
and cannot be used for this restore operation.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

In SQL 2000 you can only do partial restore from a full backup. SQL 2005
provides new options, although I am not certain that they addrss your
needs.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se
Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

restore database

Hello,
I'm restoring database backup from a sqlserver 7 to an other sqlserver 7 with the following command.

USE MASTER
RESTORE DATABASE MyBase
FROM DISK=D:\MYFIC.BAK
WITH
MOVE MyBase_Data TO D:\MSSQL7\Data\MyBase_data.MDF',
MOVE MyBase_Log TO D:\MSSQL7\Data\MyBae_Log.LDF,
REPLACE, RESTART, STANDBY=D:\MSSQL7\BACKUP\MyBase_UNDO.DAT,
STATS
GO

The restore command failed with error messages :

Msg 3120, Level 16, State 1, Server S20FRNDCSQLFR01, Line 1
The database you are attempting to restore was backed up under a different sort order ID (42) than the one currently running on this server (52), and at least one of them is a non-binary sort order.
Msg 3013, Level 16, State 1, Server S20FRNDCSQLFR01, Line 1
Backup or restore operation terminating abnormally.

Can you help me.It's urgentRead the following previous post:

link (http://dbforums.com/arch/72/2002/6/394630)

Restore being annoying

Hi,

I am trying to restore a database and call it a different name, but it comes up with the following error:

Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file '_datafile' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Please help this is doing my head in.

The database I am trying to restore from is called TMG and I would like to call the new Database TMG2Howdy

I suspect you have a filename problem - go to the options tab and make sure the filenames for the mdf & ldf files for TMG2 are unique to the server. Alter them if need be just before starting the restore.

The logical file names dont have to be unique.

Cheers,

SG.|||Hi,

TMG2 is not created as a database yet, I want the restore to create the new database.|||Hi again,

This is my code:

RESTORE FILELISTONLY
FROM Dumps_TMG_PM

RESTORE DATABASE TMG2 from Dumps_TMG_PM with RECOVERY,

MOVE 'TMG_dat.mdf' TO 'D:\Data\TMG2_data.mdf',
MOVE 'TMG_log.ldf' TO 'E:\TMG2_log.ldf'|||What does...

RESTORE FILIELISTONLY FROM DISK = '<pathname><dbname>.bak'

Give you?

Check out BOL for more examples?|||Hi,

It gives me this:

Executed as user: sa. Invalid object name 'msdb.dbo.restorestatus'. [SQLSTATE 42S02] (Error 208) Logical file 'tmg_dat.mdf' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names. [SQLSTATE 42000] (Error 3234) RESTORE DATABASE is terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.

Same error basically.|||That's from the restore...did you highlight an execute the command I gave you only?

If you just execute, it'll do the whole window...you are in QA, correct?

What did you type exactly?|||Brett .. what exactly does this table do ... 'msdb.dbo.restorestatus'... any idea. Is it created on the run time coz my msdb also does not have this object (table or sp or whatever)|||TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000

IN QA this is what it gave me.|||How about:

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'D:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO 'E:\TMG_Log.LDF'
, REPLACE

And I have no idea about that system table...

and what version of sql are we talking about?|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000

IN QA this is what it gave me.

try this command :

restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Originally posted by SQLSlammer
TMG_dat I:\Data\tmg_data.mdf D PRIMARY 6815744000 35184372080640
TMG_log k:\TMG_log.LDF L NULL 1867776000 7340032000

IN QA this is what it gave me.

try this command :

restore database TMG2 from disk = '<pathname><dbname>.bak'
with move 'TMG_dat' to ''<pathname>tmg2_data.mdf',
move 'TMG_log' to ''<pathname>tmg2_log.ldf'|||Sorry chaps still gives me the same error, by the way this database does not exist yet.

There is already a database called TMG on this server, but it is in warm standby mode as it is being log shipped.|||It doesn't have to exist...

my bad btw

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE

Cut and paste that...it should work...|||Hi in QA it came up with this:

Server: Msg 3234, Level 16, State 2, Line 1
Logical file 'TMG_Data' is not part of database 'TMG2'. Use RESTORE FILELISTONLY to list the logical file names.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Same thing........this is doing my head in!

Thanks for all your help so far chaps|||Originally posted by Brett Kaiser
It doesn't have to exist...

my bad btw

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE

Cut and paste that...it should work...

Your Bad again .. Brett !!!

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.dmp'
WITH MOVE 'TMG_Dat' TO 'I:\Data\TMG.MDF'
, MOVE 'TMG_Log' TO '\K:\TMG_Log.LDF'
, REPLACE|||Good catch...thanks...

But let me ask you...do ever set it up as dat?

dat is da funnyest ting I even sawed...|||Originally posted by Brett Kaiser
dat is da funnyest ting I even sawed...

it is dat same case here :)|||Guys,

dat was the problem! lol

Thanks very much for your help,

Take it easy!|||Guys,

dat was the problem! lol

Thanks very much for your help,

Take it easy!|||Run this first:
--Query_1:

restore headeronly from Logical_device_name

Get the position fo the backup set you want to restore.

Next as stated in the responses above, use:

--Query_2:

restore filelistonly from Logical_device_name

Get the database filenames.

RESTORE DATABASE TMG2
FROM DISK = '<database dump path>\TMG.bak'
,WITH file = 'position from Query_1', RECOVERY, REPLACE,
MOVE 'TMG_Data' TO 'I:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'K:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'

Items after the keyword 'MOVE' are obtained from Query_2.

Make sure, you have sufficient space on your discs.|||Can you specify a different location to restore to?

For instance:

MOVE 'TMG_Data' TO 'J:\Data\TMG.MDF'
,MOVE 'TMG_Log' TO 'R:\TMG_Log.LDF'
,Move 'TMG_File_n' to 'Drive:...\SomeName.ndf or ldf as the case may be'

where J and R are on a diffrent server than where it was backed up from?

The problem I am having is I get his

[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server. Connection may have been terminated by the server.
Server: Msg 3158, Level 16, State 1, Line 1
Could not create one or more files. Consider using the WITH MOVE option to identify valid locations.
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.

Connection Broken