Backup to Network Drive: user

Showing posts with label user. Show all posts

Tuesday, March 27, 2012

Basic Parameter

This is a beginning type of question - I am trying to set up a parameter that
will contain 5 numeric digits. As the user starts typing i.e. a 3 I would
like to see 30000 appear, then if they type a 1 then 31000 will appear, next
if they type a 2 then I would see 31200 then a 5 they would see 31250 and
lastly if they typed a 1 then 31251 would appear in the list and when they
hit enter that would be the number of the work order that appears.
Thank you.On Nov 8, 3:49 pm, NormaD <Nor...@.discussions.microsoft.com> wrote:
> This is a beginning type of question - I am trying to set up a parameter that
> will contain 5 numeric digits. As the user starts typing i.e. a 3 I would
> like to see 30000 appear, then if they type a 1 then 31000 will appear, next
> if they type a 2 then I would see 31200 then a 5 they would see 31250 and
> lastly if they typed a 1 then 31251 would appear in the list and when they
> hit enter that would be the number of the work order that appears.
> Thank you.
Unfortunately, this type of functionality does not exist in SSRS/
Reporting Services (aside from standard auto-complete in your browser,
which isn't quite the same thing). The best way to create this
functionality is to create a custom ASP.NET application that does this
via callback/postback. Sorry that I could not be of greater
assistance.
Regards,
Enrique Martinez
Sr. Software Consultant

Sunday, March 11, 2012

Backwards in a foreach for ADO?

I need to simulate cursor-type (groan) behavior in a dataset and am wondering if this is possible in the foreach task. Example - The user has the need to go through the data row-by-row, and if a certain value is missing in row 10 then go back to row 8, grab a value from that row, and plug it in the missing column in row 10. Then move on to row 11.

Is there a way to make the foreach ADO enumerator travel backwards? Is there a way to do this that will not be awfully inefficient? Any suggestions out there would be welcome.

I've advocated for set-based updates, and these simply aren't an option, as each successive row depends on the updates that may have happened above it in the sequence. Unless I hear of any other ideas out there I have to move forward with this sequential type operation.

This sounds like a script task to me... I don't believe that there is an effecient way to do this (if at all) with a for each loop...

|||

That's the headache I'm having - I don't see a good way to do this period, from a SQL/SSIS/relational perspective.

|||Not sure if this would meet your needs, but you can use a script transform to buffer rows. You'd have to create some structure to store the data in (an array or dataset would work) and then you can access the rows in any order you wish.|||

Sounds intriguing - do you happen to have an example handy?

Wednesday, March 7, 2012

Backups Not Deleting

We're running SQL Server 2000 service pack 4. I've created a Maintenance
Plan that backs up all user databases, each to its own subfolder, every
night. The plan is supposed to delete old copies as well which it is not
doing. If I change the plan to backup only 1 database it works (old copies
deleted). When I switch it to all user databases, no more deletes. And the
sqlmaint log file does not even show that any deletes were attempted. Note
that we do have some databases offline, so those are skipped, of course, and
the job does fail.
Please help! What's the secret to get the older files to delete?
Here's the Step on the SQL Agent job:
EXECUTE master.dbo.xp_sqlmaint N'-PlanID
21EA0A98-9812-4AEA-8BC0-08AEAEAB24EC -Rpt "c:\temp\DB Maintenance
Plan14.txt" -DelTxtRpt 1WEEKS -WriteHistory -VrfyBackup -BkUpMedia
DISK -BkUpDB "D:\Backup" -DelBkUps 1DAYS -CrBkSubDir -BkExt "BAK"'
Thanks,
Krip
>I believe that deletion is done *at the end* of the job, so if anything
>fails, it bails out and no deletion is performed. You can try by first
>including only database so the whole job succeeds.
Tibor,
Thanks for the tip! I detached all our offline databases, and job finishes
without error, and deletes the old backups. Beautiful.
-Krip
|||I have also seen this behavior when the plan includes both Full and Simple
Recovery model databases.
Kevin Hill
IC3 North Texas
www.ChristianCycling.com
Please support me in the 2008 MS150:
http://www.ms150.org/dallas/donate/donate.cfm?id=208000
"Krip" <amk@.kynetix.com> wrote in message
news:7A2C4350-5AED-4F70-A22A-47E64ED68DFD@.microsoft.com...
> Tibor,
> Thanks for the tip! I detached all our offline databases, and job
> finishes without error, and deletes the old backups. Beautiful.
> -Krip
>

backups failing

Executed as user: domain\bla. The backup data in 'DevBackupDevice2' is
incorrectly formatted. Backups cannot be appended, but existing backup sets
may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I got this message this morning when trying to do a full backup. I dropped/
recreated the backup device and then tried again. I got the same message
still.
Any ideas?
SQL2K SP3
TIA, ChrisR
Chris,
Are you backing up to tape? See KB 290787, "PRB: Error Message 3266 Occurs
When Microsoft Tape Backup Format Cannot be Read"
The SQL Server 2000 example references the same error message you are
reporting.
Ron
Ron Talmage
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:O%23VM7h44EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Executed as user: domain\bla. The backup data in 'DevBackupDevice2' is
> incorrectly formatted. Backups cannot be appended, but existing backup
sets
> may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
> I got this message this morning when trying to do a full backup. I
dropped/
> recreated the backup device and then tried again. I got the same message
> still.
> Any ideas?
> --
> SQL2K SP3
> TIA, ChrisR
>

backups failing

Executed as user: domain\bla. The backup data in 'DevBackupDevice2' is
incorrectly formatted. Backups cannot be appended, but existing backup sets
may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is
terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
I got this message this morning when trying to do a full backup. I dropped/
recreated the backup device and then tried again. I got the same message
still.
Any ideas?
--
SQL2K SP3
TIA, ChrisRChris,
Are you backing up to tape? See KB 290787, "PRB: Error Message 3266 Occurs
When Microsoft Tape Backup Format Cannot be Read"
The SQL Server 2000 example references the same error message you are
reporting.
Ron
--
Ron Talmage
SQL Server MVP
"ChrisR" <bla@.noemail.com> wrote in message
news:O%23VM7h44EHA.3820@.TK2MSFTNGP11.phx.gbl...
> Executed as user: domain\bla. The backup data in 'DevBackupDevice2' is
> incorrectly formatted. Backups cannot be appended, but existing backup
sets
> may still be usable. [SQLSTATE 42000] (Error 3266) BACKUP DATABASE is
> terminating abnormally. [SQLSTATE 42000] (Error 3013). The step failed.
> I got this message this morning when trying to do a full backup. I
dropped/
> recreated the backup device and then tried again. I got the same message
> still.
> Any ideas?
> --
> SQL2K SP3
> TIA, ChrisR
>

Backups Failed

Dear All,
We recently changed our user for the SQL Agent. On Sunday
nights we backup all our databases and perform checks,
this Sunday they did not work. The error is as follows: -
'
Microsoft (R) SQLMaint Utility (Unicode), Version Logged
on to SQL Server 'INVESTMENTS1' as 'NT AUTHORITY\SYSTEM'
(trusted)
[Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 229:
[Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
permission denied on object 'sysdbmaintplans',
database 'msdb', owner 'dbo'.'
The odd thing however is we have other jobs that run on a
day by day basis that still work, can anyone help?
Thanks
PeterHi,
Check whether local administrators group on the machine still a member of
the
sysadmin server role? Else add it and verify the job.
Incase if you still have issues then,
Can you please add that user (User inwhich u start SQL Agent) to
buildin\administrators group and verify the execution.
or else give select permission on 'sysdbmaintplans' table to the user in
which you have started SQL Agent.
Thanks
Hari
MCDBA
"Peter" <anonymous@.discussions.microsoft.com> wrote in message
news:a44e01c40a74$e3ee35e0$a601280a@.phx.gbl...
> Dear All,
> We recently changed our user for the SQL Agent. On Sunday
> nights we backup all our databases and perform checks,
> this Sunday they did not work. The error is as follows: -
> '
> Microsoft (R) SQLMaint Utility (Unicode), Version Logged
> on to SQL Server 'INVESTMENTS1' as 'NT AUTHORITY\SYSTEM'
> (trusted)
> [Microsoft SQL-DMO (ODBC SQLState: 42000)] Error 229:
> [Microsoft][ODBC SQL Server Driver][SQL Server]SELECT
> permission denied on object 'sysdbmaintplans',
> database 'msdb', owner 'dbo'.'
> The odd thing however is we have other jobs that run on a
> day by day basis that still work, can anyone help?
> Thanks
> Peter|||Thanks Hari,
Someone took out the Sysadmin access to the BUILTIN\Admin
right.
Peter

>--Original Message--
>Hi,
>Check whether local administrators group on the machine
still a member of
>the
>sysadmin server role? Else add it and verify the job.
>Incase if you still have issues then,
>Can you please add that user (User inwhich u start SQL
Agent) to
>buildin\administrators group and verify the execution.
>or else give select permission on 'sysdbmaintplans' table
to the user in
>which you have started SQL Agent.
>
>
>Thanks
>Hari
>MCDBA
>
>
>"Peter" <anonymous@.discussions.microsoft.com> wrote in
message
>news:a44e01c40a74$e3ee35e0$a601280a@.phx.gbl...
Sunday
follows: -
a
>
>.
>

Friday, February 24, 2012

backupRestore progress bar

Hi,
My app uses MSDE and I have created a UI for user which has buttons to backup and restore the database. Everything works fine but I want to give a visual display to user about the progress of the operation. You know like a progress bar indicating how mu
ch work is done.. so the question is:
Is there a way to find out how much time backup/restore will take.. does sql server provide any such event to us telling this information. I just want to trap this event and give the progress indication to my loyal users.
Thanks all.
dev
Thanks Andrea for the reply. I am using T-SQL commands right now to backup and restore database. If I use sql-dmo to trap progress event then would that mean that I will have to change the backup/restore sourcecode also to use sql-dmo. Please confirm.
Thanks
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:8CD41CBC-6EF7-46F8-BC3C-3415F14A6BD5@.microsoft.com...
> Thanks Andrea for the reply. I am using T-SQL commands right now to
backup
>and restore database. If I use sql-dmo to trap progress event then would
that
>mean that I will have to change the backup/restore sourcecode also to use
>sql-dmo. Please confirm.
yep.. you have to use the SQL-DMO backup object to trap the raised events...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||oh no.. so what is this sql-dmo. What is the purpose of it's existance. Why do we need it (besides for the progress bar). How is it different from programming with T-SQL. How can we choose which way to go, T-SQL or SQL-DMO. What are it's advantages an
d disadvantages.
Thanks
|||Also was it unwise of me to opt for T-SQL for doing everything instead of going for DMO. And is it ok to use DMO for backup/restore and use T-SQL for everything else. What are your recommendations.
We are as before an ISV, app will be deployed using MSDE, will have max a couple of databases. Will provide an interface to the user to backup and restore database. Will creare database from app and do version checks for database.
What should we do... or should we wait for SMO..
Thanks for your valuable time.
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:07DA4684-529D-48D5-9D6A-56DDFDB077A7@.microsoft.com...
> oh no.. so what is this sql-dmo. What is the purpose of it's existance.
Why do we need it
>(besides for the progress bar). How is it different from programming with
T-SQL. How can
> we choose which way to go, T-SQL or SQL-DMO. What are it's advantages and
disadvantages.
> Thanks
SQL-DMO is an acronym for SQL Distributed Management Object and is a full
object model to manage and administer SQL Server..
it exposes a nice object model you can navigate to perform quiet all
management tasks on SQL Server..
it's not designed for data manipulation even if it provides some features
to.
it comes with MSDE and/or can be installed from the Client Tools
installation package of SQL Server.
it is not provided as a separate download and/or package, so you have to
depoly it yourself in MSDE scenarios (you are legitimate to) ... this can be
count as a disadvantage too :-)
it's a little buggy =;-) and eats a lot of memory, but is very handy for
some admin scenario...
Transact-SQL, on the contrary, is the language SQL Server better understand,
and can perform both data manipulation and administration tasks... it's a
separate language where DMO is just a COM object model you can use in any
COM compliant client, so they can not be compared... you shoul'd stick with
Transact-SQL, and use DMO where and when appropriated...
by the way... there's only 1 book worth reading about SQL-DMO, by SQL Server
MVPs Allan Mitchell and Mark Allison,
http://www.compman.co.uk/cgi-win/browse.exe?ref=552118 , which I personally
recommend reading...
FWIW, you can have a look at a free prj of mine at the link following my
sign to have an idea aboout what you can do with SQL-DMO, a prj that
provides a user interface similar to Enteprise Manager written in VB6
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:AF907AC5-9610-41F9-95DD-FC68828844F6@.microsoft.com...
> Also was it unwise of me to opt for T-SQL for doing everything instead of
going for DMO.
>And is it ok to use DMO for backup/restore and use T-SQL for everything
else. What are your
>recommendations.
> We are as before an ISV, app will be deployed using MSDE, will have max a
couple of
>databases. Will provide an interface to the user to backup and restore
database.
>Will creare database from app and do version checks for database.
> What should we do... or should we wait for SMO..
if you only need DMO for presenting a progress bar indicating backup
progress, let it be...
you have to talk to SQL Server via Transact-SQL...
perform backup via ADO.Net commands...
SMO will be only available wit SQL Server 2005, and anyway I don't think it
shoul'd be used for traditional programming, but for the same things now
covered by DMO...
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.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 for the insight. I already use your tool since quiet some time and appreciate it. So do you recommend I use T-SQL for everything as now, you know db creation, data manipulation and use dmo for backup/restore which I can later port to use s
mo when yukon arrives..
dev
|||I am sorry Andrea but I didn't understand what you said here:
"if you only need DMO for presenting a progress bar indicating backup
progress, let it be...
you have to talk to SQL Server via Transact-SQL...
perform backup via ADO.Net commands..."
Do you mean that I use T-SQL for everything else and just use DMO for the backup/restore.
Sorry to bother you like this but Thanks
|||hi,
"dev" <anonymous@.discussions.microsoft.com> ha scritto nel messaggio
news:778938C6-1142-4954-B633-BBF0669342D3@.microsoft.com...
> I am sorry Andrea but I didn't understand what you said here:
> "if you only need DMO for presenting a progress bar indicating backup
> progress, let it be...
> you have to talk to SQL Server via Transact-SQL...
> perform backup via ADO.Net commands..."
> Do you mean that I use T-SQL for everything else and just use DMO for the
backup/restore.
> Sorry to bother you like this but Thanks
please excuse my poor english...
what I mean is go with Transact-SQL for all you stuffs, including
Backup/Restore, if you only need SQL-DMO for providing a progress
indicator...
if you need SQL-DMO for other things, then you can consider adding it to
your project references [and setup package :-( ]
Andrea Montanari (Microsoft MVP - SQL Server)
http://www.asql.biz/DbaMgr.shtmhttp://italy.mvps.org
DbaMgr2k ver 0.8.0 - DbaMgr ver 0.54.0
(my vb6+sql-dmo little try to provide MS MSDE 1.0 and MSDE 2000 a visual
interface)
-- remove DMO to reply

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

create login companyA default_database 'customersA'

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

use CustomersA

sp_changedbowner 'CustomersA'

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar

Backup/restore table belonging to one user/login

Hi!

I have a SQL 2000 database that has several tables with the same name but with different users/logins.

Example:

Database "Customers"

Table Customer with user / login CompanyA

Table Customer with user / login CompanyB

Table Contact with user / login CompanyA

Table Contact with user / login CompanyB

I need to split these into two different database's

Database "CustomersA"

Table Customer with user / login CompanyA

Table Contact with user / login CompanyA

Database "CustomersB"

Table Customer with user / login CompanyB

Table Contact with user / login CompanyB

Any good idea how to do this?

Ingar

You can export whichever table you need to another database using the import/export wizard and then remove it from source database
|||

Create CompanyA and CompanyB databases, and appropriate users : CompanyA in CompanyA database and CompanyB in CompanyB database.

Then run

use CompanyA

EXEC sp_changeobjectowner 'Customer', 'CompanyA';

...|||

Code Snippet

Create database CustomersA

create login companyA default_database 'customersA'

select *

into [customersA].[dbo].[customers]

from

[customers].[CompanyA].[customersA]

use CustomersA

sp_changedbowner 'CustomersA'

-- repeat this for CustomerB

|||

Thank's a lot.

This solves my problem.

Ingar

Sunday, February 19, 2012

Backup/Restore scenario

Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
--
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
--
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>> Q1 - When you apply the transaction log backups you will have all the
>> modifications that have happened up to the point of the most recent
>> transaction log that you applied.
>> Q2 - Yes, the table that is created on Tuesday will be capured within
>> Tuesday night's t-log backup. It will be "created" when you restore
>> that log backup.
>>
>> If you have "lots" of user activity as you say you might want to issue
>> transaction log backups more often than once per day.
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen

Backup/Restore scenario

Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
Gerry Hickman
Q1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>
|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)
|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen

Backup/Restore scenario

Hi,
I'm confused about the role of tLogs in a restore scenario. Imagine the
following:
Database running 24/7 with a lot of user activity. All backups at midnight
Fri - Full backup
Mon - tLog backup
Tue - tLog backup
Wed - tLog backup
Thurs - tLog backup
Imagine the server blows up one Thursday morning.
Q1. If I restore the full backup from the previous Friday, and then the
tLogs for Mon, Tue, Wed, does that mean I've got everything except the last
few user updates early Thursday morning?
Q2. What if the developers had added a new table on Tuesday? Would this new
table exist in the restored version?
Gerry HickmanQ1 - When you apply the transaction log backups you will have all the
modifications that have happened up to the point of the most recent
transaction log that you applied.
Q2 - Yes, the table that is created on Tuesday will be capured within
Tuesday night's t-log backup. It will be "created" when you restore that
log backup.
If you have "lots" of user activity as you say you might want to issue
transaction log backups more often than once per day.
Keith Kratochvil
"Gerry Hickman" <gerry1uk@.netscape.net> wrote in message
news:u6ZvvygPGHA.3164@.TK2MSFTNGP11.phx.gbl...
> Hi,
> I'm confused about the role of tLogs in a restore scenario. Imagine the
> following:
> Database running 24/7 with a lot of user activity. All backups at midnight
> Fri - Full backup
> Mon - tLog backup
> Tue - tLog backup
> Wed - tLog backup
> Thurs - tLog backup
> Imagine the server blows up one Thursday morning.
> Q1. If I restore the full backup from the previous Friday, and then the
> tLogs for Mon, Tue, Wed, does that mean I've got everything except the
> last
> few user updates early Thursday morning?
> Q2. What if the developers had added a new table on Tuesday? Would this
> new
> table exist in the restored version?
> --
> Gerry Hickman
>|||Hi Keith,
Thanks, this is very helpful and this was how I originally understood it
would work, but recently I wasn't sure. It's interesting the new table
gets carried over.
I have another question now!
If the tLogs are storing all the changes since the last backup, do they
get "emptied" next time you do a full backup?
As I understand it, you can choose to "truncate" the log as part of the
backup procedure, but I currently DON'T have this checked, so how do I
know my full backup really is "full" and will my tLogs keep growing
forever? All my backup settings are on the defaults. Can you advise the
correct backup settings to use for our simple setup?
(Point taken about doing full backup each day instead of each week)
Keith Kratochvil wrote:
> Q1 - When you apply the transaction log backups you will have all the
> modifications that have happened up to the point of the most recent
> transaction log that you applied.
> Q2 - Yes, the table that is created on Tuesday will be capured within
> Tuesday night's t-log backup. It will be "created" when you restore that
> log backup.
>
> If you have "lots" of user activity as you say you might want to issue
> transaction log backups more often than once per day.
>
Gerry Hickman (London UK)|||Gerry Hickman wrote:
> Hi Keith,
> Thanks, this is very helpful and this was how I originally understood it
> would work, but recently I wasn't sure. It's interesting the new table
> gets carried over.
> I have another question now!
> If the tLogs are storing all the changes since the last backup, do they
> get "emptied" next time you do a full backup?
> As I understand it, you can choose to "truncate" the log as part of the
> backup procedure, but I currently DON'T have this checked, so how do I
> know my full backup really is "full" and will my tLogs keep growing
> forever? All my backup settings are on the defaults. Can you advise the
> correct backup settings to use for our simple setup?
> (Point taken about doing full backup each day instead of each week)
> Keith Kratochvil wrote:
>
Hi Gary
The FULL backup will not do anything to the logfiles. It's only a log
backup that will "touch" the logfile (Actually a FULL backup will take a
little part of the logfile, but that's only what it needs to be able to
perform a RESTORE).
When you do a log backup, it will mark the transactions that it has
backed up and the space can then be reused (TRUNCATE). Now the space can
be reused by new transactions, so your physical logfile doesn't need to
grow to contain the transactions.
Try to look up "Transaction Log Backups" in Books On Line. That chapter
gives a fairly good description of how it works.
Regards
Steen

Monday, February 13, 2012

Backup User - Server Role?

I wish to create a user that can backup any or all databases in our SQL
Server 2000 Instance. I thought there would be a server role for this
function, however I can only find that after I grant access of a
database to the user, then I can choose ds_backupoperator.
I want to create a user that will have the ability to backup all the
databases. I dont wish to have to come back to the server after a new
table is created and add the backup user to that table.
I want SA w/o the full privilage...am I crazy?
Any Suggestions?
TIA
Rob
Backgroup: We currently have about 10 SQL servers, and adding more in
the future. I am using SQLBackup from Idera along with HP Surestore
Tape library (60 slots,2- DLT8000 drives with 40/80 GB capacity) with
ArcServe from Computer Associates. I want to have this automated to
backup to file then tape, regardless of what databases get created.rcamarda (rcamarda@.cablespeed.com) writes:
> I wish to create a user that can backup any or all databases in our SQL
> Server 2000 Instance. I thought there would be a server role for this
> function, however I can only find that after I grant access of a
> database to the user, then I can choose ds_backupoperator.
> I want to create a user that will have the ability to backup all the
> databases. I dont wish to have to come back to the server after a new
> table is created and add the backup user to that table.
> I want SA w/o the full privilage...am I crazy?

I guess the reason you need to be sysadmin to backup a database, is that
BACKUP DATABASE gives you access to the data in the table. Once you have
a backup, and can restore anywhere you like - and with any privilege.

I believe there is a way to add a user to db_backupoperator to all
future database: add the user to this role in model. I have not tried
this, though.

--
Erland Sommarskog, SQL Server MVP, esquel@.sommarskog.se

Books Online for SQL Server SP3 at
http://www.microsoft.com/sql/techin.../2000/books.asp

Friday, February 10, 2012

Backup to Network Drive

We are attempting to set our SQL Server 2000 backups to point to a network
drive. We have created a domain user and set the SQL Server Service and SQL
Server Agent to use this account. We have also set this account as a member
of the Administrators group on the SQL Server itself and added it to the sys
admin group within SQL Server. On the destination server, the service account
has full control on the share that we'd like to write to. Unfortunately, our
backups are failing. The SQL Server Log provides the following message
"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
create. Operating system error = 5(Access is denied.)."
What are we missing here?Marcia,
Did you restart the SQL Server once the drive was mounted on your
server? Have you verified that as the user which SQL Server starts
under on the box you can read and write data to that location? These
are some of the gotcha's. I wish SQL Server allowed you to see network
mount points when you go to Backup Database options through Enterprise
Manager.
Shahryar
MarciaN wrote:
>We are attempting to set our SQL Server 2000 backups to point to a network
>drive. We have created a domain user and set the SQL Server Service and SQL
>Server Agent to use this account. We have also set this account as a member
>of the Administrators group on the SQL Server itself and added it to the sys
>admin group within SQL Server. On the destination server, the service account
>has full control on the share that we'd like to write to. Unfortunately, our
>backups are failing. The SQL Server Log provides the following message
>"BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
>create. Operating system error = 5(Access is denied.)."
>What are we missing here?
>
Shahryar G. Hashemi | Sr. DBA Consultant
InfoSpace, Inc.
601 108th Ave NE | Suite 1200 | Bellevue, WA 98004 USA
Mobile +1 206.459.6203 | Office +1 425.201.8853 | Fax +1 425.201.6150
shashem@.infospace.com | www.infospaceinc.com
This e-mail and any attachments may contain confidential information that is legally privileged. The information is solely for the use of the intended recipient(s); any disclosure, copying, distribution, or other use of this information is strictly prohibited. If you have received this e-mail in error, please notify the sender by return e-mail and delete this message. Thank you.|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/?kbid=555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> We are attempting to set our SQL Server 2000 backups to point to a network
> drive. We have created a domain user and set the SQL Server Service and
> SQL
> Server Agent to use this account. We have also set this account as a
> member
> of the Administrators group on the SQL Server itself and added it to the
> sys
> admin group within SQL Server. On the destination server, the service
> account
> has full control on the share that we'd like to write to. Unfortunately,
> our
> backups are failing. The SQL Server Log provides the following message
> "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> create. Operating system error = 5(Access is denied.)."
> What are we missing here?|||We have followed these procedures and still no luck. Can you give some
specifics related to the access levels that the WIndows Service account
should have?
"Geoff N. Hiten" wrote:
> HowTo: Backup to UNC name using Database Maintenance Wizard
> http://support.microsoft.com/?kbid=555128
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> > We are attempting to set our SQL Server 2000 backups to point to a network
> > drive. We have created a domain user and set the SQL Server Service and
> > SQL
> > Server Agent to use this account. We have also set this account as a
> > member
> > of the Administrators group on the SQL Server itself and added it to the
> > sys
> > admin group within SQL Server. On the destination server, the service
> > account
> > has full control on the share that we'd like to write to. Unfortunately,
> > our
> > backups are failing. The SQL Server Log provides the following message
> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed to
> > create. Operating system error = 5(Access is denied.)."
> >
> > What are we missing here?
>
>|||The SQL Server service account requires FULL CONTROL over the share and the
underlying NTFS directory structure.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> We have followed these procedures and still no luck. Can you give some
> specifics related to the access levels that the WIndows Service account
> should have?
>
> "Geoff N. Hiten" wrote:
>> HowTo: Backup to UNC name using Database Maintenance Wizard
>> http://support.microsoft.com/?kbid=555128
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> > We are attempting to set our SQL Server 2000 backups to point to a
>> > network
>> > drive. We have created a domain user and set the SQL Server Service and
>> > SQL
>> > Server Agent to use this account. We have also set this account as a
>> > member
>> > of the Administrators group on the SQL Server itself and added it to
>> > the
>> > sys
>> > admin group within SQL Server. On the destination server, the service
>> > account
>> > has full control on the share that we'd like to write to.
>> > Unfortunately,
>> > our
>> > backups are failing. The SQL Server Log provides the following message
>> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> > to
>> > create. Operating system error = 5(Access is denied.)."
>> >
>> > What are we missing here?
>>|||We gave the service account FULL CONTROL over the share. After an
unsuccessful test of that, we added the service account to the local admins
group on the destination server and still no luck. It seems like we have
covered all the bases but still can not make this work. Can you think of
anything else?
"Geoff N. Hiten" wrote:
> The SQL Server service account requires FULL CONTROL over the share and the
> underlying NTFS directory structure.
> --
> Geoff N. Hiten
> Senior Database Administrator
> Microsoft SQL Server MVP
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> > We have followed these procedures and still no luck. Can you give some
> > specifics related to the access levels that the WIndows Service account
> > should have?
> >
> >
> > "Geoff N. Hiten" wrote:
> >
> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> http://support.microsoft.com/?kbid=555128
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> > network
> >> > drive. We have created a domain user and set the SQL Server Service and
> >> > SQL
> >> > Server Agent to use this account. We have also set this account as a
> >> > member
> >> > of the Administrators group on the SQL Server itself and added it to
> >> > the
> >> > sys
> >> > admin group within SQL Server. On the destination server, the service
> >> > account
> >> > has full control on the share that we'd like to write to.
> >> > Unfortunately,
> >> > our
> >> > backups are failing. The SQL Server Log provides the following message
> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> > to
> >> > create. Operating system error = 5(Access is denied.)."
> >> >
> >> > What are we missing here?
> >>
> >>
> >>
>
>|||Verify NTFS file permissions on the destination directory. This is
different from the share security permissions.
Log in to the console of the SQL Server as the service account and try to
access the network share directly. Create, rename, and delete a file.
Run a backup via Query Analyzer (BACKUP DATABASE command) to the share
location.
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local
> admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and
>> the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service
>> >> > and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the
>> >> > service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following
>> >> > message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup'
>> >> > failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||I'd start by logging in on the SQL Server machine using the service account and see if I can access
and create files. If so, use xp_cmdshell to see if you can do the same.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
Blog: http://solidqualitylearning.com/blogs/tibor/
"MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> We gave the service account FULL CONTROL over the share. After an
> unsuccessful test of that, we added the service account to the local admins
> group on the destination server and still no luck. It seems like we have
> covered all the bases but still can not make this work. Can you think of
> anything else?
> "Geoff N. Hiten" wrote:
>> The SQL Server service account requires FULL CONTROL over the share and the
>> underlying NTFS directory structure.
>> --
>> Geoff N. Hiten
>> Senior Database Administrator
>> Microsoft SQL Server MVP
>> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
>> > We have followed these procedures and still no luck. Can you give some
>> > specifics related to the access levels that the WIndows Service account
>> > should have?
>> >
>> >
>> > "Geoff N. Hiten" wrote:
>> >
>> >> HowTo: Backup to UNC name using Database Maintenance Wizard
>> >> http://support.microsoft.com/?kbid=555128
>> >>
>> >> --
>> >> Geoff N. Hiten
>> >> Senior Database Administrator
>> >> Microsoft SQL Server MVP
>> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
>> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
>> >> > We are attempting to set our SQL Server 2000 backups to point to a
>> >> > network
>> >> > drive. We have created a domain user and set the SQL Server Service and
>> >> > SQL
>> >> > Server Agent to use this account. We have also set this account as a
>> >> > member
>> >> > of the Administrators group on the SQL Server itself and added it to
>> >> > the
>> >> > sys
>> >> > admin group within SQL Server. On the destination server, the service
>> >> > account
>> >> > has full control on the share that we'd like to write to.
>> >> > Unfortunately,
>> >> > our
>> >> > backups are failing. The SQL Server Log provides the following message
>> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
>> >> > to
>> >> > create. Operating system error = 5(Access is denied.)."
>> >> >
>> >> > What are we missing here?
>> >>
>> >>
>> >>
>>|||It's working! We deleted the shared folder and recreated it. Now it works.
Thanks to all for your suggestions.
"Tibor Karaszi" wrote:
> I'd start by logging in on the SQL Server machine using the service account and see if I can access
> and create files. If so, use xp_cmdshell to see if you can do the same.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
> http://www.solidqualitylearning.com/
> Blog: http://solidqualitylearning.com/blogs/tibor/
>
> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> news:3469833A-EC8A-4755-828B-D309A0A2F72E@.microsoft.com...
> > We gave the service account FULL CONTROL over the share. After an
> > unsuccessful test of that, we added the service account to the local admins
> > group on the destination server and still no luck. It seems like we have
> > covered all the bases but still can not make this work. Can you think of
> > anything else?
> >
> > "Geoff N. Hiten" wrote:
> >
> >> The SQL Server service account requires FULL CONTROL over the share and the
> >> underlying NTFS directory structure.
> >>
> >> --
> >> Geoff N. Hiten
> >> Senior Database Administrator
> >> Microsoft SQL Server MVP
> >>
> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> news:12B2D6D3-3FFF-4356-9857-700A5E2CF2F0@.microsoft.com...
> >> > We have followed these procedures and still no luck. Can you give some
> >> > specifics related to the access levels that the WIndows Service account
> >> > should have?
> >> >
> >> >
> >> > "Geoff N. Hiten" wrote:
> >> >
> >> >> HowTo: Backup to UNC name using Database Maintenance Wizard
> >> >> http://support.microsoft.com/?kbid=555128
> >> >>
> >> >> --
> >> >> Geoff N. Hiten
> >> >> Senior Database Administrator
> >> >> Microsoft SQL Server MVP
> >> >> "MarciaN" <MarciaN@.discussions.microsoft.com> wrote in message
> >> >> news:B8AAEF33-2B4B-445A-ADBF-F2D74A78F0D2@.microsoft.com...
> >> >> > We are attempting to set our SQL Server 2000 backups to point to a
> >> >> > network
> >> >> > drive. We have created a domain user and set the SQL Server Service and
> >> >> > SQL
> >> >> > Server Agent to use this account. We have also set this account as a
> >> >> > member
> >> >> > of the Administrators group on the SQL Server itself and added it to
> >> >> > the
> >> >> > sys
> >> >> > admin group within SQL Server. On the destination server, the service
> >> >> > account
> >> >> > has full control on the share that we'd like to write to.
> >> >> > Unfortunately,
> >> >> > our
> >> >> > backups are failing. The SQL Server Log provides the following message
> >> >> > "BackupDiskFile::CreateMedia: Backup device '\\Server\SQLBackup' failed
> >> >> > to
> >> >> > create. Operating system error = 5(Access is denied.)."
> >> >> >
> >> >> > What are we missing here?
> >> >>
> >> >>
> >> >>
> >>
> >>
> >>
>|||Windows has two level access control.
You need to give the sql account first the access to the shared
drive/directory and then you need to give access to the
files
1. Access to the SHARE
2. Access to files on that share
1. On the shared directory right click-> sharing->permissions. Check the
SQL account has access rights.
2. same right click->security->permissions. check the SQL account has
rights.
That should do it
rgrds Matti

Backup to Network Drive

backup to device

I am using a device to backup my user database for both data and the transaction log. I want to automate this process and I understand the syntax for the backup of log and data. I want to know what the syntax is for having the complete backup overwrite the existing backup in the device after a number of backups have occured. I can't find anything in the users manual for this. I also want to have my transaction log backups be overwritten periodically. With my current setup the backup device grows and grows
For example
after the third data backup I want the first backup to be overwritten so the device only contains that last three backups. I will backup the transaction log 2x per day, and I want to keep that last 6 transaction log backups to be stored on the device and then the oldest transaction log backup in the device will be overwritten.
thanks>after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups.
I don't think this is doable. When you do backup you use WITH INIT or WITH
NOINIT to tell backup to orverwrite or append to the backup device. No way
you can tell it to purge the first backup set (if there are 3 exist) and
append a new backup set to the backup device. Similar to the log backup.
One thing you can do is you have 3 backup devices for each day. Lets say
you have BACKUP1, BACKUP2, BACKUP3. Do a full backup and 2 log backups
(appended) to each backup device every day. Schedule a job to run full
backup and another job to do log backup. Before each backup do an IF..ELSE
to find out what backup device was used the day before so your backup will
know what backup device to use today.
hth,
"Stephen Harris" <anonymous@.discussions.microsoft.com> wrote in message
news:2C185839-AF58-4269-B4B3-EA0335036FB3@.microsoft.com...
> I am using a device to backup my user database for both data and the
transaction log. I want to automate this process and I understand the
syntax for the backup of log and data. I want to know what the syntax is
for having the complete backup overwrite the existing backup in the device
after a number of backups have occured. I can't find anything in the users
manual for this. I also want to have my transaction log backups be
overwritten periodically. With my current setup the backup device grows and
grows.
> For example,
> after the third data backup I want the first backup to be overwritten so
the device only contains that last three backups. I will backup the
transaction log 2x per day, and I want to keep that last 6 transaction log
backups to be stored on the device and then the oldest transaction log
backup in the device will be overwritten.
> thanks

Tuesday, March 27, 2012

Basic Parameter

Sunday, March 11, 2012

Backwards in a foreach for ADO?

Wednesday, March 7, 2012

Backups Not Deleting

backups failing

backups failing

Backups Failed

Friday, February 24, 2012

backupRestore progress bar

Backup/restore table belonging to one user/login

Backup/restore table belonging to one user/login

Sunday, February 19, 2012

Backup/Restore scenario

Backup/Restore scenario

Backup/Restore scenario

Monday, February 13, 2012

Backup User - Server Role?

Friday, February 10, 2012

Backup to Network Drive

Backup to Network Drive

Backup to Network Drive

backup to device

Backup to Network Drive

Blog Archive

About Me