You can create a nightly backup job using Sql Server Management Studio using the SQL Agent that will backup the database to any place you choose, or you can do what a lot of other people do and use a program such as Backup Exec from Symantec that will also
do the same thing as well as backup other things such as IIS, Exchange servers, Files, etc...
1. Can i restore all the data scheme + Actual Data. Into some other instance of sql server same version ???( If Windows/ Hardsisk crashes i will have a new instance)
2. How can i chooose the location for my backup.
and how can i evoke Sql Agent Configuration Wizard?
A full backup will contain everything you need including schema etc... When you restore it from a full hard disk crash and if you have custom SQL user accounts you will need to reattach the orphaned users in the database you are restoring to the users inside
of the SQL server itself.
A program such as backup exec will take care of all this for you, but in the evening you need it its just a simple query:
A regular backup can be done to a tape drive or other media such as an external hard drive, or it can be done over the network to another shared file store.
To access the Sql Agent you will need to access it via Sql Srever Management Studio. Make sure its installed and running and then it will be displayed near the bottom of the list for that server and you can then right click on it and create new jobs.
Scott was here...
Marked as answer by Shanker_YPSS on Nov 05, 2012 08:54 PM
1> Using Cursor DECLARE @name VARCHAR(50) -- database name
DECLARE @path VARCHAR(256) -- path for backup files
DECLARE @fileName VARCHAR(256) -- filename for backup
DECLARE @fileDate VARCHAR(20) -- used for file name
-- specify database backup directory
SET @path = 'C:\Backup\'
-- specify filename format
SELECT @fileDate = CONVERT(VARCHAR(20),GETDATE(),112)
DECLARE db_cursor CURSOR FOR
SELECT name
FROM master.dbo.sysdatabases
WHERE name NOT IN ('master','model','msdb','tempdb') -- exclude these databases
OPEN db_cursor
FETCH NEXT FROM db_cursor INTO @name
WHILE @@FETCH_STATUS = 0
BEGIN
SET @fileName = @path + @name + '_' + @fileDate + '.BAK'
BACKUP DATABASE @name TO DISK = @fileName
FETCH NEXT FROM db_cursor INTO @name
END
CLOSE db_cursor
DEALLOCATE db_cursor
2> Using SQL agent jobs http://blogs.msdn.com/b/sqlagent/archive/2010/10/12/create-a-database-backup-job-using-sql-server-management-studio.aspx
Shanker_YPSS
Member
120 Points
220 Posts
Re : Sql Server Full Database Backup !!
Nov 05, 2012 08:30 PM|LINK
Hi
I have a Web Application Running on a single machine.
with sql server 2008 R2
I want to create a backup of my database every day in order to cover the risk of harddisk failure.
i can attach another harddisk for this Purpose.
Please throw light on the possible methods that i can use.
N_EvilScott
Star
8179 Points
1466 Posts
Re: Re : Sql Server Full Database Backup !!
Nov 05, 2012 08:32 PM|LINK
You can create a nightly backup job using Sql Server Management Studio using the SQL Agent that will backup the database to any place you choose, or you can do what a lot of other people do and use a program such as Backup Exec from Symantec that will also do the same thing as well as backup other things such as IIS, Exchange servers, Files, etc...
Shanker_YPSS
Member
120 Points
220 Posts
Re: Re : Sql Server Full Database Backup !!
Nov 05, 2012 08:37 PM|LINK
Thanks for your fast reply
Please throw some more light.
1. Can i restore all the data scheme + Actual Data. Into some other instance of sql server same version ???( If Windows/ Hardsisk crashes i will have a new instance)
2. How can i chooose the location for my backup.
and how can i evoke Sql Agent Configuration Wizard?
N_EvilScott
Star
8179 Points
1466 Posts
Re: Re : Sql Server Full Database Backup !!
Nov 05, 2012 08:52 PM|LINK
A full backup will contain everything you need including schema etc... When you restore it from a full hard disk crash and if you have custom SQL user accounts you will need to reattach the orphaned users in the database you are restoring to the users inside of the SQL server itself.
A program such as backup exec will take care of all this for you, but in the evening you need it its just a simple query:
A regular backup can be done to a tape drive or other media such as an external hard drive, or it can be done over the network to another shared file store.
To access the Sql Agent you will need to access it via Sql Srever Management Studio. Make sure its installed and running and then it will be displayed near the bottom of the list for that server and you can then right click on it and create new jobs.
Shanker_YPSS
Member
120 Points
220 Posts
Re: Re : Sql Server Full Database Backup !!
Nov 05, 2012 09:00 PM|LINK
Thanks I'll Be useing SQL AGENT.
devananddhag...
Member
222 Points
31 Posts
Re: Re : Sql Server Full Database Backup !!
Nov 05, 2012 09:16 PM|LINK
Deva
Please remember to click "Mark as Answer" on the post that helps you