>>I need to "move" a bunch of databases from one computer to another. I have created backup of all databases to an external drive. That is, the external drive now has several .BAK files. Do I need to create the databases on the target computer before I can restore from the .BAK files? Or can I restore and create database from .BAK in one step?
>>
>>TIA
>
>In order to write a script that would restore all databases from a certain folder - without hard-coding each database name - I would need to scan all files with extension *.BAK. How can I create a cursor and scan all files from a folder into the cursor (using T-SQL)?
>Or is there a better way to approach the task?
Not quite sure you can use variables of any kind in the restore script. At least, it was impossible nine years ago on SQL 2000 when I last tried it.
You may have a template in Fox where you'd merge the filenames and logical names as literals, but then I really don't know if you'd know these names from the bak files themselves. You may need to execute a query to get the database names on the first computer... Perhaps if you had created the backups programmatically, or had some kind of naming convention so you'd know the database's logical name and filename from the name of the .bak file, or if you had all three in some metadata list (even a text file would do).
IOW, if you have less than a dozen, it's not worth trying to automate the task. Just create a script to restore one, then edit it to change the names. Mind you, you need absolute paths of each file.
Here's the script I came up with back then, which I'm editing for each case (but then I have just a few databases each year):
use master
IF EXISTS (SELECT name FROM master.dbo.sysdatabases WHERE name = N'dbName')
DROP DATABASE [dbName]
create database dbName on
(name = 'dbName',
filename=N'x:\path\to\new\db\dbName.mdf')
log on (name = 'dbName_log',
filename=N'x:\path\to\new\db\dbName.ldf')
restore filelistonly
from disk='y:\path\to\backup\dbname_backup.bak'
restore database [dbName]
from disk='y:\path\to\backup\dbname_backup.bak'
with replace,
move 'dbName' to 'x:\path\to\new\db\dbName.mdf',
move 'dbName_log' to 'x:\path\to\new\db\dbName.ldf'
The first two commands may not be necessary nowadays, but I keep them just in case. I'm not really keen on knowing what each does and why.