DB Backup - Single User Mode

Hello ,

Is it possible/recommended to do SQL server instance backups in Single
user mode ?

Thanks in advance,
atv

Re: DB Backup - Single User Mode

"velu5" wrote in message
news:1193743908.840076.37120@t8g2000prg.googlegroups.com...
> Hello ,
>
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
>
> Thanks in advance,
> atv
>

It's possible. I can't think of a reason to recommend it unless you
especially needed to prevent any changes (for example if you were backing up
in advance of an upgrade or planning to decommission the original database).

--
David Portas

Re: DB Backup - Single User Mode

velu5 (thirumalaivelu@gmail.com) writes:
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?

Just to emphasize what David said: Yes, it's possible, but the only reason
you would do it, is because you have already put the database in single-
user mode. That is, it's works perfectly well to have backups running with
users active.

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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx

Re: DB Backup - Single User Mode

Tx for your response.
I completely understand and accept that there is no need to do a
backup in single user mode, but for one case if the server is already
in single user mode ..
Here is what I found, in SQL 2000 it was possible to do the
backups(in single user mode) while the SQL 2005 server fails to accept
the connection for backup (the same code/binary SQL-DMO statements are
used for both).

Are there any major changes between SQL server 2000 and SQL server
2005 ?

On Oct 31, 3:00 am, Erland Sommarskog wrote:
> velu5 (thirumalaiv...@gmail.com) writes:
> > Is it possible/recommended to doSQLserver instance backups inSingle
> >usermode?
>
> Just to emphasize what David said: Yes, it's possible, but the only reason
> you would do it, is because you have already put the database insingle-usermode. That is, it's works perfectly well to have backups running with
> users active.
>
> --
> Erland Sommarskog,SQLServer MVP, esq...@sommarskog.se
>
> Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
> Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx

Re: DB Backup - Single User Mode

"velu5" wrote in message
news:3686490e-167c-4062-9ecf-2c043615e22a@s19g2000prg.google groups.com...
> Tx for your response.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
>

Hmm, you sure you don't something else already making a connection?

> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?
>


Yes. Many changes. But I don't know any specifically that would cause this
particular issue.



> On Oct 31, 3:00 am, Erland Sommarskog wrote:
>> velu5 (thirumalaiv...@gmail.com) writes:
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>>
>> Just to emphasize what David said: Yes, it's possible, but the only
>> reason
>> you would do it, is because you have already put the database
>> insingle-usermode. That is, it's works perfectly well to have backups
>> running with
>> users active.
>>
>> --
>> Erland Sommarskog,SQLServer MVP, esq...@sommarskog.se
>>
>> Books Online forSQLServer 2005
>> athttp://www.microsoft.com/technet/prodtechnol/sql/2005/down loads/books...
>> Books Online forSQLServer 2000
>> athttp://www.microsoft.com/sql/prodinfo/previousversions/boo ks.mspx
>



--
Greg Moore
SQL Server DBA Consulting Remote and Onsite available!
Email: sql (at) greenms.com http://www.greenms.com/sqlserver.html

Re: DB Backup - Single User Mode

velu5 (thirumalaivelu@gmail.com) writes:
> Tx for your response.
> I completely understand and accept that there is no need to do a
> backup in single user mode, but for one case if the server is already
> in single user mode ..
> Here is what I found, in SQL 2000 it was possible to do the
> backups(in single user mode) while the SQL 2005 server fails to accept
> the connection for backup (the same code/binary SQL-DMO statements are
> used for both).
>
> Are there any major changes between SQL server 2000 and SQL server
> 2005 ?

Well, DMO became dusty and old with SQL 2005, and it's possible
that DMO somehow manages to cause double connections.

I've always stayed away from DMO (and its successor SMO), so I cannot
really say much more.


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

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/prodtechnol/sql/2005/downlo ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinfo/previousversions/books .mspx