MSSQL Avoid Restoring State That Never End

In my previous post I write about mirroring and I include an T-SQL, but the problem when applying this for copy the database, it showing Restoring state and nothing we could do with it.

So this is the tips.

1. For Backup the database;

alter database DB1 set recovery full
backup database DB1 to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1_DB' with format

backup log DB1 to disk = 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1_LOG' with no_truncate,init

2. copy the backup file to other place

3. Restoring

RESTORE FILELISTONLY
from disk ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1'

restore database DB1
from disk ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1'
WITH REPLACE,NORECOVERY,
move 'DB1' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB1.mdf',
move 'DB1_log' to 'C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Data\DB1.ldf';

restore log DB1 from disk ='C:\Program Files\Microsoft SQL Server\MSSQL.1\MSSQL\Backup\DB1_log' with recovery

The different is only on the restore the log file, if we make with norecovery then it will show restoring state and if the reverse you could use the database.