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.
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
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
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
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.
