During a side by side upgrade I needed to restore ~20 databases from another server. Whilst the process is extremely simple I couldn’t quite find the correct syntax to use.
In the example below the name of the database (shown in SQL Enterprise Manager / Management Studio) is Website_Lists and so it’s very easy to search and replace for this tag so that each database you import can be done using the same SQL commands. The script restores the database from a file backup, but restores it to an alternate location (creating the database at the same time). It then redirects the internal references to the database’s transaction logs to a new location consistent with the new disk layout. In this case the backup files all contained references to the E: drive which wasn’t present in the new server.
restore database “Website_Lists”
FROM DISK=’L:\DB Backups\DDWebsite_Lists.bak’
WITH MOVE ‘Website_Lists_DATA’ TO
‘J:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Website_Lists_DATA.mdf’,
MOVE ‘Website_Lists_LOG’ TO
‘K:\Microsoft SQL Server\MSSQL.1\MSSQL\Data\Website_Lists_LOG.ldf’;
Connect to the server using SQL Management Studio, right click on the server name and select New Query. Copy/paste the text above and alter as appropriate.