Creating a clone of a SQL Server database via backup/restore

Question

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?

EDIT:

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' 

Answer 1

You need to declare a length for @backupName. If you leave the length off in your declaration off then it'll default to 1 character. That's why you're seeing a truncated path.

DECLARE @backupName NVARCHAR(250) = N'C:\....... 

You also need to fix your RESTORE. For example:

RESTORE DATABASE ClonedDb FROM DISK=@backupName WITH MOVE 'MyDB_Data' TO 'C:\SQLData\MyDB2.mdf', MOVE 'MyDB_Log' TO 'C:\SQLLogs\MyDB2.ldf'; 

You would need to replace the logical and physical file names as appropriate for your environment. The WITH MOVE directives are necessary since your source database will still be online and using the original physical files. If your source database was not online anymore then you could get away with what you had.