April 14, 2010 at 2:29 am
Dear All,
I was expecting the same question would be posted for me.... I was thinking in the past about the same...
Anyone, please suggest me or provide me any information. Maintenance plans can be created but restoring for automation.
UAT server : Database should be backed up to E:\UATBkp\AMBD.bak
DEV server : The backup file (from UAT E:\UATBkp\AMBD.bak) should be copied to E:\UATBkp on
devserver on restore. This task should be automated to run everyday early hrs 6am.
Please suggest me to plan the same, backup on UAT, file copy to DEV, restore the file with overwrite and this should be automated everyday.
Is there any way to backup to shared location "\\ABMD\UATBkp\AMBD.bak" and restore to ASMD with overwrite option? any scripts or queries. How can we achive this daily. The DB should be available daily on DEV as to test the same everyday.
Fast help is apprecaited.
- Win.
Cheers,
- Win.
" Have a great day "
April 14, 2010 at 5:06 am
- Win. (4/14/2010)
UAT server : Database should be backed up to E:\UATBkp\AMBD.bak
Read about Backup Database from BOL. You'll get help on how to backup a database.
DEV server : The backup file (from UAT E:\UATBkp\AMBD.bak) should be copied to E:\UATBkp on
devserver on restore. This task should be automated to run everyday early hrs 6am.
if xp_cmdshell is enabled, you can use the dos command COPY to copy the file to destination shared folder. You can include the same in a job step and schedule it to run at 6 am.
Is there any way to backup to shared location "\\ABMD\UATBkp\AMBD.bak"
yes, you can backup directy on a shared location provided the shared location is accessible to the service account.
and restore to ASMD with overwrite option? any scripts or queries.
restore with overwrite is possible. Use Replace clause of Backup statement to achieve this.
How can we achive this daily.
create a job for this.
April 14, 2010 at 5:45 am
Thanks Pradeep Singh,
I tried working with network folders and backing up the database to NETWORK path. It was succesfully backed up.
Here now am facing to restore the same backup file with different name. Please look into the query below and please correct me if am wrong.
BACKUP DATABASE [AMBD] TO
DISK = N'\\AMBD\DBBackups\AMBD.bak'
WITH NOFORMAT, NOINIT, NAME = N'AMBD-Full Database Backup',
SKIP, NOREWIND, NOUNLOAD, STATS = 10
It succeeded..
_______________________________________________________________________________
RESTORE DATABASE [AMBD_NEW]
FROM DISK = N'\\AMBD\DBBackups\AMBD.bak'
WITH FILE = 1,
MOVE N'AMBD_new.mdf' TO N'E:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AMBD_NEW.mdf',
MOVE N'AMBD_new_log.ldf' TO N'E:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AMBD_NEW_log.ldf',
NOUNLOAD, STATS = 10
but it failed.... what could be the issue..
Can you please guide me, and also into restore db with overwrite option....
- Win.
Cheers,
- Win.
" Have a great day "
April 14, 2010 at 5:51 am
- Win. (4/14/2010)
Thanks Pradeep Singh,RESTORE DATABASE [AMBD_NEW]
FROM DISK = N'\\AMBD\DBBackups\AMBD.bak'
WITH FILE = 1,
MOVE N'AMBD_new.mdf' TO N'E:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AMBD_NEW.mdf',
MOVE N'AMBD_new_log.ldf' TO N'E:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AMBD_NEW_log.ldf',
NOUNLOAD, STATS = 10
but it failed.... what could be the issue..
Can you please guide me, and also into restore db with overwrite option....
- Win.
RESTORE DATABASE [AMBD_NEW]
FROM DISK = N'\\AMBD\DBBackups\AMBD.bak'
WITH FILE = 1,
MOVE N'AMBD_new.mdf' TO N'E:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AMBD_NEW.mdf',
MOVE N'AMBD_new_log.ldf' TO N'E:\Data\Microsoft SQL Server\MSSQL.1\MSSQL\DATA\AMBD_NEW_log.ldf',
NOUNLOAD, STATS = 10, REPLACE
I added the replace clause at the end. If you can provide the error u're facing, it'd be easier to diagnose.
April 14, 2010 at 7:05 am
Resolved the Issues...
Now am able to Backup to network and also able to restore from network path + overwrite..
Thanks.
Cheers,
- Win.
" Have a great day "
Viewing 5 posts - 1 through 4 (of 4 total)
You must be logged in to reply to this topic. Login to reply