Practical Programming

If you have done any signficant amount of programming in SQL you will have learned, with time, that nearly everything you can do in SQL Manager/Studio is 100% scriptable. Taking advantage of this can be a huge help with automating tasks… but you don’t need to bury your head in SQL Syntax to do so. Take the time to check out the wizards in studio, the context menus, etc… and you will almost always see a button or option to “generate script”. Get used to taking advantage of this because it will save you a multitude of headaches!

Here is a quick tip for restoring a table from a backup in script:

You cannot restore a database while it has active connections… to get around this you will need to set the database up in single_user mode (which deactivates all other connections except for your own). You will also need appropriate privelages to execute the commands like drop, restore, sp_adduser, and so on… db_creator roles are a simple solution for setting access priveleges but make sure you adequately protect the account as it opens up your database to lots of malicious activity if it were to fall in the wrong hands.

Here is a example to get you started down the right track:

--disconnect any one else so you can do the restore
ALTER DATABASE {your database}
    SET SINGLE_USER
    WITH ROLLBACK IMMEDIATE

USE MASTER 

--delete the old database (you could overwrite it instead
---if you wanted, merge, etc.)
DROP DATABASE {your database}

--restore the database from the backup
RESTORE DATABASE [{your database}]
   FROM  DISK = N'{insert full path to ur backup file here}' WITH  FILE = 1,
   MOVE N'{your backupset}' TO N'{insert your path and db name here}.mdf',
   MOVE N'{your backupset}_log' TO N'{insert your path and db name here}.ldf',
   NOUNLOAD,
   RECOVERY,
   REPLACE,
   STATS = 10

--grant privileges as necessary
use {your database}
exec sp_adduser '{user account}', '{user name}', 'db_datareader'

--reset the connection mode back to multi_user (this is important)
ALTER DATABASE {your database}
    SET MULTI_USER
    WITH ROLLBACK IMMEDIATE

MSDN Ref:
   1. Restore (Transact-SQL)
   2. sp_addUser (Transact-SQL)
   3. Drop Table (Transact-SQL)
   4. Alter Database (Transact-SQL)

No related posts.

Quick Tips sql