October 2, 2012 at 10:46 pm
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
October 2, 2012 at 10:59 pm
Are you asking how to restore a database?
October 2, 2012 at 11:54 pm
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.
October 3, 2012 at 5:09 am
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
Viewing 4 posts - 1 through 3 (of 3 total)
You must be logged in to reply to this topic. Login to reply