So I'm trying to 'clone' a SQL Server database by making a backup of the template, then restoring it to the new name. However, there's either something I'm missing or something I don't understand about making backups. This is what I've got so far:
DECLARE @now DATETIME2(0) = GETDATE() -- so backup file will be in C:\backupdirectory\TemplateDb_20120111145230.bak DECLARE @backupName NVARCHAR = N'C:\backupdirectory\TemplateDb_' + CAST(DATEPART(year, @now) AS VARCHAR) + CAST(DATEPART(month, @now) AS VARCHAR) + CAST(DATEPART(day, @now) AS VARCHAR) + CAST(DATEPART(hour, @now) AS VARCHAR) + CAST(DATEPART(minute, @now) AS VARCHAR) + CAST(DATEPART(second, @now) AS VARCHAR) + '.bak' BACKUP DATABASE TemplateDb TO DISK = @backupName RESTORE DATABASE ClonedDb FROM @backupName
However, the backup file doesn't get created, and the restore command fails with
Backup device 'C' does not exist
Why is it trying to find a device named 'C', and not the full path? Where is my backup actually going to? What am I missing?
It's now creating the file as necessary, but the restore command is still complaining the backup device doesnt exist:
Backup device 'C:\backupdirectory\TemplateDb_2012111125632.bak' does not exist. To view existing backup devices, use the sys.backup_devices catalog view. To create a new backup device use either sp_addumpdevice or SQL Server Management Studio.
what should I add as the backup device? Do I need to create a specific backup device for each backup I make?
RESTORE DATABASE ClonedDb FROM @backupName WITH MOVE 'TemplateDb' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ClonedDb.mdf', MOVE 'TemplateDb_log' TO 'C:\Program Files\Microsoft SQL Server\MSSQL10_50.MSSQLSERVER\MSSQL\DATA\ClonedDb_log.ldf'