Auto backup and restore in remote SQL server 2000

SQLServerCentral is supported by Red Gate Software Ltd.

Popular Topics

Home

Members

Calendar

Who's On

Home » SQL Server 7,2000 » Backups » Auto backup and restore in remote SQL server...

Auto backup and restore in remote SQL server 2000

Rate Topic

Display Mode

Topic Options

Author

Message

shohelr2003

Posted Tuesday, October 02, 2012 10:46 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 124, Visits: 324

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

foxxo

Posted Tuesday, October 02, 2012 10:59 PM

Say Hey Kid

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:07 PM
Points: 693, Visits: 1,027

Are you asking how to restore a database?

Post #1367402

shohelr2003

Posted Tuesday, October 02, 2012 11:54 PM

SSC-Enthusiastic

Group: General Forum Members
Last Login: Today @ 5:32 AM
Points: 124, Visits: 324

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

foxxo

Posted Wednesday, October 03, 2012 5:09 AM

Say Hey Kid

Group: General Forum Members
Last Login: Sunday, May 12, 2013 11:07 PM
Points: 693, Visits: 1,027

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 »

Permissions