When a TSQL script creates a temporary table, it is useful in developing mode to drop the table if it exists.
The following script does the job:
IF OBJECT_ID( N'tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable
When a TSQL script creates a temporary table, it is useful in developing mode to drop the table if it exists.
The following script does the job:
IF OBJECT_ID( N'tempdb..#MyTempTable') IS NOT NULL
DROP TABLE #MyTempTable
The task of restoring a SQL Server database with a TSQL script is as simple as executing the following script:
RESTORE DATABASE [MyDatabase] FROM DISK = N'C:\Backup\MyDatabase.bak' WITH FILE = 1, MOVE N'MyDataLogicalName' TO N'D:\DatabasesData\MyDatabase.mdf', MOVE N'MyLogLogicalName' TO N'D:\DatabasesLog\MyDatabase_log.ldf',
The only problem with the previous script is that if there are any open connections to the database the following error is generated:
Exclusive access could not be obtained because the database is in use.
To fix the issue one solution is to put the database in single user mode before the backup.
-- Put the database in single user Mode ALTER DATABASE MyDatabase SET SINGLE_USER WITH ROLLBACK IMMEDIATE
-- Restore the Database RESTORE DATABASE MyDatabase FROM DISK ...
-- Put the database back in multiuser mode ALTER DATABASE MyDatabase SET MULTI_USER GOIf this script fails, execute the last statement to put the data base back in multiuser mode.