Take the 2-minute tour ×
Server Fault is a question and answer site for professional system and network administrators. It's 100% free, no registration required.

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'
share|improve this question
add comment

1 Answer

up vote 2 down vote accepted

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.

share|improve this answer
    
Thanks... The restore command is still failing (question edited) :( –  thecoop Jan 11 '12 at 15:42
    
Can you post your corrected RESTORE statement? Make sure you use the FROM DISK=... (you are missing DISK= in your original restore statement). –  squillman Jan 11 '12 at 15:51
    
ahh, missed the DISK=. Thanks :) –  thecoop Jan 11 '12 at 15:56
    
Most excellent :) –  squillman Jan 11 '12 at 15:56
add comment

Your Answer

 
discard

By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.