How to do a SQL Restore using a script with the same quality of SQL Studio Restore

We use Microsoft SQL for our Restore and we use it direct, using the studio (right-cick database >> all tasks >> restore).

We have come with the task to Restore at random times multiple database.  We go about the standard SQL studio way, but we would like to know if there is a script we can use to Restore 5 databases at the same time.
rayluvsAsked:
Who is Participating?

[Product update] Infrastructure Analysis Tool is now available with Business Accounts.Learn More

x
I wear a lot of hats...

"The solutions and answers provided on Experts Exchange have been extremely helpful to me over the last few years. I wear a lot of hats - Developer, Database Administrator, Help Desk, etc., so I know a lot of things but not a lot about one thing. Experts Exchange gives me answers from people who do know a lot about one thing, in a easy to use platform." -Todd S.

jkofteCommented:
this is the same question, so i will copy and paste the answer. you do not need to give points for this.


this script backs up all the databases on sql server. to select one and backup only that db, write your where condition to "/* ADD WHERE HERE */".

DECLARE @name VARCHAR(50) -- database name  
DECLARE @path VARCHAR(256) -- path for backup files  
DECLARE @fileName VARCHAR(256) -- filename for backup  
DECLARE @fileDate VARCHAR(20) -- used for file name 

SET @path = 'C:\Backup\'  

SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112) 

DECLARE db_cursor CURSOR FOR  
SELECT name 
FROM master.dbo.sysdatabases /* ADD WHERE HERE */
WHERE name NOT IN ('master','model','msdb','tempdb')  

OPEN db_cursor   
FETCH NEXT FROM db_cursor INTO @name   

WHILE @@FETCH_STATUS = 0   
BEGIN   
       SET @fileName = @path + @name + '_' + @fileDate + '.BAK'  
       BACKUP DATABASE @name TO DISK = @fileName  

       FETCH NEXT FROM db_cursor INTO @name   
END   

CLOSE db_cursor   
DEALLOCATE db_cursor

Open in new window

0
rayluvsAuthor Commented:
we want a script to restore not backup; don't understand why refer to the backup script?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you can do it via T-SQL.
Just create the 2 procedures mentioned in the below link to do Backup and Restore for multiple databases.

http://www.sqlmag.com/article/tsql3/use-t-sql-to-back-up-and-restore-sql-server-user-databases

In order to restore multiple databases, we need to make sure that all the database backups are shared in the same path..
0

Experts Exchange Solution brought to you by

Your issues matter to us.

Facing a tech roadblock? Get the help and guidance you need from experienced professionals who care. Ask your question anytime, anywhere, with no hassle.

Start your 7-day free trial
Big Business Goals? Which KPIs Will Help You

The most successful MSPs rely on metrics – known as key performance indicators (KPIs) – for making informed decisions that help their businesses thrive, rather than just survive. This eBook provides an overview of the most important KPIs used by top MSPs.

EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
you can via SSMS "(right-cick database >> all tasks >> restore).
 generate restore script in the way -locations what do you need

for each of your 5 specific databases;
 
save each script
and create 5 (5 dbs ) sql agent jobs  with saved scripts:

or run all of them at same time ( as you asked) or 1 by one)
0
rayluvsAuthor Commented:
Thank you very much!!!! Worked Excellently!!
0
rayluvsAuthor Commented:
One last question.  Where should I place the database name when I want to restore to another database other the original db?
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Ramante,

Kindly let me know whether you have tried Eugenez's approach or my approach so that I can clarify your doubts accordingly.
My approach mentioned above will check for database backup and restore if the database is available in the same name automatically..
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
<One last question.  Where should I place the database name when I want to restore to another database other the original db? >

if it is from SSMS generated
do same for next db and check the mdf ldf files location (set with overwrite and you would nee to make sure to disconnect users
0
rayluvsAuthor Commented:
Sorry for the delay, lots of holidays here, but we're back.

We tried both option and both worked.  However, we would like to have the script run the restore on X number of DB; ion other words, multiple backup files.  The reason is we do backups of more than one DB and sometimes we would like to just run one script to restore all DBs.

On rrjegan17 option, we would like to control DB names and backup files name.

With EugeneZ option, we can workaround using specific locations and file names, but how can we run it with multiples restore? (see script attached)

Nevertheless, whichever can be run multiple restore in one script-run, is what we need.
RESTORE DATABASE [BPS] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\BPS' 
  WITH  FILE = 1,  MOVE N'BPS' TO N'C:\DBname\BPS.mdf',  MOVE N'BPS_log' TO N'C:\DBnameLOGS\BPS_log.ldf',
  NOUNLOAD,  REPLACE,  STATS = 10
GO

Open in new window

0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
inlude several restore scripts in 1 script and run it from sql sagint job step


for example if you have 3 DBs to be restored BPS1 BPS2 BPS3

---------

RESTORE DATABASE [BPS1] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\BPS'
  WITH  FILE = 1,  MOVE N'BPS' TO N'C:\DBname\BPS1.mdf',  MOVE N'BPS_log' TO N'C:\DBnameLOGS\BPS1_log.ldf',
  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE DATABASE [BPS2] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\BPS'
  WITH  FILE = 1,  MOVE N'BPS' TO N'C:\DBname\BPS2.mdf',  MOVE N'BPS_log' TO N'C:\DBnameLOGS\BPS2_log.ldf',
  NOUNLOAD,  REPLACE,  STATS = 10
GO
RESTORE DATABASE [BPS3] FROM  DISK = N'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\BPS'
  WITH  FILE = 1,  MOVE N'BPS' TO N'C:\DBname\BPS3.mdf',  MOVE N'BPS_log' TO N'C:\DBnameLOGS\BPS3_log.ldf',
  NOUNLOAD,  REPLACE,  STATS = 10
GO
0
rayluvsAuthor Commented:
oh ok... we though that this type of script would work. We'll try.
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
you can try to use some lookup table and do more complicated code
but Q why to make more complicated if you deal we 1-5 DBs
thus this simple approach could be the best for your quest
0
rayluvsAuthor Commented:
It worked just fine, just place one script after the other thanx.
0
rayluvsAuthor Commented:
Prior closing the question, we would like to know in rrjegan17 recommendation, if there is a way to restore X number of DB instead of all.
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you can do it..
While creating the procedure sp_backup_databases, just replace the below condition with the dbid of databases of which you need to backup and restore.

select name from sysdatabases where dbid > 6
0
rayluvsAuthor Commented:
You mean in order to restore specific DB, we have to modify the Backup SP to only backup the DBs we need to restore?  Modify the backup script to maybe something like this:

select name from sysdatabases where dbid in (x,x,x,x)
0
Raja Jegan RSQL Server DBA & Architect, EE Solution GuideCommented:
Yes, you got it correctly..
0
rayluvsAuthor Commented:
ok
0
EugeneZ ZhitomirskySQL SERVER EXPERTCommented:
restore "universal" script can be not "100%"
you need to know exact location of the DB files unless the backup was taken from db that has exact mdf. ldf files and locations : for example
  G:\Data\test.mdf
otherwice - you need to use some loopup tabe to check what withmove resore will be ..
------------------

you can try this solution :
http://social.msdn.microsoft.com/Forums/is/databasedesign/thread/a6ecf8e4-17ba-432a-ace5-ee6f7fa4097b
---again it is up to you
bulk restore is not so "popular" task-- > but can be done
0
rayluvsAuthor Commented:
understood.
0
rayluvsAuthor Commented:
Thanx
0
It's more than this solution.Get answers and train to solve all your tech problems - anytime, anywhere.Try it for free Edge Out The Competitionfor your dream job with proven skills and certifications.Get started today Stand Outas the employee with proven skills.Start learning today for free Move Your Career Forwardwith certification training in the latest technologies.Start your trial today
Microsoft SQL Server 2005

From novice to tech pro — start learning today.