Restore database

• Guras

  SSCertifiable

  Points: 6518

  More actions

  June 24, 2015 at 8:08 am

  #398641

  I am on SQL 2012 standard version and I am writing a script to restore database from .bak files on a network.

  ALTER DATABASE DB1

  SET SINGLE_USER WITH

  ROLLBACK IMMEDIATE

  ----Restore Database

  RESTORE DATABASE DB1

  FROM DISK = 'N:\SQL\Backup\Daily\DB1_backup_2015_06_22_194002_0500494.bak'

  WITH REPLACE

  ALTER DATABASE db1 SET MULTI_USER

  GO

  Since I have to restore about 100 databases , I am planning to put the script in a cursor. However my problem is how can I get the bak file name dynamically .

• Steve Jones - SSC Editor

  SSC Guru

  Points: 736212

  More actions

  June 24, 2015 at 8:23 am

  #1807761

  Are these databases that were backed up from this instance? If so, then the msdb database has the names.

  If not, this is better done from PoSh, though there are scripts here on the site that will help you. Here are a few links:

  http://www.sqlservercentral.com/scripts/31995/

  http://www.sqlservercentral.com/scripts/SQL+Powershell+Script/101648/

  https://www.simple-talk.com/sql/backup-and-recovery/backup-and-restore-sql-server-with-the-sql-server-2012-powershell-cmdlets/