SQL Clone
SQLServerCentral is supported by Redgate
 
Log in  ::  Register  ::  Not logged in
 
 
 


Auto backup and restore in remote SQL server 2000


Auto backup and restore in remote SQL server 2000

Author
Message
shohelr2003
shohelr2003
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 610
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
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2463 Visits: 2253
Are you asking how to restore a database?
shohelr2003
shohelr2003
Old Hand
Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)Old Hand (333 reputation)

Group: General Forum Members
Points: 333 Visits: 610
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.
Andrew G
Andrew G
SSCrazy
SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)SSCrazy (2.5K reputation)

Group: General Forum Members
Points: 2463 Visits: 2253
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
Go


Permissions

You can't post new topics.
You can't post topic replies.
You can't post new polls.
You can't post replies to polls.
You can't edit your own topics.
You can't delete your own topics.
You can't edit other topics.
You can't delete other topics.
You can't edit your own posts.
You can't edit other posts.
You can't delete your own posts.
You can't delete other posts.
You can't post events.
You can't edit your own events.
You can't edit other events.
You can't delete your own events.
You can't delete other events.
You can't send private messages.
You can't send emails.
You can read topics.
You can't vote in polls.
You can't upload attachments.
You can download attachments.
You can't post HTML code.
You can't edit HTML code.
You can't post IFCode.
You can't post JavaScript.
You can post emoticons.
You can't post or upload images.

Select a forum

































































































































































SQLServerCentral


Search