Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/Mats
How about just executing the TSQL BACKUP DATABASE command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The problem is that due to
> connection pooling the file is constantly locked so I can't copy the file or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats
|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?
|||Mats Lycken wrote:
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen
|||Use instances are special. You can get the name of the pipe from the parent instance and then use
that name to connect from your app where you want to execute the backup command. This is explained
in
http://msdn.microsoft.com/sql/expres...xpuserinst.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE [C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MD F] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?

Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/MatsHow about just executing the TSQL BACKUP DATABASE command?
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The problem is that due to
> connection pooling the file is constantly locked so I can't copy the file or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?|||Mats Lycken wrote:
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen|||Use instances are special. You can get the name of the pipe from the parent instance and then use
that name to connect from your app where you want to execute the backup command. This is explained
in
http://msdn.microsoft.com/sql/express/default.aspx?pull=/library/en-us/dnsse/html/sqlexpuserinst.asp
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE [C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
>> How about just executing the TSQL BACKUP DATABASE command?
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\CMSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?

Backing up SQL Server express db that runs in user instance?

Hi,
I want to do a backup of my database that runs in User Instance. The
problem is that due to connection pooling the file is constantly locked
so I can't copy the file or perform a backup in Management Studio.
Does anyone have any tips on how to do it?
I assume I'm not the only one that has these problems.
Thanks in advance
/MatsHow about just executing the TSQL BACKUP DATABASE command?
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:uDBGGEYJGHA.3912@.TK2MSFTNGP10.phx.gbl...
> Hi,
> I want to do a backup of my database that runs in User Instance. The probl
em is that due to
> connection pooling the file is constantly locked so I can't copy the file
or perform a backup in
> Management Studio.
> Does anyone have any tips on how to do it?
> I assume I'm not the only one that has these problems.
> Thanks in advance
> /Mats|||Tibor Karaszi wrote:
> How about just executing the TSQL BACKUP DATABASE command?
>
Thanks for the reply.
I'm still not getting it to work tho, actually I'm not sure how to
select the database to do the backup on.
My database instance is a .mdf file which I want to do a backup of.
How do I write the backup statement to perform the backup?
I've tried this in sqlcmd.
1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO
DISK = 'C:\backup.dat'
2> GO
But it complains about the reference to the database.
How do I tell it that I want to do a backup of that file?|||Mats Lycken wrote:
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to
> select the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?
The correct syntax is
BACKUP DATABASE CMSDB TO DISK = 'C:\backup.dat'
(Assuming your database is named CMSDB)
You can look up the BACKUP DATABASE syntax in Books On Line. Here you
can also find all the various options you can use.
Regards
STeen|||Use instances are special. You can get the name of the pipe from the parent
instance and then use
that name to connect from your app where you want to execute the backup comm
and. This is explained
in
sqlexpuserinst.asp" target="_blank">http://msdn.microsoft.com/sql/expre...expuserinst.asp
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
the database to do the
> backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO DI
SK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?|||The database name is not a string. Try doing a "select name from
sys.databases" in sseutil to see what the database name is. Chances are the
backup command would be:
1> BACKUP DATABASE & #91;C:\Inetpub\wwwroot\SlottsCMS\App_Dat
a\CMSDB.MDF] TO
DISK = 'C:\backup.dat'
This posting is provided "AS IS" with no warranties, and confers no rights.
Use of included script samples are subject to the terms specified at
http://www.microsoft.com/info/cpyright.htm
"Mats Lycken" <MatsL@.newsgroups.nospam> wrote in message
news:%23FBYdFaJGHA.524@.TK2MSFTNGP09.phx.gbl...
> Tibor Karaszi wrote:
> Thanks for the reply.
> I'm still not getting it to work tho, actually I'm not sure how to select
> the database to do the backup on.
> My database instance is a .mdf file which I want to do a backup of.
> How do I write the backup statement to perform the backup?
> I've tried this in sqlcmd.
> 1> BACKUP DATABASE 'C:\Inetpub\wwwroot\SlottsCMS\App_Data\C
MSDB.MDF' TO
> DISK = 'C:\backup.dat'
> 2> GO
> But it complains about the reference to the database.
> How do I tell it that I want to do a backup of that file?sql

Backing up logins info/data

We currently backup the system databases, user databases and logs every nigh
t
To be on the safe side should we also backup the logins data, in case we
have to recreate the master database ?
If so what would be the best approach to backup the logins info/data ?
ThanksHi
You can generate script of Logins by using sp_help_revlogin (search on
internet)
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:62AEF9F6-6A8E-4D9D-BD59-0B5E48A7D408@.microsoft.com...
> We currently backup the system databases, user databases and logs every
> night
> To be on the safe side should we also backup the logins data, in case we
> have to recreate the master database ?
> If so what would be the best approach to backup the logins info/data ?
> Thanks|||Logins are in the master database, so these are included in the backup of th
e master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in mes
sage
news:62AEF9F6-6A8E-4D9D-BD59-0B5E48A7D408@.microsoft.com...
> We currently backup the system databases, user databases and logs every ni
ght
> To be on the safe side should we also backup the logins data, in case we
> have to recreate the master database ?
> If so what would be the best approach to backup the logins info/data ?
> Thankssql

Backing up logins info/data

We currently backup the system databases, user databases and logs every night
To be on the safe side should we also backup the logins data, in case we
have to recreate the master database ?
If so what would be the best approach to backup the logins info/data ?
Thanks
Hi
You can generate script of Logins by using sp_help_revlogin (search on
internet)
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:62AEF9F6-6A8E-4D9D-BD59-0B5E48A7D408@.microsoft.com...
> We currently backup the system databases, user databases and logs every
> night
> To be on the safe side should we also backup the logins data, in case we
> have to recreate the master database ?
> If so what would be the best approach to backup the logins info/data ?
> Thanks
|||Logins are in the master database, so these are included in the backup of the master database.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in message
news:62AEF9F6-6A8E-4D9D-BD59-0B5E48A7D408@.microsoft.com...
> We currently backup the system databases, user databases and logs every night
> To be on the safe side should we also backup the logins data, in case we
> have to recreate the master database ?
> If so what would be the best approach to backup the logins info/data ?
> Thanks

Backing up logins info/data

We currently backup the system databases, user databases and logs every night
To be on the safe side should we also backup the logins data, in case we
have to recreate the master database ?
If so what would be the best approach to backup the logins info/data ?
ThanksHi
You can generate script of Logins by using sp_help_revlogin (search on
internet)
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in
message news:62AEF9F6-6A8E-4D9D-BD59-0B5E48A7D408@.microsoft.com...
> We currently backup the system databases, user databases and logs every
> night
> To be on the safe side should we also backup the logins data, in case we
> have to recreate the master database ?
> If so what would be the best approach to backup the logins info/data ?
> Thanks|||Logins are in the master database, so these are included in the backup of the master database.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"SQL Server newbie" <SQLServernewbie@.discussions.microsoft.com> wrote in message
news:62AEF9F6-6A8E-4D9D-BD59-0B5E48A7D408@.microsoft.com...
> We currently backup the system databases, user databases and logs every night
> To be on the safe side should we also backup the logins data, in case we
> have to recreate the master database ?
> If so what would be the best approach to backup the logins info/data ?
> Thanks

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations
, and conditionally
handling a mirrored database *inside* a backup task is not available. Consid
er wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL tas
k before each which
check the mirroring catalog views and if a mirror, moves to the next databas
e instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You
can still use the maint
plan to remove old backup files. (Google for how to generate the backup comm
and with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> This is what you would have to do. Maint plans are for simpler installatio
ns, and conditionally
> handling a mirrored database *inside* a backup task is not available. Cons
ider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL t
ask before each which
> check the mirroring catalog views and if a mirror, moves to the next datab
ase instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. Y
ou can still use the maint
> plan to remove old backup files. (Google for how to generate the backup co
mmand with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asph
ttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing up databases in a mirror configuration

I have a user database maintance plan with subplans for full and
transaction log backups deployed to both the Principal and Mirror
servers. Each subplan has its own schedule.
Obviously, the principal server executes a successfully backup, while
the mirror copy fails. This generates errors that are picked up by
MOM and also from the jobs themselves.
In order to eliminate these errors, I emplyed a method that prevents
the jobs from running if the server is hosting the mirror copy (note
that all the databases on the server are mirrored):
1) Remove the schedule from the job that runs the SSIS package - job
name 'User Databases.Full Database Backup'
2) Create a new job called 'User Databases.Full Database Backup Status
Check' that checks for the read/write and online status of the
databases and executes the job in step one if they are online
3) Add a schedule to the job 'User Databases.Full Database Backup
Status Check' with the same details as that removed in step 1
I have a big problem with this approach: if any of the databases are
the mirror, then the entire backup subplan is not run - even if some
of the databases are the principal on the same server - it's an all or
nothing approach since the databases checked in step 2 may not
necessarily match the list of databases defined in the subplan.
Clearly, I'd like the checking step to only be affected by the
databases defined in the subplan, but I cannot think of a way of
interrogating the subplan details using SQL code to determine this.
Ideally, I would like to edit the maintenance plan's list of databases
to only include databases that are the principal on that server and to
do it each time the job is executed - maybe using the two job approach
as described above.
I would like some advice on a way to do this or whether I am barking
up the wrong tree and should try a different angle - perhaps defining
my own backup process (I really want to avoid doing this). Clearly,
if the maintenance plan could detect the fact that the database is a
mirror copy and just skip it, the whole use goes away, but it does
not.
Thanks> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this).
This is what you would have to do. Maint plans are for simpler installations, and conditionally
handling a mirrored database *inside* a backup task is not available. Consider wishing this at
connect.microsoft.com.
Another options is to have one backup task per database, and have a TSQL task before each which
check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
but should be doable.
Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
the file name and you will find examples...)
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://sqlblog.com/blogs/tibor_karaszi
"Viracochas" <warmtea@.hotmail.com> wrote in message
news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
>I have a user database maintance plan with subplans for full and
> transaction log backups deployed to both the Principal and Mirror
> servers. Each subplan has its own schedule.
> Obviously, the principal server executes a successfully backup, while
> the mirror copy fails. This generates errors that are picked up by
> MOM and also from the jobs themselves.
> In order to eliminate these errors, I emplyed a method that prevents
> the jobs from running if the server is hosting the mirror copy (note
> that all the databases on the server are mirrored):
> 1) Remove the schedule from the job that runs the SSIS package - job
> name 'User Databases.Full Database Backup'
> 2) Create a new job called 'User Databases.Full Database Backup Status
> Check' that checks for the read/write and online status of the
> databases and executes the job in step one if they are online
> 3) Add a schedule to the job 'User Databases.Full Database Backup
> Status Check' with the same details as that removed in step 1
> I have a big problem with this approach: if any of the databases are
> the mirror, then the entire backup subplan is not run - even if some
> of the databases are the principal on the same server - it's an all or
> nothing approach since the databases checked in step 2 may not
> necessarily match the list of databases defined in the subplan.
> Clearly, I'd like the checking step to only be affected by the
> databases defined in the subplan, but I cannot think of a way of
> interrogating the subplan details using SQL code to determine this.
> Ideally, I would like to edit the maintenance plan's list of databases
> to only include databases that are the principal on that server and to
> do it each time the job is executed - maybe using the two job approach
> as described above.
> I would like some advice on a way to do this or whether I am barking
> up the wrong tree and should try a different angle - perhaps defining
> my own backup process (I really want to avoid doing this). Clearly,
> if the maintenance plan could detect the fact that the database is a
> mirror copy and just skip it, the whole use goes away, but it does
> not.
> Thanks
>|||On 21 May, 13:43, "Tibor Karaszi"
<tibor_please.no.email_kara...@.hotmail.nomail.com> wrote:
> > I would like some advice on a way to do this or whether I am barking
> > up the wrong tree and should try a different angle - perhaps defining
> > my own backup process (I really want to avoid doing this).
> This is what you would have to do. Maint plans are for simpler installations, and conditionally
> handling a mirrored database *inside* a backup task is not available. Consider wishing this at
> connect.microsoft.com.
> Another options is to have one backup task per database, and have a TSQL task before each which
> check the mirroring catalog views and if a mirror, moves to the next database instead. A bit messy,
> but should be doable.
> Having your own scripts for performing a backup isn't difficult, though. You can still use the maint
> plan to remove old backup files. (Google for how to generate the backup command with a time stamp in
> the file name and you will find examples...)
> --
> Tibor Karaszi, SQL Server MVPhttp://www.karaszi.com/sqlserver/default.asphttp://sqlblog.com/blogs/tibor_karaszi
> "Viracochas" <warm...@.hotmail.com> wrote in message
> news:1179742488.051578.275990@.b40g2000prd.googlegroups.com...
Thanks for the feedback. I feared there was no easy way, though it
won't be too difficult to set it up.

Backing up 2 databases..

Hi,
Is it ok to backup 2 databases in one file. Our vb.net app uses 2 databases (created on MSDE) and we want to prompt user to do backup during uninstallation but do not want to ask them twice (for each database). Then later on we want to be able to restor
e from the backup files too.
Any suggestions.
Thanks
hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:9A3740A3-E7C2-44B6-B717-29682B262BD5@.microsoft.com...
> Hi,
> Is it ok to backup 2 databases in one file. Our vb.net app uses 2
databases (created on MSDE) and we want to prompt user to do backup during
uninstallation but do not want to ask them twice (for each database). Then
later on we want to be able to restore from the backup files too.
> Any suggestions.
> Thanks
each backup file can contain more than 1 single database backup set, even
from multiple databases...
you have to specify the syntax as
BACKUP DATABASE databasename
TO DISK = N'c:\..\phisical path.bck'
WITH INIT
for the very first backup, in order to insure it overwrites any existing
one, while you have to omit the WITH INIT clause for additional database
backup set you want to include in the same backup device..
in order to restore from it, you have to use the syntax as
RESTORE DATABASE databasename
FROM DISK = N'c:\..\phisical path.bck'
WITH FILE = n
where n is the backup set file position within the backup device...
you can see the whole content of your backup device executing
RESTORE HEADERONLY
FROM DISK = N'c:\..\phisical path.bck'
hth
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.7.0 - DbaMgr ver 0.53.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||Thanks Andrea,
But is it regarded as good programming practise or not..
dev
|||There is nothing inherently "bad" with having several backups in one backup device.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"dev" <anonymous@.discussions.microsoft.com> wrote in message
news:D80398A4-4B44-4365-8C66-2F257114C237@.microsoft.com...
> Thanks Andrea,
> But is it regarded as good programming practise or not..
> dev

Backend generate and save the report to local drive?

Hi,
Any ideas on how to automatically generate and save the report to local
server drive backend when user click on certain link on the application?
The user do not have to render and display the report in frontend or click
on save button when prompted? The system should not prompt user for any
action instead.
Thanks in advance for any suggestion.Hi,
you can call the WebService of reporting Services (with optional
additional parameters, get the byte[] from the render method and save
this in a file on your network share of choice. That can all happen
behind the scenes and in addition if you want to asynchronously.
HTH, Jens K. Suessmeyer.
--
http://www.sqlserver2005.de
--

Back Button : Going back to parent report from the child report in Report Manager screen

Hi,
In my report manager screen, i want to place the back button option by which user can navigate to the parent report?
For example, I am viewing the orders screen(parent report), in that screen i am clicking the orderid column, it navigates to another report(child report), from which i want to go back to parent report..
I am not using the reportviewer control...
i am using only the report manager....
is it possible?
how do we achieve this?

thanks in advance

Have you figured a way to do this? I am trying to do the same thing.

Thanks,

|||It is possible using Jump To Report functionality

Back Button : Going back to parent report from the child report in Report Manager screen

Hi,
In my report manager screen, i want to place the back button option by which user can navigate to the parent report?
For example, I am viewing the orders screen(parent report), in that screen i am clicking the orderid column, it navigates to another report(child report), from which i want to go back to parent report..
I am not using the reportviewer control...
i am using only the report manager....
is it possible?
how do we achieve this?

thanks in advance

Have you figured a way to do this? I am trying to do the same thing.

Thanks,

|||It is possible using Jump To Report functionality

Avoiding the user login prompt for a reporting services report

Most of the stuff I have read on this is from 2004. I am wondering if
there is a easier fix to this problem.do you use NT authentication or basic authentication?
using NTLM the users can add the web site to the intranet list then the
current login/password will be used. (IE only) so there is no prompt for the
user.
basic authentication allways ask the user.
anonymous access... its anonymous, so no login but no security.
Enterprise edition of RS allow you to create your own authentication system
(like a form based authentication)
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
> Most of the stuff I have read on this is from 2004. I am wondering if
> there is a easier fix to this problem.
>|||Also, just in case you have a weird problem. I had a case where the issue
was that the server (it was a test server) was using DHCP (dynamic tcpip
address instead of fixed). When I went to a fixed IP address the probelm
went away. Obscure but thought I would mention it.
Bruce Loehle-Conger
MVP SQL Server Reporting Services
"Jéjé" <willgart@.BBBhotmailAAA.com> wrote in message
news:u7ALlbzCGHA.2596@.TK2MSFTNGP10.phx.gbl...
> do you use NT authentication or basic authentication?
> using NTLM the users can add the web site to the intranet list then the
> current login/password will be used. (IE only) so there is no prompt for
> the user.
> basic authentication allways ask the user.
> anonymous access... its anonymous, so no login but no security.
> Enterprise edition of RS allow you to create your own authentication
> system (like a form based authentication)
> "Tim" <TimGoard@.gmail.com> wrote in message
> news:1135719831.735427.54260@.f14g2000cwb.googlegroups.com...
>> Most of the stuff I have read on this is from 2004. I am wondering if
>> there is a easier fix to this problem.
>|||I am new to web programming. I thought I needed to say that.
In my ASP.net app authentication is set to Windows. Is that the
authentication you are talking about? I set it to none and it stills
comes up with the login prompt.|||Could you open the following file:
C:\Program Files\Microsoft SQL Server\MSSQL.3\Reporting
Services\ReportServer\rsreportserver.config
Check the tag <UrlRoot> to see if your servername is entered correctly (e.g.
not set to localhost)
Hth,
Pieter
"Tim" <TimGoard@.gmail.com> wrote in message
news:1135792805.115690.18910@.g44g2000cwa.googlegroups.com...
>I am new to web programming. I thought I needed to say that.
> In my ASP.net app authentication is set to Windows. Is that the
> authentication you are talking about? I set it to none and it stills
> comes up with the login prompt.
>|||The <UrlRoot> is entered correctly.
The authentication tag in this file is:
<Authentication>
<Extension Name="Windows"
Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
Microsoft.ReportingServices.Authorization"/>
</Authentication>
I still have no idea how to overcome the windows login prompt for
showing the report in my asp.net app.|||Does your url to your reporting server include your server's ip address? If
it does, you'll have to enter the address into the end users local Intranet
security site.
From the end users machine, open IE.
Click on Tools>Internet Options
Click on the Security Tab then Local Intranet
Click on Sites
Click on Advanced
Enter in the url of your reporting server and click add.
Save your changes and try to access the site.
HTH
"Tim" wrote:
> The <UrlRoot> is entered correctly.
> The authentication tag in this file is:
> <Authentication>
> <Extension Name="Windows"
> Type="Microsoft.ReportingServices.Authentication.WindowsAuthentication,
> Microsoft.ReportingServices.Authorization"/>
> </Authentication>
> I still have no idea how to overcome the windows login prompt for
> showing the report in my asp.net app.
>|||Where are you running the report off from? Your local Machine? or some
other machine?

avoid to push the password through OLE to OLEDB provider

Hi!
I use OLEDB to access SQL server. During connect I put login information to
OLEDB provider(User and password) in a "connection string".
Can I do this in a more secured (encrypted) form?
Can I use other authentication mode (for instance kerberos) with MS SQL 2000
.
Thank for all idea,
ImreYou can use NT authentication.
Rand
This posting is provided "as is" with no warranties and confers no rights.|||Hi Imre,
You can use Kerberos if the machines are part of a Windows 2000 or 2003
domain. You'll need to set the SPN for SQL.
See Books online for SetSPN syntax:
To encrypt the traffic, you can enable Protocol Encryption.
316898 HOW TO: Enable SSL Encryption for SQL Server 2000 with Microsoft
http://support.microsoft.com/?id=316898
276553 HOW TO: Enable SSL Encryption for SQL Server 2000 with Certificate
Server
http://support.microsoft.com/?id=276553
Thanks,
Kevin McDonnell
Microsoft Corporation
This posting is provided AS IS with no warranties, and confers no rights.

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that select query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user USER created in the server, if I try to make a sql query (select * from tablename) the sql panel writes "select * from USER.tablename" and this causes a error when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?
Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

Avoid automatically add of user name to the query

My db bellows to user USER and when I attach it to a server with the user US
ER created in the server, if I try to make a sql query (select * from tablen
ame) the sql panel writes "select * from USER.tablename" and this causes a e
rror when I try make that s
elect query from a application.
How can I avoid the part USER. in the query?Jorge
How did the application connect to the server? What is a user name?
Also , look at sp_changedbowner system stored procedure in the BOL
"Jorge M." <Jorge M.@.discussions.microsoft.com> wrote in message
news:E507C6FF-A60A-416E-AF57-B70BE5536EB6@.microsoft.com...
> My db bellows to user USER and when I attach it to a server with the user
USER created in the server, if I try to make a sql query (select * from
tablename) the sql panel writes "select * from USER.tablename" and this
causes a error when I try make that select query from a application.
> How can I avoid the part USER. in the query?

available values and free text

Hi,
I have a parameter that get his available values from a query.

I want that user will have the option to select from the values list OR to type in his own value.

Is there a way to do that?

Thanks,YOu will have to use two parameters and compose your query to accept either the one or the other value.

HTH; Jens K. Suessmeyer.

http://www.sqlserver2005.de|||Hello Jens,

I was afraid of that :(
I was sure at first that there is a simple solution other then that.

Now it mean that I need to check both parameters and if the user fill them both I need to decide which one to choose and so on...

Any way, It seems that its the only way.

Thanks for your replay,
Roy.

available values and free text

Hi,
I have a parameter that get his available values from a query.

I want that user will have the option to select from the values list OR to type in his own value.

Is there a way to do that?

Thanks,

Hello my friend,

I am assuming that your rdl report is working and the user can select from a drop down list and you just want extra functionality to allow the user to choose their own, which may not be on the list. A way to do this would be to use 2 other parameters. One will be the user's chosen value and the other will be a flag to say whether to use the selected drop down value or what the user entered

SELECT * FROM MyTable WHERE (MyID = @.DropDownListValue AND @.UsingDropDownList = 1)

OR (MyID = @.UserValue AND @.UsingDropDownList = 0)

Kind regards

Scotty

|||

Hi Scotty,

Thank for your replay!

I also thought about useing two parameters but it looked like a really poor idea.

After seeing your answer I got the feeling That Its the only way :(

The problem is that I already have to much parameters and helf the screen is with parameters textBox (since it organized them in two column and I can't do any thing about that),

Thanks for your help,

Roy.

|||

Hello again my friend,

You do not need to use the default parameter screen that the report generates. When you submit the report after choosing your parameters, observe the url it generates. It concatenates your parameters on to the URL. Instead of using the default report screen, you can make your own user-friendly web page where they choose the parameters and then just generate the URL based on their choices and redirect them.

Kind regards

Scotty

available values and free text

Hi,
I have a parameter that get his available values from a query.
I want that user will have the option to select from the values list
OR to type in his own value.
Is there a way to do that?
Thanks,You can have either type in values or drop down you cant have both.
Amarnath
"nicknack" wrote:
> Hi,
> I have a parameter that get his available values from a query.
> I want that user will have the option to select from the values list
> OR to type in his own value.
> Is there a way to do that?
> Thanks,
>|||Yap, Thats what i found out.
Thanks
Amarnath =D7=9B=D7=AA=D7=91:
> You can have either type in values or drop down you cant have both.
> Amarnath
> "nicknack" wrote:
> > Hi,
> > I have a parameter that get his available values from a query.
> >
> > I want that user will have the option to select from the values list
> > OR to type in his own value.
> >
> > Is there a way to do that?
> >
> > Thanks,
> >
> >