Please start any new threads on our new
site at https://forums.sqlteam.com. We've got lots of great SQL Server
experts to answer whatever question you can come up with.
| Author |
Topic |
|
hollywood0728
Starting Member
2 Posts |
 Posted - 2007-11-09 : 15:25:06
|
| I will try to get all the details, If I miss anything that anyone may need to know let me know..I am not a SQL GURU.What I am trying to accomplish:Saving SQL backups to a NAS (Network attached Storage)I have about 4 SQL 2000 production servers in a web hosting environment that I need to perform backups on. I have ran out of space for local backup. I need to save this over the network. All servers run Windows server 2003 standard edition. All SQL servers are attached to a dedicated backbone with connects directly to the NAS. SQL is running under the SA account, not a windows account. All the the servers are on a workgroup, NOT A DOMAIN. I am not very savy with Code or TSQL or QA so if any of that needs to be done I will need further assistance. I can create a backup device and put the UNC line in the Drive area, but I can not select that backup device in a maintance plan. Any detailed instruction on any solution would be great. I have really been banging my head on my keyboard over this. Thanks in advance |
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-10 : 00:26:16
|
| >> SQL is running under the SA accountDo you mean sql startup account is local system? That will not work. The account used needs permission to create file on NAS. |
 |
|
|
dirtydavey
Yak Posting Veteran
80 Posts |
Posted - 2007-11-13 : 06:29:23
|
| I have done this before, first of all make sure that you time the backups apart from each other or you will give the disk far too much work to do.Second make sure you can ping the nas from each of the servers this will ensure that you have access to them all.I dont think you should use maint plans, for this type of back up, and would recomend backing up using a script running in a job.Somthing like this should do it:-declare @SQLText nvarchar(4000), @DBName sysname select @DBName = min(name) from sys.databases where name not in ('tempdb') while @DBName is not null begin set @SQLText = 'backup database ['+@DBName+'] to disk = d:\backup\'+@dbname+' with init, stats = 10' select @SQLText exec (@SQLText) select @DBName = min(name) from sys.databases where name > @DBName and name not in ('tempdb') end |
|
|
|
rmiao
Master Smack Fu Yak Hacker
7266 Posts |
Posted - 2007-11-13 : 23:12:11
|
| Maintenance plan works as well. |
|
|
|
|
|
|
Can't Seem to Find My Answer In Plain Text