Hi,
I use the Transact-SQL BACKUP statement in Visual Basic to backup my local MSSQL Database. It give me this error
Error 3041
BACKUP failed to complete the command BACKUP DATABASE [BCFPC] to BCFPCBKP
I already created a backup device called BCFPCBKP and it is backup to the disk.
I tried to run the same BACKUP statement in SQL Query Analyzer and it worked fine. I tried to run my VB application in another PC. It worked fine when i use this command remotely. Can anyone tell me what's the problem?
Thanks in advance
regards,
M.Y. YapAre you sure the id which was used to start the job agent had access right to the backup device?|||Yes, I usse the same id with the one that I used at a remote PC.
The id I used was 'sa' and I connect to the master database to run the 'BACKUP' statement.
Showing posts with label local. Show all posts
Showing posts with label local. Show all posts
Tuesday, March 27, 2012
Thursday, March 22, 2012
Backup display
I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
Tom
I am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
Tom
I am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
Backup display
I created some backups for my databases and they show in the Enterprise Jobs
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
--
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
as Uncategorized [local]. All the Maintenance Plans show as Database
Maintenance.
I have about 10 of them and you can't tell which is which without going into
the properties.
Is there an easier way to see what backups you have set up and what the
status is?
Thanks,
TomI am not quite sure what you are asking. You have the ability to put what
ever description you want for any job. You also have the ability to set the
category or to even create a new custom category. So between those two
options you should certainly be able to do something descriptive enough to
group your jobs appropriately.
--
Andrew J. Kelly SQL MVP
"tshad" <tscheiderich@.ftsolutions.com> wrote in message
news:%232QEfvx5FHA.884@.TK2MSFTNGP14.phx.gbl...
>I created some backups for my databases and they show in the Enterprise
>Jobs as Uncategorized [local]. All the Maintenance Plans show as Database
>Maintenance.
> I have about 10 of them and you can't tell which is which without going
> into the properties.
> Is there an easier way to see what backups you have set up and what the
> status is?
> Thanks,
> Tom
>
Saturday, February 25, 2012
Backup and restore databases and access rights
Hi,
I am creating a way of working in order to "copy" databases from a master SQL Server 2000 database to the developers local machines.
I want to create a master SQL Server at our office location. Whenever our developers works on site the master is used. To be able to work locally, a backup has been created of the master and is distributed to the developers. On their local machines, they restore the database to be able to develop locally.
I have created a backup on my laptop using on my domain account (not the account that created the database, but I can use it when developing). I moved the backup file to another computer and restored it using my domain account on that computer. I could not see all of the tables, users or stored procedures, only those which type are System, not User. When i log in to the local administrators account I can see them all. When I log back into my domain account I can see all of the tables.
Anyone knows why?
/M
SQL Enterprise Manager doesn't refresh objects very well. You actually have to force it to refresh sometimes by hitting F5 and even that doesn't always work. Something to try in this case is a quick select statement in query analyzer of a table that you know should be there. Typically it will either show the results or give you the error associated with permissions.
It also sounds like it could be db permissions problems. Typically, SEM connects to the local server via the sa login. So when you login as yourself, you are probably connecting to the server as yourself in SEM. Also keep in mind that objects are owned by their creators and unless permissions are specifically granted for other users/roles or the current user is an admin, you won't be able to work with these objects. So if you created objects under one login (such as sa) you may not be able to see them under another login (such as your domain account). sp_changeobjectowner is a way to modify the owner for each object. sp_MScheck_uid_owns_anything will help determine if the user owns anything; just use the id of the user as the only parameter.
Another option would be to grant all permissions to the public role for all objects as they are created. This is not advised because you are not using SQL Security like it should. But roles are a good thing in environments like this, but these take some setup and administration time.
Backup and restore database from local drive to hosting server
Hello all,
I'm new to SQL server and I've just seutp a website using the new Visual Web Developer and SQL 2005 Express. I've got my website out on the hosting site server but I need to copy my database file to the hosting site's sql 2005 server. I asked my provider how to do this and they advised to use SQL Server Mgmt Stuido and to perform a backup of my local database file and then do a restore to their server. First they said to create a database on their server using a web based control panel provided with the site controls (this would be a temporary database overwritten by my local copy on the restore - I'm assuming). Then I could do my backup and restore.
I've not been successful doing this. I can connect to their server and my temporary database and can also backup my local database. I cannot, however see how to restore my backup copy to the remote site's sql server. When I choose to restore a database on the remote site, I am only provided with a file browser with folders the SQL Server instance can see. These obviously are not any of my local files so I can't use my local backup. I can't backup my file directly to the server because of the same issue, my local SQL instance can only see my local files, not the remote site directories.
I am probably overlooking something obvious but I really could use any input offered.
Thanks in advance for any help.
Regards,
Scott
Hi all:
Update to this issue, I finally resolved it. I just decided to recreate the database manually on the server using scripts. I connected in SQL Server Mgmt Studio to my remote db server and ran all the scripts I created from my local database. This took a while but I was able to finally get it finished and it now works great.
Friday, February 24, 2012
Backup a remote SQL Server to local computer?
Is there an easy way to backup a database on a remote SQL Server to a backup file on my local computer?I was thinking of that too. A straight up backup won't work, SQL needsa local device for that. You should be able to replicate the data to alocal database and back that up though. I tried and it didn't work.It's been a while and I haven't persued it so I'm sorry that I can'tprovide details. Take a look in Enterprise Manager, I think I tried theExport function. Of course any backup is only as good as its recoverycapabilities. Whatever you come up with, try it out *before* you need arecovery done :)
|||I think only a database backup tool will do what you want to do. If it is a company network this is what I have done remote into the server do the backup use Winzip to zip the file and email it to whoever needs it yourself included. When restoring use restore from device option in the backup wizard. Hope this helps.
|||I think only a database backup tool will do what you want to do. If it is a company network this is what I have done remote into the server do the backup use Winzip to zip the file and email it to whoever needs it yourself included. When restoring use restore from device option in the backup wizard. Hope this helps.
Thursday, February 16, 2012
Backup
I am very new to SQL Express. A database is located on a local machine. When I try to do a backup I get the following error:
Backup failed for server ‘server name’
(Microsoft.sql server.Express.smo)
Additional information:
Set property Devices to accomplish this action.
(Microsoft.sql server.Express.smo)
Their IT tecnicians insits that I have full administrative rights.
I am lost for a solution.
Would it be wise to shrink the database, detach from the database and copy both the mdf and idf to a different machine? I have never done this procedure before.
Please help.
Could you please send over the executed script ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I am having same problem.I am connected to sql server 2000 instance.
When trying to backup the database i got the same error
Any idea why ?|||
Which Gui are you using ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Could you please then post the resulting Backup SQL command which is available through the script functionality within the Backup UI.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Did anyone get a resolution to this? I'm having the same problem. I uninstalled and reinstalled SQL Server 2005 Express and then reinstalled and it worked fine. I was able to back up the database for two days. Then the error started coming up again and now I'm getting the "Set property Devices to accomplish this action." error again.
I have no clue what property device it's talking about or where to set them. Any help would be greatly appreciated!
Thanks,
Steve
Backup
Question:
Our Production Database is located off-site hosted by a 3rd party.
I have SQL server installed in our local site. I want to have a backup
copy of the Database from what is on Production located locally on
an ongoing basis. I don't want the DB that is located locally to be
used in anyway by the users. I just want to be sure I have a copy
of the DB at our local office just in case anything happens. I sort
of want to have a DB that mirrors the production site and also a
TEST DB for testing purposes locally.
I'm curious of the best way to go about this. Is it via backup and restore
or some sort of replication solution.
Thanks,
GeorgeI'd go for backup/restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> Question:
> Our Production Database is located off-site hosted by a 3rd party.
> I have SQL server installed in our local site. I want to have a backup
> copy of the Database from what is on Production located locally on
> an ongoing basis. I don't want the DB that is located locally to be
> used in anyway by the users. I just want to be sure I have a copy
> of the DB at our local office just in case anything happens. I sort
> of want to have a DB that mirrors the production site and also a
> TEST DB for testing purposes locally.
> I'm curious of the best way to go about this. Is it via backup and restore
> or some sort of replication solution.
> Thanks,
> George
>
>|||Can I run it as a JOB. The DB use to be in Access we just migrated over to
SQL
server. I use to schedule a copy of the DB, via FTP, from the 3rd party
hosting server like
every 4 hours so that I had a fresh copy of the DB locally. Just curious
how I
do that with SQL server.
I basically just want to feel safe that I have a relatively current copy of
the DB stored
locally. Just in case some unforeseen event happens with our ISP' Like
they forgot
to do backups, or their site goes down, etc. ONCE, they were having a
problem with
their server and took our server off the backup routine and forgot to put it
back in. I
don't think it will happen again, but one never knows. I'd rather be sure
that I have
a copy in case they don't.
Just trying to figure out the best solution to ensure we have a copy stored
locally. Being
this DB is hosted remotely can you backup to a remote location? The 3rd
party DB server
is a shared DB server so other companies also have their DBs hosted on this
server.
Thanks for any help.
George
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> I'd go for backup/restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "george" <roberts_g@.marranohomes.com> wrote in message
news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > Question:
> >
> > Our Production Database is located off-site hosted by a 3rd party.
> > I have SQL server installed in our local site. I want to have a backup
> > copy of the Database from what is on Production located locally on
> > an ongoing basis. I don't want the DB that is located locally to be
> > used in anyway by the users. I just want to be sure I have a copy
> > of the DB at our local office just in case anything happens. I sort
> > of want to have a DB that mirrors the production site and also a
> > TEST DB for testing purposes locally.
> >
> > I'm curious of the best way to go about this. Is it via backup and
restore
> > or some sort of replication solution.
> >
> > Thanks,
> > George
> >
> >
> >
>|||> Can I run it as a JOB.
Sure. Just read up on backup and restore in books online, determine whether you want to go for only db backups
or both db backups and log backups. Then create your job(s) with the desired commands and frequency.
You could either use DTS, which has built-in support for FTP and combine DTS with SQL Command job steps. Then
schedule the DTS task in SQL Server agent.
Or use SQL Agent directly and combine TSQL with CMDExec (from where you run FTP.EXE or similar) tasks.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:OrJ9lHpNEHA.2480@.tk2msftngp13.phx.gbl...
> Can I run it as a JOB. The DB use to be in Access we just migrated over to
> SQL
> server. I use to schedule a copy of the DB, via FTP, from the 3rd party
> hosting server like
> every 4 hours so that I had a fresh copy of the DB locally. Just curious
> how I
> do that with SQL server.
> I basically just want to feel safe that I have a relatively current copy of
> the DB stored
> locally. Just in case some unforeseen event happens with our ISP' Like
> they forgot
> to do backups, or their site goes down, etc. ONCE, they were having a
> problem with
> their server and took our server off the backup routine and forgot to put it
> back in. I
> don't think it will happen again, but one never knows. I'd rather be sure
> that I have
> a copy in case they don't.
> Just trying to figure out the best solution to ensure we have a copy stored
> locally. Being
> this DB is hosted remotely can you backup to a remote location? The 3rd
> party DB server
> is a shared DB server so other companies also have their DBs hosted on this
> server.
> Thanks for any help.
> George
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> > I'd go for backup/restore.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "george" <roberts_g@.marranohomes.com> wrote in message
> news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > > Question:
> > >
> > > Our Production Database is located off-site hosted by a 3rd party.
> > > I have SQL server installed in our local site. I want to have a backup
> > > copy of the Database from what is on Production located locally on
> > > an ongoing basis. I don't want the DB that is located locally to be
> > > used in anyway by the users. I just want to be sure I have a copy
> > > of the DB at our local office just in case anything happens. I sort
> > > of want to have a DB that mirrors the production site and also a
> > > TEST DB for testing purposes locally.
> > >
> > > I'm curious of the best way to go about this. Is it via backup and
> restore
> > > or some sort of replication solution.
> > >
> > > Thanks,
> > > George
> > >
> > >
> > >
> >
> >
>
Our Production Database is located off-site hosted by a 3rd party.
I have SQL server installed in our local site. I want to have a backup
copy of the Database from what is on Production located locally on
an ongoing basis. I don't want the DB that is located locally to be
used in anyway by the users. I just want to be sure I have a copy
of the DB at our local office just in case anything happens. I sort
of want to have a DB that mirrors the production site and also a
TEST DB for testing purposes locally.
I'm curious of the best way to go about this. Is it via backup and restore
or some sort of replication solution.
Thanks,
GeorgeI'd go for backup/restore.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> Question:
> Our Production Database is located off-site hosted by a 3rd party.
> I have SQL server installed in our local site. I want to have a backup
> copy of the Database from what is on Production located locally on
> an ongoing basis. I don't want the DB that is located locally to be
> used in anyway by the users. I just want to be sure I have a copy
> of the DB at our local office just in case anything happens. I sort
> of want to have a DB that mirrors the production site and also a
> TEST DB for testing purposes locally.
> I'm curious of the best way to go about this. Is it via backup and restore
> or some sort of replication solution.
> Thanks,
> George
>
>|||Can I run it as a JOB. The DB use to be in Access we just migrated over to
SQL
server. I use to schedule a copy of the DB, via FTP, from the 3rd party
hosting server like
every 4 hours so that I had a fresh copy of the DB locally. Just curious
how I
do that with SQL server.
I basically just want to feel safe that I have a relatively current copy of
the DB stored
locally. Just in case some unforeseen event happens with our ISP' Like
they forgot
to do backups, or their site goes down, etc. ONCE, they were having a
problem with
their server and took our server off the backup routine and forgot to put it
back in. I
don't think it will happen again, but one never knows. I'd rather be sure
that I have
a copy in case they don't.
Just trying to figure out the best solution to ensure we have a copy stored
locally. Being
this DB is hosted remotely can you backup to a remote location? The 3rd
party DB server
is a shared DB server so other companies also have their DBs hosted on this
server.
Thanks for any help.
George
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> I'd go for backup/restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "george" <roberts_g@.marranohomes.com> wrote in message
news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > Question:
> >
> > Our Production Database is located off-site hosted by a 3rd party.
> > I have SQL server installed in our local site. I want to have a backup
> > copy of the Database from what is on Production located locally on
> > an ongoing basis. I don't want the DB that is located locally to be
> > used in anyway by the users. I just want to be sure I have a copy
> > of the DB at our local office just in case anything happens. I sort
> > of want to have a DB that mirrors the production site and also a
> > TEST DB for testing purposes locally.
> >
> > I'm curious of the best way to go about this. Is it via backup and
restore
> > or some sort of replication solution.
> >
> > Thanks,
> > George
> >
> >
> >
>|||> Can I run it as a JOB.
Sure. Just read up on backup and restore in books online, determine whether you want to go for only db backups
or both db backups and log backups. Then create your job(s) with the desired commands and frequency.
You could either use DTS, which has built-in support for FTP and combine DTS with SQL Command job steps. Then
schedule the DTS task in SQL Server agent.
Or use SQL Agent directly and combine TSQL with CMDExec (from where you run FTP.EXE or similar) tasks.
--
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:OrJ9lHpNEHA.2480@.tk2msftngp13.phx.gbl...
> Can I run it as a JOB. The DB use to be in Access we just migrated over to
> SQL
> server. I use to schedule a copy of the DB, via FTP, from the 3rd party
> hosting server like
> every 4 hours so that I had a fresh copy of the DB locally. Just curious
> how I
> do that with SQL server.
> I basically just want to feel safe that I have a relatively current copy of
> the DB stored
> locally. Just in case some unforeseen event happens with our ISP' Like
> they forgot
> to do backups, or their site goes down, etc. ONCE, they were having a
> problem with
> their server and took our server off the backup routine and forgot to put it
> back in. I
> don't think it will happen again, but one never knows. I'd rather be sure
> that I have
> a copy in case they don't.
> Just trying to figure out the best solution to ensure we have a copy stored
> locally. Being
> this DB is hosted remotely can you backup to a remote location? The 3rd
> party DB server
> is a shared DB server so other companies also have their DBs hosted on this
> server.
> Thanks for any help.
> George
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> > I'd go for backup/restore.
> >
> > --
> > Tibor Karaszi, SQL Server MVP
> > http://www.karaszi.com/sqlserver/default.asp
> >
> >
> > "george" <roberts_g@.marranohomes.com> wrote in message
> news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> > > Question:
> > >
> > > Our Production Database is located off-site hosted by a 3rd party.
> > > I have SQL server installed in our local site. I want to have a backup
> > > copy of the Database from what is on Production located locally on
> > > an ongoing basis. I don't want the DB that is located locally to be
> > > used in anyway by the users. I just want to be sure I have a copy
> > > of the DB at our local office just in case anything happens. I sort
> > > of want to have a DB that mirrors the production site and also a
> > > TEST DB for testing purposes locally.
> > >
> > > I'm curious of the best way to go about this. Is it via backup and
> restore
> > > or some sort of replication solution.
> > >
> > > Thanks,
> > > George
> > >
> > >
> > >
> >
> >
>
Backup
How do you back up the Agent Jobs and the Local Packages under Data
Transformation services? Does this get backed up when you back up the Master
database?
Thanks
RickThey get backed up when you backup the msdb database.
-Sue
On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
<rick@.di-wave.com> wrote:
>How do you back up the Agent Jobs and the Local Packages under Data
>Transformation services? Does this get backed up when you back up the Master
>database?
> Thanks
> Rick
>|||Thanks very much!!!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:9ngljvsocne65i7n5lsftphqpddsqs579u@.4ax.com...
> They get backed up when you backup the msdb database.
> -Sue
> On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
> <rick@.di-wave.com> wrote:
> >How do you back up the Agent Jobs and the Local Packages under Data
> >Transformation services? Does this get backed up when you back up the
Master
> >database?
> >
> > Thanks
> > Rick
> >
>
Transformation services? Does this get backed up when you back up the Master
database?
Thanks
RickThey get backed up when you backup the msdb database.
-Sue
On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
<rick@.di-wave.com> wrote:
>How do you back up the Agent Jobs and the Local Packages under Data
>Transformation services? Does this get backed up when you back up the Master
>database?
> Thanks
> Rick
>|||Thanks very much!!!
"Sue Hoegemeier" <Sue_H@.nomail.please> wrote in message
news:9ngljvsocne65i7n5lsftphqpddsqs579u@.4ax.com...
> They get backed up when you backup the msdb database.
> -Sue
> On Wed, 13 Aug 2003 15:14:27 -0700, "Rick V"
> <rick@.di-wave.com> wrote:
> >How do you back up the Agent Jobs and the Local Packages under Data
> >Transformation services? Does this get backed up when you back up the
Master
> >database?
> >
> > Thanks
> > Rick
> >
>
Tuesday, February 14, 2012
Backup
Question:
Our Production Database is located off-site hosted by a 3rd party.
I have SQL server installed in our local site. I want to have a backup
copy of the Database from what is on Production located locally on
an ongoing basis. I don't want the DB that is located locally to be
used in anyway by the users. I just want to be sure I have a copy
of the DB at our local office just in case anything happens. I sort
of want to have a DB that mirrors the production site and also a
TEST DB for testing purposes locally.
I'm curious of the best way to go about this. Is it via backup and restore
or some sort of replication solution.
Thanks,
GeorgeI'd go for backup/restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.ph
x.gbl...
> Question:
> Our Production Database is located off-site hosted by a 3rd party.
> I have SQL server installed in our local site. I want to have a backup
> copy of the Database from what is on Production located locally on
> an ongoing basis. I don't want the DB that is located locally to be
> used in anyway by the users. I just want to be sure I have a copy
> of the DB at our local office just in case anything happens. I sort
> of want to have a DB that mirrors the production site and also a
> TEST DB for testing purposes locally.
> I'm curious of the best way to go about this. Is it via backup and restor
e
> or some sort of replication solution.
> Thanks,
> George
>
>|||Can I run it as a JOB. The DB use to be in Access we just migrated over to
SQL
server. I use to schedule a copy of the DB, via FTP, from the 3rd party
hosting server like
every 4 hours so that I had a fresh copy of the DB locally. Just curious
how I
do that with SQL server.
I basically just want to feel safe that I have a relatively current copy of
the DB stored
locally. Just in case some unforeseen event happens with our ISP' Like
they forgot
to do backups, or their site goes down, etc. ONCE, they were having a
problem with
their server and took our server off the backup routine and forgot to put it
back in. I
don't think it will happen again, but one never knows. I'd rather be sure
that I have
a copy in case they don't.
Just trying to figure out the best solution to ensure we have a copy stored
locally. Being
this DB is hosted remotely can you backup to a remote location? The 3rd
party DB server
is a shared DB server so other companies also have their DBs hosted on this
server.
Thanks for any help.
George
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> I'd go for backup/restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "george" <roberts_g@.marranohomes.com> wrote in message
news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
restore[vbcol=seagreen]
>|||> Can I run it as a JOB.
Sure. Just read up on backup and restore in books online, determine whether
you want to go for only db backups
or both db backups and log backups. Then create your job(s) with the desired
commands and frequency.
You could either use DTS, which has built-in support for FTP and combine DTS
with SQL Command job steps. Then
schedule the DTS task in SQL Server agent.
Or use SQL Agent directly and combine TSQL with CMDExec (from where you run
FTP.EXE or similar) tasks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:OrJ9lHpNEHA.2480@.tk2msftngp13.ph
x.gbl...
> Can I run it as a JOB. The DB use to be in Access we just migrated over t
o
> SQL
> server. I use to schedule a copy of the DB, via FTP, from the 3rd party
> hosting server like
> every 4 hours so that I had a fresh copy of the DB locally. Just curious
> how I
> do that with SQL server.
> I basically just want to feel safe that I have a relatively current copy o
f
> the DB stored
> locally. Just in case some unforeseen event happens with our ISP' Like
> they forgot
> to do backups, or their site goes down, etc. ONCE, they were having a
> problem with
> their server and took our server off the backup routine and forgot to put
it
> back in. I
> don't think it will happen again, but one never knows. I'd rather be sure
> that I have
> a copy in case they don't.
> Just trying to figure out the best solution to ensure we have a copy store
d
> locally. Being
> this DB is hosted remotely can you backup to a remote location? The 3rd
> party DB server
> is a shared DB server so other companies also have their DBs hosted on thi
s
> server.
> Thanks for any help.
> George
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> restore
>
Our Production Database is located off-site hosted by a 3rd party.
I have SQL server installed in our local site. I want to have a backup
copy of the Database from what is on Production located locally on
an ongoing basis. I don't want the DB that is located locally to be
used in anyway by the users. I just want to be sure I have a copy
of the DB at our local office just in case anything happens. I sort
of want to have a DB that mirrors the production site and also a
TEST DB for testing purposes locally.
I'm curious of the best way to go about this. Is it via backup and restore
or some sort of replication solution.
Thanks,
GeorgeI'd go for backup/restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.ph
x.gbl...
> Question:
> Our Production Database is located off-site hosted by a 3rd party.
> I have SQL server installed in our local site. I want to have a backup
> copy of the Database from what is on Production located locally on
> an ongoing basis. I don't want the DB that is located locally to be
> used in anyway by the users. I just want to be sure I have a copy
> of the DB at our local office just in case anything happens. I sort
> of want to have a DB that mirrors the production site and also a
> TEST DB for testing purposes locally.
> I'm curious of the best way to go about this. Is it via backup and restor
e
> or some sort of replication solution.
> Thanks,
> George
>
>|||Can I run it as a JOB. The DB use to be in Access we just migrated over to
SQL
server. I use to schedule a copy of the DB, via FTP, from the 3rd party
hosting server like
every 4 hours so that I had a fresh copy of the DB locally. Just curious
how I
do that with SQL server.
I basically just want to feel safe that I have a relatively current copy of
the DB stored
locally. Just in case some unforeseen event happens with our ISP' Like
they forgot
to do backups, or their site goes down, etc. ONCE, they were having a
problem with
their server and took our server off the backup routine and forgot to put it
back in. I
don't think it will happen again, but one never knows. I'd rather be sure
that I have
a copy in case they don't.
Just trying to figure out the best solution to ensure we have a copy stored
locally. Being
this DB is hosted remotely can you backup to a remote location? The 3rd
party DB server
is a shared DB server so other companies also have their DBs hosted on this
server.
Thanks for any help.
George
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> I'd go for backup/restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "george" <roberts_g@.marranohomes.com> wrote in message
news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
restore[vbcol=seagreen]
>|||> Can I run it as a JOB.
Sure. Just read up on backup and restore in books online, determine whether
you want to go for only db backups
or both db backups and log backups. Then create your job(s) with the desired
commands and frequency.
You could either use DTS, which has built-in support for FTP and combine DTS
with SQL Command job steps. Then
schedule the DTS task in SQL Server agent.
Or use SQL Agent directly and combine TSQL with CMDExec (from where you run
FTP.EXE or similar) tasks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:OrJ9lHpNEHA.2480@.tk2msftngp13.ph
x.gbl...
> Can I run it as a JOB. The DB use to be in Access we just migrated over t
o
> SQL
> server. I use to schedule a copy of the DB, via FTP, from the 3rd party
> hosting server like
> every 4 hours so that I had a fresh copy of the DB locally. Just curious
> how I
> do that with SQL server.
> I basically just want to feel safe that I have a relatively current copy o
f
> the DB stored
> locally. Just in case some unforeseen event happens with our ISP' Like
> they forgot
> to do backups, or their site goes down, etc. ONCE, they were having a
> problem with
> their server and took our server off the backup routine and forgot to put
it
> back in. I
> don't think it will happen again, but one never knows. I'd rather be sure
> that I have
> a copy in case they don't.
> Just trying to figure out the best solution to ensure we have a copy store
d
> locally. Being
> this DB is hosted remotely can you backup to a remote location? The 3rd
> party DB server
> is a shared DB server so other companies also have their DBs hosted on thi
s
> server.
> Thanks for any help.
> George
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote i
n
> message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> restore
>
Backup
I am very new to SQL Express. A database is located on a local machine. When I try to do a backup I get the following error:
Backup failed for server ‘server name’
(Microsoft.sql server.Express.smo)
Additional information:
Set property Devices to accomplish this action.
(Microsoft.sql server.Express.smo)
Their IT tecnicians insits that I have full administrative rights.
I am lost for a solution.
Would it be wise to shrink the database, detach from the database and copy both the mdf and idf to a different machine? I have never done this procedure before.
Please help.
Could you please send over the executed script ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I am having same problem.I am connected to sql server 2000 instance.
When trying to backup the database i got the same error
Any idea why ?|||
Which Gui are you using ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
|||Could you please then post the resulting Backup SQL command which is available through the script functionality within the Backup UI.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Did anyone get a resolution to this? I'm having the same problem. I uninstalled and reinstalled SQL Server 2005 Express and then reinstalled and it worked fine. I was able to back up the database for two days. Then the error started coming up again and now I'm getting the "Set property Devices to accomplish this action." error again.
I have no clue what property device it's talking about or where to set them. Any help would be greatly appreciated!
Thanks,
Steve
Backup
I am very new to SQL Express. A database is located on a local machine. When I try to do a backup I get the following error:
Backup failed for server ‘server name’
(Microsoft.sql server.Express.smo)
Additional information:
Set property Devices to accomplish this action.
(Microsoft.sql server.Express.smo)
Their IT tecnicians insits that I have full administrative rights.
I am lost for a solution.
Would it be wise to shrink the database, detach from the database and copy both the mdf and idf to a different machine? I have never done this procedure before.
Please help.
Could you please send over the executed script ?Jens K. Suessmeyer.
http://www.sqlserver2005.de
|||I am having same problem.I am connected to sql server 2000 instance.
When trying to backup the database i got the same error
Any idea why ?|||
Which Gui are you using ?
Jens K. Suessmeyer
http://www.sqlserver2005.de
Sql Server Management Studio|||
|||Could you please then post the resulting Backup SQL command which is available through the script functionality within the Backup UI.
Jens K. Suessmeyer
http://www.sqlserver2005.de
Did anyone get a resolution to this? I'm having the same problem. I uninstalled and reinstalled SQL Server 2005 Express and then reinstalled and it worked fine. I was able to back up the database for two days. Then the error started coming up again and now I'm getting the "Set property Devices to accomplish this action." error again.
I have no clue what property device it's talking about or where to set them. Any help would be greatly appreciated!
Thanks,
Steve
Backup
Question:
Our Production Database is located off-site hosted by a 3rd party.
I have SQL server installed in our local site. I want to have a backup
copy of the Database from what is on Production located locally on
an ongoing basis. I don't want the DB that is located locally to be
used in anyway by the users. I just want to be sure I have a copy
of the DB at our local office just in case anything happens. I sort
of want to have a DB that mirrors the production site and also a
TEST DB for testing purposes locally.
I'm curious of the best way to go about this. Is it via backup and restore
or some sort of replication solution.
Thanks,
George
I'd go for backup/restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> Question:
> Our Production Database is located off-site hosted by a 3rd party.
> I have SQL server installed in our local site. I want to have a backup
> copy of the Database from what is on Production located locally on
> an ongoing basis. I don't want the DB that is located locally to be
> used in anyway by the users. I just want to be sure I have a copy
> of the DB at our local office just in case anything happens. I sort
> of want to have a DB that mirrors the production site and also a
> TEST DB for testing purposes locally.
> I'm curious of the best way to go about this. Is it via backup and restore
> or some sort of replication solution.
> Thanks,
> George
>
>
|||Can I run it as a JOB. The DB use to be in Access we just migrated over to
SQL
server. I use to schedule a copy of the DB, via FTP, from the 3rd party
hosting server like
every 4 hours so that I had a fresh copy of the DB locally. Just curious
how I
do that with SQL server.
I basically just want to feel safe that I have a relatively current copy of
the DB stored
locally. Just in case some unforeseen event happens with our ISP? Like
they forgot
to do backups, or their site goes down, etc. ONCE, they were having a
problem with
their server and took our server off the backup routine and forgot to put it
back in. I
don't think it will happen again, but one never knows. I'd rather be sure
that I have
a copy in case they don't.
Just trying to figure out the best solution to ensure we have a copy stored
locally. Being
this DB is hosted remotely can you backup to a remote location? The 3rd
party DB server
is a shared DB server so other companies also have their DBs hosted on this
server.
Thanks for any help.
George
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> I'd go for backup/restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "george" <roberts_g@.marranohomes.com> wrote in message
news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
restore
>
|||> Can I run it as a JOB.
Sure. Just read up on backup and restore in books online, determine whether you want to go for only db backups
or both db backups and log backups. Then create your job(s) with the desired commands and frequency.
You could either use DTS, which has built-in support for FTP and combine DTS with SQL Command job steps. Then
schedule the DTS task in SQL Server agent.
Or use SQL Agent directly and combine TSQL with CMDExec (from where you run FTP.EXE or similar) tasks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:OrJ9lHpNEHA.2480@.tk2msftngp13.phx.gbl...
> Can I run it as a JOB. The DB use to be in Access we just migrated over to
> SQL
> server. I use to schedule a copy of the DB, via FTP, from the 3rd party
> hosting server like
> every 4 hours so that I had a fresh copy of the DB locally. Just curious
> how I
> do that with SQL server.
> I basically just want to feel safe that I have a relatively current copy of
> the DB stored
> locally. Just in case some unforeseen event happens with our ISP? Like
> they forgot
> to do backups, or their site goes down, etc. ONCE, they were having a
> problem with
> their server and took our server off the backup routine and forgot to put it
> back in. I
> don't think it will happen again, but one never knows. I'd rather be sure
> that I have
> a copy in case they don't.
> Just trying to figure out the best solution to ensure we have a copy stored
> locally. Being
> this DB is hosted remotely can you backup to a remote location? The 3rd
> party DB server
> is a shared DB server so other companies also have their DBs hosted on this
> server.
> Thanks for any help.
> George
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> restore
>
Our Production Database is located off-site hosted by a 3rd party.
I have SQL server installed in our local site. I want to have a backup
copy of the Database from what is on Production located locally on
an ongoing basis. I don't want the DB that is located locally to be
used in anyway by the users. I just want to be sure I have a copy
of the DB at our local office just in case anything happens. I sort
of want to have a DB that mirrors the production site and also a
TEST DB for testing purposes locally.
I'm curious of the best way to go about this. Is it via backup and restore
or some sort of replication solution.
Thanks,
George
I'd go for backup/restore.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> Question:
> Our Production Database is located off-site hosted by a 3rd party.
> I have SQL server installed in our local site. I want to have a backup
> copy of the Database from what is on Production located locally on
> an ongoing basis. I don't want the DB that is located locally to be
> used in anyway by the users. I just want to be sure I have a copy
> of the DB at our local office just in case anything happens. I sort
> of want to have a DB that mirrors the production site and also a
> TEST DB for testing purposes locally.
> I'm curious of the best way to go about this. Is it via backup and restore
> or some sort of replication solution.
> Thanks,
> George
>
>
|||Can I run it as a JOB. The DB use to be in Access we just migrated over to
SQL
server. I use to schedule a copy of the DB, via FTP, from the 3rd party
hosting server like
every 4 hours so that I had a fresh copy of the DB locally. Just curious
how I
do that with SQL server.
I basically just want to feel safe that I have a relatively current copy of
the DB stored
locally. Just in case some unforeseen event happens with our ISP? Like
they forgot
to do backups, or their site goes down, etc. ONCE, they were having a
problem with
their server and took our server off the backup routine and forgot to put it
back in. I
don't think it will happen again, but one never knows. I'd rather be sure
that I have
a copy in case they don't.
Just trying to figure out the best solution to ensure we have a copy stored
locally. Being
this DB is hosted remotely can you backup to a remote location? The 3rd
party DB server
is a shared DB server so other companies also have their DBs hosted on this
server.
Thanks for any help.
George
"Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> I'd go for backup/restore.
> --
> Tibor Karaszi, SQL Server MVP
> http://www.karaszi.com/sqlserver/default.asp
>
> "george" <roberts_g@.marranohomes.com> wrote in message
news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...[vbcol=seagreen]
restore
>
|||> Can I run it as a JOB.
Sure. Just read up on backup and restore in books online, determine whether you want to go for only db backups
or both db backups and log backups. Then create your job(s) with the desired commands and frequency.
You could either use DTS, which has built-in support for FTP and combine DTS with SQL Command job steps. Then
schedule the DTS task in SQL Server agent.
Or use SQL Agent directly and combine TSQL with CMDExec (from where you run FTP.EXE or similar) tasks.
Tibor Karaszi, SQL Server MVP
http://www.karaszi.com/sqlserver/default.asp
"george" <roberts_g@.marranohomes.com> wrote in message news:OrJ9lHpNEHA.2480@.tk2msftngp13.phx.gbl...
> Can I run it as a JOB. The DB use to be in Access we just migrated over to
> SQL
> server. I use to schedule a copy of the DB, via FTP, from the 3rd party
> hosting server like
> every 4 hours so that I had a fresh copy of the DB locally. Just curious
> how I
> do that with SQL server.
> I basically just want to feel safe that I have a relatively current copy of
> the DB stored
> locally. Just in case some unforeseen event happens with our ISP? Like
> they forgot
> to do backups, or their site goes down, etc. ONCE, they were having a
> problem with
> their server and took our server off the backup routine and forgot to put it
> back in. I
> don't think it will happen again, but one never knows. I'd rather be sure
> that I have
> a copy in case they don't.
> Just trying to figure out the best solution to ensure we have a copy stored
> locally. Being
> this DB is hosted remotely can you backup to a remote location? The 3rd
> party DB server
> is a shared DB server so other companies also have their DBs hosted on this
> server.
> Thanks for any help.
> George
>
>
> "Tibor Karaszi" <tibor_please.no.email_karaszi@.hotmail.nomail.com> wrote in
> message news:Oq2a27oNEHA.3492@.TK2MSFTNGP10.phx.gbl...
> news:eRJ0D6oNEHA.3380@.TK2MSFTNGP11.phx.gbl...
> restore
>
Friday, February 10, 2012
Backing up to a UNC path versus a local drive doesn't act the same way
Hi
I have just noticed a difference in how BACKUP works when I back up to a
local drive versu when I do it to a UNC path.
When I run the backup to a local drive, I can see that the .bak file gets
created, but then stays a 0 Kb until the backup is finished. When it's
finished, the .bak file is then being updated.
If I then run the same backup to the same location, but specify the location
as e.g. '\\Myserver\D$\MyBackupFolder\MybackupFile.bak' it acts differently.
Now the file id being updated on the fly every 3-4 seconds. I also noticed
that the latter takes significantly longer to complete the backup.
I assume that there's a reason for this difference so does any of you know
this reason? I have tried to look around so see if I could find something
that describes this but with no luck. Not that it matters, but I'm just
curious...:-).
Regards
SteenHi
With UNC paths, backup up to the local machine, the traffic has to go to the
network re-director, down the TCP/IP stack, get turned around when it finds
the host, and go up the stack again. Totally expected.
File access on networks, compared to local disk access is very different at
API level.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steen Persson" wrote:
> Hi
> I have just noticed a difference in how BACKUP works when I back up to a
> local drive versu when I do it to a UNC path.
> When I run the backup to a local drive, I can see that the .bak file gets
> created, but then stays a 0 Kb until the backup is finished. When it's
> finished, the .bak file is then being updated.
> If I then run the same backup to the same location, but specify the location
> as e.g. '\\Myserver\D$\MyBackupFolder\MybackupFile.bak' it acts differently.
> Now the file id being updated on the fly every 3-4 seconds. I also noticed
> that the latter takes significantly longer to complete the backup.
> I assume that there's a reason for this difference so does any of you know
> this reason? I have tried to look around so see if I could find something
> that describes this but with no luck. Not that it matters, but I'm just
> curious...:-).
> Regards
> Steen
>
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> With UNC paths, backup up to the local machine, the traffic has to go to the
> network re-director, down the TCP/IP stack, get turned around when it finds
> the host, and go up the stack again. Totally expected.
> File access on networks, compared to local disk access is very different at
> API level.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Steen Persson" wrote:
>
>>Hi
>>I have just noticed a difference in how BACKUP works when I back up to a
>>local drive versu when I do it to a UNC path.
>>When I run the backup to a local drive, I can see that the .bak file gets
>>created, but then stays a 0 Kb until the backup is finished. When it's
>>finished, the .bak file is then being updated.
>>If I then run the same backup to the same location, but specify the location
>>as e.g. '\\Myserver\D$\MyBackupFolder\MybackupFile.bak' it acts differently.
>>Now the file id being updated on the fly every 3-4 seconds. I also noticed
>>that the latter takes significantly longer to complete the backup.
>>I assume that there's a reason for this difference so does any of you know
>>this reason? I have tried to look around so see if I could find something
>>that describes this but with no luck. Not that it matters, but I'm just
>>curious...:-).
>>Regards
>>Steen
>>
>>
Thanks for the input Mike.
I know that using a UNC path involves the network, and therefore it's
also understandable that it takes longer time to do the backup.
Do you know why the file updates differently with the two scenarios? I'd
assume that it's due to the differnet ways the file is being
addressed, but do you know the correct reason?
Regards
Steen
I have just noticed a difference in how BACKUP works when I back up to a
local drive versu when I do it to a UNC path.
When I run the backup to a local drive, I can see that the .bak file gets
created, but then stays a 0 Kb until the backup is finished. When it's
finished, the .bak file is then being updated.
If I then run the same backup to the same location, but specify the location
as e.g. '\\Myserver\D$\MyBackupFolder\MybackupFile.bak' it acts differently.
Now the file id being updated on the fly every 3-4 seconds. I also noticed
that the latter takes significantly longer to complete the backup.
I assume that there's a reason for this difference so does any of you know
this reason? I have tried to look around so see if I could find something
that describes this but with no luck. Not that it matters, but I'm just
curious...:-).
Regards
SteenHi
With UNC paths, backup up to the local machine, the traffic has to go to the
network re-director, down the TCP/IP stack, get turned around when it finds
the host, and go up the stack again. Totally expected.
File access on networks, compared to local disk access is very different at
API level.
Regards
--
Mike Epprecht, Microsoft SQL Server MVP
Zurich, Switzerland
MVP Program: http://www.microsoft.com/mvp
Blog: http://www.msmvps.com/epprecht/
"Steen Persson" wrote:
> Hi
> I have just noticed a difference in how BACKUP works when I back up to a
> local drive versu when I do it to a UNC path.
> When I run the backup to a local drive, I can see that the .bak file gets
> created, but then stays a 0 Kb until the backup is finished. When it's
> finished, the .bak file is then being updated.
> If I then run the same backup to the same location, but specify the location
> as e.g. '\\Myserver\D$\MyBackupFolder\MybackupFile.bak' it acts differently.
> Now the file id being updated on the fly every 3-4 seconds. I also noticed
> that the latter takes significantly longer to complete the backup.
> I assume that there's a reason for this difference so does any of you know
> this reason? I have tried to look around so see if I could find something
> that describes this but with no luck. Not that it matters, but I'm just
> curious...:-).
> Regards
> Steen
>
>|||Mike Epprecht (SQL MVP) wrote:
> Hi
> With UNC paths, backup up to the local machine, the traffic has to go to the
> network re-director, down the TCP/IP stack, get turned around when it finds
> the host, and go up the stack again. Totally expected.
> File access on networks, compared to local disk access is very different at
> API level.
> Regards
> --
> Mike Epprecht, Microsoft SQL Server MVP
> Zurich, Switzerland
> MVP Program: http://www.microsoft.com/mvp
> Blog: http://www.msmvps.com/epprecht/
>
> "Steen Persson" wrote:
>
>>Hi
>>I have just noticed a difference in how BACKUP works when I back up to a
>>local drive versu when I do it to a UNC path.
>>When I run the backup to a local drive, I can see that the .bak file gets
>>created, but then stays a 0 Kb until the backup is finished. When it's
>>finished, the .bak file is then being updated.
>>If I then run the same backup to the same location, but specify the location
>>as e.g. '\\Myserver\D$\MyBackupFolder\MybackupFile.bak' it acts differently.
>>Now the file id being updated on the fly every 3-4 seconds. I also noticed
>>that the latter takes significantly longer to complete the backup.
>>I assume that there's a reason for this difference so does any of you know
>>this reason? I have tried to look around so see if I could find something
>>that describes this but with no luck. Not that it matters, but I'm just
>>curious...:-).
>>Regards
>>Steen
>>
>>
Thanks for the input Mike.
I know that using a UNC path involves the network, and therefore it's
also understandable that it takes longer time to do the backup.
Do you know why the file updates differently with the two scenarios? I'd
assume that it's due to the differnet ways the file is being
addressed, but do you know the correct reason?
Regards
Steen
Backing up to a remote server
I am just getting started with MS SQL and am having to learn from scratch. I have a DB running with an ISP which I need to back up to one of my local machines. I have installed MS SQL Server 2005 and also MS SQL Server Management Studio Express. Using this, I have opened a new DB on the local machine and then accessed the DB on the remote server. If I then right click on the remote DB file and select Tasks\Backup, I see the backup options screen. The problem is that the destination for the backup is automatically set to a folder on the remote server and I cannot figure out how to change this so that I can download to the local machine. If I click on the 'Add' button to create a new backup path, it will only offer me a view of the remote machine, not the local file structure.
The solution is probably pretty simple, but after several hours of experimentation and searching for solutions, I still can't come up with the answer. Can anyone offer some guidance please (in idiot language - I'm new to this).
When you are looking at the remote database you want to backup, is there a Remove button right below the Add button? If so, click the Remove button.
Then on the local server you'll need to Share out your backup folder. So if your backup folder is J:\sqlbackups, you'll need to share that folder with at least Change permissions at the share level, and Write permisisons at the NTFS folder level.
Once you have done that go back to your backup screen, and click Add, when the window pops up, instead of browsing for a location, type in the share location you just created, i.e. \\mylocalserver\sqlbackups
You also have the option of not sharing out your local backup folder and just typing in \\mylocalserver\j$\sqlbackups, but that is not usually a recommended way of doing it.
When you are backing up a remote database, it needs to be directed where it should be backed up to on your local server via a network share. Otherwise it only understands the file structure on the server it is running on, because it is on a separate physical server from where you are trying to create the backup.
Subscribe to:
Posts (Atom)