Solved

backup another instance's database

Posted on 2011-02-28
10
231 Views
Last Modified: 2012-05-11
is it possible to backup another instance's database, if there was proper permissions?

something like
backup database instancename.databasename to disk = 'c:\...'

thanks
0
Comment
Question by:anushahanna
  • 5
  • 3
  • 2
10 Comments
 
LVL 12

Accepted Solution

by:
NormanMaina earned 167 total points
ID: 35000291
Yes its possible.
If that instance is on the same machine as the C:/ drive folder you want to back up its fairly straighforward.
If the instance is in another server/computer,then the backup will be saved on the file system of that server.To get access to it in your computer,you have to share that folder and access it using \\srvername\foldername
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35000333
>>If the instance is in another server/computer,then the backup will be saved on the file system of that server.

yes, both instance is on diff servers.

the instance running the command would like to get the backup to its hard drive which is also shared with the instance that is hosting the database we are interested in.
0
 
LVL 6

Assisted Solution

by:LCSandman8301
LCSandman8301 earned 333 total points
ID: 35000542
i would probably add a win script between the servers to copy the file over to the desired machine after the copy has completed.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35000544
how does the syntax differ when you go cross-servers, like the above?
0
 
LVL 12

Expert Comment

by:NormanMaina
ID: 35000573
Paste the following Transact-SQL script in Notepad, and then save it to a file named MyBackupScript.sql:
Will require some modification to run in your environment. For example, database name, server name, and so forth.
From the command line, use the following osql syntax to run the Transact-SQL script:
  OSQL -Usa -PmyPasword -i myBackupScript.sql -n

You may want to schedule the job instead of running it manually. To do this, you can use the earlier code to create the job. Instead of calling the sp_start_job stored procedure to run the job manually, you can use the sp_add_jobschedule stored procedure to configure the job to run at a specified date and time. The SQL Agent manages the job scheduling. This code demonstrates use of the sp_add_jobschedule stored procedure:

-- Use the job creation code from the previous sample and call sp_add_jobschedule instead of sp_start_job.
-- Schedule job.
USE msdb
EXEC sp_add_jobschedule @job_name = 'myTestBackupJob',
    @name = 'ScheduledBackup_msdb',
    @freq_type = 4, --daily
    @freq_interval = 1, --once
    @active_start_time = '153000' --(3:30 pm) 24hr HHMMSS.
go

--Use the following code to remove or delete the backup job, created earlier, and then remove the job from the Scheduler.
-- Delete scheduled job.
USE msdb
EXEC sp_delete_jobschedule @job_name = N'myTestBackupJob',
      @name = N'ScheduledBackup_msdb'

-- Delete job.
USE msdb
EXEC sp_delete_job @job_name = N'myTestBackupJob'


--This Transact-SQL script creates a backup job and calls sp_start_job to run the job.

-- Create job.
-- You may specify an e-mail address, commented below, and/or pager, etc.
-- For more details about this option or others, see SQL Server Books Online.
USE msdb
EXEC sp_add_job @job_name = 'myTestBackupJob',
    @enabled = 1,
    @description = 'myTestBackupJob',
    @owner_login_name = 'sa',
    @notify_level_eventlog = 2,
    @notify_level_email = 2,
    @notify_level_netsend =2,
    @notify_level_page = 2
--  @notify_email_operator_name = 'email name'
go

-- Add job step (backup data).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb Data',
    @subsystem = 'TSQL',
    @command = 'BACKUP DATABASE msdb TO DISK = ''c:\msdb.dat_bak''',
    @on_success_action = 3,
    @retry_attempts = 5,
    @retry_interval = 5
go

-- Add job step (backup log).
USE msdb
EXEC sp_add_jobstep @job_name = 'myTestBackupJob',
    @step_name = 'Backup msdb Log',
    @subsystem = 'TSQL',
    @command = 'BACKUP LOG msdb TO DISK = ''c:\msdb.log_bak''',
    @on_success_action = 1,
    @retry_attempts = 5,
    @retry_interval = 5
go

-- Add the target servers.
USE msdb
EXEC sp_add_jobserver @job_name = 'myTestBackupJob', @server_name = N'(local)'

-- Run job. Starts the job immediately.
USE msdb
EXEC sp_start_job @job_name = 'myTestBackupJob'

Open in new window

0
How to improve team productivity

Quip adds documents, spreadsheets, and tasklists to your Slack experience
- Elevate ideas to Quip docs
- Share Quip docs in Slack
- Get notified of changes to your docs
- Available on iOS/Android/Desktop/Web
- Online/Offline

 
LVL 6

Expert Comment

by:LCSandman8301
ID: 35000604
what i am referring to is run the backup, then copy the file to the desired server after completion. but this would be in the world of winscripting not db scripting.
otherwise if worse comes to worse. you can always add the drive you are trying to copy to using the xp_cmdshell command

exec xp_cmdshell 'net use x: \\unc_path_of_desired_drive /user:username_of_person_with_privs password_of_person_with_privs'

0
 
LVL 6

Assisted Solution

by:LCSandman8301
LCSandman8301 earned 333 total points
ID: 35000622
then you would just run your backup job to the newly mapped drive. beware it will take longer since it will be running over the network.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35000779
OK- thanks for the idea- but now I will need restore also.

the scenario is:

Server1 has the database.
It has to restored to Server2 everyday at a particular time. So job is needed-yes.

but how do i time the job to backup to server 2's hard drive, and then server 2 to pick it up to restore it?

that is why i was wondering about server2 to be able to backup and then next step can restore in the job, so it is all in one job.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35000872
LCS, over the network is not an issue - it is a small DB. prefer to avoid winscripting  (unless no go).

but with that solution, we are looking at 2 jobs in 2 servers and wondering how to sync them up. that is why i had asked about directly backing up from another server, which can be accommodated in one server's job.
0
 
LVL 6

Author Comment

by:anushahanna
ID: 35000899
NormanMaina, does the job script accommodate working between 2 servers?
0

Featured Post

Zoho SalesIQ

Hassle-free live chat software re-imagined for business growth. 2 users, always free.

Join & Write a Comment

Suggested Solutions

Everyone has problem when going to load data into Data warehouse (EDW). They all need to confirm that data quality is good but they don't no how to proceed. Microsoft has provided new task within SSIS 2008 called "Data Profiler Task". It solve th…
Ever wondered why sometimes your SQL Server is slow or unresponsive with connections spiking up but by the time you go in, all is well? The following article will show you how to install and configure a SQL job that will send you email alerts includ…
Viewers will learn how to use the INSERT statement to insert data into their tables. It will also introduce the NULL statement, to show them what happens when no value is giving for any given column.
Viewers will learn how to use the UPDATE and DELETE statements to change or remove existing data from their tables. Make a table: Update a specific column given a specific row using the UPDATE statement: Remove a set of values using the DELETE s…

708 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question

Need Help in Real-Time?

Connect with top rated Experts

19 Experts available now in Live!

Get 1:1 Help Now