question

sunithalee143 avatar image
sunithalee143 asked

sql script automatic required

Hi i was asked to write a script for restoring 5 databases. i written like this. RESTORE DATABASE PAKIN FROM DISK='D:\PALIN_databases\backup20110415\PAKIN DB bak files\PAKIN.bak' WITH MOVE 'PAKIN_Data' TO 'D:\Microsoft SQl Server\MSSQL\Data\PAKIN_Data.mdf', MOVE 'PAKIN_Log' TO 'D:\Microsoft SQl Server\MSSQL\Data\PAKIN_log.ldf' RESTORE DATABASE MAKIN FROM DISK='D:\PAKIN_databases\backup20110415\PAKIN DB bak files\MAKIN.bak' WITH MOVE 'MAKIN_Data' TO 'D:\Microsoft SQl Server\MSSQL\Data\MAKIN_Data.mdf', MOVE 'MAKIN_Log' TO 'D:\Microsoft SQl Server\MSSQL\Data\MAKIN_log.ldf' RESTORE DATABASE LEO FROM DISK='D:\PAKIN_databases\backup20110415\PAKIN DB bak files\LEO.bak' WITH MOVE 'LEO' TO 'D:\Microsoft SQl Server\MSSQL\Data\LEO.mdf', MOVE 'LEOLog' TO 'D:\Microsoft SQl Server\MSSQL\Data\LEO_log.ldf' RESTORE DATABASE NEO FROM DISK='D:\PAKIN_databases\backup20110415\PAKIN DB bak files\NEO.bak' WITH MOVE 'NEO_Data' TO 'D:\Microsoft SQl Server\MSSQL\Data\NEO_Data.mdf', MOVE 'NEO_Log' TO 'D:\Microsoft SQl Server\MSSQL\Data\NEO_Log.ldf' this was executing fine and restoring good but my TL says it was taking manual execution and taking time i need automatic and the time should take less for restoring can any one help how to make it executed automatically please.
sql-server-2005backup-restore
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

1 Answer

·
WilliamD avatar image
WilliamD answered
To automate the restore, you can create a SQL Agent job (job scheduler for SQL Server). You can then set the job to run whenever and however often you want. To help speed up the restore, you need to take a look at having the backup on different physical storage than where you a restoring to. This allows there to be less i/o contention, as the backup storage reads the restore storage can write. Another option would be to make sure the SQL Server service account (Windows User running sqlserver.exe) has the local permission "Perform Volume Maintenance Tasks". This will allow the creation of the database data files to occur faster by activating a feature called "instant file initialisation", [read this blog post for more information][1]. [1]: http://sqlskills.com/blogs/Kimberly/post/Instant-Initialization-What-Why-and-How.aspx
6 comments
10 |1200

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.

You may also need to add another step to check if the database is already in use - and then either skip it or kill the active connections
1 Like 1 ·
I would suggest asking that as a separate question. There are too many things you can tweak to improve restore performance to address in a comment.
1 Like 1 ·
i am trying it in job schedular of sql server if it runs fine i will let u know thanks for the suggestion to create the job in SQL Agent
0 Likes 0 ·
yes it worked but its taking time as one of the database size is 60 GB its taking more time still the TL is asking to reduce the Restoration time.is there any possibility of reducing the restoration time apart frominstant initialization if there is please let me know thanks for your suggestion.
0 Likes 0 ·
How long is it taking, and what is the target time? If the goal is always "a little faster" you will never arrive.
0 Likes 0 ·
it was taking 40 min for that 60GB Database TL is asking it should be redused to atleast 20 min.
0 Likes 0 ·

Write an Answer

Hint: Notify or tag a user in this post by typing @username.

Up to 2 attachments (including images) can be used with a maximum of 512.0 KiB each and 1.0 MiB total.