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 |
|
CameronY
Starting Member
4 Posts |
 Posted - 2006-03-04 : 01:23:50
|
| Hi all,Am extremely new to SQL and have come stuck writing a DOS script that works well if the DB's are on the same server. My problem is that one of the DB's the script is to manage is on another server and I've been losing sleep trying to work out how to resolve this.I'm trying to keep (if possible) the script as simple as I explain below.For the successful DEMO backups, I have the following ....The SQL batch file (%SQLFile%) reads:backup database DB_DEMO to DISK = 'C:\DA_Installs\BOS\DEMO\testing\backups\dbase\DB_DEMOFri.bak' with init go In the DOS script, I have:osql -S %DBSrvr% -E -d %SQLDatabase% -n -i %SQLFile% -o %SQLBackupLog% >> %OutputLog%And this is successful, as the script and the DB reside on the same server. However I'm troubled by how I connect to the DB_LIVE db on another server and write the backup to the same server the script is run from.Any help is really appreciated.Cheers,Cameron |
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-04 : 02:42:38
|
| There are a couple of things to conquer!First you need to be able to connect to the remote server. You should test that until you get the parameters correct for your connection:osql -S MyRemoteServer -UMyUserID -PMyPassword -q "SELECT TOP 10 * FROM sysobjects"or use -E instead of username/Password if you have a trusted connection.Then you should be able to run a command like "-i %SQLFile%" - the file will be on your local computer. However, the script will run on the Remote Server and will be accessing the file system locally there. So to make a backup to your local drive you would need to use a BACKUP command like:backup database DB_DEMO to DISK = '\\MyMachine\MyShare\...\DB_DEMOFri.bak' with init go However that assumes that the domain user that SQL is using to run its tasks has permissions to reach MyShare on MyMachine.Either way, it is usually "better" to backup to a local disk on the remote server and them copy/move the file to the second machine. That way the backup will still work, even if location for the backup file across the network is "unreachable", so at least your database is backed up!Kristen |
 |
|
|
Michael Valentine Jones
Yak DBA Kernel (pronounced Colonel)
7020 Posts |
Posted - 2006-03-04 : 04:24:18
|
Is there some reason you can’t just set the backup up as a SQL Agent scheduled job on the remote server, and just let the job run automatically at a scheduled time?Even if you do want to control it remotely via OSQL, you could just set it up as a job without a schedule, and start it with the sp_start_job stored procedure via OSQL:osql -S MyRemoteServer -d msdb -E -Q "EXEC sp_start_job @job_name = 'Nightly Backup' " CODO ERGO SUM |
|
|
|
CameronY
Starting Member
4 Posts |
Posted - 2006-03-04 : 07:57:22
|
| Hello Michael & Kristen,Many thanks for your reply's. With it being the w/end I wasn't expecting much chance of a reply until next week, so thanks again.Kristen, I have full access to the remote server as I'm an administrator on all servers and both servers in question are on the same network segment (x.x.26.39 & x.x.26.40).I did perform the command that you suggested (many thanks) on the remote server (.39) and I was successfull (returning 10 rows, after which I repeated the command on (.40) with the same results.Command issued:c:\>osql -S DBSrvr -E -q "SELECT TOP 10 * FROM sysobjects"A recent attempt returned the following in the SQLBkupLog:Msg 3201, Level 16, State 1, Server [Rmt-DBSrvr], Line 1Cannot open backup device'\\[Local-DBSrvr]\c$\DA_Installs\BOS\DEMO\testing\backups\dbase\DB_DEMOSat.bak'. Device error or device off-line. See the SQL Servererror log for more details.Msg 3013, Level 16, State 1, Server [Rmt-DBSrvr], Line 1BACKUP DATABASE is terminating abnormally.Also, none of these servers are part of any domain.Sorry if it sounds like a silly question, but where specifying the database name, is it case-sensiive ??Michael, many thanks for the suggestion. But I'm trying to keep control of this script all under one umbrella. I've found some aspects of this daunting, hence why I want to keep it simple.Cheers,Cameron |
|
|
|
Kristen
Test
22859 Posts |
Posted - 2006-03-05 : 10:34:01
|
| "Device error or device off-line"Sounds to me as if the UserID that is being used as the "log on" for SQL Server's service does not have permissions to that share. Note that this will NOT run with your own file permissions on the server!"where specifying the database name, is it case-sensiive"Always best to assume that, and of course does no harm to use the correct case!, but its only true if the installation was made using a case sensitive collation (which is NOT the default, but there are far too many places that I visit using Great Plains software or somesuch which are case sensitive!)Kristen |
|
|
|
|
|
|
|
|
Using osql to backup remote db