sql backup files: manager

Showing posts with label manager. Show all posts

Thursday, March 29, 2012

Backup strategy

Hi,

In my current organisation they are using SQL Server.

They are using TSM (Tivoli Storage Manager) to back up the server on a nightly base.

However I feel that this is not the correct way. Suppose I need some data back, I call technical support and they would restore the server. If another user made some changes to another database that day, he would lose his changes.

Of course they could restore a file but I am not sure if this is correctly. What will happen to the transaction log for example ?

My idea is that they should backup the database using the normal sql backup commando, dump the data to a folder and backup that folder.

Any suggestions please ?

Constantijn Enders

TSM has it's own way of running backups. It even uses the SQL Server APIs to do so. No need to worry about it. When in doubt, test.|||

Great, thanks a lot

|||

Tivoli is a great product, just make sure you understand how to use it properly. Set up a test lab and practice, practice, practice restores.

Tuesday, March 27, 2012

Backup SQL database without using Enterprise Manager?

Hello,

Is there a way where I can backup SQL database without using Enterprise Manager? If there is, may I know how?Use the OSQL command line tool. This allows you to execute TSQL code. Use the BACKUP TSQL Command:

-- Create the backup device for the full MyNwind backup.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwind_2',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwind_2.dat'

--Create the log backup device.
USE master
EXEC sp_addumpdevice 'disk', 'MyNwindLog1',
'c:\Program Files\Microsoft SQL Server\MSSQL\BACKUP\MyNwindLog1.dat'

-- Back up the full MyNwind database.
BACKUP DATABASE MyNwind TO MyNwind_2

-- Update activity has occurred since the full database backup.

-- Back up the log of the MyNwind database.
BACKUP LOG MyNwind
TO MyNwindLog1

|||Thanks for the SQL script. But how do I run it if I don't have Query Analyzer? Is there any tools that will enable me to run it?

Basically I have this situation when MS SQL Server (or SQL Server Tools) is not installed in my PC but I need to backup a SQL database.|||As I mentioned, there is a command line tool, called OSQL.EXE, that will allow you to run an SQL Script, and it is included with any install of SQL Server or MSDE, AFAIK.sql

Sunday, March 25, 2012

Backup settings for server registrations in SQL Server Enterprise

Dear SQL-Server-Gurus
I have version 8.0 of the SQL Server Enterprise Manager with many server
registrations organized in groups. I would like to backup these settings to
use them on an other computer or to have a copy in case I have to reinstall
my computer.
Is there any way to create a backup of the setings in SQL Server Enterprise
Manager 8.0? Up until now I have not found an INI-File or matching registry
settings by myself. Any input is highly appreciated.
Kind regards
Gabriel
From what I know the information is stored in registry. You can backup(and
later restore) the following registry key:
"HKEY_CURRENT_USER\Software\Microsoft\Microsof t SQL Server\80
Tools\SQLEW\Registered Servers X".

quote:

Warning: If you use Registry Editor incorrectly, you may cause serious
problems that may require you to reinstall your operating system. Use
Registry Editor at your own risk.

Anyway if you use Enterprise Manager on multiple clients you may take a look
at this page:
"Storing SQL Server 7.0 Server Registrations on a Centralized System"
http://www.windowsitpro.com/SQLServe...9750/9750.html
Cristian Lefter, SQL Server MVP
"Gabriel Martin" <GabrielMartin@.discussions.microsoft.com> wrote in message
news:6D500EC5-3352-4CA7-BD99-22DCA99506B6@.microsoft.com...
> Dear SQL-Server-Gurus
> I have version 8.0 of the SQL Server Enterprise Manager with many server
> registrations organized in groups. I would like to backup these settings
> to
> use them on an other computer or to have a copy in case I have to
> reinstall
> my computer.
> Is there any way to create a backup of the setings in SQL Server
> Enterprise
> Manager 8.0? Up until now I have not found an INI-File or matching
> registry
> settings by myself. Any input is highly appreciated.
> Kind regards
> Gabriel
>

Monday, March 19, 2012

Backup Question

Hi,

In Enterprise Manager of my SQL Server Desktop version, I selected Backup option to have back up of my db. In General tab of the menu I set schedule to recurring backup every 2 minutes on daily basisjust to test.

Now I need to change it to back up every hr or the time I desire. I tried the same way I did before. But doesnt remove the first 2 mins back up schedule instead it adds new back up (which I set for every 1 hr) copy to the location.

My question is:

How can I make disable to first back up schedule which was set for every 2 minutes? Do I need to overwrite the destination file name? Please advice.

Thanks in advance.

With kind regards,
AshfaqueSo then, you did this via SQL Server Agent under Jobs? If so; you should be able to change the properties of your original job, or simply delete the whole job.

If you did it under Maintenance Plans, same thing. Just double-click the maintenance plan you want to change, click on the Complete Backup tab, and click on the "Change" button displayed next to the schedule description.

Note: Maintenance Plans create their own SQL Agent Jobs, but it will also change them if you change the properties under "Maintance Plans".

At least; that's how it's working on my SQL Server 2000 Standard environment.|||you did this via SQL Server Agent under Jobs?
Yeah.
If you did it under Maintenance Plans
Yeah..

Thanks vich.

I will do the same you instructed and come back to you.

Thanks.
Ashfaque|||In the mean time I have another question.

I intalled same server desktop version at my office and I need to RESTORE back up of my db that I created at home.

Hence I created same db name at office and tried to restore it form the CD drive. It proced the attached error. Then I coied back file somewhere in C: drive and again tried to restore the db. But failed.

1. Can somebody explain me how to restore the db?
2. Do I need to create the same db folder name to restore the back up?
3. Why can not I restore back up from CD drive while back up is ok?
4. Do it really need to copy backup some where in hard disk and then only can copy?

Please advice.

Thanks in advance.
Ashaque|||Sorry,

Forget to attach the JPEG.

Here it is.

Ashfaque

Sunday, March 11, 2012

backup problem 2

Has anyone encountered a situation where the backup process is still
running but Enterprise Manager already says that it was finished and
was successful.
I initiated a backup to a UNC share (but the actual drive is directly
attached to the server) and the backup started. I see the size of the
backup file increasing (via Windows Explorer) so I assumed everything
is okay. After about 10% was done, EM suddenly showed me the usual
"The backup operation has completed successfully" window, but the size
of the backup file continued increasing...and apparently went on an
on, up to the expected size of the backup. Upon checking (restore),
the backup file was okay.
Has anyone encountered a similar situation? Any thoughts on this?
AramidHi
Run sp_who2 during the process. You will see if the backup is still running.
What does the job history say about the run time? Does it match the file
datetime stamp?
Regards
Mike
"aramid" wrote:
> Has anyone encountered a situation where the backup process is still
> running but Enterprise Manager already says that it was finished and
> was successful.
> I initiated a backup to a UNC share (but the actual drive is directly
> attached to the server) and the backup started. I see the size of the
> backup file increasing (via Windows Explorer) so I assumed everything
> is okay. After about 10% was done, EM suddenly showed me the usual
> "The backup operation has completed successfully" window, but the size
> of the backup file continued increasing...and apparently went on an
> on, up to the expected size of the backup. Upon checking (restore),
> the backup file was okay.
> Has anyone encountered a similar situation? Any thoughts on this?
>
> Aramid
>|||Thanks, Mike.
I did monitor the backup process via sp_who2 and also did a TSQL
backup via QA as:
BACKUP DATABASE [database_namer] TO DISK =N'F:\temp\database_name_041105' WITH INIT , NOUNLOAD , NAME =N'Database_Name backup', NOSKIP , STATS = 10, NOFORMAT
After about 1 minute, the error below shows up in QA:
=====[Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
(WrapperRead()).
Server: Msg 11, Level 16, State 1, Line 0
General network error. Check your network documentation.
10 percent backed up.
Connection Broken
=====
Running sp_who2 shows that my QA connection still exists and is
running. The backup eventually completes without any problem from
this point onwards.
I did this several times, just to check if the pattern is consistent,
and it is. I also noticed that running something else on the QA
window (after the error has showed itself) where I ran the backup
command will immediately stop the "background backup" (tantamount to
killing the spid) and execute my new command (such as sp_who2).
Note that I am using the QA on the server itself, not from some remote
machine.
Aramid
On Mon, 11 Apr 2005 00:35:02 -0700, "Mike Epprecht (SQL MVP)"
<mike@.epprecht.net> wrote:
>Hi
>Run sp_who2 during the process. You will see if the backup is still running.
>What does the job history say about the run time? Does it match the file
>datetime stamp?
>Regards
>Mike
>
>"aramid" wrote:
>> Has anyone encountered a situation where the backup process is still
>> running but Enterprise Manager already says that it was finished and
>> was successful.
>> I initiated a backup to a UNC share (but the actual drive is directly
>> attached to the server) and the backup started. I see the size of the
>> backup file increasing (via Windows Explorer) so I assumed everything
>> is okay. After about 10% was done, EM suddenly showed me the usual
>> "The backup operation has completed successfully" window, but the size
>> of the backup file continued increasing...and apparently went on an
>> on, up to the expected size of the backup. Upon checking (restore),
>> the backup file was okay.
>> Has anyone encountered a similar situation? Any thoughts on this?
>>
>> Aramid|||Dear Aramid,
I seems encounter similar problem.
At the moment, I am suspecting it is caused by MDAC 2.8 (which is installed
with XP sp2 or Win2003),
I am just asking if someone on the forum got latest MDAC 2.8 hot fix (as no
SP1 yet).
The reason why I guess so? Check out
http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
Hope it does not mislead you anyway ... Eric
"aramid" <aramid@.hotmail.com> ?
news:hpkm51hcbgo183qaop05ju4mi7s6pitrdi@.4ax.com ?...
> Thanks, Mike.
> I did monitor the backup process via sp_who2 and also did a TSQL
> backup via QA as:
> BACKUP DATABASE [database_namer] TO DISK => N'F:\temp\database_name_041105' WITH INIT , NOUNLOAD , NAME => N'Database_Name backup', NOSKIP , STATS = 10, NOFORMAT
> After about 1 minute, the error below shows up in QA:
> =====> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
> (WrapperRead()).
> Server: Msg 11, Level 16, State 1, Line 0
> General network error. Check your network documentation.
> 10 percent backed up.
> Connection Broken
> =====> Running sp_who2 shows that my QA connection still exists and is
> running. The backup eventually completes without any problem from
> this point onwards.
> I did this several times, just to check if the pattern is consistent,
> and it is. I also noticed that running something else on the QA
> window (after the error has showed itself) where I ran the backup
> command will immediately stop the "background backup" (tantamount to
> killing the spid) and execute my new command (such as sp_who2).
> Note that I am using the QA on the server itself, not from some remote
> machine.
> Aramid
>
> On Mon, 11 Apr 2005 00:35:02 -0700, "Mike Epprecht (SQL MVP)"
> <mike@.epprecht.net> wrote:
> >Hi
> >
> >Run sp_who2 during the process. You will see if the backup is still
running.
> >What does the job history say about the run time? Does it match the file
> >datetime stamp?
> >
> >Regards
> >Mike
> >
> >
> >"aramid" wrote:
> >
> >> Has anyone encountered a situation where the backup process is still
> >> running but Enterprise Manager already says that it was finished and
> >> was successful.
> >>
> >> I initiated a backup to a UNC share (but the actual drive is directly
> >> attached to the server) and the backup started. I see the size of the
> >> backup file increasing (via Windows Explorer) so I assumed everything
> >> is okay. After about 10% was done, EM suddenly showed me the usual
> >> "The backup operation has completed successfully" window, but the size
> >> of the backup file continued increasing...and apparently went on an
> >> on, up to the expected size of the backup. Upon checking (restore),
> >> the backup file was okay.
> >>
> >> Has anyone encountered a similar situation? Any thoughts on this?
> >>
> >>
> >> Aramid
> >>
>|||Thanks, Eric.
I will check this out.
Aramid
On Tue, 12 Apr 2005 18:31:26 +0800, "Eric Fung" <Pls type eric.fung
plus @.hld.com> wrote:
>Dear Aramid,
>I seems encounter similar problem.
>At the moment, I am suspecting it is caused by MDAC 2.8 (which is installed
>with XP sp2 or Win2003),
>I am just asking if someone on the forum got latest MDAC 2.8 hot fix (as no
>SP1 yet).
>The reason why I guess so? Check out
>http://support.microsoft.com/default.aspx?scid=kb;en-us;827452
>Hope it does not mislead you anyway ... Eric
>
>"aramid" <aramid@.hotmail.com> ?
>news:hpkm51hcbgo183qaop05ju4mi7s6pitrdi@.4ax.com ?...
>> Thanks, Mike.
>> I did monitor the backup process via sp_who2 and also did a TSQL
>> backup via QA as:
>> BACKUP DATABASE [database_namer] TO DISK =>> N'F:\temp\database_name_041105' WITH INIT , NOUNLOAD , NAME =>> N'Database_Name backup', NOSKIP , STATS = 10, NOFORMAT
>> After about 1 minute, the error below shows up in QA:
>> =====>> [Microsoft][ODBC SQL Server Driver][DBNETLIB]ConnectionRead
>> (WrapperRead()).
>> Server: Msg 11, Level 16, State 1, Line 0
>> General network error. Check your network documentation.
>> 10 percent backed up.
>> Connection Broken
>> =====>> Running sp_who2 shows that my QA connection still exists and is
>> running. The backup eventually completes without any problem from
>> this point onwards.
>> I did this several times, just to check if the pattern is consistent,
>> and it is. I also noticed that running something else on the QA
>> window (after the error has showed itself) where I ran the backup
>> command will immediately stop the "background backup" (tantamount to
>> killing the spid) and execute my new command (such as sp_who2).
>> Note that I am using the QA on the server itself, not from some remote
>> machine.
>> Aramid
>>
>> On Mon, 11 Apr 2005 00:35:02 -0700, "Mike Epprecht (SQL MVP)"
>> <mike@.epprecht.net> wrote:
>> >Hi
>> >
>> >Run sp_who2 during the process. You will see if the backup is still
>running.
>> >What does the job history say about the run time? Does it match the file
>> >datetime stamp?
>> >
>> >Regards
>> >Mike
>> >
>> >
>> >"aramid" wrote:
>> >
>> >> Has anyone encountered a situation where the backup process is still
>> >> running but Enterprise Manager already says that it was finished and
>> >> was successful.
>> >>
>> >> I initiated a backup to a UNC share (but the actual drive is directly
>> >> attached to the server) and the backup started. I see the size of the
>> >> backup file increasing (via Windows Explorer) so I assumed everything
>> >> is okay. After about 10% was done, EM suddenly showed me the usual
>> >> "The backup operation has completed successfully" window, but the size
>> >> of the backup file continued increasing...and apparently went on an
>> >> on, up to the expected size of the backup. Upon checking (restore),
>> >> the backup file was okay.
>> >>
>> >> Has anyone encountered a similar situation? Any thoughts on this?
>> >>
>> >>
>> >> Aramid
>> >>
>

backup problem 2

backup problem

when i try to take back from sqlserver 2000 enterprise manager it shows the error. wheni tried with NT itsays tape not found.

backup medium ::reportloerror:write failure on backup device\\.\tape0.operating system error 23(data error(cyclic reduncancy check));

pls help out in this issueeDo you have a tape drive?

You'd be better off dumping to disk and then copying to tape (faster)

Have you tried to set up a maint plan with the wizard (easiest)

BACKUP PROBLEM

I am running SQL Server 2000, Verson 8 and within enterprise manager I do a
daily backup to another system on the network (from my server - server
running Windows NT). I have recently added a new machine running Windows XP
Professional verson 2002 service pac 2. I am unable to see this system when
I try to add a device to back up to. I see all other systems (which are
running Windows 98) but not the new system. The drive has been mapped and I
seem to have no other connection problems. How do I get recognize this new
system? Any help?
djh wrote:
> I am running SQL Server 2000, Verson 8 and within enterprise manager
> I do a daily backup to another system on the network (from my server
> - server running Windows NT). I have recently added a new machine
> running Windows XP Professional verson 2002 service pac 2. I am
> unable to see this system when I try to add a device to back up to.
> I see all other systems (which are running Windows 98) but not the
> new system. The drive has been mapped and I seem to have no other
> connection problems. How do I get recognize this new system? Any
> help?
I would recommend you backup to a local drive and then copy the backup
file across the network. This should cause the backup to occur much
faster.
David Gugick
Imceda Software
www.imceda.com

Thursday, March 8, 2012

Backup Plan

I have been using enterprise manager to run backups. I backup up all the
db's system and user under one maitenance plan. I have it set to remove the
bakups after 7 days. The backups are removed but not the tranaction log
backups. Why is this and how can I resolve this so I don't have to manaully
delete delete the tranaction log backups.Hi Geroge,
Thanks for posting.
I noticed that you posted another post abouit MSDE backup in newsgroup.
From that post, I noticed that you are using simple recovery mode. If so,
there will be no transaction log be backed up.
Regarding backup plan, please refer to:
<http://msdn2.microsoft.com/en-us/library/ms191239.aspx>
Hope this helps.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Backup Plan
>>thread-index: AcbR8vM/1aiy35xvSLanR5zrbdipVg==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>Subject: Backup Plan
>>Date: Wed, 6 Sep 2006 13:28:02 -0700
>>Lines: 5
>>Message-ID: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444422
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>I have been using enterprise manager to run backups. I backup up all the
>>db's system and user under one maitenance plan. I have it set to remove
the
>>bakups after 7 days. The backups are removed but not the tranaction log
>>backups. Why is this and how can I resolve this so I don't have to
manaully
>>delete delete the tranaction log backups.|||The other post was for an MSDE database on as erver. This post isfor one of
my SQL 2000 Servers using Enterprise manager.
"Vincent Xu [MSFT]" wrote:
> Hi Geroge,
> Thanks for posting.
> I noticed that you posted another post abouit MSDE backup in newsgroup.
> From that post, I noticed that you are using simple recovery mode. If so,
> there will be no transaction log be backed up.
> Regarding backup plan, please refer to:
> <http://msdn2.microsoft.com/en-us/library/ms191239.aspx>
> Hope this helps.
>
> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ======================================================> Get Secure! - www.microsoft.com/security
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties,and confers no rights.
> ======================================================>
> --
> >>Thread-Topic: Backup Plan
> >>thread-index: AcbR8vM/1aiy35xvSLanR5zrbdipVg==> >>X-WBNR-Posting-Host: 209.244.152.162
> >>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=> <georgedschneider@.news.postalias>
> >>Subject: Backup Plan
> >>Date: Wed, 6 Sep 2006 13:28:02 -0700
> >>Lines: 5
> >>Message-ID: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="Utf-8"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>Content-Class: urn:content-classes:message
> >>Importance: normal
> >>Priority: normal
> >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: TK2MSFTNGXA01.phx.gbl
> >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444422
> >>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>I have been using enterprise manager to run backups. I backup up all the
> >>db's system and user under one maitenance plan. I have it set to remove
> the
> >>bakups after 7 days. The backups are removed but not the tranaction log
> >>backups. Why is this and how can I resolve this so I don't have to
> manaully
> >>delete delete the tranaction log backups.
> >>
>|||George Schneider wrote:
> I have been using enterprise manager to run backups. I backup up all the
> db's system and user under one maitenance plan. I have it set to remove the
> bakups after 7 days. The backups are removed but not the tranaction log
> backups. Why is this and how can I resolve this so I don't have to manaully
> delete delete the tranaction log backups.
You say you're backing up ALL databases within a single maintenance
plan? Are any of these databases in Simple recovery mode? If so, then
transaction log backups are not possible for those DB's, and that is
likely causing the transaction log part of your maintenance plan to
fail, preventing it from running the deletion step.
Check the job history and the maintenance plan history for error messages.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||how would I change the recovery mode and what should I change the recovery
mode to if I wanted to back up the transaction logs. Wouldn't one need the
transaction logs in a disaster recovery scenario.
"Tracy McKibben" wrote:
> George Schneider wrote:
> > I have been using enterprise manager to run backups. I backup up all the
> > db's system and user under one maitenance plan. I have it set to remove the
> > bakups after 7 days. The backups are removed but not the tranaction log
> > backups. Why is this and how can I resolve this so I don't have to manaully
> > delete delete the tranaction log backups.
> You say you're backing up ALL databases within a single maintenance
> plan? Are any of these databases in Simple recovery mode? If so, then
> transaction log backups are not possible for those DB's, and that is
> likely causing the transaction log part of your maintenance plan to
> fail, preventing it from running the deletion step.
> Check the job history and the maintenance plan history for error messages.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||George Schneider wrote:
> how would I change the recovery mode and what should I change the recovery
> mode to if I wanted to back up the transaction logs. Wouldn't one need the
> transaction logs in a disaster recovery scenario.
>
Refer to Books Online for more info on recovery models. There's not a
blanket answer to your question, you'll have to decide what is
appropriate for your needs.
--
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi Geroge,
Thanks for your reply.
To switch recovery mode, please refer to following article:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_8ik3.asp>
Actually, MSDE & SQL 2000 is the same by the nature. Therefore, you can
refer to my reply in the "MSDE Backup " Thread. For your convenience, I
paste it here
==================If you perform full backup regularly, you can just restore the latest
backup when disaster occurs.
The transaction log is a serial record of all the transactions that have
been performed against the database since the transaction log was last
backed up. With transaction log backups, you can recover the database to a
specific point in time (for example, prior to entering unwanted data), or
to the point of failure. Check following article:
<http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
d_bkprst_565v.asp>
How to: Create a Transaction Log Backup (Transact-SQL)
<http://msdn2.microsoft.com/en-us/library/ms191284.aspx>
How to: Restore to the Point of Failure (Transact-SQL)
<http://msdn2.microsoft.com/en-us/library/ms175093.aspx>
How to: Restore to a Point in Time (Transact-SQL)
<http://msdn2.microsoft.com/en-us/library/ms179451.aspx>
==================
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Thread-Topic: Backup Plan
>>thread-index: AcbSsnohDScBOuYQRe6ilXpgrPC+Dg==>>X-WBNR-Posting-Host: 209.244.152.162
>>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=<georgedschneider@.news.postalias>
>>References: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
<45002CE9.5010006@.realsqlguy.com>
>>Subject: Re: Backup Plan
>>Date: Thu, 7 Sep 2006 12:19:02 -0700
>>Lines: 27
>>Message-ID: <784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
>>MIME-Version: 1.0
>>Content-Type: text/plain;
>> charset="Utf-8"
>>Content-Transfer-Encoding: 7bit
>>X-Newsreader: Microsoft CDO for Windows 2000
>>Content-Class: urn:content-classes:message
>>Importance: normal
>>Priority: normal
>>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
>>Newsgroups: microsoft.public.sqlserver.server
>>Path: TK2MSFTNGXA01.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444545
>>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>how would I change the recovery mode and what should I change the
recovery
>>mode to if I wanted to back up the transaction logs. Wouldn't one need
the
>>transaction logs in a disaster recovery scenario.
>>"Tracy McKibben" wrote:
>> George Schneider wrote:
>> > I have been using enterprise manager to run backups. I backup up all
the
>> > db's system and user under one maitenance plan. I have it set to
remove the
>> > bakups after 7 days. The backups are removed but not the tranaction
log
>> > backups. Why is this and how can I resolve this so I don't have to
manaully
>> > delete delete the tranaction log backups.
>> You say you're backing up ALL databases within a single maintenance
>> plan? Are any of these databases in Simple recovery mode? If so, then
>> transaction log backups are not possible for those DB's, and that is
>> likely causing the transaction log part of your maintenance plan to
>> fail, preventing it from running the deletion step.
>> Check the job history and the maintenance plan history for error
messages.
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com
>>|||I guess I'm a little confussed here. I thought transaction logs were
required to restore to make the db full operational again. What purpose do
transaction logs perform then if you do not have to restore then in a
disaster sceanrio?
"Vincent Xu [MSFT]" wrote:
> Hi Geroge,
> Thanks for your reply.
> To switch recovery mode, please refer to following article:
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_8ik3.asp>
> Actually, MSDE & SQL 2000 is the same by the nature. Therefore, you can
> refer to my reply in the "MSDE Backup " Thread. For your convenience, I
> paste it here
> ==================> If you perform full backup regularly, you can just restore the latest
> backup when disaster occurs.
> The transaction log is a serial record of all the transactions that have
> been performed against the database since the transaction log was last
> backed up. With transaction log backups, you can recover the database to a
> specific point in time (for example, prior to entering unwanted data), or
> to the point of failure. Check following article:
> <http://msdn.microsoft.com/library/default.asp?url=/library/en-us/adminsql/a
> d_bkprst_565v.asp>
> How to: Create a Transaction Log Backup (Transact-SQL)
> <http://msdn2.microsoft.com/en-us/library/ms191284.aspx>
> How to: Restore to the Point of Failure (Transact-SQL)
> <http://msdn2.microsoft.com/en-us/library/ms175093.aspx>
> How to: Restore to a Point in Time (Transact-SQL)
> <http://msdn2.microsoft.com/en-us/library/ms179451.aspx>
> ==================> Best regards,
> Vincent Xu
> Microsoft Online Partner Support
> ======================================================> Get Secure! - www.microsoft.com/security
> ======================================================> When responding to posts, please "Reply to Group" via your newsreader so
> that others
> may learn and benefit from this issue.
> ======================================================> This posting is provided "AS IS" with no warranties,and confers no rights.
> ======================================================>
> --
> >>Thread-Topic: Backup Plan
> >>thread-index: AcbSsnohDScBOuYQRe6ilXpgrPC+Dg==> >>X-WBNR-Posting-Host: 209.244.152.162
> >>From: =?Utf-8?B?R2VvcmdlIFNjaG5laWRlcg==?=> <georgedschneider@.news.postalias>
> >>References: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
> <45002CE9.5010006@.realsqlguy.com>
> >>Subject: Re: Backup Plan
> >>Date: Thu, 7 Sep 2006 12:19:02 -0700
> >>Lines: 27
> >>Message-ID: <784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
> >>MIME-Version: 1.0
> >>Content-Type: text/plain;
> >> charset="Utf-8"
> >>Content-Transfer-Encoding: 7bit
> >>X-Newsreader: Microsoft CDO for Windows 2000
> >>Content-Class: urn:content-classes:message
> >>Importance: normal
> >>Priority: normal
> >>X-MimeOLE: Produced By Microsoft MimeOLE V6.00.3790.1830
> >>Newsgroups: microsoft.public.sqlserver.server
> >>Path: TK2MSFTNGXA01.phx.gbl
> >>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444545
> >>NNTP-Posting-Host: TK2MSFTNGXA01.phx.gbl 10.40.2.250
> >>X-Tomcat-NG: microsoft.public.sqlserver.server
> >>
> >>how would I change the recovery mode and what should I change the
> recovery
> >>mode to if I wanted to back up the transaction logs. Wouldn't one need
> the
> >>transaction logs in a disaster recovery scenario.
> >>
> >>"Tracy McKibben" wrote:
> >>
> >> George Schneider wrote:
> >> > I have been using enterprise manager to run backups. I backup up all
> the
> >> > db's system and user under one maitenance plan. I have it set to
> remove the
> >> > bakups after 7 days. The backups are removed but not the tranaction
> log
> >> > backups. Why is this and how can I resolve this so I don't have to
> manaully
> >> > delete delete the tranaction log backups.
> >>
> >> You say you're backing up ALL databases within a single maintenance
> >> plan? Are any of these databases in Simple recovery mode? If so, then
> >> transaction log backups are not possible for those DB's, and that is
> >> likely causing the transaction log part of your maintenance plan to
> >> fail, preventing it from running the deletion step.
> >>
> >> Check the job history and the maintenance plan history for error
> messages.
> >>
> >>
> >> --
> >> Tracy McKibben
> >> MCDBA
> >> http://www.realsqlguy.com
> >>
> >>
>|||George Schneider wrote:
> I guess I'm a little confussed here. I thought transaction logs were
> required to restore to make the db full operational again. What purpose do
> transaction logs perform then if you do not have to restore then in a
> disaster sceanrio?
>
That all depends on the recovery model that you're using. The
transaction log records everything that happens in the database. Let's
say you delete a million rows from a table, and partway through that
delete operation, it fails. The delete will be rolled back, none of the
records will be deleted. In order to perform that rollback, SQL has to
record somewhere what records it's deleting - that's what the
transaction log is used for. The transaction log will need to be large
enough to hold this entire transaction. If the delete is successful,
the transaction is committed, and the events recorded in the transaction
log are flagged as committed.
In SIMPLE mode, SQL will issue periodic "checkpoint" instructions that
will flush out those committed events, and the space that was previously
used for them will be available for future transactions to use. The
transaction log will not shrink at this point, it will remain the same
physical size. Once the committed events have been flushed, they are
permanently written to the database, and the only way to "undo" them is
to restore from a full backup taken prior to the delete, probably the
backup from the night before.
In FULL or BULK-LOGGED modes, these committed events are not
automatically flushed out of the transaction log. They are flushed when
you issue a BACKUP LOG command. This gives you the ability to recover
the database to virtually any point in time. For sake of example, let's
assume you're taking transaction log backups every 5 minutes. You
delete the million rows, and an hour later, decide that you need those
rows back. You simply restore the previous full backup, and then
restore each transaction log backup up to the point where you issued the
delete. You lose an hour's worth of work instead of a full day.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Hi All,
Tracy provided great information. Well done!.
George, if youi still have anything unclear, please feel free to reply.
I'll try my best to be of assistance.
Best regards,
Vincent Xu
Microsoft Online Partner Support
======================================================Get Secure! - www.microsoft.com/security
======================================================When responding to posts, please "Reply to Group" via your newsreader so
that others
may learn and benefit from this issue.
======================================================This posting is provided "AS IS" with no warranties,and confers no rights.
======================================================>>Message-ID: <4501C028.9030606@.realsqlguy.com>
>>Date: Fri, 08 Sep 2006 14:10:32 -0500
>>From: Tracy McKibben <tracy@.realsqlguy.com>
>>User-Agent: Thunderbird 1.5.0.5 (Windows/20060719)
>>MIME-Version: 1.0
>>Subject: Re: Backup Plan
>>References: <B4DE2B3C-D601-4D03-80A0-645853CF42DA@.microsoft.com>
<45002CE9.5010006@.realsqlguy.com>
<784A7375-55CC-442D-8E6B-F86A5DE784DD@.microsoft.com>
<$tCEyDv0GHA.5212@.TK2MSFTNGXA01.phx.gbl>
<BFF0BF45-3B81-4813-98CD-EBCF3828C52D@.microsoft.com>
>>In-Reply-To: <BFF0BF45-3B81-4813-98CD-EBCF3828C52D@.microsoft.com>
>>Content-Type: text/plain; charset=UTF-8; format=flowed
>>Content-Transfer-Encoding: 7bit
>>Newsgroups: microsoft.public.sqlserver.server
>>NNTP-Posting-Host: ncsgate.ncspearson.com 12.106.111.10
>>Lines: 1
>>Path: TK2MSFTNGXA01.phx.gbl!TK2MSFTNGP01.phx.gbl!TK2MSFTNGP03.phx.gbl
>>Xref: TK2MSFTNGXA01.phx.gbl microsoft.public.sqlserver.server:444677
>>X-Tomcat-NG: microsoft.public.sqlserver.server
>>George Schneider wrote:
>> I guess I'm a little confussed here. I thought transaction logs were
>> required to restore to make the db full operational again. What
purpose do
>> transaction logs perform then if you do not have to restore then in a
>> disaster sceanrio?
>>
>>That all depends on the recovery model that you're using. The
>>transaction log records everything that happens in the database. Let's
>>say you delete a million rows from a table, and partway through that
>>delete operation, it fails. The delete will be rolled back, none of the
>>records will be deleted. In order to perform that rollback, SQL has to
>>record somewhere what records it's deleting - that's what the
>>transaction log is used for. The transaction log will need to be large
>>enough to hold this entire transaction. If the delete is successful,
>>the transaction is committed, and the events recorded in the transaction
>>log are flagged as committed.
>>In SIMPLE mode, SQL will issue periodic "checkpoint" instructions that
>>will flush out those committed events, and the space that was previously
>>used for them will be available for future transactions to use. The
>>transaction log will not shrink at this point, it will remain the same
>>physical size. Once the committed events have been flushed, they are
>>permanently written to the database, and the only way to "undo" them is
>>to restore from a full backup taken prior to the delete, probably the
>>backup from the night before.
>>In FULL or BULK-LOGGED modes, these committed events are not
>>automatically flushed out of the transaction log. They are flushed when
>>you issue a BACKUP LOG command. This gives you the ability to recover
>>the database to virtually any point in time. For sake of example, let's
>>assume you're taking transaction log backups every 5 minutes. You
>>delete the million rows, and an hour later, decide that you need those
>>rows back. You simply restore the previous full backup, and then
>>restore each transaction log backup up to the point where you issued the
>>delete. You lose an hour's worth of work instead of a full day.
>>
>>--
>>Tracy McKibben
>>MCDBA
>>http://www.realsqlguy.com|||If I understand you correctly then if I'm in Simple recovery mode for my
databases then the maitenance plan that I'm using to include tranaction logs
is not needed. Can you explain what is happening in simple recovery mode to
why tranaction log backups are not needed.
"Tracy McKibben" wrote:
> George Schneider wrote:
> > I guess I'm a little confussed here. I thought transaction logs were
> > required to restore to make the db full operational again. What purpose do
> > transaction logs perform then if you do not have to restore then in a
> > disaster sceanrio?
> >
> That all depends on the recovery model that you're using. The
> transaction log records everything that happens in the database. Let's
> say you delete a million rows from a table, and partway through that
> delete operation, it fails. The delete will be rolled back, none of the
> records will be deleted. In order to perform that rollback, SQL has to
> record somewhere what records it's deleting - that's what the
> transaction log is used for. The transaction log will need to be large
> enough to hold this entire transaction. If the delete is successful,
> the transaction is committed, and the events recorded in the transaction
> log are flagged as committed.
> In SIMPLE mode, SQL will issue periodic "checkpoint" instructions that
> will flush out those committed events, and the space that was previously
> used for them will be available for future transactions to use. The
> transaction log will not shrink at this point, it will remain the same
> physical size. Once the committed events have been flushed, they are
> permanently written to the database, and the only way to "undo" them is
> to restore from a full backup taken prior to the delete, probably the
> backup from the night before.
> In FULL or BULK-LOGGED modes, these committed events are not
> automatically flushed out of the transaction log. They are flushed when
> you issue a BACKUP LOG command. This gives you the ability to recover
> the database to virtually any point in time. For sake of example, let's
> assume you're taking transaction log backups every 5 minutes. You
> delete the million rows, and an hour later, decide that you need those
> rows back. You simply restore the previous full backup, and then
> restore each transaction log backup up to the point where you issued the
> delete. You lose an hour's worth of work instead of a full day.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||George Schneider wrote:
> If I understand you correctly then if I'm in Simple recovery mode for my
> databases then the maitenance plan that I'm using to include tranaction logs
> is not needed. Can you explain what is happening in simple recovery mode to
> why tranaction log backups are not needed.
>
In Simple mode, committed transactions are automatically purged from the
log file by SQL's checkpointing process, so there's nothing in the log
for you to back up.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Correct me if I'm wrong on this one concerning recover modes. In sim[ple
recovery mode the tranactions are recorded in the log but as soon as they are
applied to the databse sql purges them from the log so in essece the log is
empty in this recovery mode.
If I'm running simple recovery then I should change the maitenace plan
backup configuration not to backup the tranaction logs correct?
Is it alright to have one maintenace plan to backup all the db's both system
and user?
"Tracy McKibben" wrote:
> George Schneider wrote:
> > If I understand you correctly then if I'm in Simple recovery mode for my
> > databases then the maitenance plan that I'm using to include tranaction logs
> > is not needed. Can you explain what is happening in simple recovery mode to
> > why tranaction log backups are not needed.
> >
> In Simple mode, committed transactions are automatically purged from the
> log file by SQL's checkpointing process, so there's nothing in the log
> for you to back up.
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||George Schneider wrote:
> Correct me if I'm wrong on this one concerning recover modes. In sim[ple
> recovery mode the tranactions are recorded in the log but as soon as they are
> applied to the databse sql purges them from the log so in essece the log is
> empty in this recovery mode.
Correct
> If I'm running simple recovery then I should change the maitenace plan
> backup configuration not to backup the tranaction logs correct?
Correct. You cannot run a transaction log backup against a Simple mode
database. However, you should consider if the risk of data loss, and
decide if Simple mode is right for your needs.
> Is it alright to have one maintenace plan to backup all the db's both system
> and user?
Yes
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||Now I'm back to square one. I thought the problem with not removing the
tranaction backups was becuase I was running backups on db's that were in
simple recovery mode. This is not the case. I have some db's in simple and
some in full recovery mode. The one's in simple recovery mode are fine and
do not have a tranaction log backup created. Its the four pr have db's that
are in full recovery mode. The tranaction logs backups are not being deleted
like the bak files from the backup directory. What can be causing this and
how cna I resolve this?
"Tracy McKibben" wrote:
> George Schneider wrote:
> > Correct me if I'm wrong on this one concerning recover modes. In sim[ple
> > recovery mode the tranactions are recorded in the log but as soon as they are
> > applied to the databse sql purges them from the log so in essece the log is
> > empty in this recovery mode.
> Correct
> > If I'm running simple recovery then I should change the maitenace plan
> > backup configuration not to backup the tranaction logs correct?
> Correct. You cannot run a transaction log backup against a Simple mode
> database. However, you should consider if the risk of data loss, and
> decide if Simple mode is right for your needs.
> > Is it alright to have one maintenace plan to backup all the db's both system
> > and user?
> Yes
>
> --
> Tracy McKibben
> MCDBA
> http://www.realsqlguy.com
>|||Don't use the same maint plans for the databases in full vs. simple mode. If you do, maint plan is a
little bit daft and doesn't remove old tlog backups for the ones in full mode.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"George Schneider" <georgedschneider@.news.postalias> wrote in message
news:F2886839-6488-47B7-8AE6-4C0E2E7294D6@.microsoft.com...
> Now I'm back to square one. I thought the problem with not removing the
> tranaction backups was becuase I was running backups on db's that were in
> simple recovery mode. This is not the case. I have some db's in simple and
> some in full recovery mode. The one's in simple recovery mode are fine and
> do not have a tranaction log backup created. Its the four pr have db's that
> are in full recovery mode. The tranaction logs backups are not being deleted
> like the bak files from the backup directory. What can be causing this and
> how cna I resolve this?
> "Tracy McKibben" wrote:
>> George Schneider wrote:
>> > Correct me if I'm wrong on this one concerning recover modes. In sim[ple
>> > recovery mode the tranactions are recorded in the log but as soon as they are
>> > applied to the databse sql purges them from the log so in essece the log is
>> > empty in this recovery mode.
>> Correct
>> > If I'm running simple recovery then I should change the maitenace plan
>> > backup configuration not to backup the tranaction logs correct?
>> Correct. You cannot run a transaction log backup against a Simple mode
>> database. However, you should consider if the risk of data loss, and
>> decide if Simple mode is right for your needs.
>> > Is it alright to have one maintenace plan to backup all the db's both system
>> > and user?
>> Yes
>>
>> --
>> Tracy McKibben
>> MCDBA
>> http://www.realsqlguy.com|||George Schneider wrote:
> Now I'm back to square one. I thought the problem with not removing the
> tranaction backups was becuase I was running backups on db's that were in
> simple recovery mode. This is not the case. I have some db's in simple and
> some in full recovery mode. The one's in simple recovery mode are fine and
> do not have a tranaction log backup created. Its the four pr have db's that
> are in full recovery mode. The tranaction logs backups are not being deleted
> like the bak files from the backup directory. What can be causing this and
> how cna I resolve this?
>
The problem is that you're doing all of this from ONE maintenance plan,
and have told the maintenance plan to do transaction log backups of ALL
your databases. When it attempts to do that, it fails on the ones that
are in Simple mode, and never gets to the cleanup step where it deletes
the old files.
Create two seperate maintenance plans, one for the Simple mode
databases, one for the others. OR, better yet, dump the maintenance
plan altogether and use scripts to perform these tasks. Here is one:
http://realsqlguy.com/serendipity/archives/11-Send-In-Backup!.html
Tracy McKibben
MCDBA
http://www.realsqlguy.com

Backup Plan

Backup or Restore from Enterprise Manager.

We've added a new disk to our server.
When we click through the SQL Server Backup, to select
the "Backup Device Location", the new drive is not
available. The only drive that is available is the one
that existed when SQL was installed.
The same is true for restores.
Any ideas?
TIA,
Sean.
Hi,
Execute the below command from Query Analyzer;
xp_fixeddrives
If you are not able to see the new drive, Check for the existence of that
drive from Windows Explorer, if not contact your system administrator.
Thanks
Hari
MCDBA
"Sean Irving" <sbirving@.hotmail.com> wrote in message
news:178ae01c41dfe$8f99da60$a601280a@.phx.gbl...
> We've added a new disk to our server.
> When we click through the SQL Server Backup, to select
> the "Backup Device Location", the new drive is not
> available. The only drive that is available is the one
> that existed when SQL was installed.
> The same is true for restores.
> Any ideas?
> TIA,
> Sean.
>
|||If SQL Server is clustered, you have to add a dependency (SQL Server has to
depend on the disk). Also make sure you've restarted Enterprise Manager
since the disk was added, it may not have refreshed with the new
information.
Cindy Gross, MCDBA, MCSE
http://cindygross.tripod.com
This posting is provided "AS IS" with no warranties, and confers no rights.

Backup options in maintenance plan

I am trying to create a maintenance plan to backup my SQL Desktop Engine
(MSDE 2000) databases using enterprise manager. Everything goes fine in the
maintenance wizard until I get to the "Specify Backup Disk Directory"
screen. On this screen, the drop down box next to "Remove files older
than:" is blank, where it would normally say days, weeks, etc. If I finish
making the plan, when I go back in the path is gone as well as the schedule.
This is only happening on our MSDE SP3 installations. We also have SQL
Server 2000 SP3 and MSDE 7 installed in our organization on different
servers. They do not have this problem. I found someone else with this
problem on the internet at http://www.webservertalk.com/t1108273.html but the
solution describes how to execute an existing maintenance plan. I want to
know why I can't create one using EM.
OK, I found the solution for this (at least the workaround until Microsoft
fixes the problem). You need to add a registry entry to the server running
MSDE. Details can be found at:
http://www.webservertalk.com/t739692.html
"Josh Rolfe" <someone@.microsoft.com> wrote in message
news:O2xOX$JNFHA.1096@.tk2msftngp13.phx.gbl...
>I am trying to create a maintenance plan to backup my SQL Desktop Engine
>(MSDE 2000) databases using enterprise manager. Everything goes fine in
>the maintenance wizard until I get to the "Specify Backup Disk Directory"
>screen. On this screen, the drop down box next to "Remove files older
>than:" is blank, where it would normally say days, weeks, etc. If I finish
>making the plan, when I go back in the path is gone as well as the
>schedule. This is only happening on our MSDE SP3 installations. We also
>have SQL Server 2000 SP3 and MSDE 7 installed in our organization on
>different servers. They do not have this problem. I found someone else
>with this problem on the internet at http://www.webservertalk.com/t1108273.html
>but the solution describes how to execute an existing maintenance plan. I
>want to know why I can't create one using EM.
>

Wednesday, March 7, 2012

Backup on Network/Mapped drives Using Enterprise Manager

Dear all,
This is My Query step by step description:
1. I want to take backup of sql server databases on network drives(mapped
drives) Using Enterprise Manager tool only.
2. I am getting network drives list on some machines not on all machines
when I opened Backup Device location window in Enterprise Manager.
3. My SQL Server Services are also configured with domain administrator
account only.
Please let me know the way to get list of network drives when I am taking
backup using Enterprise Manager.(I know the way to work with SQL Query
Analyzer).
Thanks in Advance
Regards
Ravisrikrishna
You can't.
We've already explained this in your earlier post.
SQL EM only looks at physical drives.
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:%23jH3N$owFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>
|||Hi
You need to specify UNC names and not use mapped drives.
John
"Lokesh Bhatnagar" wrote:

> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>
|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default...b;en-us;555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:%23jH3N$owFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>

Backup on Network/Mapped drives Using Enterprise Manager

Dear all,
This is My Query step by step description:
1. I want to take backup of sql server databases on network drives(mapped
drives) Using Enterprise Manager tool only.
2. I am getting network drives list on some machines not on all machines
when I opened Backup Device location window in Enterprise Manager.
3. My SQL Server Services are also configured with domain administrator
account only.
Please let me know the way to get list of network drives when I am taking
backup using Enterprise Manager.(I know the way to work with SQL Query
Analyzer).
Thanks in Advance
Regards
RavisrikrishnaYou can't.
We've already explained this in your earlier post.
SQL EM only looks at physical drives.
--
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:%23jH3N$owFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>|||Hi
You need to specify UNC names and not use mapped drives.
John
"Lokesh Bhatnagar" wrote:
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/default.aspx?scid=kb;en-us;555128
--
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:%23jH3N$owFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>

Backup on Network/Mapped drives Using Enterprise Manager

Dear all,
This is My Query step by step description:
1. I want to take backup of sql server databases on network drives(mapped
drives) Using Enterprise Manager tool only.
2. I am getting network drives list on some machines not on all machines
when I opened Backup Device location window in Enterprise Manager.
3. My SQL Server Services are also configured with domain administrator
account only.
Please let me know the way to get list of network drives when I am taking
backup using Enterprise Manager.(I know the way to work with SQL Query
Analyzer).
Thanks in Advance
Regards
RavisrikrishnaYou can't.
We've already explained this in your earlier post.
SQL EM only looks at physical drives.
Nik Marshall-Blank MCSD/MCDBA
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:%23jH3N$owFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>|||Hi
You need to specify UNC names and not use mapped drives.
John
"Lokesh Bhatnagar" wrote:

> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>|||HowTo: Backup to UNC name using Database Maintenance Wizard
http://support.microsoft.com/defaul...kb;en-us;555128
Geoff N. Hiten
Senior Database Administrator
Microsoft SQL Server MVP
"Lokesh Bhatnagar" <lokesh@.webdunia.com> wrote in message
news:%23jH3N$owFHA.3860@.TK2MSFTNGP09.phx.gbl...
> Dear all,
> This is My Query step by step description:
> 1. I want to take backup of sql server databases on network drives(mapped
> drives) Using Enterprise Manager tool only.
> 2. I am getting network drives list on some machines not on all machines
> when I opened Backup Device location window in Enterprise Manager.
> 3. My SQL Server Services are also configured with domain administrator
> account only.
> Please let me know the way to get list of network drives when I am taking
> backup using Enterprise Manager.(I know the way to work with SQL Query
> Analyzer).
> Thanks in Advance
> Regards
> Ravisrikrishna
>
>

Friday, February 24, 2012

backup not working

In Enterprise Manager, I created a schedule to backup the
database. But the database is not being backed up.
I am able to manually take backup of my database.
What is the best option to take complete backup.
Should I append to the media or overwrite?
Thank you in advance,
LindaDo you have Server Agent runing?
Bojidar Alexnadrov|||Hi,
Check "SQLServer Agent" service is running. If not start the service and
check if the schedule is working.
What is the best option to take complete backup. Should I append to the
media or overwrite?
Both options are fine, But due to hard disk space limitations mostly people
choose the overwrite options (WITH INIT).
But ensure that old backup file is copied / moved to a remote hard disk or
Tape before overwriting.
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
> Thank you in advance,
> Linda|||Linda,
Answers inline.
Linda wrote:
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
Is SQL Server Agent running? Do you get any errors? What does the
history for the job say?
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
Depends. If backing up to disk I like to create a new file each time and
not overwrite the previous, or append. If going directly to tape you may
wish to append. What are you doing?
--
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html|||Hari,
Thank you very much for your reply!
It works now.
-Linda
>--Original Message--
>Hi,
>Check "SQLServer Agent" service is running. If not start
the service and
>check if the schedule is working.
>What is the best option to take complete backup. Should
I append to the
>media or overwrite?
>Both options are fine, But due to hard disk space
limitations mostly people
>choose the overwrite options (WITH INIT).
>But ensure that old backup file is copied / moved to a
remote hard disk or
>Tape before overwriting.
>Thanks
>Hari
>MCDBA
>
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message
>news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
>> In Enterprise Manager, I created a schedule to backup
the
>> database. But the database is not being backed up.
>> I am able to manually take backup of my database.
>> What is the best option to take complete backup.
>> Should I append to the media or overwrite?
>> Thank you in advance,
>> Linda
>
>.
>|||Mark,
Thank you very much for your reply! The agent wasn't up.
It works now.
-Linda
>--Original Message--
>Linda,
>Answers inline.
>
>Linda wrote:
>> In Enterprise Manager, I created a schedule to backup
the
>> database. But the database is not being backed up.
>> I am able to manually take backup of my database.
>Is SQL Server Agent running? Do you get any errors? What
does the
>history for the job say?
>> What is the best option to take complete backup.
>> Should I append to the media or overwrite?
>Depends. If backing up to disk I like to create a new
file each time and
>not overwrite the previous, or append. If going directly
to tape you may
>wish to append. What are you doing?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>|||Bojidar,
Thank you very much for your reply! The Agent wasn't
running. It works now.
-Linda
>--Original Message--
>Do you have Server Agent runing?
>Bojidar Alexnadrov
>
>.
>

backup not working

In Enterprise Manager, I created a schedule to backup the
database. But the database is not being backed up.
I am able to manually take backup of my database.
What is the best option to take complete backup.
Should I append to the media or overwrite?
Thank you in advance,
Linda
Do you have Server Agent runing?
Bojidar Alexnadrov
|||Hi,
Check "SQLServer Agent" service is running. If not start the service and
check if the schedule is working.
What is the best option to take complete backup. Should I append to the
media or overwrite?
Both options are fine, But due to hard disk space limitations mostly people
choose the overwrite options (WITH INIT).
But ensure that old backup file is copied / moved to a remote hard disk or
Tape before overwriting.
Thanks
Hari
MCDBA
"Linda" <anonymous@.discussions.microsoft.com> wrote in message
news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
> What is the best option to take complete backup.
> Should I append to the media or overwrite?
> Thank you in advance,
> Linda
|||Linda,
Answers inline.
Linda wrote:
> In Enterprise Manager, I created a schedule to backup the
> database. But the database is not being backed up.
> I am able to manually take backup of my database.
Is SQL Server Agent running? Do you get any errors? What does the
history for the job say?

> What is the best option to take complete backup.
> Should I append to the media or overwrite?
Depends. If backing up to disk I like to create a new file each time and
not overwrite the previous, or append. If going directly to tape you may
wish to append. What are you doing?
Mark Allison, SQL Server MVP
http://www.markallison.co.uk
Looking for a SQL Server replication book?
http://www.nwsu.com/0974973602.html
|||Hari,
Thank you very much for your reply!
It works now.
-Linda

>--Original Message--
>Hi,
>Check "SQLServer Agent" service is running. If not start
the service and
>check if the schedule is working.
>What is the best option to take complete backup. Should
I append to the
>media or overwrite?
>Both options are fine, But due to hard disk space
limitations mostly people
>choose the overwrite options (WITH INIT).
>But ensure that old backup file is copied / moved to a
remote hard disk or
>Tape before overwriting.
>Thanks
>Hari
>MCDBA
>
>
>"Linda" <anonymous@.discussions.microsoft.com> wrote in
message[vbcol=seagreen]
>news:1931e01c44ca6$7bc051a0$a501280a@.phx.gbl...
the
>
>.
>
|||Mark,
Thank you very much for your reply! The agent wasn't up.
It works now.
-Linda
[vbcol=seagreen]
>--Original Message--
>Linda,
>Answers inline.
>
>Linda wrote:
the
>Is SQL Server Agent running? Do you get any errors? What
does the
>history for the job say?
>
>Depends. If backing up to disk I like to create a new
file each time and
>not overwrite the previous, or append. If going directly
to tape you may
>wish to append. What are you doing?
>--
>Mark Allison, SQL Server MVP
>http://www.markallison.co.uk
>Looking for a SQL Server replication book?
>http://www.nwsu.com/0974973602.html
>.
>
|||Bojidar,
Thank you very much for your reply! The Agent wasn't
running. It works now.
-Linda

>--Original Message--
>Do you have Server Agent runing?
>Bojidar Alexnadrov
>
>.
>

Thursday, March 29, 2012

Backup strategy

Tuesday, March 27, 2012

Backup SQL database without using Enterprise Manager?

Sunday, March 25, 2012

Backup settings for server registrations in SQL Server Enterprise

Monday, March 19, 2012

Backup Question

Sunday, March 11, 2012

backup problem 2

backup problem 2

backup problem 2

backup problem

BACKUP PROBLEM

Thursday, March 8, 2012

Backup Plan

Backup Plan

Backup or Restore from Enterprise Manager.

Backup options in maintenance plan

Wednesday, March 7, 2012

Backup on Network/Mapped drives Using Enterprise Manager

Backup on Network/Mapped drives Using Enterprise Manager

Backup on Network/Mapped drives Using Enterprise Manager

Friday, February 24, 2012

backup not working

backup not working

sql backup files

Blog Archive

About Me