Solved

script restore 100 db automatically

Posted on 2014-09-28
2
91 Views
Last Modified: 2014-09-29
Hello,

I search how to automaticaly restore 100 db with the same structure from db_001 to db_100

RESTORE DATABASE [db_001] FROM  DISK = N'\\share\backupsql$\db_001_20140928_031842.BAK' WITH  FILE = 1,  MOVE N'db_001_Data' TO N'd:\MSSQL\db_001_Data.mdf',  MOVE N'db_001_Log' TO N'd:\MSSQL\db_001_Log.ldf',  NOUNLOAD,  STATS = 5

GO

Thanks
0
Comment
Question by:bibi92
2 Comments
 
LVL 25

Expert Comment

by:Mohammed Khawaja
ID: 40348927
Best way to do is to create a database first and then restore the backup.  Below is a sample script:

USE master
GO
CREATE DATABASE Sales
ON
( NAME = Sales_dat,
   FILENAME = 'd:\MSSQL\db_100.mdf',
   SIZE = 20,
   FILEGROWTH = 10 )
LOG ON
( NAME = 'Sales_log',
   FILENAME = 'd:\MSSQL\db_100.ldf',
   SIZE = 20MB,
   FILEGROWTH = 10MB )
GO

RESTORE DATABASE [db_001] FROM  DISK = N'\\share\backupsql$\db_001_20140928_031842.BAK'
Go
0
 
LVL 47

Accepted Solution

by:
Vitor Montalvão earned 500 total points
ID: 40349549
I didn't test it but I think this script it's maybe what you are looking for:
DECLARE @NumOfRestores INT = 100
DECLARE @CurRestore INT = 1
DECLARE @RestoreCmd VARCHAR(4000)
DECLARE @NumFormat VARCHAR(3)

WHILE @CurRestore <= @NumOfRestores
BEGIN
	SET @NumFormat=RIGHT('000'+ CONVERT(VARCHAR,@CurRestore),3)
	SET @RestoreCmd = 'RESTORE DATABASE db_' + @NumFormat +
						' FROM  DISK = N''\\share\backupsql$\db_001_20140928_031842.BAK'' WITH  FILE = 1, MOVE N''db_' + @NumFormat +
						'_Data'' TO N''d:\MSSQL\db_' + @NumFormat + '_Data.mdf'', MOVE N''db_' + @NumFormat +
						'_Log'' TO N''d:\MSSQL\db_' + @NumFormat + '_Log.ldf'',  NOUNLOAD,  STATS = 5'
	EXEC @RestoreCmd
	SET @CurRestore=@CurRestore+1
END

Open in new window

0

Featured Post

Optimizing Cloud Backup for Low Bandwidth

With cloud storage prices going down a growing number of SMBs start to use it for backup storage. Unfortunately, business data volume rarely fits the average Internet speed. This article provides an overview of main Internet speed challenges and reveals backup best practices.

Question has a verified solution.

If you are experiencing a similar issue, please ask a related question

Nowadays, some of developer are too much worried about data. Who is using data, who is updating it etc. etc. Because, data is more costlier in term of money and information. So security of data is focusing concern in days. Lets' understand the Au…
Slowly Changing Dimension Transformation component in data task flow is very useful for us to manage and control how data changes in SSIS.
Familiarize people with the process of utilizing SQL Server functions from within Microsoft Access. Microsoft Access is a very powerful client/server development tool. One of the SQL Server objects that you can interact with from within Microsoft Ac…
Using examples as well as descriptions, and references to Books Online, show the different Recovery Models available in SQL Server and explain, as well as show how full, differential and transaction log backups are performed

810 members asked questions and received personalized solutions in the past 7 days.

Join the community of 500,000 technology professionals and ask your questions.

Join & Ask a Question