Click here to monitor SSC
SQLServerCentral is supported by Red Gate Software Ltd.
 
Log in  ::  Register  ::  Not logged in
 
 
 
        
Home       Members    Calendar    Who's On


Add to briefcase

Auto backup and restore in remote SQL server 2000 Expand / Collapse
Author
Message
Posted Tuesday, October 2, 2012 10:46 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
Hi Experts,

Say I have two Servers (ServerA and ServerB),

I have performed database backup from ServerA and Put the backup file (.bak) to Remote Server(ServerB). Say the file name is "db_backup.bak"

Now if I want to restore the backup file "db_backup.bak" on ServerB and the restore process should be started automatically.

So in this case, what should I have to perform, please help me.

Thanks in advance.

Regards,
Shohel
Post #1367397
Posted Tuesday, October 2, 2012 10:59 PM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 1,220, Visits: 1,691
Are you asking how to restore a database?
Post #1367402
Posted Tuesday, October 2, 2012 11:54 PM
SSC Veteran

SSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC VeteranSSC Veteran

Group: General Forum Members
Last Login: Sunday, August 3, 2014 4:04 AM
Points: 203, Visits: 500
Yes, I am asking how to restore database automatically.

1. Actually I am taking backup of database using Database Maintenance Plans in a remote pc's disk.

2. The backup schedule runs two times at a day.

3. Now I want to schedule auto restore the latest backup file in the remote pc's sql server.

4. After that I want to auto delete the backup files of 1 week.

5. I am using SQL Server 2000 Enterprise Edition in both of the servers.

Please help me.
Post #1367409
Posted Wednesday, October 3, 2012 5:09 AM


Ten Centuries

Ten CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen CenturiesTen Centuries

Group: General Forum Members
Last Login: Yesterday @ 6:14 PM
Points: 1,220, Visits: 1,691
Ok, forget using the maintenance plan. You could use a DTS package if you like using the GUI, or read through some options for SQL2000 here:
http://msdn.microsoft.com/en-us/library/aa213832%28v=sql.80%29.aspx

However, here's my opinion on how you should proceed:

You'll need to understand the basic syntax for backing up and restoring a database first. I'm assuming you dont know this.
Use SQL Books Online. The SQL2000 version is here: http://msdn.microsoft.com/en-us/library/aa299742%28v=sql.80%29.aspx
The documentation is horrible, but still usable. If you had SQL2005 you could at least generate a script from the GUI....

You'll need to understand the implications for the transaction log backup chain when you do a full backup if your databases are in full recovery mode. (ie. it breaks it)
This information is only in SQL2005 Books Online but applies to 2000: http://technet.microsoft.com/en-US/library/ms187048%28v=sql.90%29.aspx

Once you understand that doing a full backup randomly on a database in full recovery mode will stop anyone else restoring that database to a specific point in time in the case of emergency failure if they don't know where your backup is, you can then continue writing a T-SQL script.
Step a: Create a working BACKUP DATABASE script.
BACKUP DATABASE [MyOldDB] TO DISK = N'G:\MyOldDB_Full_20121003.bak' WITH INIT, FORMAT, STATS = 1

Step b: Copy the backup file manually to the other server and try a restore script.
RESTORE DATABASE [MyOldDBNew] FROM DISK = N'G:\MyOldDB_Full_20121003.bak' WITH RECOVERY, STATS = 1

If that works,
 DROP DATABASE [MyOldDBNew]

Now share the backup folder location, DO NOT allow the "Everyone" group access (unless you want your backup visible to the world), only share to the SQL Server Service account running the SQL Server Service on Server2. Or create a share location on Server2 that Server1 SQL Service Account can write to.
Try restore again from a UNC network path from Server2:
RESTORE DATABASE [MyOldDBNew] FROM DISK = N'\\server1\Gshare\MyOldDB_Full_20121003.bak' WITH RECOVERY, STATS = 1

If that works,
 DROP DATABASE [MyOldDBNew]

Step c: Create a SQL Agent Job, read how here, http://www.quackit.com/sql_server/tutorial/sql_server_agent_jobs.cfm
You'll need a job step to do the BACKUP from step a, and another to do the RESTORE from step b. You can create CmdExec tasks to copy the file around if the share folder thing doesn't work out. Also use oSQL.exe from Server1 to connect to Server2 to do the restore, something like
oSql.exe -Sserver2 -dmaster -E -Q"RESTORE DATABASE blah..."

oSQL reference: http://msdn.microsoft.com/en-us/library/aa214012%28v=sql.80%29.aspx
Step d: Make sure the orphaned SQL users are mapped back to logins on the Server2
Post #1367562
« Prev Topic | Next Topic »

Add to briefcase

Permissions Expand / Collapse