DB Backup - Single User Mode

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:[vbcol=seagreen]
> 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.googlegrou ps.com...
>
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:
>

DB Backup - Single User Mode

Hello ,
Is it possible/recommended to do SQL server instance backups in Single
user mode ?
Thanks in advance,
atvHi
No need. BACKUP database command actually does nnot blok others
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743670.860831.196700@.k35g2000prh.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||Dear Velu,
Can I know why you want to do the database backups in single user mode?
Regards
Balaji
"velu5" wrote:
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>|||The SQL Backup command is non-blocking and provides a transactionally
consistent backup without any additional intervention such as single-user
mode.
--
Geoff N. Hiten
Senior SQL Infrastructure Consultant
Microsoft SQL Server MVP
"velu5" <thirumalaivelu@.gmail.com> wrote in message
news:1193743670.860831.196700@.k35g2000prh.googlegroups.com...
> Hello ,
> Is it possible/recommended to do SQL server instance backups in Single
> user mode ?
> Thanks in advance,
> atv
>

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
>

DB Backup - Single User Mode

Hello ,

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

Thanks in advance,
atv"velu5" <thirumalaivelu@.gmail.comwrote in message
news:1193743908.840076.37120@.t8g2000prg.googlegrou ps.com...

Quote:

Originally Posted by

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|||velu5 (thirumalaivelu@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx|||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 <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

velu5 (thirumalaiv...@.gmail.com) writes:

Quote:

Originally Posted by

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/downloads/books...
Books Online forSQLServer 2000 athttp://www.microsoft.com/sql/prodinfo/previousversions/books.mspx

|||"velu5" <thirumalaivelu@.gmail.comwrote in message
news:3686490e-167c-4062-9ecf-2c043615e22a@.s19g2000prg.googlegroups.com...

Quote:

Originally Posted by

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?

Quote:

Originally Posted by

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.

Quote:

Originally Posted by

On Oct 31, 3:00 am, Erland Sommarskog <esq...@.sommarskog.sewrote:

Quote:

Originally Posted by

>velu5 (thirumalaiv...@.gmail.com) writes:

Quote:

Originally Posted by

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/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|||velu5 (thirumalaivelu@.gmail.com) writes:

Quote:

Originally Posted by

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/pr...oads/books.mspx
Books Online for SQL Server 2000 at
http://www.microsoft.com/sql/prodin...ions/books.mspx

Dates Result Set

Hi, All,
I need a single result set that just returns today's date
plus 6 more days. I want to do this without a cursor.
Any ideas?
Thanks!
T
Here is one solution:
select cast(convert(char(8),getdate(),112) as datetime) + i as d
from (
select 0 as i union all select 1 union all select 2 union all select 3
union all select 4 union all select 5 union all select 6
) T
Steve Kass
Drew University
tom sawyer wrote:

>Hi, All,
>I need a single result set that just returns today's date
>plus 6 more days. I want to do this without a cursor.
>Any ideas?
>Thanks!
>T
>