2

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'
thecoop
  • 287
  • 1
  • 2
  • 7

1 Answers1

3

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.

squillman
  • 37,883
  • 12
  • 92
  • 146