CODEWITHSUNDEEP
sqlsql-servert-sql
Shine
Shine

Reputation: 1423

Error with the procedure written to Back up and restore the DB

I have written the below SP and getting below error. Could any one please help me finding the error.

create procedure CopyDB(@DBName varchar(100),@Newname varchar(100))
as
DECLARE @FileName AS nvarchar(4000)
set @FileName = 'D:\'+@DBName+'.bak'
/*Backup the database*/
BACKUP DATABASE @DBName
TO DISK = @FileName
WITH CHECKSUM, INIT;
/*to restore it with new name*/
RESTORE database @Newname
FROM DISK = @FileName
with 
MOVE @DBName TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\'+@Newname+'.mdf',
MOVE @DBName TO 'C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\'+@Newname+'_log.LDF',
CHECKSUM;

Error message is :

Msg 102, Level 15, State 1, Procedure CopyDB, Line 13 Incorrect syntax near '+'.

Upvotes: 2

Views: 1728

Answers (2)

Igor Borisenko
Igor Borisenko

Reputation: 3866

Try this

CREATE PROCEDURE CopyDB(@DBName varchar(100),@Newname varchar(100))
as
DECLARE @FileName AS nvarchar(4000)
DECLARE @varPathMdf varchar(200),@varPathLdf varchar(200)
SET @varPathMdf='C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\'+@Newname+'.mdf'
SET @varPathLdf='C:\Program Files\Microsoft SQL Server\MSSQL10.SQLEXPRESS\MSSQL\DATA\'+@Newname+'_log.LDF'
SET @FileName = 'D:\'+@DBName+'.bak'
/*Backup the database*/
BACKUP DATABASE @DBName
TO DISK = @FileName
WITH CHECKSUM, INIT;
/*to restore it with new name*/
RESTORE database @Newname
FROM DISK = @FileName
with 
MOVE @DBName TO @varPathMdf,
MOVE @DBName TO @varPathLdf,
CHECKSUM;

Autogenerated Script for restoring of my database:

RESTORE DATABASE [1] FILE = N'Analytics_Data' 
FROM  DISK = N'E:\DatabaseBackups\analytics.bak' 
WITH  FILE = 1,  
MOVE N'Analytics_Data' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\1.mdf',  
MOVE N'Analytics_Log' TO N'C:\Program Files\Microsoft SQL Server\MSSQL10_50.SQLEXPRESS\MSSQL\DATA\1_0.ldf',  
NOUNLOAD,  STATS = 10
GO

You can see that @DBName in my case have _Data or _Log in the end. I think you need to do te same or generate the script automatically.

enter image description here enter image description here

Upvotes: 2

Christian Specht
Christian Specht

Reputation: 36441

The error in your script is in the MOVE clause.
You have to use the logical names of your database files (one MOVE for each logical name) instead of @DBName every time.

You can get the logical file names by running the following query on your database:

select name, filename from sysfiles

name is the logical name of the database file.
filename is where it's on the disk right now.

By default, SQL Server databases have only two database files (the database and the log), but there can be more (the database can be split in several files).
The logical names are often like this:

  • DatabaseName or DatabaseName_Data for the database file
  • DatabaseName_Log for the log file

...but unfortunately you can't rely on that, because you can give the files any name you want.

If you really want to script this in a general way and you have more than one database, it's the easiest way to make sure that all databases have the same number of database files and that the logical names all match the same pattern (like DatabaseName_Data and DatabaseName_Log, for example).
If you don't do this, you have to get the number of database files and their logical names on the fly, which will make the SP much more complex.

Upvotes: 3

Related Questions