viernes, 27 de abril de 2012

Restore database from UNC

This T-SQL, allows you to add a Backup device, from remote directory (Don't forgett to give permissions to source backup folder).

First of all, add de remote backup as Device

use master
exec sp_addumpdevice 'disk','RemoteBackupDevice','\\yourRemotePath\backup.bak'
go


Then restore de database from the Device created.

restore database yourdatabasename from [RemoteBackupDevice] with file=1,
move N'DATA_FILE_NAME' to N'c:\Data\DATA.mdf'
move N'DATA_LOG_NAME' TO N'C:\LogData\LOG.LDF'
, NOUNLOAD, STATS=5
go


or restore the backup databse without creatting a device

restore database yourdatabasename from disk ='\\yourRemotePath\backup.bak' with recovery;


go


That's all.

martes, 24 de abril de 2012

SSMA Declare variable when Sp doesn't have migrated code

When for some reason, a SP code can't be migrated, and beteewn BEGIN and END it doesn't appear something, SSMA createa

declaration variable to prevent SP creation error.

For example:

 

Create procedure stp_Test_Migration_Code (@nombre varchar(50) OUTPUT) AS

BEGIN

select @nombre = db_name (tempdb_id(@@spid))


END

GO

 

This SP has to be migrated to SQL Server, but SSMA and SQL Server doesn't have tempdb_id(spid) "function", and it comment the line code.

Create procedure stp_Test_Migration_Code (@nombre varchar(50) OUTPUT) AS

BEGIN

/*

*SSMA error messages:

* S2SS0020: The identifier 'db_name(UNKNOWN)' was not converted

select @nombre = (NULL)


*/

END

GO

 

If we try to execute this, in SQL Server, shows an error,  because the SP needs to have something between BEGIN and END.

This is the reason that SSMA generate a line code declaration:

 

Create procedure stp_Test_Migration_Code (@nombre varchar(50) OUTPUT) AS

BEGIN

/*


*SSMA error messages:


* S2SS0020: The identifier 'db_name(UNKNOWN)' was not converted


select @nombre = (NULL)


*/


DECLARE @db_null_statement3 int


END

GO