DB Backup - Single User Mode

Hello ,
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvIf you mean regular backups (using BACKUP DATABASE and BACKUP LOG commands), then no, no need to set
the database to single user mode...
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Tx for your responses.
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:02 am, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
> the database tosingleusermode...
> --
> Tibor Karaszi,SQLServer MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "velu5" <thirumalaiv...@.gmail.com> wrote in message
> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
> > Hello ,
> > Is it possible/recommended to doSQLserver instance backups inSingle
> >usermode?
> > Thanks in advance,
> >atv|||What's New in SQL Server 2005:
http://www.microsoft.com/sql/prodinfo/overview/whats-new-in-sqlserver2005.mspx
http://technet.microsoft.com/tr-tr/library/ms170363(en-us).aspx
--
Ekrem Önsoy
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> 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:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands),
>> then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>|||Hmm, below worked just fine on my machine (2005 with sp2):
USE master
ALTER DATABASE pubs SET SINGLE_USER
BACKUP DATABASE pubs TO DISK = 'C:\pubs.bak'
I can only assume that your DMO code for some reason tries to open a connection to the database on
2005 and which causes the failure. I'd run a Profiler trace to verify what TSQL is submitted.
Assuming this is your own code (using DMO - which is how I read your post), then it might be
difficult to do something... Except for working with the code to see if can stay away from the
database. In the end you might have to construct the backup command and execute is using
.ExecuteImmediately or something similar.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:eb680ec9-d7c5-40c2-bec6-bbbdeada23af@.e23g2000prf.googlegroups.com...
> Tx for your responses.
> 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:02 am, "Tibor Karaszi"
> <tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
>> If you mean regular backups (usingBACKUPDATABASE andBACKUPLOG commands), then no, no need to set
>> the database tosingleusermode...
>> --
>> Tibor Karaszi,SQLServer
>> MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
>> "velu5" <thirumalaiv...@.gmail.com> wrote in message
>> news:1193743910.012534.3610@.v29g2000prd.googlegroups.com...
>> > Hello ,
>> > Is it possible/recommended to doSQLserver instance backups inSingle
>> >usermode?
>> > Thanks in advance,
>> >atv
>