October 14, 2008 at 12:17 pm
Hi,
I need to do a backup of a database on Server 1 and copy it to Server 2 and restore that database in server 2. For this I heard that we can use batch script so that we can create a job and automate?
could you plz tell me how to do this?
October 15, 2008 at 8:19 am
You can use the Import and Export wizard for this purpose, it does BCP and back end, is this a on off job , or a regular job, whats the size of your database?? what version of SQL are you using 🙂
October 15, 2008 at 5:43 pm
Thank you..
We using sql server 2000 and 2005.
The scenario is
1)backup the database in sql server 2000 (Server A Production)
2)copy the back file to sql server 2005 (Server B Production using for Repoting)
3)Restore the backup file into sql server 2005 (Server B Production for Reporting)
Need to Create a job which runs daily to refresh the data from server A to server B and the database size is 40GB
October 16, 2008 at 2:38 am
I see... sort of home-made replication, huh?
I assume you have already figured it our you would probably need two jobs... one on the source machine and one on the target one, isn't it?
What do you have so far?
_____________________________________
Pablo (Paul) Berzukov
Author of Understanding Database Administration available at Amazon and other bookstores.
Disclaimer: Advice is provided to the best of my knowledge but no implicit or explicit warranties are provided. Since the advisor explicitly encourages testing any and all suggestions on a test non-production environment advisor should not held liable or responsible for any actions taken based on the given advice.October 16, 2008 at 4:32 am
We have same process in place in Live environment... 🙂
As Server A is production server, it must be having his own maintenance plan for all maintenance activities including backup database. Server B, will have a job written in T-SQL to copy file and restore the backup file. So, start the job on Server B, once the job on Server A has finished. You have to analyse the backup time and accordingly start the job on Server B.
October 16, 2008 at 5:04 am
You can also combine step 2 and 3.
You then will get something like this
declare @lastbackupID char(10)
declare @backupname char(255)
-- Find the latest backup of [Production database]
set @lastbackupID = (select max(media_set_id) as 'backupset'
from [production server].[msdb].[dbo].backupset
where database_name = '[your database]' and type = 'D')
-- Match it with a physical filename if physical device name contains a driveletter
-- Substring is depending on the length of your databasename
set @backupname = '\\[your backup location]\' + (select substring(physical_device_name , 18, 99)
from [production server].[msdb].[dbo].backupmediafamily
where media_set_id = @lastbackupID)
-- First kill open connections to
use master
declare @KillString char(20),
@SpidValue char(3)
while (select top 1 spid from sysprocesses where dbid = db_id('[reporter database]')) is not NULL
begin
set @SpidValue = (select top 1 spid from sysprocesses where dbid = db_id('[reporter database]'))
set @KillString = 'Kill ' + @SpidValue
exec (@killString)
end
-- Start restoring it over existing reporter database
check on the right values
print @lastbackupID
print @backupname
RESTORE DATABASE
FROM DISK = @backupname
WITH
MOVE '[reporter data]' TO 'Your datafile location\[reporterdb_data.mdf]',
MOVE '[reporter log]' TO 'your logfile location \[reporterdb_log.ldf]',
replace
This script will restore the database bak file from the backup location.
You need to use an account with according privileges to read from the backup share and restore a database.
October 16, 2008 at 5:55 am
We do this for a few SQL Servers to keep backups of our databases on servers at our disaster recovery site. On live server, backup databases, Xcopy to other server, clear out folder of backups on live server. Then on second server have restore job run as needed.
October 16, 2008 at 7:14 am
SQLCMD can run scripts that include CONNECT commands to change server connections, so you could easily write one script that ran BACKUP on one server and RESTORE on another.
You could also create an Integration Services package to run the BACKUP and RESTORE commands on different servers.
Both options can be easily automated as a SQL Agent job or workstation scheduled task.
October 16, 2008 at 9:06 am
Hi Koltas
I used to have this situation earlier in my work , when we migrated, we migrated the Reporting Server to 2005 first, then we migrated the Production machines, First i had a database job that backups the database to the network folder and then an other Job on server B( report server) to restore this. Saying this it has some downtime, i copied nearly 35 GB and it took me 10 minutes downtime, when the database is been restoring, you can try this, if down time is fine for your organization
Cheers
October 16, 2008 at 5:55 pm
Thank you all... I got some idea now..
I will update you...
Viewing 10 posts - 1 through 9 (of 9 total)
You must be logged in to reply to this topic. Login to reply