Not sure if there is anyway to do hots on sql server .From all I have
heard an EMC can do clones and fractures in order to implement a hot
backup or some kind of it in sql server.
Seemingly the DBA has to do something to
enable the fracture of the clone . Is anyone familiar with this ?
Your input is highly appreciated
MB
HiI was in the situation of cloning a SQL server database last night.Today, a dependent application was behaving a little erratically thoughmost things were fine after sorting out some ntfs permissions. I'verecovered this database from backup exec before from a hot-backup, thisis the first time I've attempted this procedure though.Could I check with the experts here as to whether what I did was OK?Been googling to see if it is really the right thing to do, haven'tcome up with anything definitive...Assume server SOURCE and DEST:install DEST to same specification, OS, SQL patch level as SOURCE. Samepaths where applicable.shut down SQL server on bothCopy all datafile folder (d:sqldata) which is the full databaseincluding MASTER etc all over to DEST.Shut SOURCE down.Rename DEST to SOURCE, disable SQL server starting, set networksettings to those of SOURCE and reboot.Wait for things to settle after reboot and name change, bring SQLdatabase up.Client application now connects, I can't see any problems, but therehave been some strange anomalies in satellite applications today. Allhave been bounced to see if it's just a change they needed to notice,to no avail.Was that an OK operation to have done, or is there something just aboutto bite me when I'm not looking?Be grateful for any advice, I spent today pulling a lot of hair out.Thanks in advance :)AW
View 3 Replies View RelatedI have a table "t_prod_cat" which contains hierarchical data which is used in production to present data.
CREATE TABLE [dbo].[t_prod_cat](
[cat_node_id] [bigint] IDENTITY(1,1) NOT NULL,
[advertiser_id] [bigint] NOT NULL,
[cat_hid] [hierarchyid] NULL,
[level] AS ([cat_hid].[GetLevel]()) PERSISTED,
CONSTRAINT [PK_t_prod_cat] PRIMARY KEY CLUSTERED
(
[cat_node_id] ASC
)WITH (PAD_INDEX = OFF, STATISTICS_NORECOMPUTE = OFF, IGNORE_DUP_KEY = OFF, ALLOW_ROW_LOCKS = ON, ALLOW_PAGE_LOCKS = ON) ON [PRIMARY]
) ON [PRIMARY]
In order not to impact the production website during the time an advertiser is editing (the editing might take much time and also mainly because at any time during the editing, the advertiser could cancel all the changes he did), I was thinking of transferring all the data linked to that advertiser to another table and let the advertiser apply any modifications up to the moment he will commit the changes.
Therefore, I would like to "CLONE" the hierarchy related to a certain advertiser_id to another table "t_prod_cat_work"
CREATE TABLE [dbo].[t_prod_cat_work](
[temp_cat_node_id] [bigint] NOT NULL,
[temp_cat_hid] [hierarchyid] NOT NULL,
[advertiser_id] [bigint] NOT NULL
) ON [PRIMARY]
What can be the easiest way to clone all the hierarchical data (multi-levels) from 't_prod_cat' to 't_prod_cat_work' for a certain advertiser_id ?
I have this requirement where some store procedures from a "seed" database need to be replicated to another database (on demand, so replication is not suppose to be use in this scenario).
I know it can be achieved by exporting the store procedures and then execute that at the B database but I want something a bit more automatic since it can be a large number of sprocs. I am trying something like this (still in dev):
SET NOCOUNT ON;
--
SELECT ROW_NUMBER() OVER(ORDER BY definition) seq, definition base
into #sprocs
FROM databaseA.[sys].[procedures] p
INNER JOIN databaseA.sys.sql_modules m ON p.object_id = m.object_id
[Code] ....
But I am sure there are way better ways to accomplish that...
I am having three table tblTest,tblTestQuestion,tblAnswers
Each test can have multiple question and each Question can have multiple answers.
Now I am already having records in database. I wants to create clone copy of existing test except testdetails in tblTest because the test will be unique, and then insert questions and answers into their respective tables.
I was trying to create SP but stuck.
Please find below tables structure
[code="
SET ANSI_NULLS ON
GO
SET QUOTED_IDENTIFIER ON
GO
CREATE TABLE [dbo].[tblAnswer](
[Code].....
Ive successfully cloned an SSRS VM Server within a scale out deployment to REPLACE a current physical machine. This wasn't too difficult.
The issue im having now is in creating a new SQL 2014 Environment with 3 SSRS nodes. Ive taken the time to build one up. Changed all my config files, installed all components etc & tweaks needed within my company...
I have now hit a brick wall though. Ive cloned this first server twice to give me 3 nodes. This is a new environment so the issue I have is that the rsreportserver.config files all now have the same InstallationID. I dont have any existing nodes to overwrite these files with. So when I attach the nodes to the database they will update the keys table with the same InstallationID as the first node and none will work.
Any way to force the SSRS setup to re-generate an installation ID - by repair maybe? Or is this something I can manually just make up?
hellooI'm building an intranet accounting system, at the begining of each year the administrator must be able to create a new year DBfrom the applicationIs this possible knowing that im using SQL Server 2000, .net framework 1.1 Thank you in advanced
View 3 Replies View RelatedHelloo
Can I backup DB programatically, and then restore it with different name also programatically??
meaning:
I have db2006, can I backup this db then restore it as db2007 programatically???using a stored procedure for example????
So by doing this the user will be using db2007 @ the begininng of the year and so on each year
I'm using sql2000
Please I need your help
Thank you
Howdy,
Is the any easy way to clone a database programatically?
I've searched the web but did not come up with anything.
At the moment I create a new blank database (based on the model database)
and create tables, indexes, etc. via asp. (The databases are always exactly
the same)
It seems to me that it would be a better option to
create one database with tables, indexes, etc and copy
it to a new database (on the same server).
One of the options I'm considering would be to create a database,
fill it with tables and indexes, etc. and then detach it.
Whenever I need a new database all I would have to do is use the
file system object to copy and rename the mdf file and then reattach the
new mdf file.
I realise that I could also make the changes to the model
database so that all new databases have the same structure
but that would be my last resort.
Is there any easier way to do this? A stored procedure perhaps?
You're opinions and advice would be welcomed.
How do I go about creating a cloned copy of an existing database on the same server? I basically want to set up a test copy of a production database, but let it reside on the same server with a slightly different name, prefixed with 'Testing_".
I typically just use Studio Express Manager and not scripts to do admin tasks. Here's what I tried that didn't work so well:
* Had full backups of production db
* Detached production database I wanted to clone
* From within Win Explorer, copy the .mdf and .ldf files within same data folder and gave them a new name, prefixing with "Testing_"
* Re-attached prodution db
* Attached "Testing_..." database
Sql Express seems confused at this point with new database I attached. It references the full path of the database I copied from (c:program filesmicrosoft sql servermssql.1data...". I rename the datbase, removing full path and prefixing with "Testing_".
* Using Sql authentication, changed the default database for the Login account to the new cloned copy of database.
* Did some query level data sanitizing to the cloned copy of the db. I am positive I performed this on the correct db.
* Was able to connect/authenticate to the cloned copy through a custom VB app on my dev machine, and all looked well.
* Tried to connect to the cloned db from another machine and couldn't. Went back to my dev machine and noticed the original production db looked hosed -- there were no tree nodes available to it! Was still able to connect to the cloned "Testing_" db copy, but not able to connect/authenticate anymore to the production copy.
* Detached cloned copy of "Testing_" db and moved it out of the data folder.
* Detached production db. Got an error msg indicating operation failure of some kind, but the database was gone from the tree.
* Reattached the production db but couldn't hit it from my custom vb app. Re-did the login and user accounts used by the app and was able to hit it. However, the data sanitizing I had done earlier in the cloned copy clearly was visibile in the production db!
* Had to restore production db from backup. Had problems authenticating from app, and recreated User account before the app was to connect.
Q1: What I am doing wrong? Sql Express was obviously as confused as me somewhere along the line.
Q2: Is specifying a default database used by a Login account necessary for a VB app like mine. I think it was something I discovered in the past, however, this would seemingly present a problem whenever I want my app to hit a different database, established through a connection string.
TIA...
Rick
hai
I need to copy an instance of SqlDataReader type. I perform a query and stores its details to an instance of SqlDataReader type. Now before i read it, i want to make a clone as i got to use it again just after sometime. I found something like ICloneable.Clone. But i couldnot find much on the internet as to how to use it. Can anybody give me a sample as to how to go about this
I have a table in which I have defined 2 columns. First column is called 'name' and second is called 'number'.It looks like sample bellow:
Name       |   Number       |
---------------------------------
John         |    2                 |
Jessica      |   3                 |
Jack          |   2                 |
etc...
I need to make a query that will have the following results:
Name  |
----------
John    |
John    |
Jessica |
Jessica |
Jessica |
Jack     |
Jack    |
John for 2 rows, Jessica for three rows and Jack for two rows. Is it possible only with loops or is there in SQL a duplicate/clone function...?
The space allocated to the Log in question is 180 GB. During this time period I was running TLog backups every 5 minutes, yet the log continued to chew through to 80 GB used, even after the process was complete and a final TLog backup had been taken. It continued to stay very large until the Full backup was complete -- or something else that I'm unaware of completed. Like every other DBA I typically take a TLog backup to shrink the log, but what appeared to be the case here was the Full completed and it released the used log space. All said, will Transaction Log backups not free up the log during Full backups?
View 3 Replies View RelatedI developed a intranet web application that stores about 1000+ new entries into a SQL Server database over the course of about a half hour. This runs 24/7 so its a very busy application. SQL Server is running on a server with a RAID 5 array which runs very good but I want to better guard against data loss. If I run a backup at 8:00 AM and the roof falls on the server crushing and destroying it at 5:00 PM, we just lost thousands of records that were made between 8am and 5pm that cannot be replaced.
I was thinking of adding a second server so when an entry is made it would be stored on both servers. The second server would be located at a different location so if 1 gets crushed we could just pick up where we left off and I wouldn't have to update my resume.....
Does this sound like a good idea or could anyone recommend a better solution?
Thanks in advance.
Is it possible to backup a remote SQL Server database to my local system? It is not working for me as I'm getting error as given below
Cannot open backup device 'path'. Device error or device offline
Any idea??
Does anyone know how to get SQL Server to see a network drive for backups? Even when trying to create a backup device on a network drive using UNC naming, SQL Server seems to have a problem with accepting it. If you browse for a place to put the device, only the local drive is accessible.
Please help. Thanks. John
I am working on a new SQL Server 7.0 system and have gotten to the issue of backing up and restoring the database. I am fairly new to SQL Server 7.0 and I am not sure how good the built in backup/resore is in SQL Server 7.0. The other optioin I have is Seagate Backup Exec which is currently being used for network backups.
Does anyone have any advide/information on this?
Thanks in advance.
Mike
Can anyone tell me some advantages/disadvantages to using ARCServe for
backups vs SQL Server 6.5 backup?
Thanks for the help!
Toni
Our e-business group will be running SQL Server as a backend and C#, ASP.NET, VB.NET application on the front end. Besides my enternal backups of user data on the database. What would you suggest for the Network team to backup and how offen. This is for productions. I usally get a full drive dump on the Dev network daily appended,and weekly archived. Any suggestion for production.
View 2 Replies View RelatedI have a job scheduled to backup a production database every night at 10pm. It will run fine for a number of days and then fail with the error:
Msg 3201, Sev 16: Cannot open backup device 'PCSLogisticsDB'. Device error or device off-line. See the SQL Server error log for more details. [SQLSTATE 42000] Msg 3013, Sev 16: BACKUP DATABASE is terminating abnormally. [SQLSTATE 42000] Msg 0, Sev 0: Associated statement is not prepared [SQLSTATE HY007] The backup set is valid. [SQLSTATE 01000]
When I go back and run it manually it works fine and then will run on it's own for a few more days. I've checked and File System backups aren't running at the time the backup kicks off. Has anyone experienced this?
I'm looking for an opinion on an architecture I'm thinking of rolling out.I have a HA cluster with 3 nodes:
Primary -- Main SQL Server
Failover -- Passive node, only used in DR scenario
Reporting -- Read Intent only queries directed here, SSRS.
I am pondering with the idea of balancing the backups out between 2 nodes as follow:
Primary: Full backup weekly, daily incremental.
Reporting: Log backups every 5 minutes.
I have tested that I can indeed restore the logs from the secondary node using the Full and Diff from the other server, no worries there.My concern is what will happen to the logs on the primary and fail-over server. Will they just grow, grow and grow?
Can SQL server handle High Availability or does it have to go downoccasionally for a cold backup? Does it support incremental backups?
View 4 Replies View RelatedI'm trying to create a backup set which maintains only a fixed number ofdays. As such, I've got the following script:BACKUP DATABASE [mydb] to [mydb Backup Set] WITH NOINIT, NOUNLOAD,NAME=N'My Database Backup', NOSKIP, STATS=10, NOFORMAT,RETAINDAYS=5DECLARE @i INTselect @i=position from msdb.backupset where database name='mydb'and type!='F' amd backup set id=(select max(backupset set id) frommsdb.backupset where database name='mydb')RESTORE VERIFYONLY FROM [mydb Backup Set] WITH FILE=@iThis script was created automatically, but I added the RETAINDAYS bit. Ionly want the past five days of backups available, but when I check thelist, I've got backups dating back to December 2003! The older backupsare correctly being marked as expired, but they are still on the list!How can I purge the list to only have the last five days of backups?Thanks in advanceJohnny
View 1 Replies View RelatedCan anyone please confirm if SQL Server 7 allows differential or indeed transaction logs backups? I noticed in Enterprise Manager that while trying to configure a backup for the Master database, only complete was highlighted and the other types including file and filegroup was greyed out....
View 2 Replies View Relatedwe are looking into a new tape drive and they say it is NT compatable and that its built around NT. My question the backup that is build in to sql 7.0 is it built around NT. I would think so since its a microsoft product
View 1 Replies View RelatedI am looking for the best method to backup SQL Server databases. Currently we are
running a dump database statement to disk and backing up the files to tape through Arcserve.
One problem that I am having is the statement to dump the database. I would like to retain
the dump for at least three days and be able to restore the database from any one of those
three days. My current statement is:
"DUMP DATABASE CHOISDAT TO DISK=`D:BACKUPCHOIS.BAK` WITH
NOUNLOAD , STATS = 10, INIT , RETAINDAYS = 3, NOSKIP"
but, every other day I receive the message from SQL executive:
"Can`t open dump device `D:BACKUPCHOIS.BAK`, device error or device off line.
Please consult the SQL Server error log for more details. (Message 3201)"
What am I doing wrong? Any suggestions?
P.S.
Is there anyway to tell the Maintenance Wizard to delete the backups. I tried using the wizard but
the backup files still remain on the disk and I have to delete them every week.
Hi,
How can i make sure that i dont have any data loss in the event of DB crash.
I take daily full database backup dump to the disk and every 4 hrs transaction log dump.
What shoud be the backup strategy to get 100 % database from the backup/ to get in point in time receovery.
Best regards,
Madhu
For some reason my backups (Transaction log and full backups) have started failing. When looking at the job histoy for the step that failed it reports:
*****(0.011 MB/sec). [SQLSTATE 01000] (Message 3014) Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed. [SQLSTATE 42000] (Error 3023) Backup or restore operation terminating abnormally. [SQLSTATE 42000] (Error 3013). NOTE: The step was retried the requested number of times (3) without succeeding. The step failed.*****
I have tried running the SQL for the step manually and I get:
Backup, CHECKALLOC, bulk copy, SELECT INTO, and file manipulation (such as CREATE FILE) operations on a database must be serialized. Reissue the statement after the current backup, CHECKALLOC, or file manipulation operation is completed.
Server: Msg 3013, Level 16, State 1, Line 36
Backup or restore operation terminating abnormally.
The step is only determining what day of the week it is and putting it in to a '@bkupday' variable then: "BACKUP LOG [model] TO @bkupday WITH NOINIT , NOUNLOAD , NAME = N'Model Log Backup', NOSKIP , STATS = 10, DESCRIPTION = N'Model log backup', NOFORMAT" for each database and log.
This had been working fine, but for some reason has now started playing up. (trying to workout wether this ties in with when I started merge replication of one of the DBs)
Any one got any ideas as to what might be causing this as it's happened for a couple of nights now?
Cheerz.
Has anyone had experience using ‘VERITAS Backup Exec For Windows Servers Agent for MS SQL Server’ to run backup jobs? If so, what are the pros and cons?
I have always preferred using SQL Server Agent (not the maintenance plans) to run backup jobs and don’t know why anyone would want to use 3rd party software to do something SQL Server Agent does perfectly. Any advice?
Hello,
If I understand correctly I cannot do table & databse backups and restores or exports & imports as with Enterprise Manager in SQL 2000 because I have the lowest level version of the Studio Express Manager. Is that right?
Can anyone tell me what I need to do to regain that funtionality? It will be used for managing local (on my pc) and remote databases.
If it involves downloading and installing a different Studio version:
1. which one would that be?
2. is there a cost?
3. do I need to UNINSTALL my current version?
4. if so, how do I preserve my existing local databases (since there's no backup capability)
Many thanks in advance for any advice out there!
Richard
Hi there,
I am looking to be able to backup our database automatically on a daily basis. It is for a SQL Server 2005 Express Database. Does the Express edition have automated backups or does it have to be carried out manually?
I'm not sure of which steps to take.
Could someone please advise
Thanks
Hey guys,
I'm wondering how most people manage very very large backups. What is the best approach to breaking up the backup files if you're restricted to a drive size (450gig in my case). I unix, you can pipe the backup to gzip and split, I'm not sure how the same thing could be accomplished in windows.
Thanks,
-Kilka
Have a SQL 2014 install and cannot for the life of me get the maintenance plan to remove old backups. I've tried everything. Rights to the folder where the backups are stored are adequate, extension set in the clean up task is as it should be, etc. Log shows the job ran successfully. Running the command manually shows successful completion, but backups are still not removed.
View 9 Replies View Related