1

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

pixelerator
  • 121
  • 4

3 Answers3

0

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.

http://technet.microsoft.com/en-us/library/ms186865.aspx

http://technet.microsoft.com/en-us/library/ms186858.aspx

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

Sankar Reddy
  • 1,374
  • 8
  • 8
0

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.

RateControl
  • 1,207
  • 9
  • 20
0

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 http://msdn.microsoft.com/en-us/library/ms187658.aspx as a good start, also you can download SQL Server Books online from http://www.microsoft.com/downloads/en/details.aspx?FamilyId=765433F7-0983-4D7A-B628-0A98145BCB97&displaylang=en here which can be used as first hand resource to obtain the information.

Satya SKJ
  • 39
  • 3