Backing up SQL Server MSDE and Express

We are attempting to backup using a maintenance plan a database to a network share.

The backup job works within a domain but fails within a workgroup?

Any thoughts?

thanks

hi Larry,

Larry Sitka wrote:

We are attempting to backup using a maintenance plan a database to a network share.

The backup job works within a domain but fails within a workgroup?

under which account does the maintenance plans run?

does the account running SQL Server have enought permissions on the remote network share? or the account on which the maintenance plan runs?

regards

Backing up database over network

I am trying to get my database to backup to a network
path (\\myserver\backup\db.bak) instead of a local path.
But, unfortunately it is not working, I keep the message
that it cannot connect to the device or the device is
offline.
Here is my backup statement
BACKUP DATABASE test
TO DISK = 'c:\temp\test.bak'
WITH FORMAT,
NAME = 'Test Backup'
Any Ideas?The account that sqls erver runs under is the one that is used when backups
occur. So you need to ensure that this account has permissions to the
network share you are attempting to backup to. Chances are the account is
the local admin account which has no domain privileges.
--
Andrew J. Kelly
SQL Server MVP
"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in message
news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
> I am trying to get my database to backup to a network
> path (\\myserver\backup\db.bak) instead of a local path.
> But, unfortunately it is not working, I keep the message
> that it cannot connect to the device or the device is
> offline.
> Here is my backup statement
> BACKUP DATABASE test
> TO DISK = 'c:\temp\test.bak'
> WITH FORMAT,
> NAME = 'Test Backup'
> Any Ideas?|||I have already checked all of that and the account has
full access to my network share.
>--Original Message--
>The account that sqls erver runs under is the one that
is used when backups
>occur. So you need to ensure that this account has
permissions to the
>network share you are attempting to backup to. Chances
are the account is
>the local admin account which has no domain privileges.
>--
>Andrew J. Kelly
>SQL Server MVP
>
>"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in
message
>news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
>> I am trying to get my database to backup to a network
>> path (\\myserver\backup\db.bak) instead of a local
path.
>> But, unfortunately it is not working, I keep the
message
>> that it cannot connect to the device or the device is
>> offline.
>> Here is my backup statement
>> BACKUP DATABASE test
>> TO DISK = 'c:\temp\test.bak'
>> WITH FORMAT,
>> NAME = 'Test Backup'
>> Any Ideas?
>
>.
>|||Ben,
The example you showed was not using UNC and was obviously not the real
code. It helps to show the actual code to alleviate any assumptions or the
possibility of simply a typo that doesn't get relayed with a simpler
example. So I assume from what you state you have logged on to the server
under the account sqls erver is running under and were able to copy a file
with the exact name of the backup to the UNC you want. If not then check to
ensure there isn't already a file with that name that is locked by some
other resource.
--
Andrew J. Kelly
SQL Server MVP
"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in message
news:037301c3a589$687422d0$a601280a@.phx.gbl...
> I have already checked all of that and the account has
> full access to my network share.
>
> >--Original Message--
> >The account that sqls erver runs under is the one that
> is used when backups
> >occur. So you need to ensure that this account has
> permissions to the
> >network share you are attempting to backup to. Chances
> are the account is
> >the local admin account which has no domain privileges.
> >
> >--
> >
> >Andrew J. Kelly
> >SQL Server MVP
> >
> >
> >"Ben Skolmoski" <ben.skolmoski@.imcentric.com> wrote in
> message
> >news:02e901c3a581$03d068a0$a601280a@.phx.gbl...
> >> I am trying to get my database to backup to a network
> >> path (\\myserver\backup\db.bak) instead of a local
> path.
> >> But, unfortunately it is not working, I keep the
> message
> >> that it cannot connect to the device or the device is
> >> offline.
> >>
> >> Here is my backup statement
> >>
> >> BACKUP DATABASE test
> >> TO DISK = 'c:\temp\test.bak'
> >> WITH FORMAT,
> >> NAME = 'Test Backup'
> >>
> >> Any Ideas?
> >
> >
> >.
> >|||If you log on to the SQL Server machine with the SQL Server startup account
can you access the remote share that you are trying to restore to/
Rand
This posting is provided "as is" with no warranties and confers no rights.sql

Back up file?

Hello everyone,
I have a PC and I am using it as a "mini" server for Temp testing purpose. This PC is on the network. I am wondering if there is a DB file with specific name and extension I can save on our network drive or folder which is backed up automatically overnight daily.
Any suggestion/comments are highly appreciated!not sure I got your question .. did you want to ask if you could back up your database to a network server|||That's correct. I have this "mini server" as a testing machine which is not "supported" by IT group since it is not part of data center. I do want my "mini server" backed up. I know all the files on the network drive get backed up overnight so that I am wondering if there is a file or set of files I could push onto our network drive so that my database can be backed up.

Thank you for the help.|||moving the 'YourDB.mdf' and 'YourDB_Log.ldf' files to the network drive will help you to back it up

;)|||moving the 'YourDB.mdf' and 'YourDB_Log.ldf' files to the network drive will help you to back it up

;)

Do you think your Database will work after that ?

What you need to do is schedule a backup of your database using the "Backup database" command and move it over to the file share.|||Do you think your Database will work after that ?Definitely not, but at least it could be recovered if you had enough expertise.

On a more serious note, you really need to use the BACKUP DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ba-bz_35ww.asp) command to produce a dump file. That file can be copied wherever you like, backed up, whatever... It can be restored using RESTORE DATABASE (http://msdn.microsoft.com/library/default.asp?url=/library/en-us/tsqlref/ts_ra-rz_25rm.asp) command, and things will be exactly as they were when you issued the first BACKUP DATABASE command.

-PatP|||Thank you for all replies. Just want to be clear, I need to use database backup command to backup my current DB. Can I assume everything (tables, stored procedures, DTS package and etc) I have created in the DB will be saved and backed up on the network. Is my assumption right?
Many thanks.|||All of the transactions that are committed by the end of the backup operation will be included in the backup file. One file will be produced, regardless of how many files make up your database (you could have an MDF, a few NDF, and a couple of LDF files and still get just one dump file). The dump file created by the backup can then be zipped, copied, etc as you see fit, and that dump can be used to restore the database later.

-PatP|||... (tables, stored procedures, DTS package and etc) I have created in the DB will be saved and backed up on the network. Is my assumption right?
Many thanks.
DTS packages are not stored in user databases. If they are stored on the SQL Server, they are in the MSDB database. Soooooo ... if you want to save everything, you will need to backup **all** your databases then move them off server for storage.|||would you please more explicit about DTS packages are not stored in user databases. if you want to save everything, you will need to backup **all** your databases then move them off server for storage.
How and what I should do in order to backup **all**? Is there a function or command I should do? Please advise.|||Open Books OnLine. Search the index for the "backup" command. Make sure you run a backup for each and every database on your server. Take all the backup files created during this process and copy them to a safe place off your server.

If you are so inclined, you can create a database mainenance plan to backup all databases on the server in one job than can be run by the SQLServer Agent, if it is active. Look up "Database Maintenance Plan Wizard" in Books OnLine.|||I tried to save DTS packages. I have backed up msdb database on my local PC (which is SQL server as well) c:program files\MS SQL server\backup\msdb_backup (I just wanted to test to see if it works the way I expected since this is my first time try so that only saved on the server directory.)
I went back to Enterprise Mgr and select "Restore database...", and select the file [msdb_backup] which was created through backup command. I got the error message attached. Not sure why. Would you please give me a hint?

Thank you in advance for helping me out!|||here is the error msg.|||Have you got Query Analyzer open at the time that you try to back up the database?
If so, this spawns a process that connects to your database. When you backup a database, SQL Server tries to obtain an exclusive database lock (to ensure that data cannot be changed while the backup is in progress) and it cannot do this if there is another process connected to the database.

Check out 'How to view current server activity' in Books Online.

Once you've made sure that there are no other processes connected to your database, you can put the database in 'single user' mode by right-clicking on the database name, selecting Properties and checking the 'Restrict access' box on the Options tab.
Then backup your database.

Avoid increasing the log file

Hello All,

I have faced a network problem during some days, what forced one of our replications to be stopped.
The Publisher database is a high volume database.
After I re-started the replication, the Subscirber database has its transacting log size increased quickly, because of the high volume of information to be inserted.

My concern is the way it is working, there will be no enough space for the log or for its backup files.

So, I have created a TSQL job within the following commands:

BACKUP LOG database_name
WITH TRUNCATE_ONLY
DBCC SHRINKDATABASE (database_name,TRUNCATEONLY)

It's running every 20 minutes, however the transaction log remains increasing.

I have also changed the db_option "SELECT INTO/BULKCOPY" to TRUE, in order to avoid logging bulk copies, but I believe, it didn't work because it didn't apply to replication process.

Does Anybody know if I can disable the transaction log or avoid this incresing of size during the replication?

Thanks a lot!
Regards,
Felicia Schimidt
felicia.schimidt@.br.flextronics.comDon't really know anything about internals on MS replication...

On Sybase ASE, the replication generates a secondary truncation point. In your situation, it's possible to disable this 2nd point. Without disabling, it's not possible to truncate the part between the 1st and the 2nd truncation point.

Check on MS-SQL whether it's possible to execute a dbcc gettrunc()... or something "like"|||Hi,

Thanks for your help, but I could not find anything like gettrunc()...

Please, does anybody know anything about the issue below?
I am really in trouble!

Thanks again!
Felicia

Originally posted by fadace
Don't really know anything about internals on MS replication...

On Sybase ASE, the replication generates a secondary truncation point. In your situation, it's possible to disable this 2nd point. Without disabling, it's not possible to truncate the part between the 1st and the 2nd truncation point.

Check on MS-SQL whether it's possible to execute a dbcc gettrunc()... or something "like"|||If you're truncating the log with TRUNCATEONLY you are effectively destroying it, so you might as well ALTER DATABASE the database to SIMPLE RECOVERY mode, and you won't get a transaction log at all.

In essence, your real problem happens because a significant portion of the log is 'active' - in other words holding an open transaction, at the point the you are issuing the truncate command. Do you have a large long running transaction hanging fire?

Generally, you'll be better off either putting very large storage behind a real transaction log and backing off the log properly, or doing away with it altogether if your recovery policy will permit. Growing and shrinking a transaction log repetitively will lead to considerable disk fragmentation over time and cause performance problems particularly as you obviously have a very large load.

also

"For each database that will be published in transactional replication, ensure that the transaction log has enough space allocated. The transaction log of a published database may require more space than the log of an identical, unpublished database. This is because the log records may not be purged until they have been moved to the distribution database.

"If the distribution database is unavailable, or if the Log Reader Agent is not running, the transaction log of a publication database continues to grow. The log cannot be truncated past the oldest published transaction that has not been passed into the distribution database (unless replication is turned off completely for that database). It is recommended that you set the transaction log file to autogrow so that the log can accommodate these circumstances."

http://msdn.microsoft.com/library/default.asp?url=/library/en-us/replsql/replplan_1l4e.asp|||Agree with HanafiH. A single replicated transaction could consist of tens of thousands of commands. Those operations will cause subscriber log to grow until the last command is commited. In your case, putting the subsriber database in simple recovery mode, and/or getting enough disk space to allow largest potential log growth, is the best option.

You may also want to turn off "auto shrink" to avoid performance issue.|||Hi,

Thanks for you help.

I have setup the database to simple recovery mode and turnning subscriber agent on.
Unfortunnatelly the Transaction log grew again quickly and no disk space was availble.

Is there any other way to force the database to not use the transaction log?

Thanks a lot,
Felicia

Originally posted by rding
Agree with HanafiH. A single replicated transaction could consist of tens of thousands of commands. Those operations will cause subscriber log to grow until the last command is commited. In your case, putting the subsriber database in simple recovery mode, and/or getting enough disk space to allow largest potential log growth, is the best option.

You may also want to turn off "auto shrink" to avoid performance issue.|||some non-logged operations don't seem to fit in your scenario. You probably need to reset up the replication.