January 3, 2008 at 6:10 pm
I'm starting from a 'remote' SS2K5 Standard Edition machine. I'm trying to execute, remotely, a backup job of a small (250MB) database nightly, running under SQLExpress, so I want to originate the process from the SS2K5. I've only been successful connecting via TCP/IP, using the explicit IP address without naming the database. In SSMS I am able to connect to the database, but, in essence, the only way I can see to run a backup would be to copy the database over to my local machine and then backup locally. One bump in the road - the process the users exercise is constantly creating NEW tables so I'm not able to easily 'publish' the database and then backup via subscription.
Possible solutions:
1. establish a 'named' connection to the remote machine housing the database in question, that would allow me to 'reach out' and touch the db with a fully qualified name (can you execute 'USE a.b.c.d' or rather, 'a.b', where 'a' is an IP address?
2. establish a brute force copy of the database over to my SE machine where I can then perform a normal backup.
3. wave a magic wand?
TIA.
January 8, 2008 at 12:36 pm
Do you have to do it remotely or can you setup a .bat file on the SQL Express server to execute sqlcmd to backup the database? You can then schedule the .bat file using Windows Scheduler.
January 9, 2008 at 8:53 am
Steve,
I think an option 4 would be more viable for your situation. I have implemented the sqlcmd/.bat file via Windows Scheduler technique on our single SQL Server 2005 Express production machine, as Adam has suggested.
In my opinion, it would be easier for you to be able to pick up the .BAK file off the operating system, or even write across the network to your destination SQL box, than to "jury rig" the SQL Server Express 2005 database backup through the SQL Agent on the destination machine.
If you choose to go the sqlcmd route you will need two files per database backup, and a Windows Scheduled task.
Option 4:
** My examples are for backing up the master database. **
File 1: Backup_master.sql
Full backup of the master database, written in T-SQL backup script.
File 2: Backup_master_sqlcmd.cmd
Runs the Backup_master.sql script and writes out a little results file.
Scheduled Task: SCHTASKS.exe
Schedule a task for the frequency at which you want the backup script be executed, similar to the Schedule in SQL Agent. My example runs daily at 6:30 PM by a domain account in Active Directory.
--
File 1: Backup_master.sql
BACKUP DATABASE [master] TO DISK = N'D:\DB_BACKUPS\master.BAK' WITH NOFORMAT, INIT, NAME = N'master-Full local Database Backup', SKIP, NOREWIND, NOUNLOAD, STATS = 10, CHECKSUM
GO
declare @backupSetId as int
select @backupSetId = position from msdb..backupset where database_name=N'master' and backup_set_id=(select max(backup_set_id) from msdb..backupset where database_name=N'master' )
if @backupSetId is null begin raiserror(N'Verify failed. Backup information for database ''master'' not found.', 16, 1) end
RESTORE VERIFYONLY FROM DISK = N'D:\DB_BACKUPS\master.BAK' WITH FILE = @backupSetId, NOUNLOAD, NOREWIND
GO
--
File 2: Backup_master_sqlcmd.cmd
sqlcmd -S -i D:\DB_Backups\Backup_master.sql -o D:\DB_Backups\Backup_master_Results.txt
--
Scheduled Task: SCHTASKS.exe
SCHTASKS.exe notes
To schedule a task that runs every day
The following example schedules the MyApp program to run once a day, every day, at 8:00 A.M. until December 31, 2002. Because it omits the /mo parameter, the default interval of 1 is used to run the command every day.
In this example, because the local computer system is set to the English (United Kingdom) option in Regional and Language Options in Control Panel, the format for the end date is DD/MM/YYYY (31/12/2002)
schtasks /create /tn "My App" /tr c:\apps\myapp.exe /sc daily /st 08:00 /ed 31/12/2002
** Note that the 'schtasks' command below will need to be written in a single line to work properly, it may wrap in your view of the forum posting. **
schtasks /create /tn "Backup master" /tr D:\DB_Backups\Backup_master_sqlcmd.cmd /sc daily /st 18:30 /s
--
All of the above will write out a 'master.BAK' file to the D:\DB_Backups folder on the machine that SQL Server 2005 Express is installed on. It should not take too much modification to write that file to your SQL 2005 machine instead, all ready for a restore job by your SQL Agent on your destination server.
Best of Luck!
"Key"
MCITP: DBA, MCSE, MCTS: SQL 2005, OCP
January 9, 2008 at 9:07 am
Create a stored procedure to do backups on the SQL Express server, and then execute it with SQLCMD or OSQL in a job from another server. You will have to grant the SQL Server account that you run the remote job under proper access to the SQL Express server.
Example:
exec master.dbo.xp_cmdshell 'osql -S REMOTE_SERVERNAME -E -Q "EXEC MyDB.dbo.Backup_Stored_procedure"'
January 9, 2008 at 10:34 am
Thank you for your responses! At this point in time, until the process has settled down and we've resolved how to maintain consistent passwords across the various systems, our expectation is to schedule the daily batch job on the local machine and then zip/ship the backup files offsite once the backup has been run, all within the same batch job.
Unfortunately, the capability to do 'baby' tasks like this on an automated basis has been removed from SQL Server 2005 (I found references to this ability existing in SS2K). I understand the interest in selling, rather than giving away, SS installations, so it's hard for me to make a strong argument for provisioning a full-blooded version of SS as a freebie. I will note, however, that without some type of scheduling tool, it's hard for newcomers to learn how to use SSMS in all its glory. But don't wait for that 'protectionism' to show up in paychecks anytime soon!
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply