Restored Backup problem

I had to reinstall my entire Server week. I had everything of importance
backed up.
When I put my Sql Server database .MDF files back, I found that although the
Users within the databases were there, the Logins were not. Where are the
Logins stored? I have my entire previous hard disk contents available. I jus
t
need to know what to restore.Hi
In master database.
t_4g4w.asp" target="_blank">http://msdn.microsoft.com/library/d... />
t_4g4w.asp
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/
"Hoof Hearted" <HoofHearted@.discussions.microsoft.com> wrote in message
news:87C35929-C18C-4C8A-A80C-B36283866EE2@.microsoft.com...
>I had to reinstall my entire Server week. I had everything of importance
> backed up.
> When I put my Sql Server database .MDF files back, I found that although
> the
> Users within the databases were there, the Logins were not. Where are the
> Logins stored? I have my entire previous hard disk contents available. I
> just
> need to know what to restore.|||Hello Mike,
Yes I should have thought of that. It works fine now. Thank you.
Ian

Restore using SQLDMO

I am using SQLDMO for restoring database from back up file . If the back up file version(SQL2000) is differenent than database version(SQL Express) I am trying to restore, does SQLDMO performs upgrade if the backup files are an earlier version. if no how can i handle that situation?It should restore the database on the server, but set it to compatability level 80, so the SQL 2000 functionality will still be there.

restore transaction isolation level

Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executed
Zeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>
|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only[vbcol=seagreen]
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> the
> someone
should[vbcol=seagreen]
begins,[vbcol=seagreen]
don't
> read
>
|||Easy: simply add the statement With HoldLock to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>
|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption =
'isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...[vbcol=seagreen]
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
to[vbcol=seagreen]
to[vbcol=seagreen]
> should
PROCEDURE --[vbcol=seagreen]
> begins,
> don't
to
>
|||SET commands doesn't stick after the proc execution, so you only need to restore the isolation level
if you have further statements *inside* the procedure that you want to restore isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
> should
> begins,
> don't
>

restore transaction isolation level

Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> >
> > Within a store procedure I would like to pump up the isolation level to
> > serializable just for one particular transaction and restore it back to
> the
> > previous level when it's done even if there is a fatal error, would
> someone
> > know how to do that? Thank you very much
> >
> > Here is what I thought the implemenation/body of my store procedure
should
> > be but I see problems
> >
> >
> > --STORE PROCEDURE --
> >
> > --If I set transaction isolation level here before the transaction
begins,
> > it will stay there through out the life of the connection, right? I
don't
> > want that.
> >
> > BEGIN TRANSACTION
> > -- If I set transaction isolation level here, it might not be
> > applicable for this transaction, right?
> >
> > -- DOING SOME QUERYING AND UPDATING HERE
> >
> > COMMIT TRANSACTION
> >
> > -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> > it and restore it 2) If my update fails,this will never get executed
> >
> >
>|||Easy: simply add the statement « With HoldLock » to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption ='isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> > Zeng
> >
> > You can set the isolation level back to READ COMMITTED:
> >
> > SET TRANSACTION ISOLATION SERIALIZABLE
> > BEGIN TRANSACTION
> > ... trap errors!
> >
> > If errors,
> > ROLLBACK
> > else
> > COMMIT
> >
> > SET TRANSACTION ISOLATION READ COMMITTED
> > RETURN
> >
> > You can read the current isolation level with DBCC USEROPTIONS, but you
> only
> > see the isolation level if it's been changed once.
> >
> > Hope this helps,
> > Ron
> > --
> > Ron Talmage
> > SQL Server MVP
> >
> > "Zeng" <zzy@.nonospam.com> wrote in message
> > news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> > >
> > > Within a store procedure I would like to pump up the isolation level
to
> > > serializable just for one particular transaction and restore it back
to
> > the
> > > previous level when it's done even if there is a fatal error, would
> > someone
> > > know how to do that? Thank you very much
> > >
> > > Here is what I thought the implemenation/body of my store procedure
> should
> > > be but I see problems
> > >
> > >
> > > --STORE
PROCEDURE --
> > >
> > > --If I set transaction isolation level here before the transaction
> begins,
> > > it will stay there through out the life of the connection, right? I
> don't
> > > want that.
> > >
> > > BEGIN TRANSACTION
> > > -- If I set transaction isolation level here, it might not be
> > > applicable for this transaction, right?
> > >
> > > -- DOING SOME QUERYING AND UPDATING HERE
> > >
> > > COMMIT TRANSACTION
> > >
> > > -- If I restore the isolation level here, there are 2 issues? 1) How
to
> > read
> > > it and restore it 2) If my update fails,this will never get executed
> > >
> > >
> >
> >
>|||SET commands doesn't stick after the proc execution, so you only need to restore the isolation level
if you have further statements *inside* the procedure that you want to restore isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
>> Zeng
>> You can set the isolation level back to READ COMMITTED:
>> SET TRANSACTION ISOLATION SERIALIZABLE
>> BEGIN TRANSACTION
>> ... trap errors!
>> If errors,
>> ROLLBACK
>> else
>> COMMIT
>> SET TRANSACTION ISOLATION READ COMMITTED
>> RETURN
>> You can read the current isolation level with DBCC USEROPTIONS, but you
> only
>> see the isolation level if it's been changed once.
>> Hope this helps,
>> Ron
>> --
>> Ron Talmage
>> SQL Server MVP
>> "Zeng" <zzy@.nonospam.com> wrote in message
>> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
>> >
>> > Within a store procedure I would like to pump up the isolation level to
>> > serializable just for one particular transaction and restore it back to
>> the
>> > previous level when it's done even if there is a fatal error, would
>> someone
>> > know how to do that? Thank you very much
>> >
>> > Here is what I thought the implemenation/body of my store procedure
> should
>> > be but I see problems
>> >
>> >
>> > --STORE PROCEDURE --
>> >
>> > --If I set transaction isolation level here before the transaction
> begins,
>> > it will stay there through out the life of the connection, right? I
> don't
>> > want that.
>> >
>> > BEGIN TRANSACTION
>> > -- If I set transaction isolation level here, it might not be
>> > applicable for this transaction, right?
>> >
>> > -- DOING SOME QUERYING AND UPDATING HERE
>> >
>> > COMMIT TRANSACTION
>> >
>> > -- If I restore the isolation level here, there are 2 issues? 1) How to
>> read
>> > it and restore it 2) If my update fails,this will never get executed
>> >
>> >
>>
>sql

restore transaction isolation level

Within a store procedure I would like to pump up the isolation level to
serializable just for one particular transaction and restore it back to the
previous level when it's done even if there is a fatal error, would someone
know how to do that? Thank you very much
Here is what I thought the implemenation/body of my store procedure should
be but I see problems
--STORE PROCEDURE --
--If I set transaction isolation level here before the transaction begins,
it will stay there through out the life of the connection, right? I don't
want that.
BEGIN TRANSACTION
-- If I set transaction isolation level here, it might not be
applicable for this transaction, right?
-- DOING SOME QUERYING AND UPDATING HERE
COMMIT TRANSACTION
-- If I restore the isolation level here, there are 2 issues? 1) How to read
it and restore it 2) If my update fails,this will never get executedZeng
You can set the isolation level back to READ COMMITTED:
SET TRANSACTION ISOLATION SERIALIZABLE
BEGIN TRANSACTION
... trap errors!
If errors,
ROLLBACK
else
COMMIT
SET TRANSACTION ISOLATION READ COMMITTED
RETURN
You can read the current isolation level with DBCC USEROPTIONS, but you only
see the isolation level if it's been changed once.
Hope this helps,
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
the
> previous level when it's done even if there is a fatal error, would
someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
read
> it and restore it 2) If my update fails,this will never get executed
>|||That won't work well for me because:
1) Fatal error doesn't go through the trap
2) Read Committed is not always what my store proc might start out with.
"Ron Talmage" <rtalmage@.prospice.com> wrote in message
news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> Zeng
> You can set the isolation level back to READ COMMITTED:
> SET TRANSACTION ISOLATION SERIALIZABLE
> BEGIN TRANSACTION
> ... trap errors!
> If errors,
> ROLLBACK
> else
> COMMIT
> SET TRANSACTION ISOLATION READ COMMITTED
> RETURN
> You can read the current isolation level with DBCC USEROPTIONS, but you
only
> see the isolation level if it's been changed once.
> Hope this helps,
> Ron
> --
> Ron Talmage
> SQL Server MVP
> "Zeng" <zzy@.nonospam.com> wrote in message
> news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> the
> someone
should[vbcol=seagreen]
begins,[vbcol=seagreen]
don't[vbcol=seagreen]
> read
>|||Easy: simply add the statement With HoldLock to your Select query that
you want to be Serializable.
S. L.
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23uQxIXh7EHA.4040@.TK2MSFTNGP14.phx.gbl...
> Within a store procedure I would like to pump up the isolation level to
> serializable just for one particular transaction and restore it back to
> the
> previous level when it's done even if there is a fatal error, would
> someone
> know how to do that? Thank you very much
> Here is what I thought the implemenation/body of my store procedure should
> be but I see problems
>
> --STORE PROCEDURE --
> --If I set transaction isolation level here before the transaction begins,
> it will stay there through out the life of the connection, right? I don't
> want that.
> BEGIN TRANSACTION
> -- If I set transaction isolation level here, it might not be
> applicable for this transaction, right?
> -- DOING SOME QUERYING AND UPDATING HERE
> COMMIT TRANSACTION
> -- If I restore the isolation level here, there are 2 issues? 1) How to
> read
> it and restore it 2) If my update fails,this will never get executed
>|||Zeng,
There's nothing you can do about the fatal errors that cancel an entire
batch. For fatal errors that simply abort the procedure, you can set up
wrapper code around the call to the proc that will restore the former
isolation level.
Here's one possible way:
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
DECLARE @.SetValue varchar(100)
CREATE TABLE #useroptions (SetOption varchar(100), SetValue varchar(100))
INSERT #useroptions
EXEC ('DBCC USEROPTIONS')
SET @.SetValue = (SELECT SetValue FROM #useroptions WHERE SetOption =
'isolation level')
DROP TABLE #useroptions
SELECT @.SetValue
-- Simulates a call to a procedure, that changes the isolation level
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
-- Now back in the calling code:
IF @.SetValue IS NULL OR @.SetValue = 'read committed'
SET TRANSACTION ISOLATION LEVEL READ COMMITTED
IF @.SetValue = 'read uncommitted'
SET TRANSACTION ISOLATION LEVEL READ UNCOMMITTED
IF @.SetValue = 'serializable'
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
IF @.SetValue = 'repeatable read'
SET TRANSACTION ISOLATION LEVEL REPEATABLE READ
Ron
--
Ron Talmage
SQL Server MVP
"Zeng" <zzy@.nonospam.com> wrote in message
news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
to[vbcol=seagreen]
to[vbcol=seagreen]
> should
PROCEDURE --[vbcol=seagreen]
> begins,
> don't
to[vbcol=seagreen]
>|||SET commands doesn't stick after the proc execution, so you only need to res
tore the isolation level
if you have further statements *inside* the procedure that you want to resto
re isolation level for.
Evidence:
USE tempdb
GO
CREATE PROC p
AS
DBCC USEROPTIONS
SET TRANSACTION ISOLATION LEVEL SERIALIZABLE
DBCC USEROPTIONS
GO
EXEC p
DBCC USEROPTIONS
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
http://www.sqlug.se/
"Zeng" <zzy@.nonospam.com> wrote in message news:%23E597oh7EHA.2552@.TK2MSFTNGP09.phx.gbl...[v
bcol=seagreen]
> That won't work well for me because:
> 1) Fatal error doesn't go through the trap
> 2) Read Committed is not always what my store proc might start out with.
>
> "Ron Talmage" <rtalmage@.prospice.com> wrote in message
> news:e%23Do%23jh7EHA.2192@.TK2MSFTNGP14.phx.gbl...
> only
> should
> begins,
> don't
>[/vbcol]

Restore to a previous version

Is it possible at all to restore back up from SQL 2005 onto SQL 2000?

If not, what is the bast way to transfer database stucture and data from SQL 2005 onto SQL 2000. Two machines are not on the same network...

No, it is not possible to restore a SQL 2005 server on SQL 2000.

You can script the database to a file and then apply to SQL 2000. Since your servers are not on the same network, the best option would be to export/import using bcp or BULK INSERT. Books on-line would have more information.

Peter

restore problems

I have a db on msde on one server.
I"d like to replicate it by restoring a back up on another PC with a newly
installed MSDE.
I create a shell of the DB
I create a user name which my application uses to login with SQL
authentication.
I give the user db access to the empty-shell to be filled in by the backup
I proceed with the restore.
After the restore is done, the user I created now loses the access to the
db.
When I try to reset the dbaccess for the user, I get an error that says the
user already exists,
This is most liklely because that user name is the owner of some of the
tables in restored DB.
Any ideas as to how to do a restore correctly?
Many Thanks in advance
ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
figuring out what the heck a service account is.
hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:iqOdnZtH1JOec2PdRVn-rA@.speakeasy.net...
> I have a db on msde on one server.
> I"d like to replicate it by restoring a back up on another PC with a newly
> installed MSDE.
> I create a shell of the DB
> I create a user name which my application uses to login with SQL
> authentication.
> I give the user db access to the empty-shell to be filled in by the backup
> I proceed with the restore.
> After the restore is done, the user I created now loses the access to the
> db.
> When I try to reset the dbaccess for the user, I get an error that says
the
> user already exists,
> This is most liklely because that user name is the owner of some of the
> tables in restored DB.
> Any ideas as to how to do a restore correctly?
> Many Thanks in advance
> ps.. I've tried sql-copy and that was a teerrible disatster as I got olst
> figuring out what the heck a service account is.
>
if the "user" shoul'd be the database owner as well, do not make the
corresponing login a dbuser by granting him access to the db..
just restore the db and change the db ownership to your login , which will
automatically grant him access and ownership rights...
EXEC sp_changedbowner 'login' , 'true'
please see
http://msdn.microsoft.com/library/de...ca-cz_30s2.asp
for it's related synopsis and further info
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||I guess I shoudl have mentioned that the problem SEEMs to be that the
created user mysteriously loses its "login" name after the restore.
I found out that the query:
sp_change_users_login 'auto_fix', 'username'
Fixed the problem.
I've looked at sp_changedbowner and it just changes the owner of the db. It
does not fix the user problem.
sp_changedbowner accepts a login name as the parameter, and the problem was
that hhuser did not have a login name after the restore.
Now if I only could find a smoother way of doing this whole restore thing
without all the hassle of creating an empty db, creatina login, restoring,
and fixing the login!!
Sure looks like this ms product has a long way to go before it is user
froiendly even in its more common aspects like restoring a legitimate
backup.
|||hi Ernesto,
"Ernesto" <tsh@.mathematicuslabs.com> ha scritto nel messaggio
news:hc6dndtw6OTxRGLdRVn-hQ@.speakeasy.net...
> I guess I shoudl have mentioned that the problem SEEMs to be that the
> created user mysteriously loses its "login" name after the restore.
> I found out that the query:
> sp_change_users_login 'auto_fix', 'username'
> Fixed the problem.
>
> I've looked at sp_changedbowner and it just changes the owner of the db.
It
> does not fix the user problem.
> sp_changedbowner accepts a login name as the parameter, and the problem
was
> that hhuser did not have a login name after the restore.
> Now if I only could find a smoother way of doing this whole restore thing
> without all the hassle of creating an empty db, creatina login, restoring,
> and fixing the login!!
> Sure looks like this ms product has a long way to go before it is user
> froiendly even in its more common aspects like restoring a legitimate
> backup.
as regard this last sentence, my opinion is not Microsoft "has a long way to
go.." but just us understand how it works..
MSDE/SQL Server is NOT JET, and we have to take care understanding ith'
behaviours..
anyway,
http://www.sqlservercentral.com/colu...okenlogins.asp
Neil Boyle article about this related issue is worth reading, IMHO
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

restore problem about sql server

i reinstall the sql server on the machine, and use attach file method
to get the database back, and it works well on the server, which means
i can get the data using Query analyser, but when i use the web
application to access the database, it always come out with error
message, it seems that the connection to the database can not be
established, but it used to be ok before i reinstalled the sql server.
do i need to do any configuration after attaching the data files and
log files(.mdf and .ldf).

i think the source code(i mean the database connection code) for the
web application should be fine caze i didnt change it before after.

caze i didnt create instance of database and just use default, so the
connection string is defined below:
thisConnection = new SqlConnection(@."Data Source=pdsmfg014;Initial
Catalog=epcsii;User ID=epcs2;Password=******")

the server name is pdsmfg014
the database name is epcsii
oh, the code is designed using C#Dee (luye_qq@.hotmail.com) writes:
> i reinstall the sql server on the machine, and use attach file method
> to get the database back, and it works well on the server, which means
> i can get the data using Query analyser, but when i use the web
> application to access the database, it always come out with error
> message, it seems that the connection to the database can not be
> established, but it used to be ok before i reinstalled the sql server.
> do i need to do any configuration after attaching the data files and
> log files(.mdf and .ldf).
> i think the source code(i mean the database connection code) for the
> web application should be fine caze i didnt change it before after.
> caze i didnt create instance of database and just use default, so the
> connection string is defined below:
> thisConnection = new SqlConnection(@."Data Source=pdsmfg014;Initial
> Catalog=epcsii;User ID=epcs2;Password=******")
> the server name is pdsmfg014
> the database name is epcsii

So is this login epcs2 present on the reinstalled server? In such
case it could be that when you reinstalled the server the mapping
between logins and database users were lost. Run sp_helpuser in the
database and see if the output makes sense.

If not the procedure sp_changes_users_login can help you. Please check
Books Online for details.

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

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp|||thx, erland,
I think the databse is working properly right now.
it must be some OS environment problems that prevent the application
accessing the database.
caze i migrate the application to my laptop and also install the SQL
server on my laptop to access the database, it works. it just means,
the laptop is my application server ,and the original server is just
the database server.
thus, there is sth wrong with my IIS setting probably, but I dont know
how to fig it out.
anyone can help?|||Hi,
You did not mention what kind of error you are getting.

If you are getting access denied error then make sure your Sql instance
authentication is eiter mixed mode or in SQl server authentication mode
and check the user has proper permission on that server, the easiest
way to check is open query analyzer from your laptop and give the same
server name(type id down), userid and password.

besides this make sure both database server and your laptop are in the
same domain and also you can try switching off the windows xp fire
wall.

bye
ssk
Dee wrote:
> thx, erland,
> I think the databse is working properly right now.
> it must be some OS environment problems that prevent the application
> accessing the database.
> caze i migrate the application to my laptop and also install the SQL
> server on my laptop to access the database, it works. it just means,
> the laptop is my application server ,and the original server is just
> the database server.
> thus, there is sth wrong with my IIS setting probably, but I dont know
> how to fig it out.
> anyone can help?|||Dee (luye_qq@.hotmail.com) writes:
> I think the databse is working properly right now.
> it must be some OS environment problems that prevent the application
> accessing the database.
> caze i migrate the application to my laptop and also install the SQL
> server on my laptop to access the database, it works. it just means,
> the laptop is my application server ,and the original server is just
> the database server.
> thus, there is sth wrong with my IIS setting probably, but I dont know
> how to fig it out.
> anyone can help?

As a start, please post the exact error message you are getting.

Did you perform the check with sp_helpuser, that I suggested?

--
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 problem - it's beaten me :(

Hi, I just can't for the life of me get my dbases back.
I restore the Full database ok, but I can't apply the differentials at
all - on *any* database I've got.
All the details (quite a few pics) are here:
http://homepages.paradise.net.nz/duncanm4/sqlrestore/
please, if you can see the problem, I'd sure appreciate knowing about
it.
If further details are required, please reply to the NG.
TIA
--
DuncanThis is a known SQL2000 problem.
To Fix install Latest SQL SP
>--Original Message--
>Hi, I just can't for the life of me get my dbases back.
>I restore the Full database ok, but I can't apply the
differentials at
>all - on *any* database I've got.
>All the details (quite a few pics) are here:
>http://homepages.paradise.net.nz/duncanm4/sqlrestore/
>please, if you can see the problem, I'd sure appreciate
knowing about
>it.
>If further details are required, please reply to the NG.
>TIA
>--
>Duncan
>.
>|||In article <MPG.1a163f978c187ade989742@.msnews.microsoft.com>,
hard@.work.ok says...
> Hi, I just can't for the life of me get my dbases back.
> I restore the Full database ok, but I can't apply the differentials at
> all - on *any* database I've got.
> All the details (quite a few pics) are here:
> http://homepages.paradise.net.nz/duncanm4/sqlrestore/
> please, if you can see the problem, I'd sure appreciate knowing about
> it.
> If further details are required, please reply to the NG.
> TIA
OK, I'm goddamn blind - yes, 22nd <> 29th !@.!@.#!@.
It has already been sugested that I begin masterbating backwards,
thankyou!
--
Duncan|||In article <086501c3a51f$0b263b50$a501280a@.phx.gbl>,
anonymous@.discussions.microsoft.com says...
> This is a known SQL2000 problem.
> To Fix install Latest SQL SP
Thanks Brian, it was found to be a PEBKAC problem.
--
Duncan|||Does below work on your machine? I'm on SQL2K sp1.
CREATE TABLE pubs..test(id int identity)
insert pubs..test default values
BACKUP DATABASE pubs TO DISK = 'C:\p.bak' WITH INIT
insert pubs..test default values
BACKUP DATABASE pubs TO DISK = 'C:\p.bak' WITH DIFFERENTIAL
RESTORE DATABASE pubs FROM DISK = 'C:\p.bak' WITH FILE = 1, NORECOVERY
RESTORE DATABASE pubs FROM DISK = 'C:\p.bak' WITH FILE = 2, RECOVERY
Tibor Karaszi, SQL Server MVP
Archive at:
http://groups.google.com/groups?oi=djq&as_ugroup=microsoft.public.sqlserver
"Duncan McC" <hard@.work.ok> wrote in message
news:MPG.1a163f978c187ade989742@.msnews.microsoft.com...
> Hi, I just can't for the life of me get my dbases back.
> I restore the Full database ok, but I can't apply the differentials at
> all - on *any* database I've got.
> All the details (quite a few pics) are here:
> http://homepages.paradise.net.nz/duncanm4/sqlrestore/
> please, if you can see the problem, I'd sure appreciate knowing about
> it.
> If further details are required, please reply to the NG.
> TIA
> --
> Duncan

Restore Problem

Hi everyone. I am trying to restore a database and continue to get this
error message. Can anyone help?
The backup works fine. The back is from a primary server and the restore is
on the secondary. I wish to restore in standby.
Here is the code and the error msg. any help is appricated.
RESTORE DATABASE HSP_FMH
FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Dev ice.BAK'
WITH
MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_Data.MDF',
MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DAT'
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server.
Server: Msg 3204, Level 16, State 1, Line 1
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection Broken
Hi
Check the SQL Event Log for any events at the time. SQL Server might be
terminating the SPID due to a corrupt backup or other internal error.
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/
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> Hi everyone. I am trying to restore a database and continue to get this
> error message. Can anyone help?
> The backup works fine. The back is from a primary server and the restore
is
> on the secondary. I wish to restore in standby.
> Here is the code and the error msg. any help is appricated.
> RESTORE DATABASE HSP_FMH
> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Dev ice.BAK'
> WITH
> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_Data.MDF',
> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DA
T'
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
Server.
> Connection may have been terminated by the server.
> Server: Msg 3204, Level 16, State 1, Line 1
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Connection Broken
>
|||Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> 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/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
> STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DA
> T'
> Server.
>
|||Yes. We've come across this several times. It has to do with slow disks
and network latency mixed with the use of UNC path network shares.
Normally, we only have trouble with the create files. So, we create our
backups locally and then use UNC to "PULL" the backup to the restore
location, just as you are currently doing.
However, I suspect that it is the creation of your UNDO.DAT file for the
Standby option. I would also use a local drive for that file, say the local
MSSQL\JOBS folder.
Go ahead and use the UNC to locate the backup file, but your MOVE commands,
and UNDO files should be local drive letters.
Good luck. Let us know how it works.
Sincerely,
Anthony Thomas

"Tom" <tbrackney@.kc.rr.com> wrote in message
news:a_KWd.18954$3V3.14955@.twister.rdc-kc.rr.com...
Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> 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/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTran sactionLog\HSP_FMH_UNDO.DA
> T'
> Server.
>

Restore Problem

Hi everyone. I am trying to restore a database and continue to get this
error message. Can anyone help?
The backup works fine. The back is from a primary server and the restore is
on the secondary. I wish to restore in standby.
Here is the code and the error msg. any help is appricated.
RESTORE DATABASE HSP_FMH
FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
WITH
MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_Data.MDF',
MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DAT'
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL Server.
Connection may have been terminated by the server.
Server: Msg 3204, Level 16, State 1, Line 1
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection BrokenHi
Check the SQL Event Log for any events at the time. SQL Server might be
terminating the SPID due to a corrupt backup or other internal error.
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/
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> Hi everyone. I am trying to restore a database and continue to get this
> error message. Can anyone help?
> The backup works fine. The back is from a primary server and the restore
is
> on the secondary. I wish to restore in standby.
> Here is the code and the error msg. any help is appricated.
> RESTORE DATABASE HSP_FMH
> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
> WITH
> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_Data.MDF',
> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DA
T'
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
Server.
> Connection may have been terminated by the server.
> Server: Msg 3204, Level 16, State 1, Line 1
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Connection Broken
>|||Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> 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/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
>> Hi everyone. I am trying to restore a database and continue to get this
>> error message. Can anyone help?
>> The backup works fine. The back is from a primary server and the restore
> is
>> on the secondary. I wish to restore in standby.
>> Here is the code and the error msg. any help is appricated.
>> RESTORE DATABASE HSP_FMH
>> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
>> WITH
>> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_Data.MDF',
>> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_log.LDF',
> STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DA
> T'
>> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
> Server.
>> Connection may have been terminated by the server.
>> Server: Msg 3204, Level 16, State 1, Line 1
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Connection Broken
>>
>|||Yes. We've come across this several times. It has to do with slow disks
and network latency mixed with the use of UNC path network shares.
Normally, we only have trouble with the create files. So, we create our
backups locally and then use UNC to "PULL" the backup to the restore
location, just as you are currently doing.
However, I suspect that it is the creation of your UNDO.DAT file for the
Standby option. I would also use a local drive for that file, say the local
MSSQL\JOBS folder.
Go ahead and use the UNC to locate the backup file, but your MOVE commands,
and UNDO files should be local drive letters.
Good luck. Let us know how it works.
Sincerely,
Anthony Thomas
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:a_KWd.18954$3V3.14955@.twister.rdc-kc.rr.com...
Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> 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/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
>> Hi everyone. I am trying to restore a database and continue to get this
>> error message. Can anyone help?
>> The backup works fine. The back is from a primary server and the restore
> is
>> on the secondary. I wish to restore in standby.
>> Here is the code and the error msg. any help is appricated.
>> RESTORE DATABASE HSP_FMH
>> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
>> WITH
>> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_Data.MDF',
>> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
>> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD02\UndoTransactionLog\HSP_FMH_UNDO.DA
> T'
>> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
> Server.
>> Connection may have been terminated by the server.
>> Server: Msg 3204, Level 16, State 1, Line 1
>> Server: Msg 3013, Level 16, State 1, Line 1
>> RESTORE DATABASE is terminating abnormally.
>> Connection Broken
>>
>

Restore Problem

Hi everyone. I am trying to restore a database and continue to get this
error message. Can anyone help?
The backup works fine. The back is from a primary server and the restore is
on the secondary. I wish to restore in standby.
Here is the code and the error msg. any help is appricated.
RESTORE DATABASE HSP_FMH
FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
WITH
MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_Data.MDF',
MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
Server\MSSQL\Data\HSP_FMH_log.LDF',
STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.DA
T'
[Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL
Server.
Connection may have been terminated by the server.
Server: Msg 3204, Level 16, State 1, Line 1
Server: Msg 3013, Level 16, State 1, Line 1
RESTORE DATABASE is terminating abnormally.
Connection BrokenHi
Check the SQL Event Log for any events at the time. SQL Server might be
terminating the SPID due to a corrupt backup or other internal error.
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/
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> Hi everyone. I am trying to restore a database and continue to get this
> error message. Can anyone help?
> The backup works fine. The back is from a primary server and the restore
is
> on the secondary. I wish to restore in standby.
> Here is the code and the error msg. any help is appricated.
> RESTORE DATABASE HSP_FMH
> FROM DISK='\\Dbprod01\BACKUP\HSP_FMH\HSP_FMH.Backup.Device.BAK'
> WITH
> MOVE 'HSP_FMH_Data' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_Data.MDF',
> MOVE 'HSP_FMH_Log' TO 'G:\Program Files\Microsoft SQL
> Server\MSSQL\Data\HSP_FMH_log.LDF',
>
STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.DA
T'
> [Microsoft][ODBC SQL Server Driver]Unspecified error occurred on SQL[/vbco
l]
Server.[vbcol=seagreen]
> Connection may have been terminated by the server.
> Server: Msg 3204, Level 16, State 1, Line 1
> Server: Msg 3013, Level 16, State 1, Line 1
> RESTORE DATABASE is terminating abnormally.
> Connection Broken
>|||Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> 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/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
> STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.
DA
> T'
> Server.
>|||Yes. We've come across this several times. It has to do with slow disks
and network latency mixed with the use of UNC path network shares.
Normally, we only have trouble with the create files. So, we create our
backups locally and then use UNC to "PULL" the backup to the restore
location, just as you are currently doing.
However, I suspect that it is the creation of your UNDO.DAT file for the
Standby option. I would also use a local drive for that file, say the local
MSSQL\JOBS folder.
Go ahead and use the UNC to locate the backup file, but your MOVE commands,
and UNDO files should be local drive letters.
Good luck. Let us know how it works.
Sincerely,
Anthony Thomas
"Tom" <tbrackney@.kc.rr.com> wrote in message
news:a_KWd.18954$3V3.14955@.twister.rdc-kc.rr.com...
Well, I did that and it is not givning me any information. I have tried this
several times witht he same result. Any other ideas? Anyonw else have an
idea?
"Mike Epprecht (SQL MVP)" <mike@.epprecht.net> wrote in message
news:ehlM$RpIFHA.1096@.tk2msftngp13.phx.gbl...
> Hi
> Check the SQL Event Log for any events at the time. SQL Server might be
> terminating the SPID due to a corrupt backup or other internal error.
> 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/
> "Tom" <tbrackney@.kc.rr.com> wrote in message
> news:amKWd.1085$Zm4.566@.twister.rdc-kc.rr.com...
> is
>
STANDBY='\\Recover01\LogShipping\DBPROD0
2\UndoTransactionLog\HSP_FMH_UNDO.DA
> T'
> Server.
>