Backup to Network Drive: t-sql

Showing posts with label t-sql. Show all posts

Thursday, March 8, 2012

backups...

Hello all I have this T-SQL string.
Use master
Exec sp_addumpdevice 'disk', 'dbEvents_backup',
'\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
Backup Database dbEvents to dbEvents_backup
Exec sp_dropdevice 'dbEvents_backup'
I have two questions
When the backup runs it isn't writing over the existing backup. It
seems to be appending the information.
Secondly, is it this an effective approach in:
adding the dumpdevice
backing it up
and then deleting the device
thanksvncntj@.hotmail.com wrote:
> Hello all I have this T-SQL string.
>
> Use master
> Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> Backup Database dbEvents to dbEvents_backup
> Exec sp_dropdevice 'dbEvents_backup'
>
> I have two questions
> When the backup runs it isn't writing over the existing backup. It
> seems to be appending the information.
> Secondly, is it this an effective approach in:
> adding the dumpdevice
> backing it up
> and then deleting the device
> thanks
>
You can skip the creation/deletion of the backup device by using TO DISK
in your backup command. To overwrite the existing backup, add WITH INIT
to the backup command. See Books Online for details.
Tracy McKibben
MCDBA
http://www.realsqlguy.com|||This is a multi-part message in MIME format.
--020001090101040705090406
Content-Type: text/plain; charset=ISO-8859-1; format=flowed
Content-Transfer-Encoding: 8bit
vncntj@.hotmail.com wrote:
> Hello all I have this T-SQL string.
>
> Use master
> Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> Backup Database dbEvents to dbEvents_backup
> Exec sp_dropdevice 'dbEvents_backup'
>
> I have two questions
> When the backup runs it isn't writing over the existing backup. It
> seems to be appending the information.
> Secondly, is it this an effective approach in:
> adding the dumpdevice
> backing it up
> and then deleting the device
> thanks
>
Hi
Like mentioned by Tracy, you can skip creation of the device by backing
up to disk, but I wonder why you drop the device after every backup if
it's the same device you add again next time?
Regards
Steen Schlüter Persson
Databaseadministrator / Systemadministrator
--020001090101040705090406
Content-Type: text/html; charset=ISO-8859-1
Content-Transfer-Encoding: 7bit
<!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
<html>
<head>
<meta content="text/html;charset=ISO-8859-1" http-equiv="Content-Type">
</head>
<body bgcolor="#ffffff" text="#000000">
<a class="moz-txt-link-abbreviated" href="http://links.10026.com/?link=mailto:vncntj@.hotmail.com">vncntj@.hotmail.com</a> wrote:
<blockquote
cite="mid1152217677.737502.210350@.m73g2000cwd.googlegroups.com"
type="cite">
<pre wrap="">Hello all I have this T-SQL string.
Use master
Exec sp_addumpdevice 'disk', 'dbEvents_backup',
'\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
Backup Database dbEvents to dbEvents_backup
Exec sp_dropdevice 'dbEvents_backup'
I have two questions
When the backup runs it isn't writing over the existing backup. It
seems to be appending the information.
Secondly, is it this an effective approach in:
adding the dumpdevice
backing it up
and then deleting the device
thanks
</pre>
</blockquote>
Hi 
 
Like mentioned by Tracy, you can skip creation of the device by backing
up to disk, but I wonder why you drop the device after every backup if
it's the same device you add again next time? 
 
 
-- 
Regards 
Steen Schlüter Persson 
Databaseadministrator / Systemadministrator 

</body>
</html>
--020001090101040705090406--|||YOU GUYS ROCK!!!!!!!!!!
Steen Persson (DK) wrote:
> vncntj@.hotmail.com wrote:
> > Hello all I have this T-SQL string.
> >
> >
> > Use master
> > Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> > '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> > Backup Database dbEvents to dbEvents_backup
> > Exec sp_dropdevice 'dbEvents_backup'
> >
> >
> > I have two questions
> > When the backup runs it isn't writing over the existing backup. It
> > seems to be appending the information.
> >
> > Secondly, is it this an effective approach in:
> >
> > adding the dumpdevice
> > backing it up
> > and then deleting the device
> >
> > thanks
> >
> >
> Hi
> Like mentioned by Tracy, you can skip creation of the device by backing
> up to disk, but I wonder why you drop the device after every backup if
> it's the same device you add again next time?
>
> --
> Regards
> Steen Schl=FCter Persson
> Databaseadministrator / Systemadministrator
> --020001090101040705090406
> Content-Type: text/html; charset=3DISO-8859-1
> X-Google-AttachSize: 1287
> <!DOCTYPE html PUBLIC "-//W3C//DTD HTML 4.01 Transitional//EN">
> <html>
> <head>
> <meta content=3D"text/html;charset=3DISO-8859-1" http-equiv=3D"Content-=Type">
> </head>
> <body bgcolor=3D"#ffffff" text=3D"#000000">
> <a class=3D"moz-txt-link-abbreviated" href=3D"mailto:vncntj@.hotmail.com">=vncntj@.hotmail.com</a> wrote:
> <blockquote
> cite=3D"mid1152217677.737502.210350@.m73g2000cwd.googlegroups.com"
> type=3D"cite">
> <pre wrap=3D"">Hello all I have this T-SQL string.
>
> Use master
> Exec sp_addumpdevice 'disk', 'dbEvents_backup',
> '\\pmafiles\X$\SQL\TMS\dbEvents\eventsbackup'
> Backup Database dbEvents to dbEvents_backup
> Exec sp_dropdevice 'dbEvents_backup'
>
> I have two questions
> When the backup runs it isn't writing over the existing backup. It
> seems to be appending the information.
> Secondly, is it this an effective approach in:
> adding the dumpdevice
> backing it up
> and then deleting the device
> thanks
> </pre>
> </blockquote>
> Hi 
> 
> Like mentioned by Tracy, you can skip creation of the device by backing
> up to disk, but I wonder why you drop the device after every backup if
> it's the same device you add again next time? 
> 
> 
> -- 
> Regards 
> Steen Schlüter Persson 
> Databaseadministrator / Systemadministrator 
> 
> </body>
> </html>
> > --020001090101040705090406--

Sunday, February 12, 2012

Backup Transaction Logs Using T-SQL

I am trying to backup my Transaction Logs using T-SQL with the aid of a job. The problem I am running into is that I need it to backup every three hours and retain one days worth of transactional logs. This is the query I got so far.

BACKUP LOG CA001 TO DISK = 'S:\AZTARGP\CA001\ca001_TRN_bkuplog.trn' with retaindays = 1

Any suggestions welcomed.At my place, we have a separate cleanup job that
deletes the files using xp_cmdshell .|||Try the Database Maintenance Plan. It's a database object and one of the properties is "Remove Files Older Than ___". It creates an entry in your SQL SERVER AGENT under JOBS to run the plan as scheduled.

It also allows much more than just simple backups and it very easy to manage.

I set up a Sunday backup that keeps it's prior Sunday file but removes older ones, but that backup doesn't affect my ongoing daily backups and logfile changes (separate jobs).

Also: I notice you're referencing a "S:" drive. Does SQL backup recognize mapped drives? Maybe that's just a shortcoming of the Database Maintenance Plan Object. It would seem risky. I'd rather do it to a separate (physical) volume physically attached, then immediately copy that the mapped drive (that's what I do). That way a RAID corruption on the DB volume doesn't corrupt the backup too. Also; my backup volume is just RAID 1 (mirrored), since that's easily transfered to a different server in a disaster situation.|||The S:\ drive is actually an iscsi drive that is recognised by the server. What is funny is that the maintenance plan for the database backup works just not the transaction logs. I also have checked to make sure the database was not in simple recovery mode.

I am sort of trying to find a way to give my backup names a date and time. Then I want to only keep a days worth of transaction logs becuase we do a full backup of the database everynight.

I do have

declare @.bkupdate datetime
set @.bkupdate = getdate()

but I don't know how to include it into my sql statement. When I do I get an syntax error.

BACKUP LOG CA001 TO DISK = 'S:\aztargp\ca001\CA001_' + @.bkupdate + '_TRNLOG.trn' with retaindays = 1

I am just trying to use t-sql until I can find the problem with why my database maintenance plan is not working. Thanks for the help so far.|||OK. Here's one that works here.

BACKUP DATABASE [wfab] TO DISK = N'd:\SQL Backups\Daily\mydb_db_latest.bak' WITH INIT , NAME = N'mydbLatest', SKIP , FORMAT

This hard codes the filename. I have this running every 6 hours. Surely using a variable for the filename would be fine, although why not build the whole thing first.

So:

declare @.bkdbName varchar(50)
set @.bkdbName = 'mydb' + Convert(varchar(50),getdate(),110)

declare @.bkName varchar(255)
set @.bkName = 'd:'
set @.bkName = trim(@.bkName) + '\SQL Backups\Daily\'
set @.bkName = trim(@.bkName) + trim(@.bkdbName)

BACKUP DATABASE [wfab] TO DISK = trim(@.bkName) WITH INIT , NAME = trim(@.bkName), SKIP , FORMAT

The reason I liked it without the special naming is because then it shows up pre-registered when I want to do a restore. I do a scheduled copy of these backup files to various places before the next backup takes place. Actually; I copy it to a standby server (as with the half-hour log files), ftp it to an offsite server, and I have a scheduled CD burn (encrypted) to my desktop at 3:30 every day, that I take home with me. This in addition to replication - I think it's safe to say we'll never loose much.

I haven't tried the above - I'm curious if it would then cause all the prior backups to show on the pre-registered backup file list. That would be very nice for restores - something we do regularly to test environments.|||code works great. Had to change a couple of things. How can I get the @.bkdbName to also contain the time?|||code works great. Had to change a couple of things. How can I get the @.bkdbName to also contain the time?
Here's (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_da-db_3vtw.asp) a link to MSDN for TSQL Ref. Make this a favorite.

Look at "Cast and convert" for conversion formats.

This should help:

select CONVERT(varchar(8),getdate(),114)
Result is: 15:57:34 Note: 114 returns 11 or 12 characters, but you probably don't need miliseconds.

Use SUBSTRING to parse out the ":" since that's probably not allowed as part of a filename.

I'm curious (and too lazy to try it). After creating a couple of these backups, does it list all these backups on the drop-down list when you go into the RESTORE dialog box? I know that works if the name is static.|||Have not tried it yet. Will when I get a change this afternoon.|||Thanks guys for the help. I also found out what was wrong with my DB maintenance plans. When you set to backup master,model,msdb and other databases in one plan it works fine for the db backups but not the translog. When i created a seperate plan for my user databases the transaction log backups completed successfully.

Backup Transaction Log on TempDb

How can i backuplog on Tempdb using T-SQL? Its fullNo can do. Make sure that tempdb is still in simple recovery mode. If it is,
you can have long
running transactions, or some humongous modifications requiring lots of log
space in tempdb. Short
term: make sure you have storage and autogrow is possible, Long term, hunt d
own what it is using
this space and fix the code.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
http://www.solidqualitylearning.com/
"Lontae Jones" <LontaeJones@.discussions.microsoft.com> wrote in message
news:C70FC7C3-E8F8-4DE9-A2DA-42D0B44F1AC1@.microsoft.com...
> How can i backuplog on Tempdb using T-SQL? Its full

Friday, February 10, 2012

Backup to NAS device fails under SQL2k5 X64...

Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after some
non-deterministic period of time) with the error
BackupMedium::ReportIoError: write failure on backup device
'\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735.bak'. Operating
system error 64(The specified network name is no longer available.).
The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried this
on another box with OS patched to max/SQL2K5 at level 3215 with the same
results.
The really weird thing is if I run this exact scenario in the 32-bit
environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's related
to the 64-bit environment. If I point the landing spot for the backup file to
locally attached storage (in a 64-bit VM environment), it works fine.
Has anybody seen this in their 64-bit environment? If so, what did you do to
get around this issue?
PaulIt doesn't sound like a SQL problem. Try copying a large file with xcopy or
explorer and see if the same thing happens.
--
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"psligar" <psligar@.discussions.microsoft.com> wrote in message
news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
> Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after
> some
> non-deterministic period of time) with the error
> BackupMedium::ReportIoError: write failure on backup device
> '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735.bak'. Operating
> system error 64(The specified network name is no longer available.).
> The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried
> this
> on another box with OS patched to max/SQL2K5 at level 3215 with the same
> results.
> The really weird thing is if I run this exact scenario in the 32-bit
> environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's
> related
> to the 64-bit environment. If I point the landing spot for the backup file
> to
> locally attached storage (in a 64-bit VM environment), it works fine.
> Has anybody seen this in their 64-bit environment? If so, what did you do
> to
> get around this issue?
> Paul|||Does the NAS location your trying to write to exist?
'The specified network name is no longer available'
Can you map to the nas location via my computer? If yes to both, then make
sure the ID your using has access to the nas device (read,write, modify,
etc).
I currently have all my SQL2k5 servers backing up to a NAS and I ran into
something similiar and it was due to the Id I was using no longer had access
to the NAS device.
"psligar" <psligar@.discussions.microsoft.com> wrote in message
news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
> Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after
> some
> non-deterministic period of time) with the error
> BackupMedium::ReportIoError: write failure on backup device
> '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735.bak'. Operating
> system error 64(The specified network name is no longer available.).
> The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried
> this
> on another box with OS patched to max/SQL2K5 at level 3215 with the same
> results.
> The really weird thing is if I run this exact scenario in the 32-bit
> environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's
> related
> to the 64-bit environment. If I point the landing spot for the backup file
> to
> locally attached storage (in a 64-bit VM environment), it works fine.
> Has anybody seen this in their 64-bit environment? If so, what did you do
> to
> get around this issue?
> Paul|||Jason:
Thanks for the reply. I should have included that info in my question - that
was our thinking, as well. Our first inclination was to think we had a 64-bit
network driver issue. However, we have copied (via XCOPY) large (~30 GB)
files continuously across the network to the device in question (from the
64-bit VM SQL machine) with no problems.
"Jason Massie" wrote:
> It doesn't sound like a SQL problem. Try copying a large file with xcopy or
> explorer and see if the same thing happens.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "psligar" <psligar@.discussions.microsoft.com> wrote in message
> news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
> > Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> > use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after
> > some
> > non-deterministic period of time) with the error
> >
> > BackupMedium::ReportIoError: write failure on backup device
> > '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735.bak'. Operating
> > system error 64(The specified network name is no longer available.).
> >
> > The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried
> > this
> > on another box with OS patched to max/SQL2K5 at level 3215 with the same
> > results.
> >
> > The really weird thing is if I run this exact scenario in the 32-bit
> > environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's
> > related
> > to the 64-bit environment. If I point the landing spot for the backup file
> > to
> > locally attached storage (in a 64-bit VM environment), it works fine.
> >
> > Has anybody seen this in their 64-bit environment? If so, what did you do
> > to
> > get around this issue?
> >
> > Paul
>
>|||Mike:
Thanks for the reply. To answer your question, yes, the location exists - we
are backing up multiple SQL2K 32-bit servers to that location across the
network. Some of those machines are in the ESX environment, as well. I can
map a drive to the network share point on the NAS device from 64-bit VM's in
question. I can copy large files to that share (see my reply to Jason). The
issue in question only surfaces when I try to use the 'BACKUP DATABASE' T-SQL
cmd from within SQL2K5.
In reference to the UID issue you mention, at times, one or more databases
will successfully backup to this device, before it fails on a subsequent DB.
So I'm confident that the UID being used has access to the network share.
NOTE: It's the same UID that is being used by the 32-bit SQL boxes that do
not exhibit this problem.
If I had the hardware availability, I might try setting up a stand alone
64-bit test box (to eliminate the ESX part of this scenario). Unfortunately,
I don't have that option (currently).
Paul
"Mike" wrote:
> Does the NAS location your trying to write to exist?
> 'The specified network name is no longer available'
> Can you map to the nas location via my computer? If yes to both, then make
> sure the ID your using has access to the nas device (read,write, modify,
> etc).
> I currently have all my SQL2k5 servers backing up to a NAS and I ran into
> something similiar and it was due to the Id I was using no longer had access
> to the NAS device.
> "psligar" <psligar@.discussions.microsoft.com> wrote in message
> news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
> > Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> > use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after
> > some
> > non-deterministic period of time) with the error
> >
> > BackupMedium::ReportIoError: write failure on backup device
> > '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735.bak'. Operating
> > system error 64(The specified network name is no longer available.).
> >
> > The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried
> > this
> > on another box with OS patched to max/SQL2K5 at level 3215 with the same
> > results.
> >
> > The really weird thing is if I run this exact scenario in the 32-bit
> > environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's
> > related
> > to the 64-bit environment. If I point the landing spot for the backup file
> > to
> > locally attached storage (in a 64-bit VM environment), it works fine.
> >
> > Has anybody seen this in their 64-bit environment? If so, what did you do
> > to
> > get around this issue?
> >
> > Paul
>
>|||And the saga continues:
I installed the MS tool NetMon version 3.1 'cause I wanted to see what was
going on vis-a-vis the network, and reran the backup job. Lo and behold, it
completed - 5 DB's backed up across the network to a NAS device - a total of
approximately 52 GB. I know that the NetMon tool loaded a packet capture
driver into the network stack - I'm just unsure what other changes it made.
"psligar" wrote:
> Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after some
> non-deterministic period of time) with the error
> BackupMedium::ReportIoError: write failure on backup device
> '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735.bak'. Operating
> system error 64(The specified network name is no longer available.).
> The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried this
> on another box with OS patched to max/SQL2K5 at level 3215 with the same
> results.
> The really weird thing is if I run this exact scenario in the 32-bit
> environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's related
> to the 64-bit environment. If I point the landing spot for the backup file to
> locally attached storage (in a 64-bit VM environment), it works fine.
> Has anybody seen this in their 64-bit environment? If so, what did you do to
> get around this issue?
> Paul

Backup to NAS device fails under SQL2k5 X64...

Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after some
non-deterministic period of time) with the error
BackupMedium::ReportIoError: write failure on backup device
'\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735. bak'. Operating
system error 64(The specified network name is no longer available.).
The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried this
on another box with OS patched to max/SQL2K5 at level 3215 with the same
results.
The really weird thing is if I run this exact scenario in the 32-bit
environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's related
to the 64-bit environment. If I point the landing spot for the backup file to
locally attached storage (in a 64-bit VM environment), it works fine.
Has anybody seen this in their 64-bit environment? If so, what did you do to
get around this issue?
Paul
It doesn't sound like a SQL problem. Try copying a large file with xcopy or
explorer and see if the same thing happens.
Jason Massie
Web: http://statisticsio.com
RSS: http://feeds.feedburner.com/statisticsio
"psligar" <psligar@.discussions.microsoft.com> wrote in message
news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
> Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after
> some
> non-deterministic period of time) with the error
> BackupMedium::ReportIoError: write failure on backup device
> '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735. bak'. Operating
> system error 64(The specified network name is no longer available.).
> The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried
> this
> on another box with OS patched to max/SQL2K5 at level 3215 with the same
> results.
> The really weird thing is if I run this exact scenario in the 32-bit
> environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's
> related
> to the 64-bit environment. If I point the landing spot for the backup file
> to
> locally attached storage (in a 64-bit VM environment), it works fine.
> Has anybody seen this in their 64-bit environment? If so, what did you do
> to
> get around this issue?
> Paul
|||Does the NAS location your trying to write to exist?
'The specified network name is no longer available'
Can you map to the nas location via my computer? If yes to both, then make
sure the ID your using has access to the nas device (read,write, modify,
etc).
I currently have all my SQL2k5 servers backing up to a NAS and I ran into
something similiar and it was due to the Id I was using no longer had access
to the NAS device.
"psligar" <psligar@.discussions.microsoft.com> wrote in message
news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
> Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after
> some
> non-deterministic period of time) with the error
> BackupMedium::ReportIoError: write failure on backup device
> '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735. bak'. Operating
> system error 64(The specified network name is no longer available.).
> The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried
> this
> on another box with OS patched to max/SQL2K5 at level 3215 with the same
> results.
> The really weird thing is if I run this exact scenario in the 32-bit
> environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's
> related
> to the 64-bit environment. If I point the landing spot for the backup file
> to
> locally attached storage (in a 64-bit VM environment), it works fine.
> Has anybody seen this in their 64-bit environment? If so, what did you do
> to
> get around this issue?
> Paul
|||Jason:
Thanks for the reply. I should have included that info in my question - that
was our thinking, as well. Our first inclination was to think we had a 64-bit
network driver issue. However, we have copied (via XCOPY) large (~30 GB)
files continuously across the network to the device in question (from the
64-bit VM SQL machine) with no problems.
"Jason Massie" wrote:

> It doesn't sound like a SQL problem. Try copying a large file with xcopy or
> explorer and see if the same thing happens.
> --
> Jason Massie
> Web: http://statisticsio.com
> RSS: http://feeds.feedburner.com/statisticsio
> "psligar" <psligar@.discussions.microsoft.com> wrote in message
> news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
>
>
|||Mike:
Thanks for the reply. To answer your question, yes, the location exists - we
are backing up multiple SQL2K 32-bit servers to that location across the
network. Some of those machines are in the ESX environment, as well. I can
map a drive to the network share point on the NAS device from 64-bit VM's in
question. I can copy large files to that share (see my reply to Jason). The
issue in question only surfaces when I try to use the 'BACKUP DATABASE' T-SQL
cmd from within SQL2K5.
In reference to the UID issue you mention, at times, one or more databases
will successfully backup to this device, before it fails on a subsequent DB.
So I'm confident that the UID being used has access to the network share.
NOTE: It's the same UID that is being used by the 32-bit SQL boxes that do
not exhibit this problem.
If I had the hardware availability, I might try setting up a stand alone
64-bit test box (to eliminate the ESX part of this scenario). Unfortunately,
I don't have that option (currently).
Paul
"Mike" wrote:

> Does the NAS location your trying to write to exist?
> 'The specified network name is no longer available'
> Can you map to the nas location via my computer? If yes to both, then make
> sure the ID your using has access to the nas device (read,write, modify,
> etc).
> I currently have all my SQL2k5 servers backing up to a NAS and I ran into
> something similiar and it was due to the Id I was using no longer had access
> to the NAS device.
> "psligar" <psligar@.discussions.microsoft.com> wrote in message
> news:B7994C94-7224-4F34-82B2-37D8C7D84BB2@.microsoft.com...
>
>
|||And the saga continues:
I installed the MS tool NetMon version 3.1 'cause I wanted to see what was
going on vis-a-vis the network, and reran the backup job. Lo and behold, it
completed - 5 DB's backed up across the network to a NAS device - a total of
approximately 52 GB. I know that the NetMon tool loaded a packet capture
driver into the network stack - I'm just unsure what other changes it made.
"psligar" wrote:

> Here is my scenario: we have SQL2K5 X64 running in VM (ESX server). When I
> use T-SQL cmd "BACKUP DATABASE" landing on NAS device, if fails (after some
> non-deterministic period of time) with the error
> BackupMedium::ReportIoError: write failure on backup device
> '\\sql-snap\data\BACKUP\sql_dev\ArchiveDB_20080409132735. bak'. Operating
> system error 64(The specified network name is no longer available.).
> The OS is Win2K3 SP2 X64; SQL2K5 is running at level 1399. I have tried this
> on another box with OS patched to max/SQL2K5 at level 3215 with the same
> results.
> The really weird thing is if I run this exact scenario in the 32-bit
> environment (VM/Win2K3/SQL2K5 @. level 3215), it works fine. So it's related
> to the 64-bit environment. If I point the landing spot for the backup file to
> locally attached storage (in a 64-bit VM environment), it works fine.
> Has anybody seen this in their 64-bit environment? If so, what did you do to
> get around this issue?
> Paul

Thursday, March 8, 2012

backups...

backups...

Sunday, February 12, 2012

Backup Transaction Logs Using T-SQL

Backup Transaction Log on TempDb

Friday, February 10, 2012

Backup to NAS device fails under SQL2k5 X64...

Backup to NAS device fails under SQL2k5 X64...

Backup to Network Drive

Blog Archive

About Me