DB Backup - Single User Mode

[velu5]

Hello ,

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

Thanks in advance,
atv

[David Portas]

"velu5" <th************@gmail.comwrote in message
news:11*********************@t8g2000prg.googlegrou ps.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

[Erland Sommarskog]

velu5 (th************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx

[velu5]

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 <[e-mail]:

velu5 ([e-mail]) 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, [e-mail]

Books Online forSQLServer 2005 athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

[Greg D. Moore \(Strider\)]

"velu5" <th************@gmail.comwrote in message
news:36**********************************@s19g2000 prg.googlegroups.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 <[e-mail]:

>velu5 ([e-mail]) 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, [e-mail]

Books Online forSQLServer 2005
athttp://www.microsoft.com/technet/prodtechnol/sql/2005/downloads/books...
Books Online forSQLServer 2000
athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

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

[Erland Sommarskog]

velu5 (th************@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, es****@sommarskog.se

Books Online for SQL Server 2005 at
http://www.microsoft.com/technet/pro...ads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodinf...ons/books.mspx