[Last Call] Learn about multicloud storage options and how to improve your company's cloud strategy. Register Now

x
?
Solved

backup another instance's database

Posted on 2011-02-28
10
Medium Priority
?
252 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
[X]
Welcome to Experts Exchange

Add your voice to the tech community where 5M+ people just like you are talking about what matters.

  • Help others & share knowledge
  • Earn cash & points
  • Learn & ask questions
  • 5
  • 3
  • 2
10 Comments
 
LVL 12

Accepted Solution

by:
NormanMaina earned 668 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 1332 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
Industry Leaders: We Want Your Opinion!

We value your feedback.

Take our survey and automatically be enter to win anyone of the following:
Yeti Cooler, Amazon eGift Card, and Movie eGift Card!

 
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
 
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 1332 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

Get your Conversational Ransomware Defense e‑book

This e-book gives you an insight into the ransomware threat and reviews the fundamentals of top-notch ransomware preparedness and recovery. To help you protect yourself and your organization. The initial infection may be inevitable, so the best protection is to be fully prepared.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

This article shows gives you an overview on SQL Server 2016 row level security. You will also get to know the usages of row-level-security and how it works
One of the most important things in an application is the query performance. This article intends to give you good tips to improve the performance of your queries.
Via a live example, show how to extract information from SQL Server on Database, Connection and Server properties
This videos aims to give the viewer a basic demonstration of how a user can query current session information by using the SYS_CONTEXT function

650 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