Backup to Network Drive: including

Showing posts with label including. Show all posts

Monday, March 19, 2012

Bad results from my MDX Adventure Works query

I have an Adventure Works MDX query that I want to return all the male employees and their total reseller-sales (including the people below them)

Select [Measures].[Reseller Sales-Sales Amount] on Columns,
non empty
Exists(
[Employee].[Employees].AllMembers
,[Employee].[Gender].&[M]
) on Rows
from [Analysis Services Tutorial]

which, when run, returns

Reseller Sales-Sales Amount
All Employees $80,450,596.98
Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Amy E. Alberts $15,535,946.26
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38
Syed E. Abbas $172,524.45

so there are three problems with this result, and I think they all have 1 solution. First, I don't want the 'All Employees' row. Second, there is a female in my results, seemingly because this female is the supervisor of some of the males. I asked for no females in my query. Third, Syed shows up twice, because he is a supervisor and a salesman himself. What I want are these results..

Ken J. Sánchez $80,450,596.98
Brian S. Welcker $80,450,596.98
Ranjit R. Varkey Chudukatil $4,509,888.93
Stephen Y. Jiang $63,320,315.35
David R. Campbell $3,729,945.35
Garrett R. Vargas $3,609,447.22
Jos Edvaldo. Saraiva $5,926,418.36
Michael G. Blythe $9,293,903.01
Shu K. Ito $6,427,005.56
Stephen Y. Jiang $1,092,123.86
Tete A. Mensa-Annan $2,312,545.69
Tsvi Michael. Reiter $7,171,012.75
Syed E. Abbas $1,594,335.38

Notice that there are no "All Employees", the woman is gone, and Syed is only a supervisor, and not an underling also. What MDX query would give me these results?

Thanks,

Todd Wilder

Referring to my response to your earlier post, this query seems to return the results you want - except for the order:

Select [Measures].[Reseller Sales Amount] on Columns,
non empty Generate(exists([Employee].[Employee].[Employee],
[Employee].[Gender].&[M]),
{LinkMember([Employee].[Employee].CurrentMember,
[Employee].[Employees])}) on Rows
from [Adventure Works]

|||Your queries dont seem to return anything on my cubes - your measure is named slightly different then mine and I don't have any tuples like [Employee].[Employee].[Employee]. Where did your cube come from?|||

Are you aware of the Adventure Works standard sample cube?

http://msdn2.microsoft.com/en-us/library/ms143804.aspx

SQL Server 2005 Books Online

Running Setup to Install AdventureWorks Sample Databases and Samples

Updated: 17 July 2006

The AdventureWorks (OLTP), AdventureWorksDW (data warehouse), and Adventure Works DW (analysis services) sample databases, as well as the companion samples, are not installed by default in SQL Server 2005. You can download these from SQL Server 2005 Samples and Sample Databases at the Microsoft Download Center, or you can use the following procedures to install the sample databases and samples during or after setup. Additional instructions for deploying the Adventure Works DW analysis services project are also provided.

...

Thursday, March 8, 2012

Backups of tran log not working

I have my maintenance plan set up to backup to disk each day, including the
tran log.
I am getting an error on the Tran log backup, however.
It doesn't tell me why it failed, just that it failed.
SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
1).
For more information, see Help and Support Center at
http://go.microsoft.com/fwlink/events.asp.
The help center is no help.
The other 3 parts of the maintenance plan seem to go fine.
Also, there is no log in the Sql Logs, but there is in the Event log (where
I got this). There is plenty of disk space, so that is not the problem.
Am I missing something here?
Thanks,
TomSpecify a report file for the maint plan and check that for specific error messages.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>I have my maintenance plan set up to backup to disk each day, including the tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan 'DB Maintenance
> Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status: Failed - Invoked on: 2005-11-09 09:24:24 -
> Message: The job failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to run was
> step 1 (Step 1).
> For more information, see Help and Support Center at http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where I got this). There is
> plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:edxoSaV5FHA.1248@.TK2MSFTNGP14.phx.gbl...
> Specify a report file for the maint plan and check that for specific error
> messages.
I did that and got to following:
*****************************************************************************************************
Microsoft (R) SQLMaint Utility (Unicode), Version Logged on to SQL Server
'VENUS' as 'FTSOLUTIONS0\servicesql' (trusted)
Starting maintenance plan 'All Databases Backup' on 11/9/2005 10:33:23 AM
Backup can not be performed on database 'FTS'. This sub task is ignored.
Backup can not be performed on database 'master'. This sub task is ignored.
[3] Database model: Transaction Log Backup...
Destination: [d:\Microsoft SQL
Server\MSSQL\BACKUP\model_tlog_200511091033.TRN]
** Execution Time: 0 hrs, 0 mins, 1 secs **
[4] Database model: Verifying Backup...
** Execution Time: 0 hrs, 0 mins, 1 secs **
Backup can not be performed on database 'msdb'. This sub task is ignored.
End of maintenance plan 'All Databases Backup' on 11/9/2005 10:33:24 AM
SQLMAINT.EXE Process Exit Code: 1 (Failed)
*****************************************************************************************************
So it works for some and not others.
And why can't it backup master or msdb - they have log files
Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:ubIr1UV5FHA.3876@.TK2MSFTNGP09.phx.gbl...
>>I have my maintenance plan set up to backup to disk each day, including
>>the tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) -
>> Status: Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job
>> failed. The Job was invoked by User FTSOLUTIONS0\tfs. The last step to
>> run was step 1 (Step 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where I got this). There is plenty of disk space, so that is not the
>> problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>|||I think that master & msdb are simple recovery model, hence cannot have
tran. log backups. Just remove this portion of your maintenence plan.|||Ok.
But why did FTS not backup?
Tom
"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>I think that master & msdb are simple recovery model, hence cannot have
> tran. log backups. Just remove this portion of your maintenence plan.
>|||Most log backups fail because the database is in simple recovery mode. Check
that first. Second, verify the database has had a full backup. Finally, I
assume we're not talking about the msdb and master--but just to be safe,
it's not one of those two, correct?
"tshad" wrote:
> I have my maintenance plan set up to backup to disk each day, including the
> tran log.
> I am getting an error on the Tran log backup, however.
> It doesn't tell me why it failed, just that it failed.
> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance Plan
> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The Job
> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1 (Step
> 1).
> For more information, see Help and Support Center at
> http://go.microsoft.com/fwlink/events.asp.
> The help center is no help.
> The other 3 parts of the maintenance plan seem to go fine.
> Also, there is no log in the Sql Logs, but there is in the Event log (where
> I got this). There is plenty of disk space, so that is not the problem.
> Am I missing something here?
> Thanks,
> Tom
>
>|||>From his report, it looks like we are talking about master & msdb. As
well as one other called FTS. Like fnguy said - check to see if if FTS
is simple recovery model. If it is, you can't back up the tran. log.
Backup can not be performed on database 'FTS'. This sub task is
ignored.
Backup can not be performed on database 'master'. This sub task is
ignored.
Backup can not be performed on database 'msdb'. This sub task is
ignored.|||"fnguy" <fnguy@.discussions.microsoft.com> wrote in message
news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> Most log backups fail because the database is in simple recovery mode.
> Check
> that first. Second, verify the database has had a full backup. Finally,
> I
> assume we're not talking about the msdb and master--but just to be safe,
> it's not one of those two, correct?
No, it isn't. It's one of our normal databases.
But you mentioned, the database having a full backup.
Do you need to do the Tran backup first?
Thanks,
Tom
>
> "tshad" wrote:
>> I have my maintenance plan set up to backup to disk each day, including
>> the
>> tran log.
>> I am getting an error on the Tran log backup, however.
>> It doesn't tell me why it failed, just that it failed.
>> SQL Server Scheduled Job 'Transaction Log Backup Job for DB Maintenance
>> Plan
>> 'DB Maintenance Plan1'' (0x6B83341EBF11A04CB613509A341733CB) - Status:
>> Failed - Invoked on: 2005-11-09 09:24:24 - Message: The job failed. The
>> Job
>> was invoked by User FTSOLUTIONS0\tfs. The last step to run was step 1
>> (Step
>> 1).
>> For more information, see Help and Support Center at
>> http://go.microsoft.com/fwlink/events.asp.
>> The help center is no help.
>> The other 3 parts of the maintenance plan seem to go fine.
>> Also, there is no log in the Sql Logs, but there is in the Event log
>> (where
>> I got this). There is plenty of disk space, so that is not the problem.
>> Am I missing something here?
>> Thanks,
>> Tom
>>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >From his report, it looks like we are talking about master & msdb. As
> well as one other called FTS. Like fnguy said - check to see if if FTS
> is simple recovery model. If it is, you can't back up the tran. log.
> Backup can not be performed on database 'FTS'. This sub task is
> ignored.
> Backup can not be performed on database 'master'. This sub task is
> ignored.
> Backup can not be performed on database 'msdb'. This sub task is
> ignored.
>
How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
Tom|||Check the recovery model of your database.
If the FTS database is in Simple recovery model, then log backups make no
sense.
> Also, there are files that are unreadable there: ERRORLOG, ERRORLOG.1,
> SQLAGENT.OUT, SQLAGENT.1. What are they and how do you read them?
>
ERRORLOG.n are the SQL error logs. They are simple text files. You should
be able to open them in notepad unless there is some strange protection
applied. As an administrator you should certainly be agle to read them.
Kevin Farlee|||As Kevin suggested, it is probably in simple recovery mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%23LFHRAW5FHA.2676@.TK2MSFTNGP15.phx.gbl...
> Ok.
> But why did FTS not backup?
> Tom
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131562250.817157.160290@.g43g2000cwa.googlegroups.com...
>>I think that master & msdb are simple recovery model, hence cannot have
>> tran. log backups. Just remove this portion of your maintenence plan.
>|||> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
sp_helpdb, for instance.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> Tom
>|||Pls execute following SP from Query Analyser and post it's output:
sp_helpdb 'fts'
"tshad" wrote:
> "fnguy" <fnguy@.discussions.microsoft.com> wrote in message
> news:90AC17EA-75F3-45C0-BA2D-C5CB3600AF4C@.microsoft.com...
> > Most log backups fail because the database is in simple recovery mode.
> > Check
> > that first. Second, verify the database has had a full backup. Finally,
> > I
> > assume we're not talking about the msdb and master--but just to be safe,
> > it's not one of those two, correct?
> No, it isn't. It's one of our normal databases.
> But you mentioned, the database having a full backup.
> Do you need to do the Tran backup first?
> Thanks,
> Tom|||How do you check to see if it is a simple recovery model? I assume it
isn't, but I am not sure.
--
Also - open enterprise manager, right click the database in question,
and choose properties. Go to options tab. See what exists under the
Recovery model section. If it's simple, then all you can do is full
backups. If it's full, then you can do logs.
Tibor Karaszi wrote:
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> sp_helpdb, for instance.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
> >
> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
> >> >From his report, it looks like we are talking about master & msdb. As
> >> well as one other called FTS. Like fnguy said - check to see if if FTS
> >> is simple recovery model. If it is, you can't back up the tran. log.
> >>
> >> Backup can not be performed on database 'FTS'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'master'. This sub task is
> >> ignored.
> >> Backup can not be performed on database 'msdb'. This sub task is
> >> ignored.
> >>
> >
> > How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
> >
> > Tom
> >|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That does show it as
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
> sp_helpdb, for instance.
That was it.
Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER,
Recovery=SIMPLE, Version=539, Collation=SQL_Latin1_General_CP1_CI_AS,
SQLSortOrder=52, IsTornPageDetectionEnabled, IsAutoCreateStatistics,
IsAutoUpdateStatistics, IsFullTextEnabled
But how did it get that way? What did I do to make it that way? My other
databases aren't that way.
Can I change that?
Thanks,
Tom
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
>
> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it
>> isn't, but I am not sure.
>> Tom
>|||Yes, you can change it using the ALTER DATABASE command.
How it got set to simple in the first place? I can't answer that, except saying that someone or some
program did it.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"tshad" <tscheiderich@.ftsolutions.com> wrote in message news:uP89yUh5FHA.140@.TK2MSFTNGP10.phx.gbl...
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in message
> news:ejxz3mc5FHA.2364@.TK2MSFTNGP12.phx.gbl...
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> sp_helpdb, for instance.
> That was it.
> Status=ONLINE, Updateability=READ_WRITE, UserAccess=MULTI_USER, Recovery=SIMPLE, Version=539,
> Collation=SQL_Latin1_General_CP1_CI_AS, SQLSortOrder=52, IsTornPageDetectionEnabled,
> IsAutoCreateStatistics, IsAutoUpdateStatistics, IsFullTextEnabled
> But how did it get that way? What did I do to make it that way? My other databases aren't that
> way.
> Can I change that?
> Thanks,
> Tom
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >From his report, it looks like we are talking about master & msdb. As
>> well as one other called FTS. Like fnguy said - check to see if if FTS
>> is simple recovery model. If it is, you can't back up the tran. log.
>> Backup can not be performed on database 'FTS'. This sub task is
>> ignored.
>> Backup can not be performed on database 'master'. This sub task is
>> ignored.
>> Backup can not be performed on database 'msdb'. This sub task is
>> ignored.
>>
>> How do you check to see if it is a simple recovery model? I assume it isn't, but I am not sure.
>> Tom
>>
>|||"Corey Bunch" <unc27932@.yahoo.com> wrote in message
news:1131630158.667899.30870@.g44g2000cwa.googlegroups.com...
> How do you check to see if it is a simple recovery model? I assume it
> isn't, but I am not sure.
> --
> Also - open enterprise manager, right click the database in question,
> and choose properties. Go to options tab. See what exists under the
> Recovery model section. If it's simple, then all you can do is full
> backups. If it's full, then you can do logs.
>
Can I just change that there?
Why would you want a simple model?
It does have a tran log defined for it and it is the only one of 5 databases
I have that isn't full. So what causes it to be simple I just created
another database, it has no question about it and it automatically made it
simple. I originally created FTS in this way - so what would cause it to be
simple - I know I didn't do it manually.
Thanks,
Tom
>
> Tibor Karaszi wrote:
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> sp_helpdb, for instance.
>> --
>> Tibor Karaszi, SQL Server MVP
>> http://www.karaszi.com/sqlserver/default.asp
>> http://www.solidqualitylearning.com/
>>
>> "tshad" <tscheiderich@.ftsolutions.com> wrote in message
>> news:e%23Y3D3X5FHA.1536@.TK2MSFTNGP12.phx.gbl...
>> >
>> > "Corey Bunch" <unc27932@.yahoo.com> wrote in message
>> > news:1131567831.621737.163390@.g43g2000cwa.googlegroups.com...
>> >> >From his report, it looks like we are talking about master & msdb.
>> >> >As
>> >> well as one other called FTS. Like fnguy said - check to see if if
>> >> FTS
>> >> is simple recovery model. If it is, you can't back up the tran. log.
>> >>
>> >> Backup can not be performed on database 'FTS'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'master'. This sub task is
>> >> ignored.
>> >> Backup can not be performed on database 'msdb'. This sub task is
>> >> ignored.
>> >>
>> >
>> > How do you check to see if it is a simple recovery model? I assume it
>> > isn't, but I am not sure.
>> >
>> > Tom
>> >
>|||Yes - you can just change that there using the drop down. Not sure why
it's full instead of simple. Simple model = less maintenance, but also
= less recoverability. See below links.
http://searchsqlserver.techtarget.com/tip/1,289483,sid87_gci1069109,00.html
http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/ad_bkprst_4l83.asp
>From microsoft...
Simple Recovery Model
This recovery model facilitates the maintenance of a database by making
the transaction log virtually maintenance free. There are limitations
placed on the recoverability of a database if this recovery model is
used.

Friday, February 24, 2012

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

I had done something familiar to what you are looking for but i only backed up data. I used bcp to copy data to a file and read data from the file. I am not sure as to how u can back up the schema. If you need more details on the bcp code i can email it to you. Please give me ur email address.

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

now, regarding the other problem, scripting the data into, i use BCP & loop through all the table & merge all the files generated into one.
now the problem are
1) i am not able to distinguish between the data for table, i mean the data to which that table belongs to.
2)how can i restore that file?

Regards,
Thanks.
Gurpreet S. Gill|||Can you create individual files for each table using bcp or do you have to create one file. Also you can name the file name same as the table name. This will help you distinguish the tables. While reading the data back u need to specify the file name in the bcp command to open the particular file.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Backup/Restore Stored Procedure

Hi there

I just want to create Stored Procedure which will do following thing

i) Create a text file, which will have a Schema Script(including all the constraint & others, for a database(by passing Parameter)

ii) the same file must also have, all the data from the selected database tables,in again plain text format (could be CSV, or Tab SV, etc...)

I mean to say, the file should be act as the full backup of database, in plain text.

The same need to be done in the reverse order,this SP will do following things

i)Open the file created by first SP, create new database, with same name as backup in SP one

ii) create the tables, by reading the Schema script.

iii) Insert all the data in corrsponding tables.

For EX:

--creating backup

sp_CreateDatbaseBackup('DatabaseName')

--Restoring

sp_RestoreDatabaseBackup('FileName')

I know abou the Backup & restore in SQL Server 2005, but they gives me backup as .bkp file which is not editable. I too cant use the SSIS(just only SPs)

I want an editable full Backup and Restore.

Please help me in creating these SPs or any other idea, solution ?

any other FREE third party tool which will make the Full database backup & restore in one go & in plain text file.

Regards,

Thanks.

Gurpreet S. Gill

Hi,

Sapna

|||Sorry, actually the file created by bcp is not editable.|||

Hi Sapna

Please send me the SP or the related to it, hope it will help me. my mail ID are

gsgill76@.yahoo.com , gsgill76@.gmail.com

Thanks,

Regards.

Gurpreet S. Gill

|||I am able to solve the problem i.e the script of all the tables other like constaints, Indexs etc.

this is done with the help of Scptxfr.exe(i copied it from V2000), its working very fine in V2005 too.

For my application: We used the c sharp code to zip all the individual (tables) files to one file and then encrypt it.

Sunday, February 19, 2012

Backup/Restore MSSQL database from domain host?

My domain host provides Windows hosting, including MS SQL database.
Unfortunately it doesn't provide remote connections to their MS SQL server.
I can see no method of retrieving my database structure, or the database
populated with data, from the hosts control panel.
I'm sure that I could upload a script to my webspace that would allow me to
download and upload my database structures and data, but I have no idea
where to find a script that would allow this.
I'm hoping to be able to develop the database locally, then upload the
changes to the online database. Later I'd like to be able to download the
database to make modifications or just farm the data within them.
Any ideas?You need to ask your provider this. If they don't have a story on how you ca
n backup and get the
database and also upload such a backup, then change provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Noozer" <dont.spam@.me.here> wrote in message news:buMTf.164960$H%4.121289@.pd7tw2no...[colo
r=darkred]
> My domain host provides Windows hosting, including MS SQL database. Unfort
unately it doesn't
> provide remote connections to their MS SQL server. I can see no method of
retrieving my database
> structure, or the database populated with data, from the hosts control pan
el.
> I'm sure that I could upload a script to my webspace that would allow me t
o download and upload my
> database structures and data, but I have no idea where to find a script th
at would allow this.
> I'm hoping to be able to develop the database locally, then upload the cha
nges to the online
> database. Later I'd like to be able to download the database to make modif
ications or just farm
> the data within them.
> Any ideas?
>[/color]

Backup/Restore MSSQL database from domain host?

My domain host provides Windows hosting, including MS SQL database.
Unfortunately it doesn't provide remote connections to their MS SQL server.
I can see no method of retrieving my database structure, or the database
populated with data, from the hosts control panel.
I'm sure that I could upload a script to my webspace that would allow me to
download and upload my database structures and data, but I have no idea
where to find a script that would allow this.
I'm hoping to be able to develop the database locally, then upload the
changes to the online database. Later I'd like to be able to download the
database to make modifications or just farm the data within them.
Any ideas?
You need to ask your provider this. If they don't have a story on how you can backup and get the
database and also upload such a backup, then change provider.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"Noozer" <dont.spam@.me.here> wrote in message news:buMTf.164960$H%4.121289@.pd7tw2no...
> My domain host provides Windows hosting, including MS SQL database. Unfortunately it doesn't
> provide remote connections to their MS SQL server. I can see no method of retrieving my database
> structure, or the database populated with data, from the hosts control panel.
> I'm sure that I could upload a script to my webspace that would allow me to download and upload my
> database structures and data, but I have no idea where to find a script that would allow this.
> I'm hoping to be able to develop the database locally, then upload the changes to the online
> database. Later I'd like to be able to download the database to make modifications or just farm
> the data within them.
> Any ideas?
>

Backup/restore Databases remotely.

Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
Thanks
I don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>
|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/featu...0894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "
|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:

>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/featu...0894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "
|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself?

Thanks for your help.
|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE
|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinf...2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE

Backup/restore Databases remotely.

Hi i've tried a few remote administration tools including Microsofts own Web Data Administrator for SQL server... but none of them seem to do what is essential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remotely. I dont really want to have to use export/import data as things like triggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But PHP would also be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But PHP would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
See this page:
http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:
>SQL server itself can backup/restore yes. But the remote web admin tool cannot.. it can only import/export data which does not include things like triggers.
>See this page:
>http://www.databasejournal.com/features/mssql/article.php/10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration tool
>- Can't create a database backup and restore a database "|||Hi Sue
What would the syntax be for backup/restore in TSQL? Or do you have a link where I can find this out myself? :
Thanks for your help.|||No matter found it myself on MSDN
BACKUP DATABASE DBNameGoesHer
TO DISK = 'C:\temp\Somefilename.bak
WITH INI
RESTORE DATABASE DBNameGoesHer
FROM DISK = 'C:\temp\Somefilename.bak
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =that you have SQL Server installed).
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techinfo/productdoc/2000/books.asp
-- Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
> > BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
> > RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE

Backup/restore Databases remotely.

Hi i've tried a few remote administration tools including Microsofts own Web
Data Administrator for SQL server... but none of them seem to do what is es
sential to me and that is backup/restore databases to a file.
Does anyone know of a free tool that can just backup/restore databases remot
ely. I dont really want to have to use export/import data as things like tri
ggers get lost when using Microsoft Web data administrator.
A free web tool written in ASP.NET or ASP would be ideal. But php would also
be fine.
ThanksI don't understand . Why did you say that you can not
backup/restore to a file. As long as SQL server can
access that folder, it can backup/restore to/from that
folder.
>--Original Message--
>Hi i've tried a few remote administration tools
including Microsofts own Web Data Administrator for SQL
server... but none of them seem to do what is essential
to me and that is backup/restore databases to a file.
>Does anyone know of a free tool that can just
backup/restore databases remotely. I dont really want to
have to use export/import data as things like triggers
get lost when using Microsoft Web data administrator.
>A free web tool written in ASP.NET or ASP would be
ideal. But php would also be fine.
>Thanks
>.
>|||SQL server itself can backup/restore yes. But the remote web admin tool cann
ot.. it can only import/export data which does not include things like trigg
ers.
See this page:
http://www.databasejournal.com/feat...10894_3330301_2
And I quote:
"Database Administration Functions missing from Web Data Administration tool
- Can't create a database backup and restore a database "|||The article also says:
Even though these are not provided with the tool, some of
these items can be performed by using the Query tool to
submit T-SQL code to perform these tasks.
You can do backups and restores through T-SQL. In many
cases, most of what any of the tools do can be done in
T-SQL.
-Sue
On Thu, 8 Apr 2004 12:46:05 -0700, "GregD"
<anonymous@.discussions.microsoft.com> wrote:

>SQL server itself can backup/restore yes. But the remote web admin tool can
not.. it can only import/export data which does not include things like trig
gers.
>See this page:
>http://www.databasejournal.com/feat...10894_3330301_2
>And I quote:
>"Database Administration Functions missing from Web Data Administration too
l
>- Can't create a database backup and restore a database "|||Hi Sue,
What would the syntax be for backup/restore in TSQL? Or do you have a link w
here I can find this out myself?

Thanks for your help.|||No matter found it myself on MSDN:
BACKUP DATABASE DBNameGoesHere
TO DISK = 'C:\temp\Somefilename.bak'
WITH INIT
RESTORE DATABASE DBNameGoesHere
FROM DISK = 'C:\temp\Somefilename.bak'
WITH REPLACE|||Books Online is available within the SQL Server program group (assuming =
that you have SQL Server installed). =20
Books Online can be downloaded separately if necessary:
http://www.microsoft.com/sql/downloads/default.asp
Here is the specific link to the BOL download:
http://www.microsoft.com/sql/techin.../2000/books.asp
--=20
Keith
"GregD" <anonymous@.discussions.microsoft.com> wrote in message =
news:FBB86780-E276-4A02-B618-A7360E899FDC@.microsoft.com...
> No matter found it myself on MSDN:
>=20
> BACKUP DATABASE DBNameGoesHere
> TO DISK =3D 'C:\temp\Somefilename.bak'
> WITH INIT
>=20
> RESTORE DATABASE DBNameGoesHere
> FROM DISK =3D 'C:\temp\Somefilename.bak'
> WITH REPLACE

Monday, March 19, 2012

Bad results from my MDX Adventure Works query

Thursday, March 8, 2012

Backups of tran log not working

Backups of tran log not working

Backups of tran log not working

Friday, February 24, 2012

Backup/Restore Stored Procedure

Backup/Restore Stored Procedure

Sunday, February 19, 2012

Backup/Restore MSSQL database from domain host?

Backup/Restore MSSQL database from domain host?

Backup/restore Databases remotely.

Backup/restore Databases remotely.

Backup/restore Databases remotely.

Backup to Network Drive

Blog Archive

About Me