Server Fault is a question and answer site for system and network administrators. Join them; it only takes a minute:

Sign up
Here's how it works:
  1. Anybody can ask a question
  2. Anybody can answer
  3. The best answers are voted up and rise to the top

Hello i am new to sql server and i don't know any thing about backing up database using tsql i am trying to find out the procedure to do following but unsuccessful.

T-SQL query to schedule the back-up by using Transactional logs on every Monday at 12.00pm before shutting down the database and also mention a sample T-SQL query to recover the data by using Transactional log

share|improve this question
up vote 0 down vote accepted

I might get flamed by some SQL people, but since you are self described unfamiliar with SQL and tSQL. You can use SSMS' built-in Maintenance Plans. It is an easy graphical way to setup backup/restore tasks without knowing any code. You are able to choose which database, what type of backup and even the scheduling of the backup. I am still using them while I learn the proper way to script everything out in tSQL and use an SQL agent job.

SQL Maintenance plans may be dirty words, but for the novice they really help.

share|improve this answer
Maintenance Plans are indeed a good start! – Peter Schofield May 3 '11 at 19:01
Haha don't feel bad about the maintenance plans. As long as you pay attention to their special brand of issues they tend to be safe. – Sean Howat May 3 '11 at 19:30

If you are NOT familiar with BACKUPs in SQL Server then I strongly suggest you get some reading first [no sarcasm here] on this topic before going ahead with this task.

Understanding SQL Server Backups

SQL Server: Recovering from Disasters Using Backups

Why are you shutting down the database? Are you using SQL Server Express edition? Can you share your SELECT @@version information? Also you need to do Transaction Log backups only if the database is in full recovery model.

select name, recovery_model_desc from sys.databases

If you need help with the actual syntax for your needs then you need to share the data/log file structure a bit.

SELECT name AS 'FileName' , physical_name AS 'PhysicalName', size/128 AS 'TotalSizeinMB', size/128.0 - CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'AvailableSpaceInMB', CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0 AS 'ActualSpaceUsedInMB', (CAST(FILEPROPERTY(name, 'SpaceUsed') AS int)/128.0)/(size/128)*100. as '%SpaceUsed' FROM sys.database_files;

Some additional BACKUP & RESTORE commands from BOL.

If you need additional help, don't hesitate to ask but share clear details.

share|improve this answer

As you are new to SQL Server world using Maintenance plans is a good start which gives you the clear and consice method to task the required processses within your database server.

Refer to as a good start, also you can download SQL Server Books online from here which can be used as first hand resource to obtain the information.

share|improve this answer

Your Answer


By posting your answer, you agree to the privacy policy and terms of service.

Not the answer you're looking for? Browse other questions tagged or ask your own question.